Do not use Bulk collect without a LIMIT clause in Cursors

Normally when we use a cursor to fetch all the records at once we make use of BULK COLLECT. Without using a LIMIT clause will take all the records returned by the cursor and which will lead to extreme physical memory tiredness or we can say memory exhaustion.  So it’s always better to use LIMIT clause if we are using a cursor to fetch all the records at one shot and doing the operations.

To check this and validate the above told statement let us create a table which will be our target table to insert huge set of records using cursors:


SQL> SET SERVEROUTPUT ON
SQL> 
SQL> CREATE TABLE my_big_table(
  2    my_data VARCHAR2(42)
  3  ); 

Table created 

I am only using one column to demonstrate the LIMIT clause.

Let us insert some sample records into our sample table which we have created .

 

SQL> DECLARE
  2          timestart NUMBER;
  3  BEGIN
  4          timestart := dbms_utility.get_time();
  5          FOR i IN 1 .. 200001
  6          LOOP
  7                  INSERT INTO my_big_table VALUES ('test' || i);
  8  
  9          END LOOP;
10          dbms_output.put_line(dbms_utility.get_time() - timestart);
11  END;
12  / 

552 

SQL> select count(*) from my_big_table; 

  COUNT(*)
----------
    200001 

Now let us use BULK collect without an LIMIT clause and check how much time it takes to process the data just count the number of records.


SQL> DECLARE
  2          TYPE my_big_tablearraytype IS TABLE OF my_big_table%ROWTYPE;
  3          my_big_tablerowtype my_big_tablearraytype;
  4          CURSOR mycursor IS
  5                  SELECT * FROM my_big_table;
  6          timestart NUMBER;
  7  BEGIN
  8          timestart := dbms_utility.get_time();
  9          OPEN mycursor;
10  
11          FETCH mycursor BULK COLLECT
12                  INTO my_big_tablerowtype; -- Non-compliant
13  
14          dbms_output.put_line('Without Using Limit : ' || my_big_tablerowtype.COUNT ||
15                               ' records');
16  
17          CLOSE mycursor;
18          dbms_output.put_line(dbms_utility.get_time() - timestart);
19  END;
20  / 

Without Using Limit : 200001 records
10 

PL/SQL procedure successfully completed 

We can see from the above statement that it took 10 sec to process the data for 200001 records.

Now let us use LIMIT clause and check how much it takes to process same set of data for taking the count.


SQL> DECLARE
  2          TYPE my_big_tablearraytype IS TABLE OF my_big_table%ROWTYPE;
  3          my_big_tablerowtype my_big_tablearraytype;
  4          CURSOR mycursor IS
  5                  SELECT * FROM my_big_table;
  6          timestart NUMBER;
  7          counter   PLS_INTEGER := 0;
  8  BEGIN
  9          timestart := dbms_utility.get_time();
10          OPEN mycursor;
11  
12          LOOP
13                  FETCH mycursor BULK COLLECT
14                          INTO my_big_tablerowtype LIMIT 1000; -- Compliant
15  
16                  counter := counter + my_big_tablerowtype.COUNT;
17  
18                  EXIT WHEN mycursor%NOTFOUND;
19          END LOOP;
20  
21          dbms_output.put_line('Using Limit : ' || counter || ' records');
22  
23          CLOSE mycursor;
24          dbms_output.put_line(dbms_utility.get_time() - timestart);
25  END;
26  / 

Using Limit : 200001 records
8 

PL/SQL procedure successfully completed 

From the above execution we can see that it took only 8 sec for taking the count as without using limit clause it takes more time.

So better to use LIMIT clause on any bulk collect operation used without PLSQL code.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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