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

如果可以明確的使用日期常量來表示，那麼就儘量避免使用SYSDATE作為替代。以前寫過一篇SQL中如何處理常量的，其實已經包含了這個含義。

sql語句中常量的處理：http://yangtingkun.itpub.net/post/468/20038

 

 

有時候出於偷懶的目的，有些人習慣在應該輸入常量的地方使用類似SYSDATE的函式來代替，但是這會帶來額外的效能代價：

[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> set timing on
SQL> select count(*) from t where created >= to_date('2009-1-1', 'yyyy-mm-dd');

  COUNT(*)
----------
    744960

Elapsed: 00:00:00.56
SQL> select count(*) from t where created >= to_date('2009-1-1', 'yyyy-mm-dd');

  COUNT(*)
----------
    744960

Elapsed: 00:00:00.40
SQL> select count(*) from t where created >= trunc(sysdate, 'yyyy');

  COUNT(*)
----------
    744960

Elapsed: 00:00:01.58
SQL> select count(*) from t where created >= trunc(sysdate, 'yyyy');

  COUNT(*)
----------
    744960

Elapsed: 00:00:01.54

上面兩個SQL等價，但是使用常量方式所需的執行時間，僅是使用SYSDATE函式的1/3左右。這時由於對於常量的計算，Oracle只需要在執行前執行一次得到結果就可以了，但是對於SYSDATE函式，則需要在與每條記錄進行比較的時候都進行呼叫。

不僅僅是SYSDATE函式，其他函式也是一樣的道理。應該只是在需要的時候進行呼叫：

SQL> select count(*) from t where name = 'TEST';

  COUNT(*)
----------
       768

Elapsed: 00:00:00.32
SQL> select count(*) from t where name = 'TEST';

  COUNT(*)
----------
       768

Elapsed: 00:00:00.31
SQL> select count(*) from t where name = user;

  COUNT(*)
----------
       768

Elapsed: 00:00:00.58
SQL> select count(*) from t where name = user;

  COUNT(*)
----------
       768

Elapsed: 00:00:00.57