 |
|
Chunking Bulk
Collections Using
the LIMIT Clause
Oracle Tips by Burleson Consulting
|
The following Tip is from the
outstanding book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by
Dr. Tim Hall, Oracle ACE of the year, 2006:
PL/SQL collections are essentially arrays in
memory, so massive collections can have a detrimental effect on system
performance due to the amount of memory they require. In some
situations, it may be necessary to split the data being processed into
chunks to make the code more memory-friendly. This
“chunking” can be achieved using the LIMIT clause of the BULK COLLECT
syntax.
The bulk_collect_limit.sql script uses the
LIMIT clause to split the collection into chunks of 10,000; processing
each chunk in turn. Notice the use of the explicit cursor for
this operation.
bulk_collect_limit.sql
SET
SERVEROUTPUT ON
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
l_tab
t_bulk_collect_test_tab;
CURSOR c_data IS
SELECT *
FROM bulk_collect_test;
BEGIN
OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 10000;
-- Process contents of collection here.
DBMS_OUTPUT.put_line(l_tab.count || ‘ rows’);
EXIT WHEN c_data%NOTFOUND;
END LOOP
CLOSE c_data;
END;
/
For the purpose of this test, the
bulk_collect_limit.sql script simply displays the size of the
collection. But in a real situation, the contents of the collection
would be processed at this time. The output from this script
clearly demonstrates that the query has been split into 10,000 row
chunks, with the remainder being processed in the last chunk.
SQL> @bulk_collect_limit.sql
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
1202 rows
PL/SQL
procedure successfully completed.
The bulk_collect_limit_8i.sql script displays
the same behavior, but is coded to use individual collections to
support previous Oracle versions.
bulk_collect_limit_8i.sql
SET
SERVEROUTPUT ON
DECLARE
TYPE t_owner_tab IS TABLE OF bulk_collect_test.owner%TYPE;
TYPE t_object_name_tab IS TABLE OF bulk_collect_test.object_name%TYPE;
TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;
l_owner_tab t_owner_tab;
l_object_name_tab t_object_name_tab;
l_object_id_tab t_object_id_tab;
CURSOR c_data IS
SELECT owner,
object_name,
object_id
FROM bulk_collect_test;
BEGIN
OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_owner_tab,
l_object_name_tab,
l_object_id_tab
LIMIT 10000;
DBMS_OUTPUT.put_line(l_owner_tab.count || ' rows');
EXIT WHEN c_data%NOTFOUND;
END LOOP;
CLOSE c_data;
END;
/
Oracle 10g performs implicit array fetches of
100 records in cursor FOR loops, making this limit clause method
unnecessary provided an array size of 100 records is suitable for your
purposes. The effects of this implicit array processing are
displayed by the implicit_array_processing.sql script.
implicit_array_processing.sql
SET
SERVEROUTPUT ON
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
l_tab
t_bulk_collect_test_tab;
CURSOR c_data IS
SELECT *
FROM bulk_collect_test;
l_start NUMBER;
BEGIN
-- Time a regular population.
l_start := DBMS_UTILITY.get_time;
FOR
cur_rec IN (SELECT *
FROM bulk_collect_test)
LOOP
NULL;
END LOOP;
DBMS_OUTPUT.put_line('Regular : ' ||
(DBMS_UTILITY.get_time - l_start));
--
Time bulk with LIMIT 10.
l_start := DBMS_UTILITY.get_time;
OPEN
c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 10;
EXIT WHEN c_data%NOTFOUND;
END LOOP;
CLOSE c_data;
DBMS_OUTPUT.put_line('LIMIT 10 : ' ||
(DBMS_UTILITY.get_time - l_start));
--
Time bulk with LIMIT 100.
l_start := DBMS_UTILITY.get_time;
OPEN
c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 100;
EXIT WHEN c_data%NOTFOUND;
END LOOP;
CLOSE c_data;
DBMS_OUTPUT.put_line('LIMIT 100: ' ||
(DBMS_UTILITY.get_time - l_start));
END;
/
The results of running this script against an
Oracle 9i and 10g database are displayed below.
SQL> -- 9i
SQL> @implicit_array_processing.sql
Regular : 36
LIMIT 10 : 12
LIMIT 100: 7
PL/SQL
procedure successfully completed.
SQL>
SQL> -- 10g
SQL> @implicit_array_processing.sql
Regular : 19
LIMIT 10 : 51
LIMIT 100: 17
PL/SQL
procedure successfully completed.
When the script is run against an Oracle 9i
database, both bulk operations are faster than the regular for loop.
Against an Oracle 10g database, the bulk operation using an array size
of 10 rows is actually slower than the cursor for loop, while the
operation with an array size of 100 rows is slightly faster.
This clearly demonstrates the implicit array processing being done by
Oracle 10g.
The next section shows alternative methods of
limiting the data returned by bulk collections.
Manually Limiting Bulk Collection Volumes
Although large operations can be processed in
chunks, sometimes only a subset of the total number of rows needs to
be processed. To process a subset, four possible methods are
available:
-
Using an explicit cursor and the LIMIT
clause of the BULK COLLECT syntax to limit the total number of rows
returned.
-
Using the ROWNUM pseudo column to restrict
the total number of rows returned.
-
Using the SAMPLE clause to return
approximately the desired percentage of the total rows based on a
random sample.
-
Using the SAMPLE BLOCK clause to return rows
from approximately the desired percentage of total blocks based on a
random sample.
The bulk_collect_manual_limit.sql script shows
how these four options are implemented.
bulk_collect_manual_limit.sql
SET
SERVEROUTPUT ON
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
l_tab
t_bulk_collect_test_tab;
CURSOR c_data IS
SELECT *
FROM bulk_collect_test;
BEGIN
-- Fixed number of rows using LIMIT.
OPEN c_data;
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 100;
CLOSE c_data;
DBMS_OUTPUT.put_line('LIMIT (100 rows) :
' || l_tab.count || ' rows');
--
Fixed number of rows using ROWNUM.
SELECT *
BULK COLLECT INTO l_tab
FROM bulk_collect_test
WHERE rownum < 101;
DBMS_OUTPUT.put_line('ROWNUM (100 rows) : ' ||
l_tab.count || ' rows');
--
Approximate percentage of rows.
SELECT *
BULK COLLECT INTO l_tab
FROM bulk_collect_test SAMPLE (10);
DBMS_OUTPUT.put_line('SAMPLE (~ 10% rows) : ' || l_tab.count
|| ' rows');
--
Approximate percentage of blocks.
SELECT *
BULK COLLECT INTO l_tab
FROM bulk_collect_test SAMPLE BLOCK (10);
DBMS_OUTPUT.put_line('SAMPLE (~ 10% blocks) : ' || l_tab.count || '
rows');
END;
/
The type of output expected from this script
is shown below.
SQL> @bulk_collect_manual_limit.sql
LIMIT (100 rows) : 100 rows
ROWNUM (100 rows) : 100 rows
SAMPLE (~ 10% rows) : 6174 rows
SAMPLE (~ 10% blocks) : 3358 rows
PL/SQL procedure successfully completed.
The SAMPLE options are approximations, so the
number of rows may vary. In addition, the SAMPLE BLOCK value
could vary greatly, depending on the spread of rows within the blocks
returned.
The bulk_collect_manual_limit_8i.sql script
shows how these four options are implemented in legacy versions of
Oracle.
bulk_collect_manual_limit_8i.sql
SET
SERVEROUTPUT ON
DECLARE
TYPE t_owner_tab IS TABLE OF bulk_collect_test.owner%TYPE;
TYPE t_object_name_tab IS TABLE OF bulk_collect_test.object_name%TYPE;
TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;
l_owner_tab t_owner_tab;
l_object_name_tab t_object_name_tab;
l_object_id_tab t_object_id_tab;
CURSOR c_data IS
SELECT owner,
object_name,
object_id
FROM bulk_collect_test;
BEGIN
-- Fixed number of rows using LIMIT.
OPEN c_data;
FETCH c_data
BULK COLLECT INTO l_owner_tab,
l_object_name_tab,
l_object_id_tab
LIMIT 100;
CLOSE c_data;
DBMS_OUTPUT.put_line('LIMIT (100 rows) :
' || l_owner_tab.count || ' rows');
--
Fixed number of rows using ROWNUM.
SELECT owner,
object_name,
object_id
BULK COLLECT INTO l_owner_tab,
l_object_name_tab,
l_object_id_tab
FROM bulk_collect_test
WHERE rownum < 101;
DBMS_OUTPUT.put_line('ROWNUM (100 rows) : ' ||
l_owner_tab.count || ' rows');
--
Approximate percentage of rows.
SELECT owner,
object_name,
object_id
BULK COLLECT INTO l_owner_tab,
l_object_name_tab,
l_object_id_tab
FROM bulk_collect_test SAMPLE (10);
DBMS_OUTPUT.put_line('SAMPLE (~ 10% rows) : ' ||
l_owner_tab.count || ' rows');
--
Approximate percentage of blocks.
SELECT owner,
object_name,
object_id
BULK COLLECT INTO l_owner_tab,
l_object_name_tab,
l_object_id_tab
FROM bulk_collect_test SAMPLE BLOCK (10);
DBMS_OUTPUT.put_line('SAMPLE (~ 10% blocks) : ' || l_owner_tab.count
|| ' rows');
END;
/
The output from the
bulk_collect_manual_limit_8i.sql script is similar to the output of
the previous script. Notice the variation in the values
displayed by the SAMPLE options.
SQL>
@bulk_collect_manual_limit_8i.sql
LIMIT (100 rows) : 100 rows
ROWNUM (100 rows) : 100 rows
SAMPLE (~ 10% rows) : 6113 rows
SAMPLE (~ 10% blocks) : 8093 rows
PL/SQL
procedure successfully completed.
The following section shows how bulk
collections can be used in conjunction with the returning clause of
DML statements.
This is the BC Oracle DBA Scripts collection with Oracle DBA Scripts for tuning, monitoring, a professional download of over 600 Oracle DBA Scripts.
|
|
and include the URL for the page.
