Replace inefficient nested loops executing SQL, with carefully written SQL

Assume a scenario, where a large number of rows, fetched from a cursor, are iterated through a loop. For each iteration, some other information is needed and is fetched from a different cursor. This can be described as a nested loop implementation. Even though this is a technically feasible solution, this might be inefficient due to the large number of context switches (Between SQL Engine and PLSQL Engine) Oracle has to perform while executing. More efficient solution might be to look at the possibility of combining these two cursors via a join, and use a single cursor to fetch the needed information.

It is always recommended to evaluate the solution case by case with respect to performance, and decide on the most efficient approach.

Example

Accounting year information is fetched for each row iterated via a sperate cursor get_accounting_year

PROCEDURE Nested_Loop
IS
   accounting_year_ NUMBER;
   company_         VARCHAR2(20);
   matching_date_   DATE;
   objid_           ROWID;

   CURSOR get_accounting_year(company_ VARCHAR2, date_param_ DATE) IS
      SELECT accounting_year
        FROM accounting_period_tab
       WHERE company    = company_
         AND date_param_ BETWEEN date_from AND date_until;

   CURSOR get_records IS
       SELECT v.company,v.matching_date, v.ROWID objid
         FROM gen_led_voucher_row_tab v
        WHERE v.matching_year IS NULL
          AND v.matching_date IS NOT NULL
     ORDER BY v.company, v.matching_date ;
BEGIN
   OPEN get_records;
   FETCH get_records INTO company_, matching_date_, objid_;
   WHILE (get_records%FOUND) LOOP 
      OPEN get_accounting_year(rec_.company, TRUNC(rec_.matching_date));
      FETCH get_accounting_year INTO accounting_year_;
      CLOSE get_accounting_year;
      --Do_Something...;
      FETCH get_records INTO company_, matching_date_, objid_;
   END LOOP;
   CLOSE get_records;
END Nested_Loop;

Solution

Two tables are joined together and all necessary information is fetched via a single cursor. This will eliminate the context switch, which existed for each iteration, in the previous algorithm.

PROCEDURE Avoid_Nested_Loop
IS
   accounting_year_ NUMBER;
   company_         VARCHAR2(20);
   matching_date_   DATE;
   objid_           ROWID;
   CURSOR get_records IS
      SELECT v.company,v.matching_date, v.ROWID objid, b.accounting_year accounting_year
        FROM gen_led_voucher_row_tab v, accounting_period_tab b
       WHERE v.matching_year IS NULL
         AND v.matching_date IS NOT NULL
         AND b.company    =  v.company
         AND TRUNC(v.matching_date) BETWEEN b.date_from AND b.date_until;
BEGIN
   OPEN get_records;
   FETCH get_records INTO company_, matching_date_, objid_, accounting_year_;
   WHILE (get_records%FOUND) LOOP 
      --Do_Something...;
      FETCH get_records INTO company_, matching_date_, objid_, accounting_year_;
   END LOOP;
   CLOSE get_records;
END Avoid_Nested_Loop;