Hi All, 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_clean.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. Why 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!
Message was edited by: Migs_Isip