Bulk Processing with BULK COLLECT and FORALLTECHNOLOGY: PL/SQLBy Steven Feuerstein Part 9 in a series of articles on understanding and using PL/SQL In the previous article in this series, I introduced readers to PL/SQL collections. These data structures come in very handy when implementing algorithms that manipulate lists of program data, but they are also key elements in some of the powerful performance optimization features in PL/SQL. In this article, I will cover the two most important of these features: BULK COLLECT and FORALL. BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval. FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly. You may be wondering what very quickly might mean—how much impact do these features really have? Actual results will vary, depending on the version of Oracle Database you are running and the specifics of your application logic.

You can download and run the script to compare the performance of row- by- row inserting with FORALL inserting. On my laptop running Oracle Database 1. Release 2, it took 4. With FORALL, those 1. Wow! Answer to the Challenge. The PL/SQL Challenge question in last issue’s “Working with Collections” article tested your knowledge of iterating through the contents of a sparsely populated collection. Choice (c) is the only correct choice, and offers the simplest algorithm for accomplishing this task.

The explanation has everything to do with how the runtime engines for both PL/SQL and SQL communicate with each other—through a context switch. Context Switches and Performance.

Hi, This update was useful as it helped me understand certain run-time features which I wasnt using so far. I have one doubt regarding one run-time setting. From millions of real job salary data. 0 salary data. Average salary is Detailed starting salary, median salary, pay scale, bonus data report. Union Knights Football Team: Sports league web site provided and hosted free of charge by LeagueLineup.com - The Youth and Amateur Sports Portal!

Almost every program PL/SQL developers write includes both PL/SQL and SQL statements. PL/SQL statements are run by the 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 (see Figure 1). This transfer of control is called a context switch, and each one of these switches incurs overhead that slows down the overall performance of your programs.

Figure 1: Switching between PL/SQL and SQL engines. Let’s look at a concrete example to explore context switches more thoroughly and identify the reason that FORALL and BULK COLLECT can have such a dramatic impact on performance.

TECHNOLOGY: PL/SQL. Bulk Processing with BULK COLLECT and FORALL By Steven Feuerstein. Part 9 in a series of articles on understanding and using PL/SQL.

Yes you can. This would be just like not having batch management at all, except that you would still have to enter a batch at every goods movement.

InformationWeek.com: News, analysis and research for business technology professionals, plus peer-to-peer knowledge sharing. Engage with our community. Please Note: You may either pay with your PayPal account or Credit/Debit card. What videos and slides does the Download contain? A common request often voiced on list groups and other professional forums is for a standard cross-reference between SAP IDocs and EDI transaction sets and messages. Hi Mani, Thx for the reply. Is there any way that I can get the start time and run time of the instances? I checked the SI Here is a single SQL statement you can use to populate a table with random data.

Suppose my manager asked me to write a procedure that accepts a department ID and a salary percentage increase and gives everyone in that department a raise by the specified percentage. Taking advantage of PL/SQL’s elegant cursor FOR loop and the ability to call SQL statements natively in PL/SQL, I come up with the code in Listing 1. Code Listing 1: increase. When I execute this block.

Tom Kyte, of Ask. Tom (asktom. oracle. I will show you how you can use PL/SQL’s bulk processing features to escape from “slow- by- slow processing.” First, however, you should always check to see if it is possible to avoid the context switching between PL/SQL and SQL by doing as much of the work as possible within SQL. Take another look at the increase.

The SELECT statement identifies all the employees in a department. The UPDATE statement executes for each of those employees, applying the same percentage increase to all. In such a simple scenario, a cursor FOR loop is not needed at all. I can simplify this procedure to nothing more than the code in Listing 2.

Code Listing 2: Simplified increase. All the work is done in the SQL engine. Of course, in most real- world scenarios, life—and code—is not so simple. We often need to perform other steps prior to execution of our data manipulation language (DML) statements. Suppose that, for example, in the case of the increase.

My procedure might then look like the version in Listing 3. Code Listing 3: increase? Not with BULK COLLECT and FORALL in PL/SQL. Bulk Processing in PL/SQLThe bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine. Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch. Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values. The UPDATE statement in the increase.

One variable, l. The other, l. There are three phases of execution: Fetch rows with BULK COLLECT into one or more collections. A single context switch is needed for this step.

Modify the contents of collections as required (in this case, remove ineligible employees). Change the table with FORALL using the modified collections. Rather than move back and forth between the PL/SQL and SQL engines to update each row, FORALL “bundles up” all the updates and passes them to the SQL engine with a single context switch. The result is an extraordinary boost in performance. I will first explore BULK COLLECT in more detail, and then cover FORALL. About BULK COLLECTTo take advantage of bulk processing for queries, you simply put BULK COLLECT before the INTO keyword and then provide one or more collections after the INTO keyword. Here are some things to know about how BULK COLLECT works: It can be used with all three types of collections: associative arrays, nested tables, and VARRAYs.

You can fetch into individual collections (one for each expression in the SELECT list) or a single collection of records. The collection is always populated densely, starting from index value 1.

If no rows are fetched, then the collection is emptied of all elements. Listing 5 demonstrates an example of fetching values for two columns into a collection of records. Code Listing 5: Fetching values for two columns into a collection. TYPE two. To help you avoid such errors, Oracle Database offers a LIMIT clause for BULK COLLECT. Suppose that, for example, there could be tens of thousands of employees in a single department and my session does not have enough memory available to store 2. IDs in a collection. Instead I use the approach in Listing 6.

Code Listing 6: Fetching up to the number of rows specified. Then, inside a loop, I use FETCH- BULK COLLECT- INTO to fetch up to the number of rows specified by the c. Now, no matter how many rows I need to fetch, my session will never consume more memory than that required for those 1. Rough Dry Patch Of Skin On Top Of Foot on this page. I will still benefit from the improvement in performance of bulk querying.

About FORALLWhenever you execute a DML statement inside of a loop, you should convert that code to use FORALL. The performance improvement will amaze you and please your users.

The FORALL statement is not a loop; it is a declarative statement to the PL/SQL engine: “Generate all the DML statements that would have been executed one row at a time, and send them all across to the SQL engine with one context switch.”As you can see in Listing 4, lines 3. FORALL statement looks just like a numeric FOR loop, yet there are no LOOP or END LOOP keywords. Here are some things to know about FORALL: Each FORALL statement may contain just a single DML statement.

If your loop contains two updates and a delete, then you will need to write three FORALL statements. PL/SQL declares the FORALL iterator (indx on line 3. Listing 4) as an integer, just as it does with a FOR loop. You do not need to—and you should not—declare a variable with this same name. In at least one place in the DML statement, you need to reference a collection and use the FORALL iterator as the index value in that collection (see line 3. Listing 4). When using the IN low. That is, every index value between the low.

FORALL and DML Errors. Suppose that I’ve written a program that is supposed to insert 1. After inserting 9,0. DUP. The SQL engine passes that error back to the PL/SQL engine, and if the FORALL statement is written like the one in Listing 4, PL/SQL will terminate the FORALL statement. The remaining 9. 99 rows will not be inserted.