日期字段建立函数索引

根据应用程序中的需求往往需要创建函数索引，如 to_char(salesdatetime,'yyyy
mmdd')


SQL> create index map_red on fwd.salestrack(salestel,to_char(salesdatetime,'yyyy
mmdd'));

索引已创建。

SQL> select * from salestrack where salestel='13768875509' and to_char(salesdate
time,'yyyymmdd')>'20120701';

已选择1263行。


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

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

----------

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

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

----------

| 0 | SELECT STATEMENT | | 67 | 5226 | 8 (0)|
00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| SALESTRACK | 67 | 5226 | 8 (0)|
00:00:01 |

|* 2 | INDEX RANGE SCAN | MAP_RED | 12 | | 3 (0)|
00:00:01 |

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

----------


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

2 - access("SALESTEL"='13768875509' AND
TO_CHAR(INTERNAL_FUNCTION("SALESDATETIME"),'yyyymmdd')>'20120701'
AND

TO_CHAR(INTERNAL_FUNCTION("SALESDATETIME"),'yyyymmdd') IS NOT NULL

)



统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
679 consistent gets
6 physical reads
0 redo size
68599 bytes sent via SQL*Net to client
1308 bytes received via SQL*Net from client
86 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1263 rows processed

SQL> select * from salestrack where salestel='13768875509' and to_char(salesdate
time,'yyyymmdd')='20120719';

已选择80行。


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

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

----------

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

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

----------

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

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

|* 2 | INDEX RANGE SCAN | MAP_RED | 5 | | 3 (0)|
00:00:01 |

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

----------


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

2 - access("SALESTEL"='13768875509' AND
TO_CHAR(INTERNAL_FUNCTION("SALESDATETIME"),'yyyymmdd')='20120719')



统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
52 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