how to make fast update in a huge table
360792Mar 17 2004 — edited Mar 23 2004Hi,
i have a performance problem while handling some huge tables... can anyone please suggest me what should i do for make it fast or where i am doing mistake to configur this table.
*******************************
UPDATE reading
SET camount_oifc = 123
WHERE area_code = 10
AND cons_code = 125
AND ryear = 2004
AND rmonth = 2
AND adtl = 'N';
*******************************
the 'reading' table has more then 12 million records. this table basically a child table of another one that has almost 3.1 million records. In a process i need to execute above query for every parent record that mean the above query will run 3.1 million number of times. it is taking almost 11 hours that is not good techniquely and not acceptable for user as well.
the structure of the table and primary key elements and indexes created on this table are written below.
**************STRUCTURE
CREATE TABLE Reading (
Rmonth NUMBER(2) NOT NULL,
Ryear NUMBER(4) NOT NULL,
Area_Code NUMBER(3) NOT NULL,
Cons_Code NUMBER(5) NOT NULL,
Type_Code VARCHAR2(3) NULL,
Meterno VARCHAR2(10) NULL,
Adtl VARCHAR2(1) NOT NULL,
cp_code VARCHAR2(4) NULL,
UCode VARCHAR2(1) NULL,
RDate DATE NULL,
reader_code VARCHAR2(4) NULL,
MReading NUMBER(10) NULL,
BAmount NUMBER(13,3) NULL,
Quar VARCHAR2(1) NULL,
BType VARCHAR2(3) NULL,
CAmount NUMBER(13,3) NULL,
CAmount_OIFC NUMBER(13,3) NULL,
LMReading NUMBER(10) NULL,
LBAmount NUMBER(13,3) NULL,
LDate DATE NULL,
LBType VARCHAR2(3) NULL,
Consum NUMBER(14) NULL,
Curr_Arr NUMBER(15,3) NULL,
LBMonth NUMBER(2) NULL,
nomonth NUMBER(4) NULL,
Oifc_Arr NUMBER(15,3) NULL,
PR_Nov_Arr NUMBER(15,3) NULL,
Onec_Old_Arr NUMBER(15,3) NULL,
InvDate DATE NULL,
MergeDate DATE NULL,
Userid VARCHAR2(15) NULL,
CrtDate DATE NULL,
MergeDate DATE NULL
)
TABLESPACE mnb_reading
STORAGE(INITIAL 250M NEXT 100M PCTINCREASE 0);
**************************************
**************PRIMARY KEY
ALTER TABLE Reading
ADD (CONSTRAINT pk_reading PRIMARY KEY (Area_Code, Cons_Code, Ryear, Rmonth, Adtl)
USING INDEX TABLESPACE mnb_reading STORAGE(INITIAL 100M NEXT 100M PCTINCREASE 0));
************************************
**************INDEXES
CREATE INDEX reading_mont ON reading(ryear, rmonth, area_code, cons_code)
TABLESPACE mnb_reading STORAGE(INITIAL 200M NEXT 100M PCTINCREASE 0);
CREATE INDEX reading_cons ON reading(area_code, cons_code)
TABLESPACE mnb_reading STORAGE(INITIAL 200M NEXT 100M PCTINCREASE 0);
******************************