SQL限制條件應儘量避免使用SYSDATE（二）
如果可以明確的使用日期常量來表示，那麼就儘量避免使用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')
hh24:mi:ss'))
6 - filter("CREATED"
7 - filter(TRUNC(SYSDATE@!,'fmyyyy')>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
8 - filter("CREATED"
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"
5 - filter("CREATED"
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"
5 - filter("CREATED"
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')
hh24:mi:ss'))
6 - filter("CREATED"
7 - filter(TRUNC(SYSDATE@!,'fmyyyy')>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
8 - filter("CREATED"
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL限制條件應儘量避免使用SYSDATESQL
- 使用 XML 時儘量避免使用的技術XML
- 儘量使用簡單的sqlSQL
- 面試官：如何在開發階段就儘量避免寫出慢 SQL ？面試SQL
- 總結在SQL Server檢視管理中限制條件SQLServer
- SQL Server檢視管理中的四個限制條件SQLServer
- PLSQL Language Reference-PL/SQL語言基礎-條件編譯-條件編譯指令限制SQL編譯
- SQL Server檢視管理中需要遵守的四個限制條件SQLServer
- PL/SQL 條件SQL
- 避免動態SQL(二)SQL
- 儘量使用 useReducer，不要使用 useStateuseReducer
- 高效的SQL（index skip scan使用條件）SQLIndex
- 儘量用簡單的SQL替代PL/SQL邏輯SQL
- 報表任務眾多……下週要儘量避免天天加班 ◎◎
- SQL中on條件與where條件的區別[轉]SQL
- [AlwaysOn] 建立SQL Server高可用性組T-SQL語法：先決條件和限制SQLServer
- SQL多條件查詢SQL
- oracle表空間傳輸的限制條件Oracle
- Oracle中left join中右表的限制條件Oracle
- 【ORACLE】物化檢視快速重新整理限制條件Oracle
- MySQL版本對varchar的定義和限制條件MySql
- PL/SQL 條件控制語句SQL
- sql 查詢條件問題SQL
- SQL Server解惑——查詢條件IN中能否使用變數SQLServer變數
- 條件限制性進入某個程式流程的限制演算法演算法
- FORM 10g的限制查詢條件引數ORM
- 通過新增條件優化SQL優化SQL
- 查詢作為條件的SQLSQL
- 動態SQL-條件分頁SQL
- Oracle 是分割槽表，但條件不帶分割槽條件的SQLOracleSQL
- OLTP系統中儘量使用繫結變數變數
- 二所應急自動化系統自動相關條件
- LINQ系列：LINQ to SQL Where條件SQL
- SQL SERVER 條件語句的查詢SQLServer
- 無合適where條件過濾時儘量選擇order by後的欄位以驅動表進行查詢薦
- 《Oracle EXP工具QUERY引數使用方法和限制條件》-使用場景-對比測試-可下載Oracle
- 關於在SQL語句中ON和WHERE中條件使用的差異SQL
- Linux Qt使用POSIX多執行緒條件變數、互斥鎖(量)LinuxQT執行緒變數