Compute Oracle Index Statistics

Many times, we forget to gather index statistics after creating the index. Optimizer will not use the index effectively if index does not have statistics.

Prior to Oracle9i, we have to issue two commands for creating index and gathering index statistics.

SQL> create index idx on test(object_name);

Index created.

SQL> exec dbms_stats.gather_index_stats(null, 'IDX');

PL/SQL procedure successfully completed.

SQL>

Starting in Oracle 9i, we have a compute statistics clause. We can both creating index and gathering index statistics in one command.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table test as select * from user_objects;

Table created.

SQL> create index idx on test(object_name);

Index created.

SQL> select table_name, num_rows, last_analyzed
2 from user_tables
3 where table_name ='TEST'
4 /

TABLE_NAME NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
TEST

SQL> drop index idx;

Index dropped.

SQL> create index idx on test(object_name) compute statistics;

Index created.

SQL> select table_name, num_rows, last_analyzed
2 from user_tables
3 where table_name ='TEST'
4 /

TABLE_NAME NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
TEST 184 31-OCT-09

SQL>

In Oracle10g, we do not need to use compute statistics clause at all. Oracle gather statistics while creating index automatically.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create index idx on test(object_name);

Index created.

SQL> select num_rows, last_analyzed from user_ind_statistics where index_name ='IDX';

NUM_ROWS LAST_ANAL
---------- ---------
50484 30-OCT-09

SQL>