create table t_big as select * from all_objects;create or replace view v_big
(owner,cre_dt,ddl_dt)
as
select owner,created + 0.24999,last_ddl_time
from t_big
;create index idx_t_big_cre_dt on t_big(to_char(created + 0.24999,'yyyy-mm-dd')) compute statistics;
create index idx_t_big_cre_dt2 on t_big(created + 0.24999) compute statistics;
create index idx_t_big_ddl_dt on t_big(to_char(last_ddl_time,'yyyy-mm-dd') compute statistics;analyze table t_big compute statistics
for table
for all indexes
for all indexed columns
;则对以下查询:
1. SELECT * FROM v_Big WHERE Cre_Dt = to_date('2003-07-08','yyyy-mm-dd')
2. SELECT * FROM t_Big WHERE To_Char(created + 0.24999, 'yyyy-mm-dd') = '2003-07-08'
3. SELECT * FROM v_Big WHERE To_Char(ddl_Dt, 'yyyy-mm-dd') = '2003-07-08'
4. SELECT * FROM v_Big WHERE To_Char(Cre_Dt, 'yyyy-mm-dd') = '2003-07-08'第4个不能利用索引idx_t_big_cre_dt,为什么?这种情况发生在视图字段本身是计算列,而外部的查询又有函数调用,oracle好像不能合并两项,去找对应的索引.如何解决呢?
(owner,cre_dt,ddl_dt)
as
select owner,created + 0.24999,last_ddl_time
from t_big
;create index idx_t_big_cre_dt on t_big(to_char(created + 0.24999,'yyyy-mm-dd')) compute statistics;
create index idx_t_big_cre_dt2 on t_big(created + 0.24999) compute statistics;
create index idx_t_big_ddl_dt on t_big(to_char(last_ddl_time,'yyyy-mm-dd') compute statistics;analyze table t_big compute statistics
for table
for all indexes
for all indexed columns
;则对以下查询:
1. SELECT * FROM v_Big WHERE Cre_Dt = to_date('2003-07-08','yyyy-mm-dd')
2. SELECT * FROM t_Big WHERE To_Char(created + 0.24999, 'yyyy-mm-dd') = '2003-07-08'
3. SELECT * FROM v_Big WHERE To_Char(ddl_Dt, 'yyyy-mm-dd') = '2003-07-08'
4. SELECT * FROM v_Big WHERE To_Char(Cre_Dt, 'yyyy-mm-dd') = '2003-07-08'第4个不能利用索引idx_t_big_cre_dt,为什么?这种情况发生在视图字段本身是计算列,而外部的查询又有函数调用,oracle好像不能合并两项,去找对应的索引.如何解决呢?
如果用RBO的话,oracle是会利用索引的,但用cbo不行。SELECT * /*+rule*/ FROM v_Big WHERE To_Char(Cre_Dt, 'yyyy-mm-dd') = '2003-07-08'但是我不想用增加hint的方式来优化.
有没有其它办法呢?
必须针对那些表建立带 + 0.24999的函数索引.用to_date的确能够避免第4个查询的情况,
但我的问题就是第4个查询为什么不用索引.这是我的疑问啊.