Bulk Collect loop and Cursor Loop

35 pts.
Tags:
BULK COLLECT statement
Cursor
Cursor Loop
loop
Oracle
Why there is such a huge performace difference between Collection loop and a cursor Loop? Why collection populated using Bulk collect is way faster? Detailed explaination will be helpful. Thanks
ASKED: December 29, 2008  9:58 AM
UPDATED: January 12, 2009  5:32 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

“PL/SQL sends SQL statements such as DML and queries to the SQL engine for execution, and SQL returns the results to PL/SQL. You can minimize the performance overhead of this communication between PL/SQL and SQL by using the PL/SQL features that are known collectively as bulk SQL.

The FORALL statement sends INSERT, UPDATE, or DELETE statements <i><b>in batches, rather than one at a time</b></i>. The BULK COLLECT clause <b><i>brings back batches of results from SQL</i></b>. If the DML statement affects four or more database rows, bulk SQL can improve performance considerably.

Bulk SQL <b><i>uses PL/SQL collections to pass large amounts of data back and forth in single operations</i></b>. This process is called bulk binding. <b><i>If the collection has n elements, bulk binding uses a single operation to perform the equivalent of n SELECT INTO, INSERT, UPDATE, or DELETE statements. A query that uses bulk binding can return any number of rows, without requiring a FETCH statement for each one</i></b>.”

Ref: <a href=”http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/tuning.htm#insertedID0″>Tuning PL/SQL Applications for Performance</a>

Hope this helps.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following