No stats after creating index

Starting from 10gR2, i believe (as i haven’t worked much on 10gR1 version) Oracle started computing/gathering statistics  automatically when we create index.

SQL> create table latitude (a number, b number);

Table created.

SQL> insert into latitude values (1,2);

1 row created.
SQL> insert into latitude values (1,3);

1 row created.

SQL> insert into latitude values (1,4);

1 row created.

SQL> commit;

SQL> exec dbms_stats.gather_table_stats('ANAND','LATITUDE');

PL/SQL procedure successfully completed.

Now create an index —

SQL> create index lat_indx on latitude(a);

Index created.
SQL> @index
Enter value for owner: anand
Enter value for table_name: latitude

TABLE_OWNER          TABLE_NAME                     INDEX_OWNER          INDEX_NAME                  TABLESPACE_NAME        NUM_ROWS      CLUST STATUS   INDEX_TYPE
-------------------- ------------------------------ -------------------- ------------------------------ -------------------- ---------- ---------- -------- ------------
ANAND                LATITUDE                       ANAND                LAT_INDX                    TEST                             3          1 VALID    NORMAL

As when we create index, oracle internally runs something similar to “compute statistics” command and we see values for NUM_ROWS column.

Now, in case we create an index and don’t see stats value what could be reason for it.

SQL> drop index LAT_INDX;

Index dropped.
SQL> create index lat_indx on latitude(a);

Index created.
SQL> @index
Enter value for owner: anand
Enter value for table_name: latitude

TABLE_OWNER          TABLE_NAME                     INDEX_OWNER          INDEX_NAME                  TABLESPACE_NAME        NUM_ROWS      CLUST STATUS   INDEX_TYPE
-------------------- ------------------------------ -------------------- ------------------------------ -------------------- ---------- ---------- -------- ------------
ANAND                LATITUDE                       ANAND                LAT_INDX                    TEST                                          VALID    NORMAL

So, what could be the reason? The reason is , stats on table LATITUDE is locked.

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where table_name='LATITUDE';

OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
ANAND                          LATITUDE                       ALL

As the table stats are locked oracle creates the index but doesn’t gather the stats and even doesn’t show any error.But creating an index with “compute statistics” clause gives an error

SQL> create index lat_b_indx on latitude(b) compute statistics;
create index lat_b_indx on latitude(b) compute statistics
                           *
ERROR at line 1:
ORA-38029: object statistics are locked

In such case, simply unlock the table stats using dbms_stats.unlock_table_stats, create an index, and lock the index back using dbms_stats.lock_table_stats.

While just checking it, if on 11g db we run the explian plan for the create index command, the output shows the estimated size of the index

SQL> create table micro as select * from all_objects;

Table created.

Elapsed: 00:00:07.40
SQL>
SQL>
SQL> explain plan for
  2  create index obj_id_indx on micro(object_id);

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3871790352

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |             | 86500 |  1098K|   339   (1)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| OBJ_ID_INDX |       |       |            |          |
|   2 |   SORT CREATE INDEX    |             | 86500 |  1098K|            |          |
|   3 |    TABLE ACCESS FULL   | MICRO       | 86500 |  1098K|   290   (1)| 00:00:01 |
--------------------------------------------------------------------------------------

Note
-----
   - estimated index size: 3145K bytes

14 rows selected.

Elapsed: 00:00:00.10
SQL>
SQL>
SQL> create index obj_id_indx on micro(object_id);

Index created.

Elapsed: 00:00:00.86

SQL> select owner,segment_name,bytes/1024/1024 from dba_segments where segment_name='OBJ_ID_INDX';

OWNER                          SEGMENT_NAME                                                              BYTES/1024/1024
------------------------------ --------------------------------------------------------------------------------- ---------------
ANAND                          OBJ_ID_INDX                                                                             2

Elapsed: 00:00:00.45
Advertisements

4 thoughts on “No stats after creating index

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