执行计划检测索引性能

SQL> set autotrace traceonly;
SQL> select * from salestrack where salestel='13768875509' and trunc(salesdateti
me)=to_date('20120719','yyyymmdd');

已选择80行。


执行计划
----------------------------------------------------------
Plan hash value: 876668552

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 13 | 1014 | 654 (2)| 00:00:08 |

|* 1 | TABLE ACCESS FULL| SALESTRACK | 13 | 1014 | 654 (2)| 00:00:08 |

--------------------------------------------------------------------------------


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

1 - filter("SALESTEL"='13768875509' AND
TRUNC(INTERNAL_FUNCTION("SALESDATETIME"))=TO_DATE('2012-07-19
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2964 consistent gets
0 physical reads
0 redo size
5058 bytes sent via SQL*Net to client
439 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
80 rows processed

SQL> create index map_red on fwd.salestrack(salestel,salesdatetime);

索引已创建。

SQL> select * from salestrack where salestel='13768875509' and trunc(salesdateti
me)=to_date('20120719','yyyymmdd');

已选择80行。


执行计划
----------------------------------------------------------
Plan hash value: 1625202595

--------------------------------------------------------------------------------

----------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

--------------------------------------------------------------------------------

----------

| 0 | SELECT STATEMENT | | 13 | 1014 | 14 (0)|
00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| SALESTRACK | 13 | 1014 | 14 (0)|
00:00:01 |

|* 2 | INDEX RANGE SCAN | MAP_RED | 14 | | 7 (0)|
00:00:01 |

--------------------------------------------------------------------------------

----------


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

2 - access("SALESTEL"='13768875509')
filter(TRUNC(INTERNAL_FUNCTION("SALESDATETIME"))=TO_DATE('2012-07-19
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
61 consistent gets
6 physical reads
0 redo size
8240 bytes sent via SQL*Net to client
439 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
80 rows processed


可以看到建了索引之后， 速度为原来的1/8