Oracle10g如何对近似查询字段建立索引？

Question

数据库为Oracle 10g，表scott.RH_BASE_INFO，字段为：
PROD_DATE date(类型为'YYYY-MM-DD HH:MI:SS')，SHIFT NUMBER(1)查询语句为
select count(*) from scott.RH_BASE_INFO where PROD_DATE like to_date('2007-4-5','YYYY-MM-DD') and shift=1;//选出PROD_DATE日期在'2007-4-5'那一天和shift为1的数据创建索引：
CREATE bitmap INDEX scott.idx_BASE_SHIFT ON scott.RH_BASE_INFO(SHIFT);  //班次只有三种，因此创建位图索引
CREATE INDEX scott.idx_BASE_PROD ON scott.RH_BASE_INFO(PROD_DATE);执行计划任务，发现PROD_DATE的索引没有启动(主要是PROD_DATE索引没有启动)，请问应该如何设置索引才能够起作用？谢谢。

Answer 1

不要用like,用=
select count(*) from scott.RH_BASE_INFO where PROD_DATE =to_date('2007-4-5','YYYY-MM-DD') 
或select count(*) from scott.RH_BASE_INFO where trunc(PROD_DATE) =to_date('2007-4-5','YYYY-MM-DD')

Answer 2

select count(*) from scott.RH_BASE_INFO where PROD_DATE =to_date('2007-4-5','YYYY-MM-DD')
两个索引全部引用，但是查询的结果是错误的。select count(*) from scott.RH_BASE_INFO where trunc(PROD_DATE) =to_date('2007-4-5','YYYY-MM-DD')
我建立了函数索引
CREATE INDEX scott.idx_BASE_PROD ON scott.RH_BASE_INFO(trunc(PROD_DATE));
idx_BASE_PROD索引被引用，但idx_BASE_SHIFT没有被引用(是否位图索引和函数索引不能够同时被引用？)，查询结果正确。

Answer 3

首先，索引字段不能出現類型轉換，並且索引字段不能出現在表達式中。否則索引失效。
trunc(PROD_DATE)就違背了這個原則。
所以你在PROD_DATE字段中的數據要和to_date('2007-4-5','YYYY-MM-DD')是一樣的，就是説，除了日期一樣，時間也要一樣。
to_date('2007-8-3','YYYY-MM-DD')實際的值是
SQL> select to_char(to_date('2007-8-3','yyyy-mm-dd'),'yyyy/mm/dd hh24:mi:ss') from dual;TO_CHAR(TO_DATE('20
-------------------
2007/08/03 00:00:00而要是用系統時間的話：
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'YY
-------------------
2007/08/03 16:15:05它們是不等的。所以檢索結果是錯的。

Answer 4

用between
select count(*) from scott.RH_BASE_INFO 
where PROD_DATE between to_date('2007-04-05','YYYY-MM-DD') and to_date('2007-04-06','YYYY-MM-DD')
and shift=1;

Answer 5

select count(*) from scott.RH_BASE_INFO 
where PROD_DATE between to_date('2007-04-05','YYYY-MM-DD') and to_date('2007-04-06','YYYY-MM-DD') and shift=1;只有IDX_BASE_PROD索引被引用，结果正确

Answer 6

PROD_DATE between to_date('2007-04-05','YYYY-MM-DD') and to_date('2007-04-06','YYYY-MM-DD')会比楼主原先要求查的多那么一点数据，应该改为
PROD_DATE >=to_date('2007-04-05','YYYY-MM-DD') and PROD_DATE < to_date('2007-04-06','YYYY-MM-DD')