Create index and check the Execution Plan : Index STATISTICS « Index « Oracle PL / SQL

1. Oracle PL / SQL
2. Index
3. Index STATISTICS

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>

 

Related examples in the same category