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

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

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

これで、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/での リモート講義も 行っています。

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

Use The Index, Luke のカップは

ステッカー、コースター、本、コーヒーマグ。 学習に必要なものすべて。

今すぐ購入

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | お問い合わせ | 無保証 | 商標 | プライバシーとGDPR | CC-BY-NC-ND 3.0 license