Execution Plan : autotrace « SQL Plus « Oracle PL / SQL






  1. Oracle PL / SQL
  2. SQL Plus
  3. autotrace

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

1.autotrace command
2.Autotrace on and off
3.Autotrace lower text function
4.Autotrace running total
5.Autotrace a query on a huge table
6.Autotrace a large table
7.Autotrace a query with group clause
8.autotrace a nested query
9.set autotrace on explain for every single statement
10.set autotrace traceonly explain for bitmap index
11.set autotrace traceonly explain, and condition
12.set autotrace traceonly statistics
13.set autotrace traceonly statistics for 'select * from tableName'
14.set autotrace traceonly
15.AUTOTRACE exists (subquery)
16.AUTOTRACE table joining
17.AUTOTRACE table joining and aggregate function
18.autotrace ansi full outer join
19.autotrace count(*)
20.autotrace ctxsys.context index
21.autotrace merge command
22.autotrace table with/without an index