Oracle INDEXを作成したときのパフォーマンスへの効果を探る

これまで、パフォーマンスチューニングに関する記事を公開してきました。

今回は、

「インデックスの作成はパフォーマンス改善に役立つ」

という定説について、結合クエリを例にデータを取得して再確認していきます。初心を忘れないようインデックスの作成前後の効果を素直にみていこうかと思います。

パフォーマンスチューニングに関する技術については専門書にお任せし、今回はサンプルのデータで実験を行った結果について記載します。

検証対象のテーブル

今回使用するテーブルは以下のようなテーブルです。比較的シンプルなものにします。

・100万件の受注テーブル

・500万件の受注明細テーブル（5明細/受注)

ER図は以下の通り。SI Object Browser ERを使って作成しました。

実際のDDLは英語にします。下記になります。
 

 DDLはこちら。SI Object Browser ERはER図を作成すると、DDLが自動生成されるので便利です。

CREATE TABLE DBLAB_ORDER_TBL
(
    ORDER_NO                        NVARCHAR2(10) NOT NULL,
    ORDER_DATE                      DATE NOT NULL,
    STORE_ID                        NUMBER(8,0) NOT NULL,
    ORDER_AMNT                      NUMBER(10,0) NOT NULL,
    MEMBER_ID                       NVARCHAR2(10) NOT NULL,
    CMP_TAX                         NUMBER(10,0) NOT NULL,
    FLAG_01                         NUMBER(1,0),
    FLAG_02                         NUMBER(1,0),
    SLIP_COMMENT                    NVARCHAR2(1000),
    UPDATE_DATE                     DATE NOT NULL,
    UPDATER                         NVARCHAR2(12)
)
/
ALTER TABLE DBLAB_ORDER_TBL
ADD(CONSTRAINT PK_DBLAB_ORDER_TBL PRIMARY KEY (ORDER_NO) USING INDEX)
/

--受注明細テーブル
CREATE TABLE DBLAB_ORDER_DETAIL_TBL
(
    ORDER_NO                        NVARCHAR2(10) NOT NULL,
    SO_ROW_NO                       NUMBER(3,0) NOT NULL,
    PROD_CODE                       NVARCHAR2(16) NOT NULL,
    UNITPRICE                       NUMBER(8,0) NOT NULL,
    QUANTITY                        NUMBER(4,0) NOT NULL,
    CMP_TAX_RATE                    NUMBER(3,0),
    DISCOUNT                        NUMBER(4,0) NOT NULL,
    DELIVERY_DATE                   DATE,
    UPDATE_DATE                     DATE NOT NULL,
    UPDATER                         NVARCHAR2(12)
)
/
ALTER TABLE DBLAB_ORDER_DETAIL_TBL
ADD(CONSTRAINT PK_DBLAB_ORDER_DETAIL_TBL PRIMARY KEY (ORDER_NO, SO_ROW_NO) USING INDEX)
/

検証するクエリ

下記のようなものとします。

(1)二つを結合して100万件の受注額（明細の集計）を取得する。

SELECT A.ORDER_NO
      ,SUM(B.UNITPRICE * B.QUANTITY - B.DISCOUNT) 販売額
      ,SUM(B.QUANTITY) 販売数
FROM   DBLAB_ORDER_TBL A
      ,DBLAB_ORDER_DETAIL_TBL B
WHERE  A.ORDER_NO = B.ORDER_NO
GROUP BY A.ORDER_NO
ORDER BY A.ORDER_NO;

(2)二つを結合して100万件の受注額（明細の集計）を取得する。
受注のフラグ1が1のものを対象とする。（実は全件となります）

SELECT A.ORDER_NO
      ,SUM(B.UNITPRICE * B.QUANTITY - B.DISCOUNT) 販売額
      ,SUM(B.QUANTITY) 販売数
FROM   DBLAB_ORDER_TBL A
      ,DBLAB_ORDER_DETAIL_TBL B
WHERE  A.ORDER_NO = B.ORDER_NO
AND    A.FLAG_01 = 1    -- この条件が増えている
GROUP BY A.ORDER_NO
ORDER BY A.ORDER_NO ;

二つのクエリの違いは、WHERE句のFLAG_01の有無です。処理する件数や集計方法はそのままとしています。

集計結果が同一件数となるこの二つのクエリについて、インデックスを作成する前後での効果の度合いを見たいと思います。 

検証する条件

以下の条件で検証します。

・追加インデックスなし

・インデックス追加（受注明細）

・インデックス追加（受注）

追加するインデックスは下記の通りです。SI Object Browserでインデックスを作成し、自動生成されたDDLです。

受注明細のインデックス

集計対象となる金額や個数にインデックスを付与します。

CREATE INDEX DBLAB_IDX_TEST
ON DBLAB_ORDER_DETAIL_TBL
(ORDER_NO, UNITPRICE, QUANTITY, DISCOUNT)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
VISIBLE

受注のインデックス

WHERE句の対象となるPKとフラグにインデックスを付与します。

