Friday, May 18, 2012

DBMS_STATS Does Not Count Chained Rows



I read about this on page 538 of the book above and tested it 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>

No comments: