19 May 2010

Make your PL/SQL loops EFFICIENT!!!

Make Loops as Efficient as Possible

Because PL/SQL applications are often built around loops, it is important to optimize both the loop itself and the code inside the loop:

•To issue a series of DML statements, replace loop constructs with FORALL statements.

•To loop through a result set and store the values, use the BULK COLLECT clause on the query to bring the query results into memory in one operation (see "Reducing Loop Overhead with Bulk SQL").

•If you must loop through a result set more than once, or issue other queries as you loop through a result set, you can probably enhance the original query to give you exactly the results you want. Some query operators to explore include UNION, INTERSECT, MINUS, and CONNECT BY.

•You can also nest one query inside another (known as a subquery) to do the filtering and sorting in multiple stages. For example, instead of invoking a PL/SQL function in the inner WHERE clause (which might invoke the function once for each row of the table), you can filter the result set to a small set of rows in the inner query, and invoke the function in the outer query.

And last but not least, use the NOLOGING option!

0 reacties:

Post a Comment