SQL限制条件应尽量避免使用SYSDATE（二）

最新推荐文章于 2024-03-18 19:25:50 发布
最新推荐文章于 2024-03-18 19:25:50 发布
阅读量313 收藏
点赞数
文章标签： 数据库

如果可以明确的使用日期常量来表示，那么就尽量避免使用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')   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/4227/viewspace-609279/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-609279/

ckawt40802
关注
SQL优化 干货总结
weixin_50998109的博客
07-13 564
1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下： 优化方式：尽量在字段后面使用模糊查询。如下： 如果需求是要在前面使用模糊查询，2. 尽量避免使用in 和not in，会导致引擎走全表扫描。如下： 优化方式：如果是连续数值，可以用between代替。如下： 如果是子查询，可以用exists代替。 3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下： 优化方式：可以用union代替or。如下 4. 尽量避免进行null值的判断，会导致
MySQL基础篇2——SQL之SELECT使用篇
无
03-18 9807
此笔记来自b站尚硅谷课程，仅做复习使用。 一、SQL语言的规则与规范1、SQL大小写规范2、注释3、命名规则4、SQL分类5、数据导入指令二、基本的SELECT语句1、SELECT...FROM2、列的别名3、去除重复行4、空值参与运算5、着重号6、查询常数7、显示表结构8、过滤数据：WHERE三、4大类运算符1、算术运算符2、比较运算符3、逻辑运算符4、位运算符5、运算符的优先级四、排序与分页1、排序：ORDER BY2、分页：LIMIT五、多表查询1、笛卡尔积（或交叉连接）2、多表查询分类讲解3、SQ
SQL限制条件应尽量避免使用SYSDATE
cm333666的博客
06-24 350
今天在工作中遇到SQL使用SYSDATE 效率低的问题，GOOGLE了一把， 发现yangtingkun的网站上有相关的测试案例，在此引用他的文章： SQL限制条件应尽量避免使用SYSDATE （http://yangtin...
Oracle可以使用sysdate函数，mysql中使用sysdate函数要小心
冯立彬的博客
10-13 3893
    近期Offer数据库load飙高，导致中文站重要应用load升高，响应时间延长，经查是由于大量的SQL使用了sysdate()函 数， 大家以前在使用oracle时都习惯用这个函数来获取数据库当前时间，mysql中虽然也有sysdate（）这个函数，但是在MySQL规范里面不推荐使用的，在高并发下会有性能问题。MySQL规 范里面建议大家都用 now()，如果使用sysdate（），可能会...
mysql sql sysdate_mysql sql 常用命令和函数
weixin_42393245的博客
02-17 550
MySQL中时间函数NOW()和SYSDATE()的区别SELECT NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE()先查询了NOW()和SYSDATE()，然后sleep了3秒，再查询NOW()和SYSDATE()NOW()还有3个同义词，效果跟NOW()一样，而且都有2种形式写法：LOCALTIME或LOCALTIME()LOCALTIMESTAMP或LOCAL...
ORACLE物化视图--物化视图日志如何避免系统时间变化带来的影响
古渡闲人
01-05 1984
ORACLE物化视图--物化视图日志如何避免系统时间变化带来的影响 转载▼ 标签：  时间   系统   如何   影响   杂谈   物化视图通过利用物化视图日志来进行快速刷新。通过记录在物化视图日志中的SNAPTIME$$列来判断这个记录是否需要刷新。 一般情况下，如果物化视图日志支持多
SQL开发规范
09-11
- **分区表查询**：查询分区表时，条件需包含分区字段，并尽量使用闭区间，如`order_time >= sysdate - 1/24 AND order_time <= sysdate`。 - **时间类型字段**：使用时间类型字段时，变量需声明为字符串并通过`to...
SQL最佳实践
08-26
然而，使用`LIKE`可能会导致全表扫描，因此应该尽量避免在大型表中使用它，或者确保模式的开头不是通配符。 ##### 分析函数 分析函数（Analytic Functions）是一类特殊的函数，可以在一组行上进行计算，而不仅仅是...
SQL性能优化
12-20
5. 索引字段，尽量避免值为 null 6. 复合索引的建立需要仔细分析；尽量考虑用单字段索引代替：  正确选择复合索引中的第一个字段，一般是选择性较好的且在 where 子句中常的字段上。  复合索引的几个字段是否...
了解SYSDATE函数
weixin_34198797的博客
03-19 1049
SYSDATE是我们在Oracle开发中经常要用到的一种单行函数(single row function),该函数用以返回当前的日期与时间，常和DUAL伪表一起合作。SYSDATE实际上指的是SYSDATE()函数，但是因为这个函数没有参量，所以这里可以省略()圆括号。另外SYSDATE函数是用于获取数据库所在的操作系统的当前时间值的，所以它和数据库或会话的时区没有关系。 我们可以使用NLS_D...
SQL优化常用实践
yudian1991的博客
02-09 488
sql优化 sql常用实践
千云物流- mysql数据库SQL检查规范
Hello Word
10-09 853
研发行为规范，SQL检查规范
ORACLE时间函数(SYSDATE)深入理解
自强不息;厚德载物
01-27 3094
加法  select sysdate,add_months(sysdate,12) from dual; --加1年  select sysdate,add_months(sysdate,1) from dual; --加1月  select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期  sele
MYSQL 研发规范
JR_Way的博客
01-14 1125
MYSQL 研发规范 表设计规范： 1）  所有数据库表，函数，视图等对象都定义为小写,字符之间用下划线连接 2）  库名、表名、字段名禁止使用MySQL保留字，且名称长度不能超过32字节 3）  默认使用INNODB存储引擎，使用其他存储引擎需要DBA评审 4）  表必须有主键,推荐使用 自增id列作为主键 5）  字符集默认使用UTF-8，特殊定制会导致数据迁移，jdbc配置等
SQL中的SYSDATE函数
最新发布
wenxuankeji的博客
03-18 3650
SQL中的SYSDATE函数
时间和日期函数 sysdate 的作用
weixin_50077855的博客
09-21 7911
sysdate 的作用 和 now 函数作用大同小异 以字符串或数字形式使用该函数， 以 ‘YYYY-MM-DD hh:mm:ss’ 或 YYYYMMDDhhmmss 格式返回当前日期和时间 sysdate 的语法格式 SYSDATE([fsp]) 语法格式说明 fsp：可以指定 0 - 6 位的秒精度；不写就只返回时分秒，不会精确到毫秒 sysdate 和 now 的区别 sysdate() 返回执行时间 now() 返回 sql 语句开始执行的时间 例子 SELECT NOW(), SLEEP
mysql 避坑宝典(转)
weixin_40897630的博客
10-19 1877
数据库
mysql sysdate() 慢_使用Oracle时遇到的一些问题
weixin_30369183的博客
02-26 361
Oracle与Mysql的差异Oracle没有主键自增主键一般使用的序列 Mysql主键自增长单引号的处理 Mysql里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。时间函数1)Mysql now() Mysql的sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值