Incorrect Migrated/Chained rows...

From: MAK <maks70_at_comcast.net>
Date: 26 Apr 2004 17:31:50 -0700
Message-ID: <b7178504.0404261631.76c73a1d@posting.google.com>

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.... Received on Mon Apr 26 2004 - 19:31:50 CDT