Cursor performance : Introduction « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. Introduction

SQL>
SQL>
SQL> create table myTable as select * from all_objects;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats( user, 'myTable' );

PL/SQL procedure successfully completed.

SQL>
SQL> create table myTable2 as select * from myTable;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats( user, 'myTable2' );

PL/SQL procedure successfully completed.

SQL>
SQL> set timing on
SQL>
SQL> update myTable set object_name = lower(object_name);

12599 rows updated.

Elapsed: 00:00:00.60
SQL> declare
  2      type ridArray is table of rowid;
  3      type vcArray is table of myTable2.object_name%type;
  4
  5      l_rids  ridArray;
  6      l_names vcArray;
  7
  8      cursor c is select rowid, object_name from myTable2;
  9  begin
 10      open c;
 11      loop
 12          fetch c bulk collect into l_rids, l_names LIMIT 100;
 13          forall i in 1 .. l_rids.count
 14              update myTable2 set object_name = lower(l_names(i)) where rowid = l_rids(i);
 15          commit;
 16          exit when c%notfound;
 17      end loop;
 18      close c;
 19  end;
 20  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.05
SQL>
SQL> set timing off
SQL>
SQL>
SQL> drop table myTable;

Table dropped.

SQL> drop table myTable2;

Table dropped.

SQL>