SQL限制条件应尽量避免使用SYSDATE[转帖]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方技术论坛-国内领先的Weblogic、Tuxedo中间件技术论坛
联动北方-国内领先的云技术服务提供商
»  游客             当前位置：  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3788 | 回复: 0   主题： SQL限制条件应尽量避免使用SYSDATE[转帖]        下一篇 
wayne
注册用户
等级：中校
经验：1690
发帖：221
精华：0
注册：2011-7-21
状态：离线
发送短消息息给wayne 加好友    发送短消息息给wayne 发消息
发表于： IP：您无权察看 2011-8-25 17:52:15 | [全部帖] [楼主帖] 楼主

如果可以明确的使用日期常量来表示，那么就尽量避免使用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方式带来的影响可能会更大。


赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码： 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论

Weblogic中间件技术论坛

 |

Tuxedo中间件技术论坛

 |

数据库论坛

 |

Java论坛

 |

Linux/unix论坛

 |

网站地图


Powered by LandingBJ     @ 2008-2018     联动北方   
京ICP证号090597    京ICP备案09017624号    京公网安备110112000093号    
400-810-2327     Email：service@landingbj.com   

Powered by landingbj @ 2008-2015 联动北方
京ICP证号090597 京ICP备案09017624号 京公网安备110112000093号
86-27-87056338 Email：service@landingbj.com