カーソル処理とファンクションとインデックスの件

質問

はじめまして。
環境 WinXP(Pro)/Oracle9i

まず、やりたいことは和暦の年号と日付で別々になっているフィールドをファンクションで西暦に直し、その対象とする西暦をカーソル処理で１行つかって抽出したいのです。
具体的には以下の例ですが
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^(ファンクション
FUNCTION FNCWSDATECNV(STRGEN IN VARCHAR2) RETURN DATE
IS
(一部省略しますが、A.WGENを和暦のアルファベット記記号に変換編集する[M/T/S/H]。
　　　そして、文字列形式に連結してS10/12/10として西暦に変換して戻り値にセット ([S10/12/31 -> 1935/12/31]となる。)
--------------------------------(カーソル処理定義
DECLARE
SELYMD DATE;
CURSOR CRSL1 IS
SELECT A.SELID,A.SELNAME
FROM KOJIN A
WHERE FNCWSDATECNV(A.WGEN, A.WDATE) < SELYMD;
---------------------------------(実行
BEGIN
SELYMD := GET_SELYMD; -- パラメータから得られた変数
OPEN CRSL1;
FETCH CRSL1 INTO
VAL_SID, VAL_SNAME;
LOOP
EXIT CRSL1%NOT FOUND;
(省略)
END LOOP;
END;
---------------------
初回のときは、データは50件ならばうまく抽出できましたが、２０万件あるとFETCHしたときには時間がかかると思うので、

CREATE INDEX KOJIN_IDX ON KOJIN(SELID);
としておくことで通常SELECTすると早くなりましたが、
上記の定義したカーソルにはファンクションが効かず、０件として終わってしまいます。

また、カーソル１行ずつ読み込むのは別テーブルへ追加する際、必要なデータを対象とする別表に参照するためです。
カーソル上でTO_CHAR(DECODE(A.WGEN,0,'M',・・・),'JAPANESE・・・
というような形式にしてもうまくいきませんでした。(カレンダによるエラー)

どうしたらよいのでしょうか？よろしくお願いします。

回答No.2

k_o_r_o_c_h_a_n

話の繋がりが見えないのですが・・

質問に書いてあるSQLとは、大きく異なるSQLを使っていて、他の要素に問題があったということでしょうか？
それとも、KOJINというのがビューであり、ビューの構成要素に問題があったということでしょうか？

質問は過不足無く書かないと。
（過不足無く書いても解決できるとは限りませんけどね）

２０分で充分な速度あれば良いのですが、SQLの動作速度を気にする場合、
実行計画を見て、適正なアクセスをしているかを確認しなければ、
どこに問題があるのか解りません。

例えば、
>SELYMD側を、元号(SELYMD_WGEN)と和暦年月日(SELYMD_WDATE)に分解した上で、
>where WGEN < SELYMD_WGEN or (WGEN = SELYMD_WGEN and WDATE < SELYMD_WDATE)
で、KOJIN_SEL_IDXという索引が使われているか、全表検索しているかで、
大きく速度が異なります。
KOJIN_SEL_IDXという索引が使われていないとすれば、如何にしたら使われるかを
模索することになります。

補足

返事が遅れて申し訳ありません。
質問した内容で問題があったと書きましたが、前提処理として行わなかったことで遅く感じとってしまっていました。
前提処理には、先ほど書きましたように、インデックス処理を設定することでアクセス効能が圧倒的に速くなることがわかりました。

確かに実行計画といい、統計情報(ANALYZE文)を使って検討しなければ問題がわかりませんね。

>>SELYMD側を、元号(SELYMD_WGEN)と和暦年月日(SELYMD_WDATE)に分解した上で、
>>where WGEN < SELYMD_WGEN or (WGEN = SELYMD_WGEN and WDATE < SELYMD_WDATE)
>で、KOJIN_SEL_IDXという索引が使われているか、全表検索しているかで、大きく速度が異なります。
>KOJIN_SEL_IDXという索引が使われていないとすれば、如何にしたら使われるかを模索することになります。
確かに、その索引を定義してあるのとしてないのではアクセス速度が違いますね。
SELECT /*+ INDEX(A KOJIN_SEL_IDX) */ SELID FROM KOJIN_SEL A
という具合ですね。
(ただ、書いていただいたSQL文の内容が異なりますが。本来はSELYMDの年月日を対象として抽出するべきですが、WGEN<SELYMD_WGENがあるので月日に関係ないその年のみ抽出してしまいます)

結局は、質問した内容のSQL文で良いとして、インデックスのあるなしによってアクセス効率が変わってしまうということがわかりました。

回答No.1

k_o_r_o_c_h_a_n

WHERE FNCWSDATECNV(A.WGEN, A.WDATE) < SELYMD;
というように、テーブル上の項目に対して、演算式や関数を使うと、通常の索引は使いません。
こういった問題に対処するために、ファンクション索引というものが用意されています。
ファンクション索引については、マニュアルで詳細を確認してください。

さて、このSQLですが、ファンクション索引を使わないと実現できないかというと、
SELYMD側を、元号(SELYMD_WGEN)と和暦年月日(SELYMD_WDATE)に分解した上で、

where WGEN < SELYMD_WGEN or (WGEN = SELYMD_WGEN and WDATE < SELYMD_WDATE)

のようにすれば、WGENとWDATEに対する通常の索引でも対処可能かと思います。

お礼

自己レス的で解決・対策が見つかったようなので書きます。
今まで記述していたSQLを応用としてやっていましたが、
SELECT ～ FROM A.SELNO IN (SELECT DISTINCT ～として記述したり、
SELECTと和集合であるUNIONとして連結していたりしたため、かなり遅くしていたのが原因でした。

そのためには、IN述語でなくEXISTS述語を使うことで圧倒的に速くなる(実は別サイトでインデックスに関して調べているうちにヒントらしきものが1つだけと)、
それからUNIONでなくUNIONALLで使う。
そうしたことで、前回では１時間経っても終わらなかった(あきらめて強制終了したりもした)のが、２０分程度で(10万件)読み取ることができました。

あと、ビットマップ索引というのが別サイトから出てきましたが、まだ未検証ですが、おそらくWHERE句で論理とするORなどある場合には有効だと聞きました。
今度時間あるときに試してみたいと思います。

ありがとうございました。

補足

さっそくの回答ありがとうございます。

＞こういった問題に対処するために、ファンクション索引というものが用意されています。

そういうのあったんですね。

＞さて、このSQLですが、ファンクション索引を使わないと実現できないかというと、
＞SELYMD側を、元号(SELYMD_WGEN)と和暦年月日(SELYMD_WDATE)に分解した上で、
＞where WGEN < SELYMD_WGEN or (WGEN = SELYMD_WGEN and WDATE < SELYMD_WDATE)
＞のようにすれば、WGENとWDATEに対する通常の索引でも対処可能かと思います。

これは、ファンクション関数を使わない方法でのインデックス有効法のことを説明しているのでしょうか？
上記の条件をもとに、抽出する日付側を変換して処理する形式にして実行しましたが、まだ早くなりません。

CREATE INDEX KOJIN_SEL_IDX ON KOJIN(SELID, WGEN, WDATE);
これを処理してカーソル処理に
SELECT /*+ A KOJIN_SEL_IDX(WGEN,WDATE) */ A.SELID,A.SELNAME
FROM KOJIN A
WHERE WGEN < SELYMD_WGEN or (WGEN = SELYMD_WGEN and WDATE < SELYMD_WDATE)
として処理させるようにしましたが、あまり遅すぎているせいか
処理が長い感じです。
初歩的ですみません。何かINDEXの定義など設定が足りないのかもしれません。よろしくお願いします。

ちなみに
(訂正)
FNCWSDATECNV(STRGEN IN VARCHAR2) ではなく
FNCWSDATECNV(STRGEN IN VARCHAR2, STRDATE IN VARCHAR2)でした。