Bulk Collect With LIMIT Clause In Oracle Database

So far we have learned how to improve query performance using bulk collect with SELECT-INTO which uses the implicit cursor and FETCH-INTO statement of an explicit cursor. But the question remains that is there still scope for further query optimization? In this blog, we will learn how can we further improve our query using the Limit clause with bulk collect?

We have already learned the process of compressing multiple switches by using bulk collect with select-into and in turn get greater control over the query by using the same with a fetch-into statement of an explicit cursor. But there is still a problem which requires our attention and that is excessive memory exhaustion caused by bulk collect.

What do you mean by excessive memory exhaustion caused by bulk collect?

Whenever we retrieve or fetch a large number of records using bulk collect clause, our program starts consuming a lot of memory in order to be fast and efficient. This is not just any memory. Unlike the SGA memory that is shared among all the sessions of Oracle Database, the program consumes the PGA memory that is specifically allotted for each session.

This degrades the performance of the database. This means that our query must surely be performing well but at the same time, our database may not.

We cannot have a well-optimized query by compromising the performance of our entire database. Right?

How can we solve this problem of memory exhaustion by bulk collect?

This problem of memory exhaustion can easily be overcome if we can control and constraint the amount of data fetched using the bulk collect. We can do that by using Bulk Collect with the LIMIT clause.

What is the syntax of the LIMIT clause?

LIMIT clause works as an attribute of a FETCH-INTO statement:

FETCH <cursor_name> BULK COLLECT INTO <plsql_collection> LIMIT number;

As LIMIT works as an attribute of the FETCH-INTO statement thus to use it you can add keyword LIMIT followed by a specific numerical digit which will specify the number of rows that the bulk-collect clause will retrieve in one go at the end of FETCH-INTO statement.

What does LIMIT clause do?

LIMIT clause restricts the number of rows fetched using BULK COLLECT with FETCH statement.

Can we use the LIMIT clause with SELECT-INTO statement?

No, we cannot use the LIMIT clause with SELECT-INTO statement. LIMIT clause works as an attribute to the FETCH-INTO statement because LIMIT clause requires an explicit cursor to work and FETCH-INTO statement is a part of explicit cursor life cycle.

So always remember the LIMIT clause can only be used when you are using BULK COLLECT with FETCH-INTO statement. It cannot be used when you are using bulk collect with SELECT-INTO statement.

Example: How to use LIMIT clause with Bulk Collect statement in Oracle Database

Here is a very simple example showing you how you can work with the LIMIT clause.

SET SERVEROUTPUT ON;
DECLARE
    CURSOR exp_cur IS
    SELECT first_name FROM employees;
    
    TYPE nt_fName   IS TABLE OF VARCHAR2(20);
    fname   nt_fName;
BEGIN
    OPEN exp_cur;
    FETCH exp_cur   BULK COLLECT INTO fname     LIMIT 10;
    CLOSE exp_cur;
    --Print data
    FOR idx IN 1 .. fname.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE (idx||' '||fname(idx) );
    END LOOP;
END;
/

You can refer to the video tutorial on my YouTube channel for the detailed explanation of the above code.

So this time instead of fetching all the records and exhausting an expensive resource like memory, thanks to the LIMIT clause, we are retrieving only the necessary rows and that too without any resource wastage. This way we can take our query performance a notch higher with bulk collect.

So now you ask, Manish what is the right amount of rows we can fetch?

In order to know the answer to this question I suggest you to read this blog by my dear friend Steven Feuerstein on Oracle’s website. He has answered this question really well.

There is a shortcoming with this approach also and that is: If you execute this same program again, then this FETCH-INTO statement with LIMIT clause will not append the collection with next 10 records. Rather, it will truncate the table and populate the nested table again from the index no.1.

My dear friend Connor McDonald has done a blog explaining how you can overcome this problem using MULTISET. Go ahead and checkout his article.

That is the detailed PL/SQL blog on how to use LIMIT clause with Bulk Collect in Oracle Database. Hope you enjoyed reading, if so then do make sure to share this tutorial with your friends on your social media. Thanks for reading. Have a great day!