Incorrect Migrated/Chained rows...

Giganews Newsgroups
Subject: Incorrect Migrated/Chained rows...
Posted by:  MAK (maks…@comcast.net)
Date: 26 Apr 2004

I am trying to reduce nos of migrated/chained rows  from our database.
I did following things to get them identified and corrected. The
database is using 8K block size.

1) analyze table s_contact list chained rows into chained_rows;
2) select count(*) from chained_rows where table_name = 'S_CONTACT'
  count(*)
----------
488080
3) I got all the migrated/chained rows in following temp table.

create table temp_s_contact_03
pctfree 0 pctused 5
inittrans 5
unrecoverable
as
(
select * from s_contact
where rowid in ( select head_rowid from chained_rows where table_name
= 'S_CONTACT'));

Table created.

Wanted to check , if this has reduced any migrated/chained rows. So
ran following things.

4) analyze table temp_s_contact_03 list chained rows into
chained_rows.

5) select count(*) from chained_rows where table_name =
'TEMP_S_CONTACT_03';
  count(*)
----------
  6056

Why I still got 6056 chained/migrated rows? I thought I might have
some chained rows (rows bigger than the block size which is 8K). So I
ran following command to get average row lengh.

6) analyze table temp_s_contact_03 compute statistics ;
7) select avg_row_len, chain_cnt from dba_tables where table_name =
'TEMP_S_CONTACT_03';

AVG_ROW_LEN  CHAIN_CNT
----------- ----------
        565      6056

Its shows average row length is < 8K so most likely all 6056  should
be migrated rows[ Why they became migrated rows with no update
activity??? ]. I ran  following query to get the max , min, avg row
length to confirm.

8) select max(ln),min(ln), avg(ln)
  from
    ( select nvl(vsize(col1),0)+nvl(vsize(col1),0)+...nvl(vsize(col_n),0)
ln from temp_s_contact_03 );

  MAX(LN)    MIN(LN)    AVG(LN)
---------- ---------- ----------
      715        154    258.20

So maximum row length is 715bytes. So if this true then all the
rows(6056) are migrated and not the chained rows. Why do I have
migrated rows with no update activity? Can some one explain?

I also noticed an interesting thing, If I create another table from
temp_s_contact_03 and analyze that table, I got 0 chained rows . Any
clue?

Thanks in advance....

Replies