Testing Different Access Paths : With and Without Index

=> Query a table that has no indexes to let optimizer choose full table scan. 

=> The command "set autotrace traceonly explain statistics" is used to execute the statement and display execution plan and statistics without displaying the actual statement output.

SQL> set lines 1000
SQL> set autotrace traceonly explain statistics
SQL> select * from mysales where prod_id=0;


Execution Plan
----------------------------------------------------------
Plan hash value: 3597614299

-----------------------------------------------------------------------------
| Id  | Operation	  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	    |	402K|	 11M| 40025   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| MYSALES |	402K|	 11M| 40025   (1)| 00:00:02 |
-----------------------------------------------------------------------------

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

   1 - filter("PROD_ID"=0)


Statistics
----------------------------------------------------------
	 47  recursive calls
	  0  db block gets
     141638  consistent gets
      22108  physical reads
	  0  redo size
       1042  bytes sent via SQL*Net to client
	399  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  5  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL> 

=> Notice that the Cost is 40025 and operation performed is TABLE ACCESS FULL

=> Now create an index on the column used in the where clause and run the same statement 

SQL> create index mysales_prodid_idx on mysales(prod_id) nologging;

Index created.

SQL>

SQL> set lines 1000
SQL> set autotrace traceonly explain statistics
SQL> select * from mysales where prod_id=0;

Execution Plan
----------------------------------------------------------
Plan hash value: 732979826

----------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name		 | Rows  | Bytes | Cost (%CPU)| Time	 |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |			 |   402K|    11M|  6092   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MYSALES		 |   402K|    11M|  6092   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | MYSALES_PRODID_IDX |   402K|	 |   819   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   2 - access("PROD_ID"=0)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  5  consistent gets
	  0  physical reads
	  0  redo size
       1046  bytes sent via SQL*Net to client
	399  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL>

=> Notice that the cost is dropped to 6092 and operation is INDEX RANGE SCAN