如果可以明確的使用日期常量來表示，那麼就儘量避免使用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

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方式帶來的影響可能會更大。

SQL限制條件應儘量避免使用SYSDATE（二）

相關文章