0

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?

1

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.

0

Chained Rows is caused by rows that are too wide to fit it a (% of a) Data Block. Easiest way to create chained rows is to have lots of columns.

Your choices are:

  • accept the fact that a 250 column table has a high probability of having chained rows
  • rework your table design

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.