Oracle : Verify index access without creating a real index

Index creation is a resource consuming operation. Creating index on a very big table is waste of resource and time, if it finally appears to be not used by query.

There is a simple way to understand if an index can be useful before creating the real index.

[ SYS @ DDB ] create table test.t_obj as select object_id,object_name from dba_objects;

Table created.

[ SYS @ ddb ] exec dbms_stats.gather_table_stats('TEST','T_OBJ');

PL/SQL procedure successfully completed.

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------

Plan hash value: 172510092

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    29 |   105   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_OBJ |     1 |    29 |   105   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

13 rows selected.

[ SYS @ ddb ] create index test.t_idx on test.t_obj(object_id) nosegment;

Index created.

[ SYS @ ddb ] select owner,table_name,index_name from dba_indexes where table_name='T_OBJ' and owner='TEST';

no rows selected

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------

Plan hash value: 172510092

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    29 |   105   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_OBJ |     1 |    29 |   105   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

13 rows selected.

[ SYS @ ddb ] alter session set "_use_nosegment_indexes"=true;

Session altered.

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------

Plan hash value: 1850536683

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    29 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ |     1 |    29 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)

14 rows selected.

[ SYS @ ddb ]

But the execution plan above shows the index can be used if we create it. The nosgment index is not a real index.

Now as we undersrtood index can be used, we can create a real index using the below statement without "nosegment" key word.


[ SYS @ ddb ] alter session set "_use_nosegment_indexes"=false;

Session altered.

[ SYS @ ddb ] drop index test.t_idx;

Index dropped.

[ SYS @ ddb ] create index test.t_idx on test.t_obj(object_id);

Index created.

[ SYS @ ddb ] select owner,table_name,index_name from dba_indexes where table_name='T_OBJ' and owner='TEST';

OWNER                          TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           T_OBJ                          T_IDX

[ SYS @ ddb ] explain plan for select * from test.t_obj where object_id=100;

Explained.

[ SYS @ ddb ] select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------

Plan hash value: 1850536683

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    29 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJ |     1 |    29 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)

14 rows selected.


[ SYS @ ddb ]