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好像不能合并两项,去找对应的索引.如何解决呢?
解决方案 »
- 求思路,关于程序设计。极为复杂
- SQL server编程接口//接口是什么意思?有什么语言进行编程接口?
- RMAN备份归档日志失败?
- oracle游标问题-求救大哥大姐们
- 很奇怪的sql语句的问题,高手速度来
- Oracle Database Express Edition 11g Release 2 支持windows 8 X64位操作系统吗?
- 关于查找重复记录的问题。
- 谁给我介绍一下Oracle里的事务怎么用啊,不会
- 安装oracle8.17时,配置工具中Net8 configuration Assistant这一项过不去了,请各位大侠指教!
- 紧急求助关于ORACLE数据库的连接问题.
- 能否对ORACLE数据库的修改进行跟踪监控,具体情况见内容
- 关于db link的怪问题,问题解决即结贴
如果用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个查询为什么不用索引.这是我的疑问啊.