Bulk Collects

Bulk Collects aren’t new but often times they are overlooked in providing efficiency PL/SQL or SQL.  It’s been well published that row by row is slow by slow.   Steve Feuerstein’s High-Performance PL/SQL webinar (click here) is just one publication example taunting the benefits of bulk collects.

I’m not offering anything new on this subject, but an example of an update statement written with a nested sub-query that ran for over 45 minutes before killed. I had the developer re-write it using bulk collect. The end result 3 minutes and 7 seconds.

Original query:

update table1 a set a.col2 = (select b.col_id from
temp_table2 b where a.id = b.id and col2 is not null)
where a.id in (select id from temp_table2 t
where t.col2 is not null) and a.col2 is not null;

Bulk Collect version:

DECLARE
CURSOR rec_cur IS
SELECT id,col2
FROM temp_table2;

TYPE num_tab_t IS TABLE OF NUMBER(38);
TYPE vc2_tab_t IS TABLE OF VARCHAR2(4000);

pk_tab NUM_TAB_T;
fk_tab NUM_TAB_T;
fill_tab VC2_TAB_T;
BEGIN
OPEN rec_cur;
LOOP
FETCH rec_cur BULK COLLECT INTO pk_tab, fk_tab;
EXIT WHEN pk_tab.COUNT() = 0;

FORALL i IN pk_tab.FIRST .. pk_tab.LAST
UPDATE table1
SET col2 = fk_tab(i)
WHERE id = pk_tab(i);
END LOOP;
CLOSE rec_cur;
END;
/

 

 

Advertisements

Tagged:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: