Re: bulk collect in pl/sql

Andrew wrote:

>I wrote a program that reads a table with a million plus rows and
>writes to another table. I understand that use SQL is the fatest way
>to do this. However, I need to keep track what records fail and
>implement some logic. So, I have to use pl/sql to do this. I tried to
>use bulk collect within cursor to improve performance (maybe set a
>limit to 100 rows a time). However, I was told by an expert not to do
>this because this database need to be up 24 by 7. Is that right? I did
>a quick check in the manual and I could not find any restrictions like
>that. I only saw the restrictions like 'run the program in the server
>side not the client side', etc.
>
>Did I miss something in the manual??? I thought it'll only reduce
>burden for Oracle datebase engine. Thanks.
>
>

The only thing you missed is that your "expert" should probably never be allowed in the kitchen where
there are sharp objects. The "advice" is absolute total and complete nonsense.

That said stick with your original solution. 100 rows at a time is no different from 100,000 rows at a time. But that doesn't mean you are stuck. You didn't say why records fail. The answer to that question might provide a
key for substantially improving the situation.

Suppose you run a test and determine that rows fail because columns 2,5, and 9 must be not null and column 3
sometimes violates a foreign key. Just rewrite your SQL statement to something like:

SELECT *
FROM table
WHERE col2 is not null
AND col5 is not null
AND col9 is not null
AND col3 IN (

   SELECT col
   FROM fk_table);

Though exists might be faster this is easy to demo the concept.

Or alternatively mark those bad records by reversing 180 degrees and getting those records that do violate the data integrity rules.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)