Hi All,
I am on Oracle version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
I would like to know if we need to gather stats on table after index creation? Does creation of index force system to gather statistics on table automatically? I tested this by looking at dba_tab_statistics.
I created a table with 100000 rows and looked at dba_tab_statistics - confirmed that number of rows, no of blocks and row length was populated. I then created index and again checked dba_tab_statistics. I could not see any difference.
I then used DBMS_STATS.GATHER_TABLE_STATS and rechecked dba_tab_statistics. I could not see any difference in all above 3 test cases. Am I missing something here? My question is: do we need to run DBMS_STATS.GATHER_TABLE_STATS after index creation? I know index creation will gather index stats but does gathering index stats also gather table stats? please clarify with dba_tab_statistics as proof of concept.
Thanks
kumar