Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

インデックス・スキップ・スキャン (INDEX SKIP SCAN : INDEX SS)

Oracle 8i 以前は複合インデックスの第 1 キーを WHERE に持たない検索はインデックスを使用できなかった。
しかし、Oracle 9i から、このオラクルの高速化の仕組みによりインデックスを利用でき、パフォーマンスが向上する可能性がある。

例として 3つのカラムからインデックスが作成されていると想定する。

このような DDL

CREATE TABLE SKIP_SAMPLE
(
   USER_ID                        NUMBER(5,0) NOT NULL,
   GENDER                         CHAR(1),
   ZIP                            VARCHAR2(7),
   AGE                            NUMBER(3,0),
   NAME                           VARCHAR2(100),
   CONSTRAINT SKIP_SAMPLE_PK PRIMARY KEY (USER_ID)
) ;

CREATE INDEX SKIP_SAMPLE_IX ON SKIP_SAMPLE (GENDER, ZIP, AGE) ;
...データの投入...

統計情報を取らないとコストベースオプティマイザ(CBO) が有効に働いてくれない。
Oracle 9i 以降の場合には DBMS_STATS を使用する。

簡単な統計情報の取得例

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS( TABNAME=> 'SKIP_SAMPLE');
	DBMS_STATS.GATHER_INDEX_STATS( INDNAME=> 'SKIP_SAMPLE_IX');
END;

参考：詳細な使い方 ⇒ 統計情報の収集 (DBMS_STATS)

（おすすめできない例）
ANALYZE 作成、削除用 SQL (COMPUTEは負荷が掛かるので要注意)

ANALYZE TABLE  my_tablename { ESTIMATE | COMPUTE | DELETE } statistics ;

オラクルのマニュアル(10g) には CBO 向けに ANALYZE を行わないようにして下さいと書いてある。

INDEX SKIP SCAN の動作を確認する

第1キーのカーディナリティ度が低い場合を想定(例:男女)

• 第1キーだけを検索条件にした場合

  第1キーはカーディナリティが少ないので TABLE FULL SCAN が有効と判断される。

• 第2キーだけを検索条件にした場合

  第1キーのカーディナリティが低いので、INDEX SKIP SCAN (INDEX SS) が行われる。
  (第1キーのカーディナリティが高いとオプティマイザはTABLE FULL SCANの方が有効と判断する。)

  総レコード数 10000件の場合で 第1キーの カーディナリティ 10 程度までは、INDEX SS が使用され、20 程度になると FULL SCAN になった。
  (注意) データの更新後は統計情報の再取得を行わないと CBO は正しい判断をしない。

実行計画 (INDEX SSを使用している例)

SELECT * FROM SKIP_SAMPLE WHERE  ZIP = '102-0093'
--------------------------------------------------------
3	SELECT STATEMENT
2	└TEST.SKIP_SAMPLE TABLE ACCESS [BY INDEX ROWID]
1	    └ TEST.SKIP_SAMPLE_IX INDEX [SKIP SCAN]

• 第3キーだけを検索条件にした場合

  第1、第2の複合キーのカーディナリティが低い場合、インデックス・スキップ・スキャンが行われるかもしれない (たぶん、無理だとは思うが未検証)。
  テストケースでの第 2 キーのカーディナリティは高いのでテーブル・フル・スキャンになっている。

肝心のレスポンスは、専用のインデックスにくらべると若干低下するが、十分満足できるものに。

レコード件数 n の場合
理想的な B-Tree の状態な計算ですが… 
B-Tree インデックスの探索所要コストのオーダ関数は O(log n) 
フルスキャンでは O(n)(※) として一般的に知られている。

(※) 正確には平均 n/2 に比例する関数になるがオーダ関数では 1/2 は影響度が低いので省いて表記される。

実際問題ではインデックスの第 1 キーに カーディナリティが 10 程度*1 のカラムを指定する設計をすることがあるかというと、かなり疑問符。実運用でそのような場面には会うことは少なそう。(パーティション表をともなう特殊な用途やオプティマイザが陰でこっそり・・・ということが稀にあるかないかだと思う)

• Index Compress オプション

  インデックスの作成オプション、キーの重複データをサプレスして領域を節約する。
  compress を使用することで領域の節約と作成時の DISK I/O減少によりレスポンスが向上する。
  逆に検索時には、わずかに展開コストが生じます。

CREATE INDEX table_name
ON col1_col2_col3_ix (col1, col2, col3)
COMPRESS 2 ; /* 圧縮するキーの個数を指定 col1, col2 の場合*/

(注意) インデックス・スキップ・スキャンはカラムの情報を元にするため ANALYZE TABLE が必要。
ANALYZE 済みかどうかは、以下のSQLで確認できる。(データがあるかないか)

確認用 SQL

SELECT num_distinct, density, low_value, high_value
FROM
    user_tab_columns
WHERE
    table_name = 'MY_TABLENAME';  ← 必ず大文字

---

インデックス関連事項

• Bツリー インデックス