ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL限制条件应尽量避免使用SYSDATE(二)

SQL限制条件应尽量避免使用SYSDATE(二)

原创 Linux操作系统 作者:yangtingkun 时间:2009-07-15 23:28:16 0 删除 编辑

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10426060