Cursor performance : Explicit Cursor « Cursor « Oracle PL / SQL






Cursor performance

  
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>

   
    
  








Related examples in the same category

1.Explicit Cursor Demo
2.Implicit and Explicit Cursors
3.an explicit cursor that selects data
4.An explicit cursor fetch loop.
5.Use cursor to store the row count
6.Column value indexed cursor
7.Combine for loop and if statement to check the value in cursor
8.Delete from table where current of cursor
9.If statement and single piece value in cursor
10.Use explicit cursor to fetch and store value to number variable
11.Write an explicit cursor in a FOR loop and use the data