SQL>
SQL> create table indextest as select * from dba_objects
2 where owner in ('OUTLN','PUBLIC','SCOTT','SYS','SYSTEM');
Table created.
SQL>
SQL> analyze table indextest compute statistics;
Table analyzed.
SQL>
SQL> set autotrace trace explain
SQL> select owner, object_name from indextest
2 where object_name = 'DBA_INDEXES';
Execution Plan
----------------------------------------------------------
Plan hash value: 2792531790
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INDEXTEST | 1 | 21 | 36 (3)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBA_INDEXES')
SQL>
SQL> create index indxtest_objname_idx
2 on indextest (object_name);
Index created.
SQL>
SQL> select owner, object_name from indextest
2 where object_name = 'DBA_INDEXES';
Execution Plan
----------------------------------------------------------
Plan hash value: 1012932391
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| INDEXTEST | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDXTEST_OBJNAME_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='DBA_INDEXES')
SQL>
SQL>
SQL> drop table indextest;
Table dropped.
SQL>
SQL>