CREATE INDEX COMPUTE STATISTICS

When creating an index, “COMPUTE STATISTICS” option is no longer required for 10g onwards databases. Here is a quick demonstration:

Create a table and insert a couple of sample records:

SQL> create table test ( id number, name varchar(20));
Table created.

SQL> insert into test values (1,'james1');
1 row created.

SQL> insert into test values (2,'james2');
1 row created.

SQL> insert into test values (3, 'james3');
1 row created.

SQL> commit;
Commit complete.

No statistics for newly created table:

SQL> select table_name,num_rows, last_analyzed 
       from user_tables 
      where table_name='TEST';

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

Create a new index :

SQL> create index ind_test_id on test(id);
Index created.

Check the index statistics:

SQL> select INDEX_NAME,TABLE_NAME,NUM_ROWS,LAST_ANALYZED 
      from user_indexes 
     where INDEX_NAME='IND_TEST_ID';

INDEX_NAME    TABLE_NAME   NUM_ROWS  LAST_ANAL
-----------   ------------ --------- --------
IND_TEST_ID    TEST         3        05-JAN-17

From the example, we can see even there is no statics for the table, but the statistics is collected for the index when it is created.