DBMS_STATS Does Not Count Chained Rows

I tested this example on Oracle 11.2. First you need a table with some chained rows. An easy way to do this is to create a table with pctfree = 0 then add lots of data to each row:

SQL> create table chaining_test

  2  pctfree 0

  3  as select object_id

  4  from dba_objects

  5  where rownum < 5000

  6  /

Table created.

SQL> alter table chaining_test add

  2  (owner varchar2(30),

  3   object_name varchar2(128))

  4  /

Table altered.

SQL> update chaining_test x set owner =

  2  (select owner from dba_objects

  3   where object_id = x.object_id)

  4  /

4999 rows updated.

SQL> update chaining_test x set object_name =

  2  (select object_name from dba_objects

  3   where object_id = x.object_id)

  4  /

4999 rows updated.

SQL>

You can still use analyze to count the chained rows:

SQL> analyze table chaining_test compute statistics

  2  /

Table analyzed.

SQL> select chain_cnt from user_tables

  2  where table_name = 'CHAINING_TEST'

  3  /

CHAIN_CNT

----------

      4906

SQL>

If you then run dbms_stats.gather_table_stats against the table, the chain_cnt is not updated although, in this case, it makes no difference as the number of chained rows has not changed:

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'chaining_test');

PL/SQL procedure successfully completed.

SQL> select chain_cnt from user_tables

  2  where table_name = 'CHAINING_TEST'

  3  /

CHAIN_CNT

----------

      4906

SQL>

However, if you delete the statistics:

SQL> analyze table chaining_test delete statistics

  2  /

Table analyzed.

SQL>

... then run dbms_stats.gather_table_stats against it again:

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'chaining_test');

PL/SQL procedure successfully completed.

SQL>

The chain_cnt is left as zero:

SQL> select chain_cnt from user_tables

  2  where table_name = 'CHAINING_TEST'

  3  /

CHAIN_CNT

----------

         0

SQL>