dbms_stats vs analyze table
Posted on 2014-02-19
This was an interview question asked:
What is the major difference between analyze table versus dbms_stats.gather table stats
I answered Oracle strongly recommends using dbms_stats for gathering statistics.
Suppose there are 100 million of records getting inserted in a table, and there is an index on a column. What would be your option for performance tuning, would it be analyzing table stats ... rebuild index or dbms_stats. gather table stats with Cascade = True..
Which is better, why?
Since there is an index on table, which one would actually be slower. Would setting cascade=True slow down the performance.
My answer: I said using dbms_stats.gather_table_stats with setting Cascade= True would be better.
However, I am not sure about this. Could someone answer..
Quick response is appreciated