=> 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
No comments:
Post a Comment