|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'
3) I got all the migrated/chained rows in following temp table.
create table temp_s_contact_03
pctfree 0 pctused 5
select * from s_contact
where rowid in ( select head_rowid from chained_rows where table_name
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
5) select count(*) from chained_rows where table_name =
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 =
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)
( 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
Thanks in advance....