[20131204]sql語句優化.txt

lfree發表於2013-12-04
SQL優化
[20131204]sql語句優化.txt

昨天優化sql語句，遇到一些細節問題，做一個記錄：

SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t (id number,create_date date,pad varchar2(80));
create index i_t_create_date on t(create_date);

SCOTT@test> select /*+ index(t i_t_create_date) */ * from t where create_date>=trunc(sysdate) and  create_date>sysdate - 6/1440;
no rows selected

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6tqcjs9bj623v, child number 0
-------------------------------------
select /*+ index(t i_t_create_date) */ * from t where
create_date>=trunc(sysdate) and  create_date>sysdate - 6/1440

Plan hash value: 2174186695

-----------------------------------------------------------------------------
| Id  | Operation                   | Name            | E-Rows | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |        |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |      1 |     1   (0)|
|*  2 |   INDEX RANGE SCAN          | I_T_CREATE_DATE |      1 |     1   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CREATE_DATE">=TRUNC(SYSDATE@!))
       filter("CREATE_DATE">SYSDATE@!-.004166666666666666666666666666666
              666666667)


SCOTT@test> select /*+ index(t i_t_create_date) */ * from t where create_date>sysdate - 6/1440 and create_date>=trunc(sysdate);
no rows selected

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fcc58jafh38ym, child number 0
-------------------------------------
select /*+ index(t i_t_create_date) */ * from t where
create_date>sysdate - 6/1440 and create_date>=trunc(sysdate)

Plan hash value: 2174186695

-----------------------------------------------------------------------------
| Id  | Operation                   | Name            | E-Rows | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |        |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |      1 |     1   (0)|
|*  2 |   INDEX RANGE SCAN          | I_T_CREATE_DATE |      1 |     1   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CREATE_DATE">SYSDATE@!-.004166666666666666666666666666666
              666666667)
       filter("CREATE_DATE">=TRUNC(SYSDATE@!))


-- 先是感覺奇怪的是兩者寫法，為什麼access的條件不一樣。開始感覺第2種寫法應該快一些，正常的業務這樣掃描的日期範圍窄一些。
-- oracle優化器應該能作出正確的選擇，後來想起來以前遇到的問題，我給它起一個名字叫"零點魔鬼"，在凌晨切換日期時
-- 程式就有問題了。
SELECT trunc(to_date('2013-12-05 00:01:01','yyyy-mm-dd hh24:mi:ss')) a1,
       to_date('2013-12-05 00:01:01','yyyy-mm-dd hh24:mi:ss') a2,
       to_date('2013-12-05 00:01:01','yyyy-mm-dd hh24:mi:ss') -8/1440 a3
FROM dual ;

A1                  A2                  A3
------------------- ------------------- -------------------
2013-12-05 00:00:00 2013-12-05 00:01:01 2013-12-04 23:53:01

-- 很明顯在凌晨執行時，日期範圍越來越小，到0點6分後業務在正常。
-- 修改很簡單,刪除 create_date>=trunc(sysdate)條件。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1062126/，如需轉載，請註明出處，否則將追究法律責任。

相關文章