takami_hirokiの日記

2011-02-21

SQLでの日付条件に注意

以下のエントリーを読んでいて、過去に自分もハマッたなあと思ったのでメモ。

ヒビコレショウジン - フレームワーク開発時代のSQLチューニング基礎(2)SQLのやっちゃだめ前編 について確認してみた


※ほとんど引用のみになっちゃいました。。


SELECT empno, ename, hiredate FROM emp

WHERE TO_CHAR(hiredate, 'YYYY/MM/DD') = '2011/02/16';


よくありがちですが、これはHIREDATE列にINDEXがあっても使えません。

列値が関数などで加工されると、INDEX内の列値を使ってマッチングができないからですね。

日付での絞り込みが遅いシステムの原因としてよく見かけます。

これは常識ですね。いつも気をつけています。


でも、この書き方じゃINDEXが使えないのはわかったので、改良してみます。


SELECT empno, ename, hiredate FROM emp

WHERE hiredate = TO_DATE('2011/02/16', 'YYYY/MM/DD');


で、これを実行すると、データが1件もヒットしなかったりします。

原因は、DATE型は年月日 時分秒を持っているため、「年月日」だけの文字列を変換すると、時刻部分は00:00:00扱いになるためです。

ココ!!注意。不具合としてよくありがち。


なので、時刻部分は無視できるようにします。


SELECT empno, ename, hiredate FROM emp

WHERE hiredate >= TO_DATE('2011/02/16 00:00:00', 'YYYY/MM/DD HH:MI:SS')

AND hiredate <= TO_DATE('2011/02/16 23:59:59', 'YYYY/MM/DD HH:MI:SS');


betweenのほうがすっきりするかもしれませんが。

同様に、データ型の暗黙変化でもINDEXは使えなくなるので、文字と数値の比較などを行わないように注意が必要です。


  • 条件指定する列自体を関数で加工するとINDEXが利かないよね。
  • だから、条件指定する値のほうを加工しちゃえ!
  • あれ、正しい条件のはずが、一件もヒットしない(時刻部分は00:00:00扱いだから)

という感じで間違えちゃうんですよね。以前、ほぼ同じ間違いをやって、同じような修正をして記憶が。。。みなさんもお気をつけください。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/takami_hiroki/20110221/p1