Nested Loops

Nested loops are an implementation that leads to the execution of many rows into two or more data sets, e.g. a loop over rows where each row requires lookup9s) in another table where a large number of rows are accessed. There is nothing wrong with writing code that performs nested loops BUT if the loops can be replaced by some other mechanism such as pure SQL, then this should be done. The more data we are processing, then the more important this will be, since pure SQL is much faster than PL loops. This is because of the context switch between the SQL engine and the PL/SQL engine when running SQL in PLSQL. Please remember that an efficient solution is always preferred, this isnot only when processing large amounts of data.

Contents

Example

Solution

The problem with the example code is that it performs a PL/SQL loop through a large table and for each new combination of company and matching_date performs another SELECT. This might be rather time consuming.

To avoid the "penalty rounds"/"context switch" and make the processing as efficient as possible it's better to let the SQL engine do most of the work. This is achieved by writing one SELECT statement.
The statement can be rewritten to the following SQL: