If you have lots of tables -- and you do not know which may or may not contain chained rows, you'll need to analyze them all to discover which do and how many they have.

In order to discover which have chained rows, an analyze will be needed -- or, a SELECT COUNT() can be used if you don't want to analyze. A SELECT COUNT can be used to tell you if you have lots of chained/migrated rows in a table. We can do this by watching the statistic 'table fetch continued row'. You might even combine this with SAMPLE to only look at some percentage of the table.

Consider this small example. It will use a SELECT to determine the amount of chaining.


ops$tkyte@ORA8I.WORLD> create table t1
( a char(2000) default '*',
2 b char(2000) default '*',
3 c char(2000) default '*',
4 d char(2000) default '*',
5 e char(2000) default '*' );

Table created.

That is a table where if a,b,c,d,e have values -- the row will be chained as the rowsize is 10,000 bytes which is bigger then my 8k blocksize

ops$tkyte@ORA8I.WORLD> create table t2 ( a varchar2(4000),
2 b varchar2(4000),
3 c varchar2(4000) );

Table created.

this is a table that could be chained, if we put 4000 bytes into each of a,b,c

ops$tkyte@ORA8I.WORLD> insert into t1 values ( 'a', 'b', 'c', 'd', 'e' );
ops$tkyte@ORA8I.WORLD> insert into t1 values ( 'a', 'b', 'c', 'd', 'e' );
ops$tkyte@ORA8I.WORLD> insert into t1 values ( 'a', 'b', 'c', 'd', 'e' );
ops$tkyte@ORA8I.WORLD> insert into t1 values ( 'a', 'b', 'c', 'd', 'e' );
ops$tkyte@ORA8I.WORLD> insert into t1 values ( 'a', 'b', 'c', 'd', 'e' );

ops$tkyte@ORA8I.WORLD> insert into t2 values ( 'a', 'b', 'c' );
ops$tkyte@ORA8I.WORLD> insert into t2 values ( 'a', 'b', 'c' );
ops$tkyte@ORA8I.WORLD> insert into t2 values ( 'a', 'b', 'c' );
ops$tkyte@ORA8I.WORLD> insert into t2 values ( 'a', 'b', 'c' );
ops$tkyte@ORA8I.WORLD> insert into t2 values ( 'a', 'b', 'c' );

So, we put 5 rows into each... Now we are ready to test. We'll start by using COLUMN VALUE NEW_VAL V to let us save the starting "table fetch continued row" value in our session

ops$tkyte@ORA8I.WORLD> column value new_val V

ops$tkyte@ORA8I.WORLD> select b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'table fetch continued row'
5 /

VALUE
----------
50

Now, to show how to do the count correctly, I'll start by doing it wrong! I'll select the count of the first column A:

ops$tkyte@ORA8I.WORLD> select count( a ) from t1;

COUNT(A)
----------
5

ops$tkyte@ORA8I.WORLD> select b.value-&V
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'table fetch continued row'
5 /
old 1: select b.value-&V
new 1: select b.value- 50

B.VALUE-50
----------
0

According to that, we did NO 'table fetch continued row'. That makes sense because A would be expected to be on the first block of the row. We never fetched the rest of the row -- we didn't need to (this also shows why you would want to put your "stable" columns that are frequently fetched FIRST in the create table -- put the columns that will cause chaining LAST. If you fetch the stable columns like dates, ids and such freqently and don't fetch the "big" columns that cause chaining as often -- chaining won't affect you as much)

So, lets see how to measure this correctly now:

ops$tkyte@ORA8I.WORLD> select b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'table fetch continued row'
5 /

VALUE
----------
50

ops$tkyte@ORA8I.WORLD> select count( e ) from t1;

COUNT(E)
----------
5

ops$tkyte@ORA8I.WORLD> select b.value-&V
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'table fetch continued row'
5 /
old 1: select b.value-&V
new 1: select b.value- 50

B.VALUE-50
----------
5

There we go -- this shows that we did 5 table fetch continued rows in order to count the E column. This is what we were expecting. What you need to do is count the LAST column in the table -- if the row is chained, we will need to fetch the continued row to get that column.

Now lets try our unchained table:

ops$tkyte@ORA8I.WORLD> select b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'table fetch continued row'
5 /

VALUE
----------
55

ops$tkyte@ORA8I.WORLD> select count( c ) from t2;

COUNT(C)
----------
5

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> select b.value-&V
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'table fetch continued row'
5 /
old 1: select b.value-&V
new 1: select b.value- 55

B.VALUE-55
----------
0

Ok, it has no chaining yet, as expected. We'll update to force some and check again:

ops$tkyte@ORA8I.WORLD> update t2 set a = rpad('*',4000,'*'),
2 b = rpad('*',4000,'*'),
3 c = rpad('*',4000,'*')
4 /

5 rows updated.

ops$tkyte@ORA8I.WORLD> select b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'table fetch continued row'
5 /

VALUE
----------
55

ops$tkyte@ORA8I.WORLD> select count( c ) from t2;

COUNT(C)
----------
5

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> select b.value-&V
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'table fetch continued row'
5 /
old 1: select b.value-&V
new 1: select b.value- 55

B.VALUE-55
----------
5


That shows that the update cause the 5 rows we had to chain (they became 12,000 bytes in length, they have to be chained).



Now, this also points out something interesting -- if your row size is close to your blocksize OR you have a long/long raw, you will have chained rows and you will not be able to get rid of them by definition. Don't even try, it won't be possible!