Fix Chained Rows in Oracle Database

oracleoracle-12crow

A Oracle 12c Database has a table with Chained rows:

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','CLOB','BLOB','NCLOB')
 )
and
chain_cnt > 0
order by chain_cnt desc
;
RESULT:
C1  C2  C3  C4  C5  C6  C7  C8
SCHEMA_XY   PERSON  10      662 98.930  85.872  1

I understand that this table has 85.872 % Chained rows. I tried:

alter table SCHEMA_XY.PERSON move;

alter table SCHEMA_XY.PERSON move pctfre 30;

SELECT 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild online ;'FROM DBA_INDEXES where TABLE_NAME='PERSON';

Chained Rows remain high. The table has 250 columns mostly varchar2 and number. How can I fix it?

Best Answer

You have a lot of columns and a high average row length (662). Maybe you even have rows longer than the block size.

With long rows, it is inevitable to have chained rows. Example with 8K block size:

SQL>  create table t1(c1 char(2000 byte), c2 char(2000 byte), c3 char(2000 byte), c4 char(2000 byte), c5 char(2000 byte));

Table created.

SQL> insert into t1 select 'c1', 'c2', 'c3', 'c4', 'c5' from dual connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL>  exec dbms_stats.gather_table_stats(user, 'T1');

PL/SQL procedure successfully completed.

SQL> select chain_cnt from user_tables where table_name = 'T1';

 CHAIN_CNT
----------
         0

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> select chain_cnt from user_tables where table_name = 'T1';

 CHAIN_CNT
----------
     10000

SQL> alter table t1 move;

Table altered.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> select chain_cnt from user_tables where table_name = 'T1';

 CHAIN_CNT
----------
     10000

Also make sure you run ANALYZE TABLE, because that is what updates CHAIN_CNT.

Related Question