Migs Isip Migs Isip - 1 month ago 9
SQL Question

Best Syntax for Bulk Collect

i was wondering if there's a better or preferred way to write Bulk Collects.
I've seen different approaches and some are in the below sample Code:

set serveroutput on;
declare

cursor ext_data_file is
select *
from PER_ALL_PEOPLE_F;

TYPE type_emp_rec_ext IS TABLE OF ext_data_file%ROWTYPE index by pls_integer;

l_emp_tab_ext_raw type_emp_rec_ext;
l_emp_tab_ext_clean type_emp_rec_ext;
l_start number;
l_count number := 0;
l_count2 number := 0;

begin

/* Regular Cursor Fetch */

l_start := DBMS_UTILITY.get_time;

FOR cur_rec IN ext_data_file LOOP
l_count2 := l_count2 + 1;
END LOOP;

DBMS_OUTPUT.put_line('Regular (' || l_count2 || ' rows): ' || (DBMS_UTILITY.get_time - l_start) ||' ms');

/* Bulk Collect 1 */

l_start := DBMS_UTILITY.get_time;

open ext_data_file;
fetch ext_data_file
bulk collect
into l_emp_tab_ext_raw;
close ext_data_file;

DBMS_OUTPUT.put_line('Bulk 1 (' || l_emp_tab_ext_raw.count || ' rows): ' || (DBMS_UTILITY.get_time - l_start) ||' ms');

/* Bulk Collect 2 */

l_start := DBMS_UTILITY.get_time;

SELECT *
BULK COLLECT INTO
l_emp_tab_ext_clean
FROM PER_ALL_PEOPLE_F;

DBMS_OUTPUT.put_line('Bulk 2 (' || l_emp_tab_ext_raw.count || ' rows): ' || (DBMS_UTILITY.get_time - l_start) ||' ms');

/* Bulk Collect 3 */

l_start := DBMS_UTILITY.get_time;

OPEN ext_data_file;
LOOP
FETCH ext_data_file
BULK COLLECT INTO l_emp_tab_ext_raw LIMIT 1000;
l_count := l_emp_tab_ext_raw.count + l_count;
EXIT WHEN l_emp_tab_ext_raw.count = 0;
END LOOP;
CLOSE ext_data_file;

DBMS_OUTPUT.put_line('Bulk 3 (' || l_count || ' rows): ' || (DBMS_UTILITY.get_time - l_start) ||' ms');

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
end;


In Terms of Performance, i was surprised to see a difference. the Regular Fetch was faster than the Bulk Collects!

Regular (1202666 rows): 4174 ms
Bulk 1 (1202666 rows): 6369 ms
Bulk 2 (1202666 rows): 7204 ms
Bulk 3 (1202666 rows): 4380 ms


Here are my Observations:

Bulk Collect 1 : Commonly used when using an Explicit Cursor and has no LIMIT Clause. Also when the cursor Involves numerous Joined Tables.
Bulk Collect 2 : Commonly used when using a single table or an Implicit Cursor and has no LIMIT Clause.
Bulk Collect 3 : Commonly used when using an Explicit Cursor and has a LIMIT Clause.


Kindly confirm if above observations are correct.

And my Questions are:

1. Why is the Regular Fetch Faster?
2. Which of these Bulk Collects are the Best Syntax to Use in Terms of Overall Performance and Memory Consumption)


I have always been using Syntax 1, but i reckon the best is Syntax 3.

Database Details:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


Thanks!

Answer

Why it is faster without bulk collect? Because you're doing no operations on data fetched from cursor. Normally one use bulk collect when updating/inserting data to process it in batches no row by row. Try to execute in your loops inserting those rows to another table and you'll see the difference.

Which way is better? It depends on your requirements. For me the most sense make solution with limit. Why should I bulk collect if I'm going to process whole set of data as in 1 and 2? It probably means that I was able to process data in one massive statement. In case of using limit you can optimise batches size to obtain best performance of processing data. Also limit is good for finding compromise between speed of inserting and time of locking table where you're inserting/updating.