|
|
|
|
| Re: Heavy Update ( Update on Millions of Rows ) [message #110612 is a reply to message #110607] |
Wed, 09 March 2005 01:04  |
win_vj
Messages: 16
Registered: March 2005
Location: India
|
Junior Member |
|
|
Hi,
I have 2 tables on Production CASE and CASE_AUDIT. Table CASE has 13 Million unique Case_IDs.
The ralationship between Case and Case_Audit is based on Case_ID. Case_Audit has 46 Million records and Case_ID in Case_Audit table has a reference from Case table.
I have added a new field say (NCOL VARCHAR(90)) in Case_Audit table and I will make it NOT NULL after updating that field.
Now i want to update that field by a Space eg. ' '. If i fire a normal Update "Update Case_Audit set NCOL=' ';" , It takes around 48 hrs. During the testing of any Solution, I don't want my ROWID to be changed because some of our Reports are based on ROWID. I have followed some approaches, Which r following.
1. ------------------------Bulk Update----------------
drop Sequence CASE_SEQ;
drop table TEMP_TABLE;
create sequence CASE_SEQ Minvalue 1 Maxvalue 999999999999999999 Start With 1 Increment By 1 Cache 100;
create table TEMP_TABLE NOLOGGING PARALLEL 4 as select Case_SEQ.Nextval Seq_No,CASE_ID from CASE;
Declare
TYPE RC_CASE_TYPE IS TABLE OF CASE.CASE_ID%TYPE;
v_CASE RC_CASE_TYPE;
CURSOR c IS SELECT CASE_ID FROM TEMP_TABLE WHERE SEQ_NO >= 1 and SEQ_NO <= 1000000;
BEGIN
OPEN c;
FETCH c BULK COLLECT INTO v_CASE;
FORALL i IN 1 .. v_CASE.count()
update CASE_AUDIT set NCOL=' ' where CASE_ID=v_CASE(i);
COMMIT;
CLOSE c;
END;
/
2. -----------------Parallel Update------------------ alter table CASE_AUDIT parallel 4;
alter session enable parallel dml ;
alter session enable parallel query ;
update /*+ parallel(a,4) */ CASE_AUDIT set NCOL = ' ';
alter session disable parallel dml ;
alter session disable parallel query ;
alter table CASE_AUDIT noparallel ;
/
The Performance of Bulk Update is very bad But Parallel Update has produced some good results. But i want some excellent solution from u. Thanks a Ton!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
