(no subject)

From: Anne Yu <AYu_at_sos.state.tx.us>
Date: Mon, 25 Jun 2001 07:35:34 -0700
Message-ID: <F001.003366DD.20010625071644@fatcity.com>

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: AYu_at_sos.state.tx.us

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: ListGuru_at_fatcity.com (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). Received on Mon Jun 25 2001 - 09:35:34 CDT