RE: CHAINED ROWS

From: SARKAR, Samir <Samir.SARKAR_at_nottingham.sema.slb.com>
Date: Wed, 09 Jan 2002 02:13:00 -0800
Message-ID: <F001.003EACEC.20020109015654@fatcity.com>

Thanks Jacques.......forgot
about the Index-organized tables.

Samir

Samir Sarkar
Oracle DBA - Lennon
Team SchlumbergerSema
Email :
samir.sarkar_at_nottingham.sema.slb.com 
samir.sarkar_at_sema.co.uk Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418
Ext. 76217 Fax
: +44 (0) 115 - 957
6018

-----Original Message-----From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_quest.com]Sent: 08 January 2002 18:56To: Multiple recipients of list ORACLE-LSubject: 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 [<A
 href="mailto:Samir.SARKAR_at_nottingham.sema.slb.com">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; <FONT
 size=2> 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>; <FONT

 size=2>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 [<A href="mailto:bunyamink_at_havelsan.com.tr">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 ?

This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema.
If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited.

If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.

Received on Wed Jan 09 2002 - 04:13:00 CST