RE: CHAINED ROWS

If your chained rows are inside an Index-Organized table, be sure to read the Oracle manual on the "Analyze" command. There are two versions of the "chained-row" table, for index-organized tables you should create the "chained-row" table with universal rowids (head_rowid has datatype urowid). The "chained-row" table with universal rowids is the one named "...1.sql", i.e. has a 1 at the end of the file name.

-----Original Message-----
From: SARKAR, Samir [mailto:Samir.SARKAR_at_nottingham.sema.slb.com]

If Oracle 8i is ur current version, run the utlchain1.sql script available in ur ORACLE_HOME/rdbms/admin directory. This will create the chained_rows table for u. Now analyze the affected table using the command :
 

analyze table <table_name> list chained rows into chained_rows;
 

Now when u select from the chained_rows table, u will get the rowid of all the rows that r chained in the table as head_rowid.
 

The best way to deal with chained rows is to export the table's data, rebuild the table with a higher pctfree and import the data back.
 

Otherwise, copy the chained rows into a temporary work table in the following way :
 

create table <temp_table_name> as
select * from <table_name> where rowid in (select head_rowid from chained_rows where table_name = '<table_name>'
 and owner_name = '<owner_name>');
 

Then delete the chained rows from the main table in the following way :
 
 delete from <table_name>
 where rowid in

(select head_rowid from chained_rows where table_name = '<table_name>'
 and owner_name = '<owner_name>');
 

Next, re-insert the chained rows into the table :
 

insert into table_name
    select * from <temp_table_name>;
 

Commit ur work. Remember to disable any foreign key constraints during the deletion stage and re-enable them again after re-insertion of the rows.
This should eliminate most of ur chained rows.

        

-----Original Message-----
From: Bunyamin K. Karadeniz [mailto:bunyamink_at_havelsan.com.tr]

I have seen that There are some number of  chained rows in several tables of a schema in my database . What is it done in such a situation ? Received on Tue Jan 08 2002 - 13:19:26 CST