Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> (no subject)

(no subject)

From: Mohan, Ross <MohanR_at_STARS-SMI.com>
Date: Mon, 25 Jun 2001 08:29:39 -0700
Message-ID: <F001.00336936.20010625080648@fatcity.com>

Anne,

sorry, but.....Not clear and not enough info to answer completely.

tell more...you'll get better answers....

-----Original Message-----

Sent: Monday, June 25, 2001 11:17 AM
To: Multiple recipients of list ORACLE-L

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mohan, Ross
  INET: MohanR_at_STARS-SMI.com
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 - 10:29:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US