Database Reference
In-Depth Information
Now, we'll set up the table
T
to query and modify. Note that we are ordering the data randomly in this table. The
CREATE TABLE AS SELECT
tends to put the rows in the blocks in the order it fetches them from the query. We'll just
scramble the rows up so they are not artificially sorted in any order, randomizing their distribution:
EODA@ORA12CR1> drop table t purge;
Table dropped.
EODA@ORA12CR1> create table t
2 as
3 select *
4 from all_objects
5 order by dbms_random.random;
Table created.
EODA@ORA12CR1> alter table t add constraint t_pk primary key(object_id);
Table altered.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T', cascade=> true );
PL/SQL procedure successfully completed.
And now we are ready to do our modifications:
EODA@ORA12CR1> begin
2 for x in ( select rowid rid from t )
3 loop
4 update t set object_name = lower(object_name) where rowid = x.rid;
5 commit;
6 end loop;
7 end;
8 /
Now, while that PL/SQL block of code is running, we will run a query in another session. That other query will
read table
T
and process each record. It will spend about 1/100 of a second processing each record before fetching the
next (simulated using
DBMS_LOCK.SLEEP(0.01)
). We will use the
FIRST_ROWS
hint in the query to have it use the index
we created to read the rows out of the table via the index sorted by
OBJECT_ID
. Since the data was randomly inserted
into the table, we would tend to query blocks in the table rather randomly. This block will only run for a couple of
seconds before failing:
EODA@ORA12CR1> declare
2 cursor c is
3 select /*+ first_rows */ object_name
4 from t
5 order by object_id;
6
7 l_object_name t.object_name%type;
8 l_rowcnt number := 0;
9 begin
10 open c;
11 loop
12 fetch c into l_object_name;
13 exit when c%notfound;
14 dbms_lock.sleep( 0.01 );
