How to Perform Bulk Insert in Oracle PL/SQL

If you’re working with PL/SQL code and processing multiple rows, there are two techniques you can use to improve the performance of your code.

These techniques are BULK COLLECT and FORALL.

In this guide, you’ll learn what these techniques are, see examples of them, and see how they can improve the performance of your code.

Let’s get into it.

Sample Code

For this article, we’ll use some sample code to demonstrate how to use these two features and what the performance impact is.

Here’s the table with some sample data:

id	product_name	price	active_status
1	Chair	150	active
2	Long Dining Table	180	inactive
3	3 Seater Couch	99	active

The real table has 1,000 records, but I’ve only shown a few here.

Let’s say we had to write a procedure to increase the price of a product that met certain criteria. This procedure would check if a product is active, and if so, increase the price by the percentage specified.

Here’s the stored procedure to make our updates:

CREATE OR REPLACE PROCEDURE increasePrice (
    pctToIncrease IN NUMBER)
IS
BEGIN
    FOR productRec IN (
        SELECT id, active_status
        FROM product
    )
    LOOP
        IF (productRec.active_status = 'active') THEN
            UPDATE product p
            SET p.price = p.price + (p.price * pctToIncrease / 100)
            WHERE p.id = productRec.id;
        END IF;
    END LOOP;
END increasePrice;

If we run this procedure with a parameter value of 100, we would expect the prices to double (increase by 100%).

Here’s the function call:

CALL increasePrice(100);

And here are the results:

id	product_name	price	active_status
1	Chair	300	active
2	Long Dining Table	180	inactive
3	3 Seater Couch	198	active

We can see that the price has doubled for products 1 and 3, but not 2. This is expected because product 2 is inactive.

 

First Procedure Example

Let’s take another look at that procedure we wrote.

CREATE OR REPLACE PROCEDURE increasePrice (
    pctToIncrease IN NUMBER)
IS
BEGIN
    FOR productRec IN (
        SELECT id, active_status
        FROM product
    )
    LOOP
        IF (productRec.active_status = 'active') THEN
            UPDATE product p
            SET p.price = p.price + (p.price * pctToIncrease / 100)
            WHERE p.id = productRec.id;
        END IF;
    END LOOP;
END increasePrice;

 

This procedure does the job we want, but there are a few issues.

The main issue is that it uses something called “context switching”.

In the Oracle database, there is an area that runs the SQL code (SELECT, UPDATE, etc) and an area that runs the PL/SQL code (LOOP, IF, etc). Whenever a procedure or function needs to switch between these two areas, it’s called a context switch.

The reason this is not ideal is that it can massively slow down the execution of a procedure. Each time a context switch happens it adds a little overhead. If this is done many times then it slows down the code.

This example procedure does that. The records are selected and stored in the productRec variable. Then a loop is performed. In each loop iteration, the active_status is checked. If it’s active, an update statement is run.

The context switch happens on each loop and in each IF statement.

 

Using BULK COLLECT and FORALL

How can we avoid this?

We can use a combination of two features in PL/SQL:

• BULK COLLECT: a clause to let you fetch multiple rows into a collection
• FORALL: a feature to let you execute the same DML statement multiple times for different values

A combination of these should improve our stored procedure.

Here’s what our procedure would look like with these two features.

CREATE OR REPLACE PROCEDURE increasePriceImproved (
    pctToIncrease IN NUMBER)
IS
    TYPE product_ids_t IS TABLE OF product.id%TYPE INDEX BY PLS_INTEGER;
    TYPE product_active_t IS TABLE OF product.active_status%TYPE INDEX BY PLS_INTEGER;
    product_ids product_ids_t;
    product_active_statuses product_active_t;
    active_ids product_ids_t;
BEGIN
    SELECT id, active_status
    BULK COLLECT INTO product_ids, product_active_statuses
    FROM product;
    
    FOR ix IN 1 .. product_ids.COUNT
    LOOP
        IF (product_active_statuses(ix) = 'active') THEN
            active_ids(active_ids.COUNT + 1) := product_ids(ix);
        END IF;
    END LOOP;
    
    FORALL ix IN 1 .. active_ids.COUNT
        UPDATE product p
        SET p.price = p.price + (p.price * pctToIncrease / 100)
        WHERE p.id = active_ids(ix);
        
END increasePriceImproved;

What does this code do? Let’s take a look.

CREATE OR REPLACE PROCEDURE increasePriceImproved (
    pctToIncrease IN NUMBER)
IS

These lines declare the procedure called increasePriceImproved and a parameter of pctToIncrease. This is similar to the original procedure.

TYPE product_ids_t IS TABLE OF product.id%TYPE INDEX BY PLS_INTEGER;
TYPE product_active_t IS TABLE OF product.active_status%TYPE INDEX BY PLS_INTEGER;

These lines declare two new “types”, which are like declaring a custom data type in PL/SQL. The first line declares a new type called product_ids_t which is the same type as the product.id column. The INDEX BY PLS_INTEGER will index each record of this type with an integer, so it can be referred to in the procedure by number.

The second line declares a new type that matches the product.active_status data type.

product_ids product_ids_t;
product_active_statuses product_active_t;
active_ids product_ids_t;

These lines declare new variables based on the new types. We have:

• product_ids which is based on the product.id column
• product_active_statuses which is based on the product.active_status column
• active_ids which is based on the product_ids column.

We’ll see how they are used later in the code.

BEGIN
SELECT id, active_status
BULK COLLECT INTO product_ids, product_active_statuses
FROM product;

We start the executable part of the procedure with BEGIN, and then run a SELECT statement.

This SELECT statement includes BULK COLLECT. This means the values from the SELECT statement will be stored into the two variables specified in BULK COLLECT.

The product.ids that are found will be stored in the product_ids variable, and the product.status will be stored in the product_active_statuses variable.

FOR ix IN 1 .. product_ids.COUNT
LOOP

We then start a FOR loop. We specify a new variable called ix, and loop from the value of 1 to the count of product_ids – so, loop through all records found in the earlier SELECT query.

IF (product_active_statuses(ix) = 'active') THEN
    active_ids(active_ids.COUNT + 1) := product_ids(ix);
END IF;
END LOOP;

Inside the loop, we check if the product_active_status value for the current index is active. If so, we add it to the active_ids variable. We specify the index (inside the brackets) as the current count of the variable and add 1, essentially adding it as the next index.

We set it to the product id for the index.

So, if product id 1 is active, product id 1 is added to this active_ids variable.

We then end the loop.

FORALL ix IN 1 .. active_ids.COUNT
    UPDATE product p
    SET p.price = p.price + (p.price * pctToIncrease / 100)
    WHERE p.id = active_ids(ix);

END increasePriceImproved;

The next step is to run this FORALL command. This specifies that the following statement should run once for all of the items found inside the specified range, which is from 1 to the number of active_ids.

The UPDATE statement is run once and includes all of the active IDs, as we can see in the WHERE clause. We run the statement when the WHERE clause matches the active_ids for the specific index.

We then end the stored procedure.

When you run this procedure, you’ll get the same result (after resetting the data):

id	product_name	price	active_status
1	Chair	300	active
2	Long Dining Table	180	inactive
3	3 Seater Couch	198	active

However, you should see a performance improvement compared to the first version as there is less context switching in your code.

 

Conclusion

Oracle offers two helpful features in PL/SQL: BULK COLLECT and FORALL. If you’re writing a procedure that loops through results or runs the same statement many times, consider using BULK COLLECT and FORALL to improve the performance of your code.