今天在工作中遇到SQL使用SYSDATE 效率低的问题，GOOGLE了一把，
发现yangtingkun的网站上有相关的测试案例，在此引用他的文章：
SQL限制条件应尽量避免使用SYSDATE （http://yangtingkun.itpub.net/post/468/487542）
SQL限制条件应尽量避免使用SYSDATE（二）（http://yangtingkun.itpub.net/post/468/487590）
文章相当不错，很有说服性。
正文粘贴如下：
有时候出于偷懒的目的，有些人习惯在应该输入常量的地方使用类似SYSDATE的函数来代替，但是这会带来额外的性能代价：
[oracle@yans1 ~]$ sqlplus test/test
SQL*Plus: Release 10.2.0.3.0 - Production on 星期二 7月 14 10:00:52 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> set pages 100 lines 120
SQL> create table t (id number, name varchar2(30), created date);
Table created.
SQL> insert into t select rownum, object_name, created from dba_objects;
70739 rows created.
SQL> insert into t select * from t;
70739 rows created.
SQL> insert into t select * from t;
141478 rows created.
SQL> insert into t select * from t;
282956 rows created.
SQL> insert into t select * from t;
565912 rows created.
SQL> insert into t select * from t;
1131824 rows created.
SQL> insert into t select * from t;
2263648 rows created.
SQL> insert into t select * from t;
4527296 rows created.
SQL> commit;
Commit complete.
SQL> set timing on
SQL> select count(*) from t where created >= to_date('2009-1-1', 'yyyy-mm-dd');
COUNT(*)
----------
744960
Elapsed: 00:00:00.56
SQL> select count(*) from t where created >= to_date('2009-1-1', 'yyyy-mm-dd');
COUNT(*)
----------
744960
Elapsed: 00:00:00.40
SQL> select count(*) from t where created >= trunc(sysdate, 'yyyy');
COUNT(*)
----------
744960
Elapsed: 00:00:01.58
SQL> select count(*) from t where created >= trunc(sysdate, 'yyyy');
COUNT(*)
----------
744960
Elapsed: 00:00:01.54
上面两个SQL等价，但是使用常量方式所需的执行时间，仅是使用SYSDATE函数的1/3左右。这时由于对于常量的计算，Oracle只需要在执行前运行一次得到结果就可以了，但是对于SYSDATE函数，则需要在与每条记录进行比较的时候都进行调用。
不仅仅是SYSDATE函数，其他函数也是一样的道理。应该只是在需要的时候进行调用：
SQL> select count(*) from t where name = 'TEST';
COUNT(*)
----------
768
Elapsed: 00:00:00.32
SQL> select count(*) from t where name = 'TEST';
COUNT(*)
----------
768
Elapsed: 00:00:00.31
SQL> select count(*) from t where name = user;
COUNT(*)
----------
768
Elapsed: 00:00:00.58
SQL> select count(*) from t where name = user;
COUNT(*)
----------
768
Elapsed: 00:00:00.57
除此之外，SYSDATE由于是函数调用，很可能使得CBO无法确定查询限制条件过滤的结果集，而使得CBO选择与使用常量不同的执行计划。
[oracle@yans1 ~]$ sqlplus test/test
SQL*Plus: Release 10.2.0.3.0 - Production on 星期二 7月 14 10:00:52 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> set pages 100 lines 120
SQL> create table t (id number, name varchar2(30), created date);
Table created.
SQL> insert into t select rownum, object_name, created from dba_objects;
70739 rows created.
SQL> insert into t select * from t;
70739 rows created.
SQL> insert into t select * from t;
141478 rows created.
SQL> insert into t select * from t;
282956 rows created.
SQL> insert into t select * from t;
565912 rows created.
SQL> insert into t select * from t;
1131824 rows created.
SQL> insert into t select * from t;
2263648 rows created.
SQL> insert into t select * from t;
4527296 rows created.
SQL> commit;
Commit complete.
SQL> create index ind_t_created on t (created);
Index created.
SQL> exec dbms_stats.gather_table_stats(user, 'T')
PL/SQL procedure successfully completed.
建立了测试表、索引后，对表进行分析。
下面检查使用sysdate和常量的不同：
SQL> explain plan for select * from t where created > trunc(sysdate) - 30;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1670768762
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60768 | 2077K| 4407 (1)| 00:01:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 60768 | 2077K| 4407 (1)| 00:01:02 |
|* 2 | INDEX RANGE SCAN | IND_T_CREATED | 61873 | | 84 (2)| 00:00:02 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CREATED">TRUNC(SYSDATE@!)-30)
14 rows selected.
SQL> select to_char(trunc(sysdate) - 30, 'yyyy-mm-dd') from dual;
TO_CHAR(TR
----------
2009-06-14
SQL> explain plan for select * from t where created > to_date('2009-06-14');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1670768762
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 454K| 15M| 5929 (1)| 00:01:24 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 454K| 15M| 5929 (1)| 00:01:24 |
|* 2 | INDEX RANGE SCAN | IND_T_CREATED | 83260 | | 112 (1)| 00:00:02 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CREATED">TO_DATE('2009-06-14'))
14 rows selected.
虽然使用SYSDATE和使用常量的执行计划一样，但是Oracle认为返回记录数，返回字节数，以及执行的代价都是有差异的。
SQL> explain plan for select * from t where created > trunc(sysdate) - 45;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 620K| 20M| 8805 (5)| 00:02:04 |
|* 1 | TABLE ACCESS FULL| T | 620K| 20M| 8805 (5)| 00:02:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED">TRUNC(SYSDATE@!)-45)
13 rows selected.
SQL> select to_char(trunc(sysdate) - 45, 'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(TRUNC(SYSDA
-------------------
2009-05-30 00:00:00
SQL> explain plan for select * from t where created > to_date('2009-05-30', 'yyyy-mm-dd');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 620K| 20M| 8518 (2)| 00:02:00 |
|* 1 | TABLE ACCESS FULL| T | 620K| 20M| 8518 (2)| 00:02:00 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED">TO_DATE('2009-05-30 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
14 rows selected.
看来Oracle对于SYSDATE的分析还是比较准确的，大部分情况下都和使用常量的结果一致，但是如果将情况变得复杂一些：
SQL> create table t2 as select * from t;
Table created.
SQL> create index ind_t2_created on t2(created);
Index created.
SQL> exec dbms_stats.gather_table_stats(user, 'T2')
PL/SQL procedure successfully completed.
SQL> create view t1 as select * from t
2 union all select * from t2;
View created.
下面对视图进行查询：
SQL> explain plan for
2 select count(*) from t1
3 where created >= to_date('2008-1-1', 'yyyy-mm-dd')
4 and created < trunc(sysdate, 'yyyy');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2892334184
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 8240 (7)| 00:01:56 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | FILTER | | | | | |
| 3 | VIEW | T1 | 9219K| 70M| 8240 (7)| 00:01:56 |
| 4 | UNION-ALL PARTITION | | | | | |
|* 5 | FILTER | | | | | |
|* 6 | INDEX FAST FULL SCAN| IND_T_CREATED | 8287K| 63M| 4086 (8)| 00:00:58 |
|* 7 | FILTER | | | | | |
|* 8 | INDEX FAST FULL SCAN| IND_T2_CREATED | 4610K| 35M| 4164 (7)| 00:00:59 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss')5 - filter(TRUNC(SYSDATE@!,'fmyyyy')>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
6 - filter("CREATED""CREATED">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
7 - filter(TRUNC(SYSDATE@!,'fmyyyy')>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
8 - filter("CREATED""CREATED">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
29 rows selected.
SQL> explain plan for
2 select count(*) from t1
3 where created >= to_date('2008-1-1', 'yyyy-mm-dd')
4 and created < to_date('2009-1-1', 'yyyy-mm-dd');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 90982281
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 7839 (3)| 00:01:50 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | VIEW | T1 | 9219K| 70M| 7839 (3)| 00:01:50 |
| 3 | UNION-ALL PARTITION | | | | | |
|* 4 | INDEX FAST FULL SCAN| IND_T_CREATED | 8287K| 63M| 3888 (3)| 00:00:55 |
|* 5 | INDEX FAST FULL SCAN| IND_T2_CREATED | 4610K| 35M| 3961 (3)| 00:00:56 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("CREATED"AND "CREATED">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
5 - filter("CREATED"AND "CREATED">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
20 rows selected.
显然Oracle为了第一个SQL可以顺利的执行，在索引扫描的外层又嵌套了一层FILTER，而且二者的执行效率也有明显的差异：
SQL> set timing on
SQL> set autot on
SQL> select count(*) from t1
2 where created >= to_date('2008-1-1', 'yyyy-mm-dd')
3 and created < to_date('2009-1-1', 'yyyy-mm-dd');
COUNT(*)
----------
16619264
Elapsed: 00:00:02.48
Execution Plan
----------------------------------------------------------
Plan hash value: 90982281
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 7839 (3)| 00:01:50 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | VIEW | T1 | 9219K| 70M| 7839 (3)| 00:01:50 |
| 3 | UNION-ALL PARTITION | | | | | |
|* 4 | INDEX FAST FULL SCAN| IND_T_CREATED | 8287K| 63M| 3888 (3)| 00:00:55 |
|* 5 | INDEX FAST FULL SCAN| IND_T2_CREATED | 4610K| 35M| 3961 (3)| 00:00:56 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("CREATED"AND "CREATED">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
5 - filter("CREATED"AND "CREATED">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23752 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from t1
2 where created >= to_date('2008-1-1', 'yyyy-mm-dd')
3 and created < trunc(sysdate, 'yyyy');
COUNT(*)
----------
16619264
Elapsed: 00:00:04.93
Execution Plan
----------------------------------------------------------
Plan hash value: 2892334184
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 8240 (7)| 00:01:56 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | FILTER | | | | | |
| 3 | VIEW | T1 | 9219K| 70M| 8240 (7)| 00:01:56 |
| 4 | UNION-ALL PARTITION | | | | | |
|* 5 | FILTER | | | | | |
|* 6 | INDEX FAST FULL SCAN| IND_T_CREATED | 8287K| 63M| 4086 (8)| 00:00:58 |
|* 7 | FILTER | | | | | |
|* 8 | INDEX FAST FULL SCAN| IND_T2_CREATED | 4610K| 35M| 4164 (7)| 00:00:59 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss')5 - filter(TRUNC(SYSDATE@!,'fmyyyy')>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
6 - filter("CREATED""CREATED">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
7 - filter(TRUNC(SYSDATE@!,'fmyyyy')>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
8 - filter("CREATED""CREATED">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23752 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这仅仅是将单表扫描的例子变成访问包含UNION ALL的视图，如果在加上多表连接查询等复杂情况，SYSDATE方式带来的影响可能会更大。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8183550/viewspace-666083/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/8183550/viewspace-666083/
扫一扫
544
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
