pl/sql bulk collect statement in oracle database by manish sharma

Introduction To PL/SQL Bulk Collect

A well structured query, written today could save you from catastrophic events in the future. Query performance is something we all seek, but very few truly find it. Learning small concepts could help you in gaining experience which could lead to a better query writing skill. Today in this blog you are going to learn one of those small concepts that is the “Bulk Collect”.

Bulk collect is all about reducing context switches and improving the performance of the query. Thus in order to understand what is bulk collect we have to first learn what is Context Switching?

What is Context Switching?

Whenever you write a PL/SQL block or say a PL/SQL program and execute it, the PL/SQL runtime engine starts processing it line by line. This engine processes all the PL/SQL statements by itself but it passes all the SQL statements which you have coded into that PL/SQL block to the SQL runtime engine. Those SQL statements will then get processed separately by the SQL engine. Once it is done processing them the SQL engine then returns the result back to the PL/SQL engine. So that a combined result can be produced by the latter. This to and fro hopping of control is called context switching.

bulk collect context switching in oracle database by manish sharma

How does context switching affect the query performance?

Context switching has a direct impact on the performance of the query. The higher the hopping of controls the greater will be the overhead which in turn will degrade the performance. This means that lesser the context switching better will be the query performance.

Now you must be thinking “Can’t we do something about this?” Can we reduce those control transitions? Is there anyway using which we can reduce the context switches? The answer to all those questions is yes, we do have an option which can help us. That option is Bulk Collect clause.

What is Bulk Collect Clause?

Bulk collect clause compresses multiple switches into a single context switch and increases the efficiency and performance of a PL/SQL program.

Bulk collect clause reduces multiple control hopping by collecting all the SQL statement calls from PL/SQL program and sending them to SQL Engine in just one go and vice versa.

Where can we use Bulk Collect clause?

Bulk collect clause can be used with SELECT-INTO, FETCH-INTO and RETURN-INTO clauses.

With the help of Bulk Collect Statement we can SELECT, INSERT, UPDATE or DELETE large data sets from database objects such as Tables or Views.

What is Bulk Data Processing?

The process of fetching batches of data from PL/SQL runtime engine to SQL engine and vice versa is called Bulk Data Processing.

How many bulk data processing statements do we have?

We have one bulk data processing clause which is Bulk Collect and one bulk data processing statement which is FORALL in Oracle Database.

I’ve heard that bulk collect clause uses both implicit as well as explicit cursors?

Yes, you heard it right. We can use Bulk collect clause either inside a SQL Statement or with FETCH statement. When we use bulk collect clause with SQL Statement i.e. SELECT INTO it uses implicit cursor. Whereas if we use bulk collect clause with FETCH statement it uses explicit cursor.

This was a quick introduction to first PL/SQL bulk data processing clause which is BULK COLLECT. We will learn about the second bulk data processing statement once we are done with the first one. Meanwhile do make sure to subscribe to our YouTube channel because in the next tutorial we will learn how we can improve the efficiency of a SQL Statement using a Bulk Collect clause.

Thanks and have a great day!