Oracle Database 10g: Analyze Index Vs. Gather_Index_Stats

 One of the difference I noticed about analyze index command and gather_index_stats procedure is that when we use analyze index command it updates the index statistics with number of leaf blocks equals the number of leaf blocks below HWM.

However if we use gather_index_stats statistics shows number of leaf blocks equals number of leaf blocks that actually has the data.

Here is the illustration for the same.

SQL> select owner,index_name,table_name,BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS

from dba_indexes where _name='ID_TICKET_STOCK_MASTER'

OWNER	INDEX_NAME	TABLE_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
AE1T3	STK_UKEY	ID_TICKET_STOCK_MASTER	2	10185	2515695

SQL> analyze index STK_UKEY estimate statistics;

SQL> select owner,index_name,table_name,BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS

from dba_indexes where _name='ID_TICKET_STOCK_MASTER'

OWNER	INDEX_NAME	TABLE_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
AE1T3	STK_UKEY	ID_TICKET_STOCK_MASTER	2	10184	2515448

SQL> EXEC DBMS_STATS.gather_index_stats('AE1T3','STK_UKEY');

PL/SQL procedure successfully completed.

SQL> select owner,index_name,table_name,BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS

from dba_indexes where _name='ID_TICKET_STOCK_MASTER'

OWNER	INDEX_NAME	TABLE_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
AE1T3	STK_UKEY	ID_TICKET_STOCK_MASTER	2	9428	2320245

Conclusion:

Action	INDEX_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
SQL Query	STK_UKEY	2	10185	2515695
analyze index STK_UKEY estimate statistics;	STK_UKEY	2	10184	2515448
DBMS_STATS.gather_index_stats('AE1T3','STK_UKEY');	STK_UKEY	2	9428	2320245

Now if we see the numbers of leaf blocks reported are 9428. This plays quiet a big role for optimizer in creating plans for queries. If we don’t have correct stats for the index, it may lead to expensive explain plans.

So it’s better to use gather_index_stats rather than analyze index.

Hope this helps!!