Remove Chained Rows from a Table

Finding and repairing chained rows is an important part of Oracle administration. When an Oracle rows expands, it sometimes chains onto multiple data blocks. Excessive row chaining can cause a dramatic increase in disk I/O because several I/O’s are required to fetch the block instead of one single I/O.

Of course, row chaining can be prevented by setting the PCTFREE storage parameter high enough to keep space on each data block for row expansion.

In cases where the data columns contain RAW and LONG RAW columns, row chaining may be unavoidable because the average row length may exceed the data block size. That is why the query below filters out tables with RAW data types.

set ECHO off

ACCEPT chaintabl PROMPT 'Enter the table with chained rows: '

drop table chaintemp;
drop table chained_rows;

start $ORACLE_HOME/rdbms/admin/utlchain

set ECHO OFF

REM ANALYZE table to locate chained rows
analyze table &chaintabl
list chained rows into chained_rows;

REM CREATE Temporary table with the chained rows
create table chaintemp as
select *
from &chaintabl
where rowid in (select head_rowid
from chained_rows);

REM DELETE the chained rows from the original table
delete from &chaintabl where rowid in (select head_rowid from chained_rows);

REM INSERT the formerly chained rows back into table
insert into &chaintabl
select *
from chaintemp;

REM DROP the temporary table
drop table chaintemp;