Do you think you know how Oracle records access to migrated or chained rows ? You may need to check your theories. Here’s a little demonstration that may amuse you. It uses an 8KB block size, a locally managed tablespaces (LMT) with uniform extent sizes of 1MB, and freelist management rather than automatic segment space management (ASSM).
create table t1 as with generator as ( select --+ materialize rownum id from dual connect by rownum <= 10000 ) select cast(null as varchar2(100)) padding1, lpad(rownum,50,' ') padding2, rownum id from generator v1, generator v2 where rownum <= 10000 ; update t1 set padding1 = rpad('x',100); commit; -- collect statistics (compute, no histograms)
Note that I’ve left the value for pctfree to its default of 10 – so the update of padding1 from null to rpad(‘x’,100) makes all the rows grow from about 60 bytes to about 160 bytes, which means that some of them are going to have to migrate. (Reminder: migration means the whole row is moved to another block, and a 9-byte row stub is left behind that consiss of the flag byte, the lock byte, the column count (which will be zero) and a pointer – in the form of a restricted rowid – to the new row location.)
While running the following three lines of SQL, I collected session statistics (v$sesstat) so that I could calculate the work done by each statement:
select count(id) from t1; create index t1_i1 on t1(id); alter index t1_i1 rebuild online;
In all three cases the optimizer chose a full tablescan to execute the statement. The statistics I want to show you are “session logical reads” and “table fetch continued row”, and they look like this:
session logical reads 261 table fetch continued row 0
session logical reads 350 table fetch continued row 0
session logical reads 7,162 table fetch continued row 162
So – a few questions:
How come I don’t appear to have any continued (which means chained or migrated) rows when I count the rows or create the index, but I have continued rows when I rebuild the index ?
How many migrated rows do I actually have ?
Which version of Oracle am I using for this demonstration ?
And a few more for the adventurous:
Is there a fairly easy way to find out roughly how many migrated rows I have – and what problems will other people see with your suggestion ?
What do you think would happen if I used the “online” option for the create index ?
What if I didn’t use the “online” option for the index rebuild ?