CREATE INDEX COMPUTE STATISTICS

“COMPUTE STATISTICS” is the default behavior for “CREATE INDEX”.

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.