One of the difference I noticed about analyze index command and gather_index_stats procedure is that when we use analyze index command if 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.

 

Create a table

SQL> create table t1 pctfree 99 pctused 1  
2  as  
3  select rownum id,  
4  trunc(100 * dbms_random.normal) val,  
5  rpad('X',100) padding  
6  from all_objects where rownum <= 10000;

Table created.

I intentionally created table with pctfree as 99 so that each block will have 1 row and it uses 10000 blocks to store 10000 rows.

Create an index

SQL> create index t1_i on t1(val) ;
Index created.

Check the number of leaf blocks

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';

 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
1          21           563

We have around 1600 rows whose val > 100

SQL> select count(*) from t1 where val > 100;
 COUNT(*)
----------      
  1598

Delete rows from table, which will also delete entries from index and some of the leaf blocks will get empty.

SQL> delete from t1 where val > 100;
1598 rows deleted.

Analyze index

SQL> analyze index t1_i validate structure;
Index analyzed.

Check again the number of leaf blocks

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';
 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
 1          21           563

Number of leaf blocks reported are still 21

Gather stats on index

SQL> exec dbms_stats.gather_index_stats('ADVAITD_DBA','T1_I');
PL/SQL procedure successfully completed.

Check the number of leaf blocks again

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';
 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
 1          18           379

Now if we see the number of leaf blocks reported are 18. 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 its better to use gather_index_stats rather than analyze index.

Hope this helps !!