Re: Outcome of my research on OCP.

simon wrote:

>>True to a point. But we all know cursor loops exist ... but that does
>>not mean you should use them. The same thing goes for unique indexes.
>>There is no valid reason I can think of, in Oracle, to ever create a
>>unique index.
>>
>>Addendum: And yes I am going to intentionally wait for someone to ask
>>me why even though this has been covered in this group many times before.

> 
> 
> Yes, may I ask why CURSOR LOOP (I assume you mean CURSOR FOR LOOP in the
> PLSQL stored proc) should not be used.  I am new to this group and missed
> many old posts - and I think it doesnt hurt to discuss the same issue again
> as there are always newcomers learning Oracle from scratch.

Because they are far less efficient than bulk binding. There is no valid reason for making 100 separate trips to the database for 1 record each when you can collect all 100 records at once. Same thing goes for using FORALL to perform DML.

Demos of bulk binding can be found in Morgan's Library at www.psoug.org

Expect to see, for the simple addition of one line of code, performance improvements of 25 - 1000%.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)