インデックスによるSQLチューニング(カバーリングインデックス)

ORACLE SQLでインデックスを使ったチューニング方法を紹介します。

通常、インデックスというとデータの絞り込みを行う際に利用します。

ただ実は少し違った使い方もできます。
それがカバーリングインデックスと呼ばれる使い方です。

下のようなSQLでは顧客マスタ、担当者マスタ、部門マスタを参照していますが
必要なのは主キーとなる項目と名称の2つだけです。

しかし、このとき名称を取得すると同時に1レコード文のデータを読み込んでいます。
顧客マスタでは住所や代表者の名前や各種区分など、他の項目も取得していることになります。
ディスクI/O的には非常に無駄なアクセスになります。

これを解消するためにインデックスを利用します。

select
  x.顧客コード
  ,b.顧客名
  ,x.担当者コード
  ,c.担当者名
  ,x.部署コード
  ,d.部署名
  ,x.売上金額
from
  (a.顧客コード
    ,a.担当者コード
    ,a.部署コード
    ,SUM(売上金額) as 売上金額
  from
    売上 a
  group by
    a.顧客コード
    ,a.担当者コード
    ,a.部署コード
  ) x
  ,顧客マスタ b
  ,担当者マスタ c
  ,部門マスタ d
where
  x.顧客コード = b.顧客コード
  and x.担当者コード = c.担当者コード
  and x.部署コード = d.部署コード
;

下のように主キーとなる項目と名称の2つのみでインデックスを作成します。
このインデックスを利用することで他の項目の取得を減らし、僅かですがI/Oを改善することが出来ます。

◆インデックスの例

CREATE INDEX 顧客マスタ_idx0
  ON test01.顧客マスタ(顧客コード,顧客名) TABLESPACE index_tbs;

CREATE INDEX 担当者マスタ_idx0
  ON test01.担当者マスタ(担当者コード,担当者名) TABLESPACE index_tbs;

CREATE INDEX 部門マスタ_idx0
  ON test01.部門マスタ(部署コード,部署名) TABLESPACE index_tbs;

もしインデックスが使われない場合はヒントを使ってみても良いです。

select /*+INDEX(b 顧客マスタ_idx0) INDEX(b 担当者マスタ_idx0) INDEX(b 部門マスタ_idx0) */
  x.顧客コード
  ,b.顧客名
  ,x.担当者コード
  ,c.担当者名
  ,x.部署コード
  ,d.部署名
  ,x.売上金額
from
  (a.顧客コード
    ,a.担当者コード
    ,a.部署コード
    ,SUM(売上金額) as 売上金額
  from
    売上 a
  group by
    a.顧客コード
    ,a.担当者コード
    ,a.部署コード
  ) x
  ,顧客マスタ b
  ,担当者マスタ c
  ,部門マスタ d
where
  x.顧客コード = b.顧客コード
  and x.担当者コード = c.担当者コード
  and x.部署コード = d.部署コード
;

Oracle SQLチューニング パフォーマンス改善と事前対策に役立つ (DB selection) [ 加藤祥平 ]

価格:2,808円
(2018/11/19 19:23時点)

インデックスによるSQLチューニング(カバーリングインデックス)” に対して1件のコメントがあります。

この投稿はコメントできません。