PL/SQL - Bulk Processing

Context Switch:

Every PL/SQL code includes both PL/SQL and SQL statements. PL/SQL statements are run by PL/SQL statement executor. SQL statements are run by the SQL statement executor.

When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine.

The transfer of control is called a Context Switch and each of these switches incurs ovethead that slows down the overall performance of our programs.

Bulk Processing concepts:

Bulk Collect:

SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval. We will use BULK COLLECT functionality to fetch all the ORDER_IDs and store it in collection.

FOR ALL:

INSERTs, UPDATEs and DELETEs that use collections to change multiple rows of data very quickly. We will use FOR ALL functionality to update all the rows stored in the collection. FORALL "bundles up" all the updates and passes them to the SQL engine with a single contet switch.

Example:

CREATE OR REPLACE PROCEDURE UPDATE_TAX(TAX_RATE IN NUMBER)

AS

L_ELIGIBLE BOOLEAN;

-- Associative array

TYPE ORDERID_TYPE IS TABLE OF SALES.ORDERID%TYPE INDEX BY PLS_INTEGER

L_ORDER_IDS ORDERID_TYPE;

BEGIN

-- bulk collect the order ids into a collection

SELECT DISTINCT ORDER_ID BULK COLLECT INTO L_ORDER_IDS FROM SALES;

-- for all to update all the rows as a bundle.

FORALL INDX IN 1 .. L_ORDER_IDS.COUNT

UPDATE SALES S

SET S.TAX_AMOUNT = S.TOTAL_AMOUNT *TAX_RATE WHERE S.ORDER_ID = L_ORDER_IDS(INDX);

END;

Note: suppose we have 10 orders, if we fetch the order one by one then it will have 10 context switch but if we use above code to select bulk data and update forall then it will have 1 context switch thus reducing the overhead and peformances.

Bulk Collect with Limit:

PL/SQL collections are 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 mote memory friendly. The chunking can be achieved using the LIMIT clause of the BULK collect.

CREATE OR REPLACE PROCEDURE UPDATE_TAX(TAX_RATE IN NUMBER)

AS

L_ELIGIBLE BOOLEAN;

-- Associative array

TYPE ORDERID_TYPE IS TABLE OF SALES.ORDERID%TYPE INDEX BY PLS_INTEGER

L_ORDER_IDS ORDERID_TYPE;

L_ELIGIBLE_ORDERS  ORDERID_TYPE;

CURSOR SALES_CURSOR

IS

SELECT DISTINCT ORDER_ID FROM SALES;

BEGIN

OPEN SALES_CURSOR;

LOOP

FETCH SALES_CURSOR

BULK COLLECT INTO L_ORDER_IDS

LIMIT 100;

FOR INDX IN 1 .. L_ORDER_IDS.COUNT

LOOP

CHECK_ELIGIBLE(L_ORDER_IDS(INDX), L_ELIGIBLE);

IF L_ELIGIBLE

THEN

L_ELIGIBLE_ORDERS(L_ELIGIBLE_ORDERS.COUNT+1):= L_ORDER_IDS(INDX);

END IF;

END LOOP;

EXIT WHEN L_ORDER_IDS.COUNT =0;

END LOOP;

-- for all to update all the rows as a bundle.

FORALL INDX IN 1 .. L_ELIGIBLE_ORDER.COUNT

UPDATE SALES S

SET S.TAX_AMOUNT = S.TOTAL_AMOUNT *TAX_RATE WHERE S.ORDER_ID = L_ELIGIBLE_ORDERS(INDX);

COMMIT;

CLOSE SALES_CURSOR;

END;

BULK collect with Rowtype:

CREATE OR REPLACE PROCEDURE FETCH_SALES_CUR(S_DATE DATE)

AS

CURSOR SALE_CURSOR

IS

SELECT SALES_DAYE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,SALESPERSON_ID,QUANTITY,UNIT_PRICE,SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT

FROM SALES

WHERE SALES_DATE =S_DATE;

TYPE L_SALESTABLE IS TABLE OF SALES%ROWTYPE;

L_SALES L_SALESTABLE;

BEGIN

OPEN SALE_CUTSOR;

LOOP

OPEN FETCH SALE_CURSOR BULK COLLECT INTO L_SALES LIMIT 100;

FOR INDX IN 1 .. L_SALES.COUNT

Loop

Dbms_output.put_line(L_SALES(INDX).SALES_DATE);

Dbms_output.put_line(L_SALES(INDX).ORDER_ID);

Dbms_output.put_line(L_SALES(INDX).PRODUCT_ID);

Dbms_output.put_line(L_SALES(INDX).customer_id);

...

END LOOP;

EXIT WHEN SALE_CURSOR%NOTFOUND;

END LOOP;

CLOSE SALE_CURSOR;

END;

/