Foundation1 / Development Guide / Core Server / Base Server Dev / Performance / This page

Cursor For-Loop

The 'Cursor For-Loop' is often used when you want to fetch and process all records of a selection. It might seem like the simplest solution, but it is not the optimal solution from a performance perspective. A more efficient approach is to use BULK COLLECT as a faster way to query data. If you expect to retrieve a relatively small number of rows (in the hundreds at most), then the performance of a 'Cursor For-Loop' will most likely meet your requirements. But for larger numbers, it is better to use BULK COLLECT instead. PL/SQL compiler automatically optimizes 'Cursor For-Loops' to execute similarly to BULK COLLECT if the loop body does not have any non-query DML (insert/update/delete) statements.

Contents

• Rules
• Examples
• Solution

Rules

Instead of 'Cursor For-Loop':

• If it is expected to retrieve multiple rows of data and the upper limit does not exceed an acceptable value, then BULK COLLECT into a collection via an implicit cursor.
• If it is expected to retrieve multiple rows of data and the upper limit is not known, use BULK COLLECT with an explicit cursor and a FETCH statement that relies on a LIMIT clause. This ensures that you do not consume too much per-session memory.

Examples

Below is an example of where 'Cursor For-loop' is typically used.

Solution

This solution shows the use of BULK COLLECT. The developer will have to write some additional lines of code, but performance will be enhanced.

BULK COLLECT helps retrieve multiple rows of data quickly. Rather than retrieving one row of data at a time into a record or a set of individual variables, BULK COLLECT lets you retrieve hundreds, thousands, even tens of thousands of rows with a single context switch to the SQL engine and deposit all that data into a collection. But that boost in performance results in an increase in the amount of per-session memory consumed by the collection populated by the query. In addition, each session connected to Oracle Database has its own per-session memory area. Therefore, a BULK COLLECT that fetches numerous rows can seriously affect memory management on the entire database server.

So when you need to fetch multiple rows of data and the number of rows fetched can be large or grow over time, you should use BULK COLLECT with the LIMIT clause.

The following example selects only 1000 records per fetch to reduce memory consumption while minimizing the context switch by bulk processing.