如果可以明确的使用日期常量来表示,那么就尽量避免使用SYSDATE作为替代。以前写过一篇SQL中如何处理常量的,其实已经包含了这个含义。
这一篇介绍SYSDATE和常量对执行计划的影响。
sql语句中常量的处理:http://yangtingkun.itpub.net/post/468/20038
SQL限制条件应尽量避免使用SYSDATE(一):http://yangtingkun.itpub.net/post/468/487542
上一篇文章提到,SYSDATE是一个函数,对于表扫描的每一行比较都需要一次调用。因此会影响性能。
除此之外,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')<TRUNC(SYSDATE@!,'fmyyyy'))
5 - filter(TRUNC(SYSDATE@!,'fmyyyy')>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
6 - filter("CREATED"<TRUNC(SYSDATE@!,'fmyyyy') AND
"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"<TRUNC(SYSDATE@!,'fmyyyy') AND
"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"<TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND "CREATED">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
5 - filter("CREATED"<TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
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"<TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND "CREATED">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
5 - filter("CREATED"<TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
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')<TRUNC(SYSDATE@!,'fmyyyy'))
5 - filter(TRUNC(SYSDATE@!,'fmyyyy')>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
6 - filter("CREATED"<TRUNC(SYSDATE@!,'fmyyyy') AND
"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"<TRUNC(SYSDATE@!,'fmyyyy') 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
这仅仅是将单表扫描的例子变成访问包含UNION ALL的视图,如果在加上多表连接查询等复杂情况,SYSDATE方式带来的影响可能会更大。