Vinayak Tripathi

• Dec 14th, 2005

 

Bulk Binds (BULK COLLECT , FORALL ) are a PL/SQL technique where, instead of multiple individual SELECT, INSERT, UPDATE or DELETE statements are executed to retrieve from, or store data in, at table, all of the operations are carried out at once, in bulk.

This avoids the context-switching you get when the PL/SQL engine has to pass over to the SQL engine, then back to the PL/SQL engine, and so on, when you individually access rows one at a time. To do bulk binds with Insert, Update and Delete statements, you enclose the SQL statement within a PL/SQL FORALL statement.

To do bulk binds with Select  statements, you include the Bulk Collect INTO a collection clause in the SELECT Statement instead of using Simply into .

Collections, BULK COLLECT and FORALL are the new features in Oracle 8i, 9i and 10g PL/SQL that can really make a different to you PL/SQL performance

Ashok kr. Choubey

• Mar 4th, 2006

 

Hi ,

Bulk Binding is used for avoiding the context switching between the sql engine and pl/sql engine. If we use simple For loop in pl/sql block it will do context switching between sql and pl/sql engine for each row processing that degrades the performance of pl/sql bloack.

So that for avoiding the context switching betn two engine we user FORALL keyword by using the collection pl/sql tables for DML. forall is pl/sql keyword.

It will provides good result and performance increase.

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 7th, 2008

 

Generally to process large number of records in pl/sql we use cursors . But cursors process records sequentially. It increases number of context switches between SQL and PL/SQL there by hampers performance. We may over come this problem using bulk binding.

FOR ALL and BULK COLLECT together are called bulk binding.

In Bulk binding instead of handling  records sequentially  the operations are carried out at once in bulk.  There by Bulk binding improves performance by minimizing no of context switches between SQL and PL/SQL.

dayakark

• Feb 20th, 2017

 

Whenever, we are submitting PL/SQL blocks into Oracle server always SQL statements are executed. Through SQL engine and also procedural statements are executed. Through Procedural statement executor. This procedural statement executor is available in PL/SQL engine, whenever we are using large amount of loading through SQL, PL/SQL statements always Oracle server executes these statements separately through these engines. This type of execution methodology always content switching execution methodology degrades performance of the application. To overcome this problem, Oracle introduced "bulk bind" process using collections, i.e. in this method oracle server executes all SQL statements at a time.

Bulk Collect:
Whenever we are using this clause automatically. Oracle server selecting date wise and store it into collections. Bulk collect clause used in

1. Select…INTO…clause
2. Cursor fetch statement
3. DML returning clauses

Rajan

• Mar 17th, 2017

 

Within a PL SQL code if there is any DML statement, it involves context switching. Every time there is a context switch, the cost is more. To handle this we have a functionality called bulk binding were all the DML are bind together and sent to SQL engine at once, which reduces the contact switching enormously thus improving cost/performance of the code.

Nand Kumar Sahu

• Jul 13th, 2017

 

Bulk binding is very useful in performance tunning scenarios. BULK COLLECT and FORALL are tow main key of bulk binding. in bulk binding any DML operation is performed in a single execute so context swiching is not going to happening.