Oracle Between用法-迄日要包含時分秒-加映改善效能

2011-11-22

47899
0
2012-04-20

Dotblogs 的標籤：Oracle ,隨手記

有個需求要產報表，每次抓一個月的資料，利用 Oracle 的 Between 函式可以解決，但是要特別注意，若未加時分秒，會導致日期欄位若有記錄時分秒，該筆資料正好位於迄日時，會漏掉喔！

資料來源範例如下：

序號    日期區間
------    --------------------------
1000    2010/1/30
2000    2010/1/30
3000    2010/1/30 上午 12:00:58
4000    2010/1/30 下午 09:11:57
5000    2010/1/31
6000    2010/1/31 上午 12:01:12
7000    2010/1/31 下午 04:58:25

若用以下的 Sql，迄日未加時分秒，則最後兩筆 (6000、7000) 會漏掉喔！


SELECT CT.SEQ, CT.EFF_DATE FROM CT
 WHERE CT.EFF_DATE BETWEEN TO_DATE('2010/01/30', 'YYYY/MM/DD') 
 AND TO_DATE('2010/01/31', 'YYYY/MM/DD');

正確應該要在迄日加上時分秒如下：


SELECT CT.SEQ, CT.EFF_DATE FROM CT
 WHERE CT.EFF_DATE BETWEEN TO_DATE('2010/01/30', 'YYYY/MM/DD') 
 AND TO_DATE('2010/01/31 23:59:59', 'YYYY/MM/DD HH24:MI:SS');

*****************************************************************************

同場加映：

上述的方式，會因為使用了 Between 和 To_Date 函式，造成用不到 Index Key，效能頗差，可利用 Function-base index 解決。

參考Oracle官方：Creating a Function-Based Index

所以上述 Sql 要做效能調整，可以建立以下 Index：


CREATE INDEX IDX_EFF_DATE ON CT 
(TO_CHAR(EFF_DATE, 'YYYYMMDDHH24MISS'));

然後查詢的 Sql 改成：


SELECT CT.SEQ, CT.EFF_DATE FROM CT
 WHERE TO_CHAR(CT.EFF_DATE, 'YYYYMMDDHH24MISS') 
 BETWEEN '20100130000000' AND '20100131235959';

就會用到 Index，筆數多時，效能差可大了~~

--------
沒什麼特別的~
不過是一些筆記而已

回首頁