Hey List , I am using the below script updating 4.3 millions rows to a
table. This table has 1500 bytes per record but actually the avgrow is 156
bytes. I did a test on NT, with same table (no index). It takes 54 minutes
to load. However, the same load, same table (no index), it takes 16 hours
to load. There is no chained_rows. What will make Oracle behavior this
way? Any idears?
Thanks,
****************************************************************************
**************
create or replace PROCEDURE SOS_PROC_PARTY_NUMBER IS
CURSOR PARTYNUM IS
SELECT ROWID,ORIGINAL_FILING_NUMBER
FROM test_ufp
order by ORIGINAL_filing_number;
FNUM NUMBER;
NNUM NUMBER DEFAULT 0;
RID VARCHAR2(20);
COUNTER NUMBER DEFAULT 0;
dcounter number default 0;
BEGIN
dbms_output.put_line('Start of party Number '||to_char(sysdate,'dd-mon-yyyy
hh:m
i'));
OPEN PARTYNUM;
--commit;
--set transaction use rollback segment rb_temp1;
--generate party number sequentially for each filing number, but starting at
one
for each filing number
LOOP
FETCH PARTYNUM INTO RID,FNUM;
EXIT WHEN PARTYNUM%NOTFOUND;
IF NNUM = FNUM THEN
COUNTER := COUNTER + 1;
ELSE
COUNTER := 1;
END IF;
UPDATE test_ufp SET PARTY_NUMBER = COUNTER WHERE ROWID=RID;
NNUM := FNUM;
dcounter := dcounter + 1;
if dcounter = 10000 then
commit;
set transaction use rollback segment rb_temp2;
dcounter := 0;
end if;
END LOOP;
COMMIT;
CLOSE PARTYNUM;
dbms_output.put_line('End of Party Number '||to_char(sysdate,'dd-mon-yyyy
hh:mi'
));
END;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Anne Yu
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
