Wednesday, May 2, 2012

Oracle Bulk Collect & PL/SQL Collection: Ultimate Way to Speedup Fetching Cursors

PL/SQL should be used only when the work cannot be performed using SQL as SQL engine is faster than PL/SQL engine. Cursors reside at the heart of PL/SQL and it is one of the most eminent reason of slow performance of PL/SQL. But most of the time you can speedup the cursor fetching for large dataset using Bulk Collect.

This option enables you to retrieve multiple rows of data in a single request. The retrieved data is then populated into a collection variables. This query runs significantly faster than if it were done without the bulk collect. The operations on collection variables are in memory operations and thus much much faster. There are three types of PL/SQL collection variables - Nested Tables, VArrays and Associative Arrays.

Here I will show you an example of bulk collect using nested table to speed up fetching. In the sample code, I will use sys_refcursor instead of declaring any explicit cursor. It is a week type cursor available from Oracle 9i. There is also another predefined cursor from Oracle 9i which is refcursor_pkg.strong ref_cursor. Here is the PL/SQL code:

declare


TYPE type_base_rec IS RECORD (cx NUMBER, event_date DATE,
balance NUMBER);
--instead of creating any type,
--I use record and it will be available only within PL/SQL block
TYPE type_base_tbl IS TABLE OF type_base_rec;
ref_cur_base SYS_REFCURSOR;

begin
OPEN ref_cur_base FOR 'select c1,c2,c3 from Tab';
FETCH ref_cur_base BULK COLLECT INTO base_data;
CLOSE ref_cur_base;
end;
/

You can use limit option to limit the number of rows to be fetched. Like -

FETCH ref_cur_base BULK COLLECT INTO base_data limit 5000;

Now, you can use the collection methods on the collection variables. Most of the time, you need count, First, last, trim, extend, delete or indexing like base_data(i). A common example where bulk collect can speed up your operation is writing files from database.