经常看sql语句，经常会遇到使用函数trunc(create_date)的情况，这种情况经常出现。
很多情况下，是开发人员的无知，实际上修改为create_date between trunc(sysdate) and trunc(sysdate+1)-1/86400就ok了。
就可以避免函数索引的建立，但是我这里提到的情况是这种函数索引是否对普通的查询是否有效。
拿scott.emp表来说。
create index if_emp_hiredate on emp(trunc(hiredate));
SCOTT@test01p> select * from emp where hiredate=to_date('1980/12/17','yyyy-mm-dd');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1ct8dum3uyy7u, child number 0
-------------------------------------
select * from emp where hiredate=to_date('1980/12/17','yyyy-mm-dd')
Plan hash value: 4059437819
-------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IF_EMP_HIREDATE | 1 | 1 (0)|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("HIREDATE"=TO_DATE(' 1980-12-17 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
2 - access("EMP"."SYS_NC00009$"=TRUNC(TO_DATE(' 1980-12-17 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
--可以发现查询使用函数索引，即使是查询条件hiredate=to_date('1980/12/17','yyyy-mm-dd')，这种情况与使用substr(a,1,N)一样。
--即使使用hiredate between to_date('1980/12/17','yyyy-mm-dd')-1 and to_date('1980/12/17','yyyy-mm-dd')+1;条件也一样。
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0phtjuq0q7u8u, child number 0
-------------------------------------
select * from emp where hiredate between
to_date('1980/12/17','yyyy-mm-dd')-1 and
to_date('1980/12/17','yyyy-mm-dd')+1
Plan hash value: 4059437819
-------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IF_EMP_HIREDATE | 1 | 1 (0)|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("HIREDATE" hh24:mi:ss') AND "HIREDATE">=TO_DATE(' 1980-12-16 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
2 - access("EMP"."SYS_NC00009$">=TRUNC(TO_DATE(' 1980-12-16 00:00:00',
'syyyy-mm-dd hh24:mi:ss')) AND "EMP"."SYS_NC00009$" 1980-12-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
--反过来讲并不是讲鼓励大家建立这样的函数索引，而是如果应用中已经存在trunc(hiredate)这样的条件，并且hiredate = :1或者
--hiredate between :b1 and :b2这样的查询条件存在，要考虑是否可以减少1个索引的建立，比如如果trunc(create_date)条件返回在
--100条以内，也许create_date这样的索引可以不建立，同样可以获得很好的效果，当然最好测试评估。另外这样的函数索引重复值很
--高，可以选择索引前缀压缩。
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t as select rownum id ,sysdate-100+rownum/100 create_date,lpad('X',80,'X') vc from dual connect by levelselect trunc(create_date),count(*) from t group by trunc(create_date) order by 1;
create index if_t_create_date on t (trunc(create_date)) compress 1;
create index i_t_create_date on t (create_date) ;
exec dbms_stats.gather_table_stats(user,'T');
select /*+ gather_plan_statistics */ id,create_date,substr(vc,1,20) c20 from t where create_date between trunc(sysdate) and trunc(sysdate+1)-1/86400;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID actssvadvtbsd, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ id,create_date,substr(vc,1,20) c20
from t where create_date between trunc(sysdate) and
trunc(sysdate+1)-1/86400
Plan hash value: 3730485863
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 38 |00:00:00.01 | 5 |
|* 1 | FILTER | | 1 | | | 38 |00:00:00.01 | 5 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 39 | 3 (0)| 38 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | I_T_CREATE_DATE | 1 | 39 | 2 (0)| 38 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(SYSDATE@!) 3 - access("CREATE_DATE">=TRUNC(SYSDATE@!) AND "CREATE_DATE" 407407407407407407)
--可以发现这样逻辑读仅仅5个。使用普通索引.
select /*+ gather_plan_statistics index(t if_t_create_date) */ id,create_date,substr(vc,1,20) c20 from t
where create_date between trunc(sysdate) and trunc(sysdate+1)-1/86400;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID anu23m7rj13cx, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t if_t_create_date) */
id,create_date,substr(vc,1,20) c20 from t where create_date between
trunc(sysdate) and trunc(sysdate+1)-1/86400
Plan hash value: 231836870
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| 38 |00:00:00.01 | 5 |
|* 1 | FILTER | | 1 | | | 38 |00:00:00.01 | 5 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 39 | 4 (0)| 38 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | IF_T_CREATE_DATE | 1 | 99 | 2 (0)| 38 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(SYSDATE@!) 2 - filter(("CREATE_DATE">=TRUNC(SYSDATE@!) AND "CREATE_DATE" 407407407407407407))
3 - access("T"."SYS_NC00004$">=TRUNC(TRUNC(SYSDATE@!)) AND
"T"."SYS_NC00004$"
--上下执行对比，cost一样。
--改名
alter index i_t_create_date rename to iz_t_create_date;
select /*+ gather_plan_statistics */ id,create_date,substr(vc,1,20) c20 from t where create_date between trunc(sysdate) and trunc(sysdate+1)-1/86400;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID actssvadvtbsd, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ id,create_date,substr(vc,1,20) c20
from t where create_date between trunc(sysdate) and
trunc(sysdate+1)-1/86400
Plan hash value: 1209507809
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 38 |00:00:00.01 | 5 |
|* 1 | FILTER | | 1 | | | 38 |00:00:00.01 | 5 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 39 | 3 (0)| 38 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | IZ_T_CREATE_DATE | 1 | 39 | 2 (0)| 38 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(SYSDATE@!) 3 - access("CREATE_DATE">=TRUNC(SYSDATE@!) AND "CREATE_DATE" 07407407407407407)
-- 可以发现如果索引名字重新排序，优先选择依旧是普通索引。
-- 如果数据聚集性很好，逻辑读会很小。
-- 如果数据分布不聚集,大家有兴趣测试一下,不做了。
--换一个思路，大家认为还有什么函数索引也能使用在一般常规的查询呢？我仅仅知道substr（name,1,N) 以及trunc。
--其他的呢？
