Hi
I was doing an exercise to create migrated/chained rows for a table (ROW_CHAIN_MIG). [using 11.2.0.1]
After inserting/updating the rows in the table I tried collecting statistics for the table to check on the USER_TABLES.CHAIN_CNT
using the DBMS_STATS.GATHER_TABLE_STATS as:
----------------------------
SQL> exec dbms_stats.gather_table_stats( -
> ownname => 'SYS', -
> tabname => 'ROW_CHAIN_MIG');
SQL> select chain_cnt from user_tables where table_name='ROW_CHAIN_MIG';
CHAIN_CNT
----------
0
----------------------------
But it returned Zero count for chained rows.
When I did an ANALYZE TABLE .. COMPUTE STATISTICS, it returned 3 as count of chained rows
----------------------------
SQL> analyze table row_chain_mig where compute statistics;
SQL> select chain_cnt from user_tables where table_name='ROW_CHAIN_MIG';
CHAIN_CNT
----------
3
----------------------------
Is this expected behaviour out of the DBMS_STATS.GATHER_TABLE_STATS ? (i.e. it doesnt collect the CHAIN_CNT statistic)
My understanding was that DBMS_STATS collects all the stats collected by ANALYZE + more stats needed by CBO.
OR was there something missing from the DBMS_STATS run that this stat (CHAIN_CNT) was missed.
TIA.