Steps to Eliminate Row Chaining & Row Migration

Steps to Eliminate Row Chaining & Row Migration

Eliminating Migrated or Chained Rows in a Table

You can use the information in the CHAINED_ROWS table to reduce or eliminate migrated and chained rows in an existing table. Use the following procedure.

1.Use the ANALYZE statement to collect information about migrated and chained rows.
ANALYZE TABLE order_hist LIST CHAINED ROWS;

2.Query the output table:
SELECT *
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';

OWNER_NAME  TABLE_NAME  CLUST... HEAD_ROWID          TIMESTAMP
----------  ----------  -----... ------------------  ---------
SCOTT       ORDER_HIST       ... AAAAluAAHAAAAA1AAA  04-MAR-96
SCOTT       ORDER_HIST       ... AAAAluAAHAAAAA1AAB  04-MAR-96
SCOTT       ORDER_HIST       ... AAAAluAAHAAAAA1AAC  04-MAR-96

The output lists all rows that are either migrated or chained.

3.If the output table shows that you have many migrated or chained rows, then you can eliminate migrated rows by continuing through the following steps:

4.Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:

CREATE TABLE int_order_hist
AS SELECT *
FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');

5.Delete the migrated and chained rows from the existing table:

DELETE FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');

6.Insert the rows of the intermediate table into the existing table:

INSERT INTO order_hist
SELECT *
FROM int_order_hist;

7.Drop the intermediate table:
DROP TABLE int_order_history;

8. Delete the information collected in step 1 from the output table:
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';

9. Use the ANALYZE statement again, and query the output table.

10. Any rows that appear in the output table are chained. You can eliminate chained rows only by increasing your data block size. It might not be possible to avoid chaining in all situations. Chaining is often unavoidable with tables that have a LONG column or long CHAR or VARCHAR2 columns.
-----------------------------------------------------------------------

select owner, table_name, pct_free, pct_used , avg_row_len, num_rows, chain_cnt, chain_cnt/num_rows from dba_tables
where owner not in ('SYS','SYSTEM')
and
table_name not in (select table_name from dba_tab_columns where data_type in ('RAW','LONG RAW'))
and chain_cnt > 0
order by chain_cnt desc;


spool chain.lst;
set pages 9999;
column c1 heading "Owner"   format a9;
column c2 heading "Table"   format a12;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows"    format 999,999,999;
column c7 heading "Chains"  format 999,999,999;
column c8 heading "Pct"     format .99;

set heading off;
select 'Tables with migrated/chained rows and no RAW columns.' from dual;
set heading on;

select
owner                   c1,
table_name        c2,
pct_free                c3,
pct_used                c4,
avg_row_len       c5,
num_rows          c6,
chain_cnt               c7,
chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
(select table_name from dba_tab_columns
where
data_type in ('RAW','LONG RAW')
)
and
chain_cnt > 0
order by chain_cnt desc;




SELECT owner, table_name,
NVL(chain_cnt,0) "Chained Rows"
FROM all_tables
WHERE owner NOT IN ('SYS', 'SYSTEM')
AND NVL(chain_cnt,0) > 0
ORDER BY owner, table_name;

 

 

Thanks

Srini