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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: QUESTION ON RESTRICTING BULK COLLECT

RE: QUESTION ON RESTRICTING BULK COLLECT

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Mon, 24 Sep 2001 07:56:33 -0700
Message-ID: <F001.003963DA.20010924074524@fatcity.com>

Rajan,

For what it's worth I have never been able to get bulk collect to work on a large number of records restricting with recno.  It was so painfully slow.  I use it to bring small lookup tables into memory but I don't use it for cursor-driven programs, like data loads.

I've found that speed is OK (600,000 recs/hr) without bulk collect on the driving cursor, but using FORALL commits. 

But if your question is, how can you pull the records in order with bulk collect, can't you do it with a subquery?

CURSOR c1 is
SELECT * FROM
( select * from table_a
order by col1);

OPEN c1;
fetch c1 bulk collect into ...   limit rows;

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
954-935-4117

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

From:   Sakthi , Raj [SMTP:rajan_sakthi_at_yahoo.com]
Sent:   Monday, September 24, 2001 10:40 AM
To:     Multiple recipients of list ORACLE-L
Subject:        QUESTION ON RESTRICTING BULK COLLECT

Hi Folks,
I know this has been discussed before.  As usual I inherited a procedure which runs like a 'wool in the oil bath'.It deals with 10 Million row table. I was planning on using 'Bulk collect' feature. I knew bulk collect runs 'amok' if you don't restrict the number of rows you process. In my case rownumber may not work because I need to bring in all the rows in sequesnce of say 10000 in one bulk run so that I could process all 10 Mil. I would greatly appreciate any ideas and suggestions.
TIA.
BTW I am on 8.1.6.3

Regards,
RS



Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com
--

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

Author: Sakthi , Raj
  INET: rajan_sakthi_at_yahoo.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 Sep 24 2001 - 09:56:33 CDT

Original text of this message

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