Impact of CHAIN_CNT on execution plan??
I cam across one link from Tom Kyte.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4347359891525
"DBMS_STATS gathers statistics only for cost-based optimization; it does not gather other
statistics. For example, the table statistics gathered by DBMS_STATS include the number
of rows, number of blocks currently containing data, and average row length but not the
number of chained rows, average free space, or number of unused data blocks."
But when we collect stats with gather_table_stats then it collects statistics for chained rows in column chaint_cnt for tables.
My question is does chain_cnt make a difference to oracle optimizer while choosing for a execution plan? My understanding is YES, as more chained rows for table means more Db blocks scan and that might allow optimizer to pick FTS over index scan.