刚刚作为DBA入职新单位,查看了一下AWR,发现很多SQL需要查询当天的记录。
模拟环境如下：
-
create table test
-
(
-
c1 timestamp
-
);
-
-
insert into test select systimestamp+(rownum-10) from dual connect by level<=20;
-
-
commit;
-
- create index inx_test on test(c1);
-
SQL> select * from test;
C1
---------------------------------------------------------------------------
04-APR-14 11.35.43.000000 PM
05-APR-14 11.35.43.000000 PM
06-APR-14 11.35.43.000000 PM
07-APR-14 11.35.43.000000 PM
08-APR-14 11.35.43.000000 PM
09-APR-14 11.35.43.000000 PM
10-APR-14 11.35.43.000000 PM
11-APR-14 11.35.43.000000 PM
12-APR-14 11.35.43.000000 PM
13-APR-14 11.35.43.000000 PM
14-APR-14 11.35.43.000000 PM
15-APR-14 11.35.43.000000 PM
16-APR-14 11.35.43.000000 PM
17-APR-14 11.35.43.000000 PM
18-APR-14 11.35.43.000000 PM
19-APR-14 11.35.43.000000 PM
20-APR-14 11.35.43.000000 PM
21-APR-14 11.35.43.000000 PM
22-APR-14 11.35.43.000000 PM
23-APR-14 11.35.43.000000 PM
20 rows selected.
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
14-APR-14 12.12.43.878915 AM +08:00
SQL> select * from test where trunc(c1)=trunc(sysdate);
C1
---------------------------------------------------------------------------
14-APR-14 11.35.43.000000 PM
没有创建函数索引的情况下，都是全表扫描的。
改写问题SQL
select * from test
where c1 >= to_timestamp(trunc(sysdate,'dd'))
and c1
查看执行计划
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29254281/viewspace-1141412/ ，如需转载，请注明出处，否则将追究法律责任。
