oracle-databasedatabase-cursorbulk-collect

Difference between bulk collect and cursor


I wonder what is the difference between using a bulk collect and then loop on the result and using a cursor. And subquestion : Are there cases where the only option is a cursor? Thanks


Solution

  • Oracle implicitly optimizes PL/SQL for-cursor-loop to use fetches by 100 rows, so it's similar to bulk collect limit 100.

    Simple example:

    SQL> begin
      2      for r in (select/*+findme*/ level n from dual connect by level<=100) loop
      3        exit;
      4      end loop;
      5  end;
      6  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select fetches, rows_processed,sql_text
      2  from v$sql
      3  where lower(sql_text) like 'select/*+findme*/%';
    
       FETCHES ROWS_PROCESSED SQL_TEXT
    ---------- -------------- ----------------------------------------------------------
             1            100 SELECT/*+findme*/ LEVEL N FROM DUAL CONNECT BY LEVEL<=100
    
    1 row selected.
    

    NB: Such optimisation works only if plsql_optmize_level parameter>=2 (default=2). In case of plsql_optmize_level < 2, for-loop fetches by 1 row. You can try to change it and check the difference.

    PS. + You can't use a cursor for-loop with dynamic sql