関数や式を使ったデータの抽出SQLをチューニングする方法

ここではSQLの抽出条件に関数を使った場合のチューニング方法を紹介します。

例えば、次のようなSQLです。
抽出条件で、「SUBSTR(a.col1,1,4) = ‘TEST’」とあり、col1にSUBSTR関数を使っています。

関数が使われると、その項目にインデックスがあってもインデックスの効果が発揮されません。

このようなケースでは、ファンクションインデックスを使います。
ファンクションインデックスとは、関数や式の値を事前に計算したインデックスを作成することが出来ます。

次の例では、「SUBSTR(a.col1,1,4)」をそのままインデックスの項目とすることでファンクションインデックスが作成されます。これにより、col1にSUBSTRを使った場合でもインデックス検索が有効になります。

ファンクションインデックスは関数と式に使えます。
例えば、where句に「( col2 * col3 ) = 100」のような条件がある場合、「col2 * col3」の項目でインデックスを作成するとインデックス検索が可能になります。

◆関数を使ったSQLの例

select
  a.*
from
  table1 a
where
  SUBSTR(a.col1,1,4) = ‘TEST’
;

◆ファンクションインデックスの例

create index ix_table1_01 on table1( SUBSTR(col1,1,4) );