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.

 

 

Advertisements
This entry was posted in Performance Tunning, SQL Scripts For DBA and tagged , , , , , , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s