CREATE INDEX DBLAB_IDX_ORDER_FLG1
ON DBLAB_ORDER_TBL
(ORDER_NO, FLAG_01)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
VISIBLE

検証方法

上記の条件ごとに5回クエリを発行した実行時間の平均値で検証します。1回目の実行は除き、キャッシュ情報を消さないで実行した２～６回目の結果を対象とします。

実行時間の値は、SI Object Browser のSQL画面ステータスバーに表示される値を使用します。 

検証結果

結果は以下の通りとなりました。

(1)二つを結合して100万件の受注額（明細の集計）を取得する

集計処理の対象となる項目にインデックスを付与することで効果が出る、が確認できました。

定説通りですが、100万件の結果に対して8割近くの効果があるんですね。

(2)二つを結合して100万件の受注額（明細の集計）を取得する。
受注のフラグ1が1のものを対象とする。（実は全件となります）

まず、WHERE句が一つ追加になるだけで、追加インデックスなしの実行時間が2.5倍弱になりました。全件検索が入ったためであり、これは件数に依存して増加するものでしょう。

集計処理の対象となる項目にインデックスを付与しても、大きな効果はありませんでした。

しかしながら、WHERE句のフラグに対するインデックスを付与したとたん、パフォーマンスは一気に改善されました。これも定説通りといえます。

WHERE句が増えただけで、インデックスの無い状態でのパフォーマンスには大きな隔たりがありましたが、インデックスを付与することでほぼ同じくらいの速度となりました。 [RELATED_POSTS]

追加検証（WHERE句を変えたらどうなる）

では、このパフォーマンスが上がったクエリに対して仕様変更が発生した場合にどうなるかをやってみます。例として、WHERE条件が追加になった場合、で検証してみます。

(3)二つを結合して100万件の受注額（明細の集計）を取得する。
受注のフラグ1が1のものを対象とする。さらに受注のフラグ2が2のものを対象とする。（実は全件となります）

SELECT A.ORDER_NO
      ,SUM(B.UNITPRICE * B.QUANTITY - B.DISCOUNT) 販売額
      ,SUM(B.QUANTITY) 販売数
FROM   DBLAB_ORDER_TBL A
      ,DBLAB_ORDER_DETAIL_TBL B
WHERE  A.ORDER_NO = B.ORDER_NO
AND    A.FLAG_01 = 1
AND    A.FLAG_02 = 2    -- 仕様変更でこの条件を追加！
GROUP BY A.ORDER_NO
ORDER BY A.ORDER_NO ;

このクエリのパフォーマンスは、下記となりました。

フラグ1に付与したインデックスの効果がなくなってしまいました。

フラグ2を追加したことにより、全件アクセスが発生したことに起因しています。これは実行計画からも確認できます。以下は、SI Object Browser のSQL画面から確認した実行計画の内容です。WHERE句を修正するということは、現在のパフォーマンスに影響を与えることがある。という意識を忘れてはいけないようです。

フラグ2追加前の実行計画

フラグ2追加後の実行計画

追加検証（インデックスを変えたらどうなる）

さらにインデックスを変更した場合のパフォーマンスを見てみます。以下の2パターンのデータを測定した結果をグラフに追加します。

・フラグ2だけのインデックス

CREATE INDEX DBLAB_IDX_ORDER_FLG2_ONLY
ON DBLAB_ORDER_TBL
(FLAG_02)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
VISIBLE

・受注番号、フラグ1、フラグ2の複合インデックス（インデックス再作成）

CREATE INDEX DBLAB_IDX_ORDER_FLG1_FLG2
ON DBLAB_ORDER_TBL
(ORDER_NO, FLAG_01, FLG_2)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
VISIBLE

上記の結果をプロットした結果が、以下となりました。

インデックスの追加によりパフォーマンスの改善は確認できました。

一方で、フラグ2のみのインデックスを追加と複合インデックスに変えたとでは、複合インデックスが若干パフォーマンスが勝る結果となりました。

フラグ2のみのインデックスを追加したときの実行計画は以下です。インデックスはきちんと使われていますね。

複合インデックスにした場合の実行計画は以下です。コストが抑えられる結果となります。

結論

「インデックスの作成はパフォーマンス改善に役立つ」という定説は、当たり前のことではありますが、効果があるケースとないケースを再認識できました。

パフォーマンスチューニングを施したSQLに、不具合対応でWHERE情報を変更する必要が発生し、結果としてパフォーマンスが元に戻る。なんていう事象も発生しうることも見えました。

インデックスを追加すればSELECTのクエリには効果がある。でも、INSERTとUPDATEのパフォーマンスは下がる。

対象のテーブルについてどのパフォーマンスを求めるのか。インデックスを作成する場合は、対象テーブルで処理されるクエリの件数、CRUDの頻度などを鑑みてインデックスを作成する必要がありそうです。定説は定説ですが、あくまで基本、利用者次第、バランスを見て検討ください。

次は、データ件数をもう少し多く、条件をもう少し複雑、にして再度検証してみようかなと考えています。