Oracle 検索列にSUBSTRを使用せずに検索する方法

Question

タイトルに記載した通り、検索列にSUBSTRを使用せずに検索する方法を教えていただきたいです。

※Oracle11gを使用しています。

テーブル：Table01
検索列：test_ymd varchar(8)

現在以下のように検索をしています。
例)
select*
from Table01
where SUBSTR(test_ymd,1,6)> '201502'

2015年2月以降のデータを取得したく、上記のように組んでいるのですが、検索列に関数を使用するのは良くないようなので、他の案を模索しています。いい方法はないでしょうか？

よろしくお願いします。

Answer 1

where test_ymd> '20150200'

Comments

[Zuishin]

3 月以降なら
where test_ymd> '20150300'

Answer 2

回答は他の人が書かれていますので、そちらを参考にしてください。

日時情報に文字型や数値型を使って設計して、わざわざ日付の何日何時間前後を求める...の類の関数をいくつも作ったり、DATE型を使わないでパフォーマンスを下げているシステムでチューニングを依頼されることが多いです。わたしが呼ばれた段階でテーブルを変更するのは影響が大き過ぎてお客様は変更を躊躇してしまい、他の部分でチューニングできるところを探すことが多いです。

テーブル設計する時には、SQLの基礎を理解しているメンバーを一人でも入れてくださるよう切にお願いします。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS' ;

SQL> SELECT SYSDATE, TRUNC(SYSDATE, 'MM') AS YYYYMM FROM  DUAL ;

SYSDATE             YYYYMM
------------------- -------------------
2017/02/22 11:18:43 2017/02/01 00:00:00

Comments

[Zuishin]

まさに仰る通りです。
私も日付時刻型をお勧めしようかと頭に一瞬よぎりましたが、結局そのままにしてしまいました。

ここからは全く余計なことですが、元のデータ形式でパフォーマンスが悪く、変更が面倒もとい危険な場合に私がよく使う方法があります。

取りあえずフィールドをもう一つ増やし、そこには日付時刻型を入れます。
そしてストアドプロシージャで元のフィールドと新しいフィールドを同期させ、検索には新しいフィールドを使います。また影響のない範囲で少しずつ古いフィールドの使用を無くします。
データの追加や更新の時のパフォーマンスはやや落ちますが、問題にならないことがほとんどです。

[Orlofsky]

DATE型列を追加してINSERT, UPDATE時にトリガーで TO_DATEする簡単な手は考えられますが、バッチの中でトリガーを呼ぶと極端に遅くなることがあります。元のテーブル設計者に最初に設計した方法ではこの部分がパフォーマンスが悪かったのでこういう風に変更しました。次からの設計ではご参考に願います、程度のフィードバックができると良いです。

[Zuishin]

まあ余計なことでしたね。確かに大量に変更する操作を頻繁にするような場合は使えないと思います。

Answer 3

「2015年2月以降のデータを取得」と例のSQLの条件(SUBSTR(test_ymd,1,6)> '201502'と2015年02月より大きいなので2015/03/01以降)が合いませんが。

関数使わないとしたら以下しかないと思います。

-- 2015/03/01以降なら
select * from Table01 where test_ymd >  '20150228';
select * from Table01 where test_ymd >= '201503';
select * from Table01 where test_ymd >= '20150300';
select * from Table01 where test_ymd >= '20150301';
-- 2015/02/01以降なら
select * from Table01 where test_ymd >  '20150131';
select * from Table01 where test_ymd >  '201502';
select * from Table01 where test_ymd >  '20150200';
select * from Table01 where test_ymd >= '20150201';

Answer 4

もし where 条件に関数を使うのを避けたいなら

select *
from (select Table01.*,SUBSTR(test_ymd,1,6) as ymd from Table01)
where ymd > '201502'