Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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 an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational