Mega Code Archive

Create index and check the Execution Plan

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>