Bulk Binding and Row Processing
PL/SQL passes all SQL to the SQL engine for processing. This causes a context switch where PL/SQL will wait for the result set back from the SQL engine for each SQL statement processed.
Passing any kind of variable to SQL is called binding. The variables in the SQL statement are called bind variables. Passing an entire collection to a SQL statement in one step is called bulk binding.
Bulk binding is very quick because the SQL is processed with very few context switches. The larger the collection, the more efficient bulk binding becomes.
This section will discuss various types of bulk binding such as:
- Bulk Collect – populates collections very quickly from SQL or Return clauses
- FORALL – performs DML based on contents of collections
- IN-Bound – binding from a collection
- OUT-Bound – bulk collect return clause
- SELECT … INTO clauses
- FETCH … INTO clauses
- RETURNING clauses from DML
The number of elements returned to a collection can be controlled using the LIMITS syntax.
Bulk Collect works equally well with all the collection types.
- Bulk Collect initializes and extends the Associative Array and the Nested Table collections automatically.
- Bulk Collect cannot automatically extend a VARRAY collection, however, this unit will illustrate the LIMIT command and a loop can easily be setup to load and extend a VARRAY as well.
For empinfo_rec IN ‘select * from empinfo’
LOOP
***your code here***
END LOOP;
The above code is used to do what I call ‘row at a time’ processing. This is an implicit cursor loop. PL/SQL creates the cursor, opens, reads, and closes the cursor for you. This code is fine when you are only looping through maybe a hundred rows or less. Oracle10 converted this to a cursor using bulk binding! So, technically, you don’t have to fix this code, Oracle fixed it for you.
Tip : I would use the bulk collect method below anytime I’m accessing more than just a handful of rows.
TYPE EMP_INFO_AA_TYPE IS TABLE OF
USER0.EMP_INFO%ROWTYPE
INDEX BY BINARY_INTEGER;
EMPNO_AA_Table EMP_INFO_AA_TYPE;
TYPE EMP_INFO_NT_TYPE IS TABLE OF USER0.EMP_INFO%ROWTYPE;
EMPNO_NT_TABLE EMP_INFO_NT_TYPE := EMP_INFO_NT_TYPE();
BEGIN
DBMS_OUTPUT.PUT_LINE('User0 Timer Starts');
SELECT * FROM user0.emp_info
BULK COLLECT into EMPNO_AA_Table;
Notice in the above code the key work ‘BULK COLLECT into EMPNO_AA_Table’ syntax. This will execute the SQL statement and populate all the returned rows into the array in one database action. All other syntax remains the same.
Bulk Collect Timing Example
The illustration on the left is not using bulk collect. In this example, using the bulk collect syntax on the right to load these 15000 rows was six times faster.
DECLARE
TYPE Emp_TYPE IS TABLE OF EMP.ENAME%TYPE;
EMP_TABLE Emp_TYPE;
CURSOR C_EMP IS
SELECT ENAME FROM EMP;
BEGIN
OPEN C_EMP;
FETCH C_EMP BULK COLLECT INTO EMP_TABLE;
CLOSE C_EMP;
DBMS_OUTPUT.PUT_LINE(EMP_TABLE.COUNT || 'Rows Fetched');
FOR i in EMP_TABLE.FIRST .. EMP_TABLE.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(EMP_TABLE(i));
END LOOP;
END;
/
You can use Bulk Collect works equally well with a FETCH command. This method gives you a little better control over the cursor perhaps. Maybe you are converting other code to use this method and maybe this will allow for fewer changes to your code.
DECLARE
TYPE Emp_ENAME IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER;
EMPNO_TABLE Emp_ENAME;
CURSOR C_EMP IS
SELECT ENAME FROM EMP;
BEGIN
OPEN C_EMP;
LOOP
FETCH C_EMP
BULK COLLECT INTO EMPNO_TABLE
LIMIT 1000;
EXIT WHEN EMPNO_TABLE.COUNT = 0;
--
-- Do some additional PL/SQL processing perhaps
--
DBMS_OUTPUT.PUT_LINE(C_EMP%ROWCOUNT || ' Rows Fetched so far');
END LOOP;
CLOSE C_EMP;
END;
/
This bulk collect illustrates the use of the LIMIT clause. This loop will bring back 1000 rows at a time. The <collection>.COUNT variable will contain the number of rows returned and when this variable is 0, then all the rows will have been returned. If you just check the cursor variable %NOTFOUND to exit, you might miss some rows.
----------------------------------------------------------------------------------------------
Tip: Even a limit of 100 rows will produce better performance than just returning a single row at a time.
Why is this important? Say you have a longer row size and you are building your collection off of a %ROWTYPE type syntax. You could make your PL/SQL routine larger than your assigned program global area will allow…causing your Oracle session to incur swapping or paging…an operating system memory management technique that will greatly slow your code execution.
Program Global Area Sizing
This SQL statement shows the total memory allocated and used by all PGA’s on a particular Oracle database.
- Collections, like cursors, are created and maintained in the user’s Program Global Area, or PGA. Each user gets one of these memory structures upon successful logon.
Make sure your ‘PGA inuse’ is not larger than your ‘PGA allocated’ while your code is executing.
Now, we will see bulk binding to put rows back to the database super fast using Forall. The FORALL statement that quickly puts rows back into the database.
Forall
The FORALL syntax bulk binds the values in a collection to any bind variables (PL/SQL variables) present in the DML syntax.FORALL supports all three collection types. However, FORALL is not particularly efficient with the Associative Array, as it has to always check for the presence of a value.
FORALL can save exceptions/errors by the element in the collection causing an issue.
FORALL supports the RETURNING bulk bind syntax.
SQL%ROWCOUNT contains the row count affected by a cursor SELECT or DML statement.
SQL%BULK_ROWCOUNT(<subscript>) contains the rows affected by each element passed to the DML statement.
The subscript would be the same subscript pointing to the element in the collection.
There are two new terms to share:
- In-binding: takes what is in a collection and pushes it back to the Oracle database.
- Out-binding: is a return clause from a FORALL statement that bulk collects into a collection, showing useful information about what was processed by the DML associated with the FORALL statement.
1:FOR i IN EMPNO_TABLE.FIRST .. EMPNO_TABLE.LAST
2: LOOP
3: update emp set sal = sal * 1.1 where empno =
4: EMPNO_TABLE(i);
5: END LOOP;
6: FORALL i in EMPNO_TABLE.FIRST .. EMPNO_TABLE.LAST
7: update emp set sal = sal * 1.1 where empno =
8: EMPNO_TABLE(i);
The FORALL clause (at lines 6 thru 8) performs the same task at a fraction of the time by processing all the contents of the collection in a single call to the database.
Looping and FORALL Syntax Execution and Times
This illustration shows the results from the code execution. Notice the UPDATE Emp via Loop versus the UPDATE EMP via FORALL. Even with the 14 rows in the EMP table, there is a considerable time difference.
No comments:
Post a Comment