Mega Code Archive
Cursor performance
<![CDATA[
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> drop table myTable;
Table dropped.
SQL> drop table myTable2;
Table dropped.
SQL>
]]>