Re: Reduce row chaining - when/how ?

analyze table TNAME list chained rows INTO another_tname;

See the file $ORACLE_HOME/rdbms/admin/utlchain.sql for the schema definition of another table.

Basically, into that another_table you will get the downer/table, ROWID and some other stuff for each row that is chained.

You can then:

create table tmp_tname
as
select * from TNAME
where rowid in ( select head_rowid from another_tname );

delete from TNAME where rowid in ( select head_rowid from another_tname );

insert into TNAME select * from tmp_tname;

drop table tmp_tname;

To unchain the rows.

NOTE: Long columns are almost always 'chained' rows (since they don't fit in a block). The above won't work on tables with longs.

If your avg row size > block size, you will have lots of chained rows.

On 8 Aug 1996 23:16:07 GMT, Danny Roosens <roosens> wrote:

>Hi,
>
>I was wondering when a DBA should do something about row chaining :
>
>one of my three biggestr tables jas 5% chained rows.
>
>At wich treshold should I act.
>
>
>
>To reduce it I see 3possiblities :
>
>1) * lock table for update
> * copy all data to a tempory table
> * delete all data
> * insert from temp-table
> * commit;
>
> ? will the lock stay ones all data is deleted ??
>
>2) export / import --> not possible since the database haa tow remain up , the table has to stay actif.
>
>3) same as 1 but only for the chained rows
>
>Does anyone automates his search for chained rows, + an automated solution to 'unchain' ??
>

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Aug 09 1996 - 00:00:00 CEST