Tuning PLSQL Applications for Performance. PLSQL sends SQL statements such as DML and queries to the SQL engine for execution, and SQL returns the results to PLSQL. You can minimize the performance overhead of this communication between PLSQL and SQL by using the PLSQL features that are known collectively as bulk SQL. The FORALL statement sends INSERT, UPDATE, or DELETE statements in batches, rather than one at a time. The BULKCOLLECT clause brings back batches of results from SQL. If the DML statement affects four or more database rows, bulk SQL can improve performance considerably. Assigning values to PLSQL variables in SQL statements is called binding. PLSQL binding operations fall into these categories Bulk SQL uses PLSQL collections to pass large amounts of data back and forth in single operations. This process is called bulk binding. If the collection has n elements, bulk binding uses a single operation to perform the equivalent of n. SELECTINTO, INSERT, UPDATE, or DELETE statements. A query that uses bulk binding can return any number of rows, without requiring a FETCH statement for each one. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PLSQL FORALL statement instead of a LOOP statement. To speed up SELECTINTO statements, include the BULKCOLLECT clause. Running One DML Statement Multiple Times FORALL StatementThe keyword FORALL lets you run multiple DML statements very efficiently. It can only repeat a single DML statement, unlike a general purpose FOR loop. For full syntax and restrictions, see FORALL Statement. How To Update Data In Sql Using Cursor' title='How To Update Data In Sql Using Cursor' />The SQL statement can reference more than one collection, but FORALL only improves performance where the index value is used as a subscript. Usually, the bounds specify a range of consecutive index numbers. How To Update Data In Sql Using Cursor' title='How To Update Data In Sql Using Cursor' />If the index numbers are not consecutive, such as after you delete collection elements, you can use the INDICESOF or VALUESOF clause to iterate over just those index values that really exist. The INDICESOF clause iterates over all of the index values in the specified collection, or only those between a lower and upper bound. The VALUESOF clause refers to a collection that is indexed by PLSINTEGER and whose elements are of type PLSINTEGER. The FORALL statement iterates over the index values specified by the elements of this collection. The FORALL statement in Example 1. DELETE statements to the SQL engine at once. Example 1. 2 2 Issuing DELETE Statements in a Loop. CREATE TABLE employeestemp AS SELECT FROM employees. TYPE Num. List IS VARRAY2. OF NUMBER. depts Num. List Num. List1. FORALL i IN depts. FIRST. depts. LAST. DELETE FROM employeestemp WHERE departmentid deptsi. Example 1. 2 3 loads some data into PLSQL collections. Then it inserts the collection elements into a database table twice first using a FOR loop, then using a FORALL statement. The FORALL version is much faster. Example 1. 2 3 Issuing INSERT Statements in a Loop. CREATE TABLE parts. INTEGER, pname VARCHAR21. CREATE TABLE parts. Ease the process of DB2 for Linux, UNIX, and Windows data movement using the DB2 LOAD utilitys FROM CURSOR option. This article introduces the LOAD FROM CURSOR. INTEGER, pname VARCHAR21. TYPE Num. Tab IS TABLE OF parts. TYPE INDEX BY PLSINTEGER. TYPE Name. Tab IS TABLE OF parts. Learn the step by step process to bulk insert data into a Azure SQL database with PowerShell to support Big Data projects. Craig S. Mullins Database Performance Management Return to Home Page. October 2004 Using Dynamic SQL for Maximum Flexibility. TYPE INDEX BY PLSINTEGER. Num. Tab. pnames Name. Tab. iterations CONSTANT PLSINTEGER 5. FOR j IN 1. iterations LOOP load index by tables. Part No. TOCHARj. DBMSUTILITY. gettime. FOR i IN 1. iterations LOOP use FOR loop. INSERT INTO parts. VALUES pnumsi, pnamesi. DBMSUTILITY. gettime. FORALL i IN 1. iterations use FORALL statement. INSERT INTO parts. VALUES pnumsi, pnamesi. DBMSUTILITY. gettime. DBMSOUTPUT. PUTLINEExecution Time secs. DBMSOUTPUT. PUTLINE. DBMSOUTPUT. PUTLINEFOR loop TOCHARt. DBMSOUTPUT. PUTLINEFORALL TOCHARt. Executing this block shows that the loop using FORALL is much faster. The bounds of the FORALL loop can apply to part of a collection, not necessarily all the elements, as shown in Example 1. Example 1. 2 4 Using FORALL with Part of a Collection. CREATE TABLE employeestemp AS SELECT FROM employees. TYPE Num. List IS VARRAY1. OF NUMBER. depts Num. List Num. List5,1. FORALL j IN 4. 7 use only part of varray. DELETE FROM employeestemp WHERE departmentid deptsj. You might need to delete some elements from a collection before using the collection in a FORALL statement. The INDICESOF clause processes sparse collections by iterating through only the remaining elements. You might also want to leave the original collection alone, but process only some elements, process the elements in a different order, or process some elements more than once. Instead of copying the entire elements into new collections, which might use up substantial amounts of memory, the VALUESOF clause lets you set up simple collections whose elements serve as pointers to elements in the original collection. Example 1. 2 5 creates a collection holding some arbitrary data, a set of table names. Deleting some of the elements makes it a sparse collection that does not work in a default FORALL statement. The program uses a FORALL statement with the INDICESOF clause to insert the data into a table. It then sets up two more collections, pointing to certain elements from the original collection. The program stores each set of names in a different database table using FORALL statements with the VALUESOF clause. Example 1. 2 5 Using FORALL with Nonconsecutive Index Values. Create empty tables to hold order details. CREATE TABLE validorders custname VARCHAR23. NUMBER1. 0,2. CREATE TABLE bigorders AS SELECT FROM validorders. WHERE 1 0. CREATE TABLE rejectedorders AS SELECT FROM validorders. Collections for set of customer names order amounts. SUBTYPE custname IS validorders. TYPE. TYPE custtyp IS TABLe OF custname. SUBTYPE orderamount IS validorders. TYPE. TYPE amounttyp IS TABLE OF NUMBER. Collections to point into CUSTTAB collection. TYPE indexpointert IS TABLE OF PLSINTEGER. PROCEDURE setupdata IS BEGIN. Set up sample order data. Company. 1,Company. Company. 3,Company. Company. 5. amounttab amounttyp5. NULL. setupdata. DBMSOUTPUT. PUTLINE. Original order data. FOR i IN 1. custtab. LAST LOOP. DBMSOUTPUT. PUTLINE. Customer i, custtabi. Delete invalid orders where amount is null or 0. FOR i IN 1. custtab. LAST LOOP. IF amounttabi is null or amounttabi 0 THEN. DBMSOUTPUT. PUTLINE. Data with invalid orders deleted. FOR i IN 1. custtab. LAST LOOP. IF custtab. EXISTSi THEN. DBMSOUTPUT. PUTLINECustomer i,. Because subscripts of collections are not consecutive. Awesome Cnc Projects For Vectric Aspire more. FORALL. INDICES OF to iterate through actual subscripts. COUNT. FORALL i IN INDICES OF custtab. INSERT INTO validorderscustname, amount. VALUEScusttabi, amounttabi. Now process the order data differently. Extract 2 subsets and store each subset in a different table. Initialize the CUSTTAB and AMOUNTTAB collections again. FOR i IN custtab. FIRST. custtab. LAST LOOP. IF amounttabi IS NULL OR amounttabi 0 THEN. Add a new element to this collection. EXTEND. Record the subscript from the original collection. LAST i. IF amounttabi 2. THEN. Add a new element to this collection. EXTEND. Record the subscript from the original collection. LAST i. Now its easy to run one DML statement. DML statement on a different subset. FORALL i IN VALUES OF rejectedordertab. INSERT INTO rejectedorders. VALUES custtabi, amounttabi. FORALL i IN VALUES OF bigordertab. INSERT INTO bigorders. VALUES custtabi, amounttabi. Verify that the correct order details were stored. SELECT custname Customer. Valid order amount FROM validorders. SELECT custname Customer. Big order amount FROM bigorders. SELECT custname Customer. Rejected order amount FROM rejectedorders. Topics How FORALL Affects Rollbacks.