Re: Incorrect Migrated/Chained rows...

You don't quote an Oracle version - and oddities like this can be highly version dependent.

Was there any difference between the table parameters you used for the first copy and the second copy ? Was the pctfree zero, was it unrecoverable ?

It is possible that the CTAS did something that overestimated the number of rows that could get into a block, and then had to correct on the fly. (direct path load had a similar problem a long time ago).

One minor detail - in your SQL to check row lengths nvl(vsize(col),1) would probably be a better approximation, as most columns will have an extra one byte for the length. (trailing nulls being one exception, and columns longer than about 250 bytes which use a 3-byte length).

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland  http://www.index.is/oracleday.php
June  2004      UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"MAK" <maks70_at_comcast.net> wrote in message
news:b7178504.0404261631.76c73a1d_at_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....