Tip : How to eliminate migrated or chained rows ?

To eliminate migrated or chained rows

You have to

           (i) Create  chained rows table;
          (ii) Analyze table  < table_name> list chained rows;
           (iii) Eliminate the migrated or chained rows either by export/import or insert/delete  method.


Pratical example.

Let us consider a table samtest  in  scott user.  Now we will determine rowchaining in it and  the way to eliminiate it.


SQL> connet scott/tiger

Let us consider your oracle home is e:\ora816

SQL> @ e:\ora816\rdbms\admin\utlchain.sql

Above will create a chained_rows table in scott  user.

SQL> analyze table samtest list chained rows;

Above will create populate a chained_rows table in case of chaining.


SQL > select owner_name,table_name,head_rowid from chained_rows;

Above will displays the rowid of migrated or chained rows ,if any.

If you found chaining  eliminate is by export/import or insert/delete method.
I am discussing here insert delete method.

SQL > Prompt create a intermediate table first
SQL > create table  samtest_tab2
           as
             select * from samtest
             where rowid in  (select head_rowid from chained_rows
                                      where table_name = 'samtest');

Above will create a table samtest_tab2 with all the chained rows.

SQL> Prompt Delete the chained rows from the original table
SQL> delete from samtest
          where rowid in (select head_rowid from chained_rows
                                   where table_name='samtest');

SQL> Prompt  insert the rows from the intermediate table samtest_tab2  to samtest
SQL> insert into samtest  select * from samtest_tab2;

SQL> prompt drop the intermediate table
SQL> drop table samtest_tab2;
SQL> DELETE * from chained_rows where table_name='samtest';
SQL>Commit;

This way you can eliminate row chaining . To avoid row-chaining  set pctfree and pctused
correctly.  The  row moves to another block when it is not able to fit in the same block.  If you have more update on the table set pctfree more to make room in the block for increase in the size of the row after updation.

End of Tip	Welcome to send comments or feedback at Wadhwa_s@Hotmail.com
Sameer Wadhwa	Copyright © Oracle Techniques All Rights Reserved