by Hayato Matsuura.

Oracleで部分インデックスを エミュレートする


OracleがインデックスにおいてNULLを扱うこの奇妙な 方法を利用すると、部分インデックスをエミュレートできます。 インデックスに含みたくない行でNULLをうまく使う必要が あります。

以下の部分インデックスをエミュレートしてみましょう。

CREATE INDEX messages_todo
          ON messages (receiver)
       WHERE processed = 'N'

まず、PROCESSED'N'の時だけ RECEIVERを返す関数が必要です。

CREATE OR REPLACE
FUNCTION pi_processed(processed CHAR, receiver NUMBER)
RETURN NUMBER
DETERMINISTIC
AS BEGIN
   IF processed IN ('N') THEN
      RETURN receiver;
   ELSE
      RETURN NULL;
   END IF;
END

この関数は、インデックスの定義に 使えるよう、確定的である必要があります。

これで、PROCESSED='N'の行だけを含むインデックスを 作ることができました。

CREATE INDEX messages_todo
          ON messages (pi_processed(processed, receiver))

このインデックスを使うには、クエリ内ではインデックスを使った 表現をする必要があります。

SELECT message
  FROM messages
 WHERE pi_processed(processed, receiver) = ?
----------------------------------------------------------
|Id | Operation                   | Name          | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT            |               | 5330 |
| 1 |  TABLE ACCESS BY INDEX ROWID| MESSAGES      | 5330 |
|*2 |   INDEX RANGE SCAN          | MESSAGES_TODO | 5303 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("PI_PROCESSED"("PROCESSED","RECEIVER")=:X)

部分インデックス パートII

11gには、Oracleで部分インデックスを エミュレートする、前述の方法と同じようにぞっとする別のやり方が あります。それは、故意にパーティションインデックスを 壊したものと、SKIP_UNUSABLE_INDEX を組み合わせる方法です。

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | 接触 | 無保証 | 商標 | Privacy | CC-BY-NC-ND 3.0 license