When you get statistics of an index with null values Oracle lowers the num_row column of index with number of nulls and the CBO start to use value index selectivity with filters for value of index_selectivity -which has the effect of losing the cost of the leaf_block accesses (can be seen by 10053 trace file).

below is the writers note about the occurance of this case is randomly, but i couldn’t create the case about the wrong behaviour of gather_table_stats package with cascade option.

“The underlying problem is that the call to dbms_stats.gather_table_stats(), with the cascade option set to true, sometimes fails to update the index statistics. Counterintuitively, when dbms_stats gets it wrong, the execution plan comes up with the right cost (because the value of user_indexes.num_rows stays the same as user_tables.num_rows), and when dbms_stats gets it right, the execution plan comes up with the wrong costs because user_indexes.num_rows is (correctly) recorded as being less than user_tables.num_rows. ”

I’ve been exploring for a little bit for any high quality articles or blog posts on this sort
of space . Exploring in Yahoo I at last stumbled upon this
site. Studying this information So i am happy to exhibit that I
have a very good uncanny feeling I found out exactly what
I needed. I so much unquestionably will make sure to do not disregard this web site and give it a glance on a relentless basis.