Oracleチューニングの裏技！標準機能だけでパーティションテーブルを作る方法
こんにちは羽山です。
今回はOracleデータベースの標準機能だけでテーブルのパーティション化とそれに伴うパフォーマンス改善を実現する方法を紹介します。
本来Oracleでテーブルのパーティション化をするためには対応するエディションと追加オプションが必要なので活用できる環境にいる方は多くはなさそうですが、パーティション化自体のメリットは大規模DBだけでなく、中・小規模のDBでもパフォーマンスチューニングの手段として有用です。
今回のテクニックはそのパーティションテーブルのおいしい部分を再現できるので、今までパーティションテーブルに関心を持ったことがない方にこそ、是非参考にしていただけたらと思っています。
本稿ではある程度のインデックスの基礎知識を前提としています。もし先にインデックスについて学びたい場合は前回のインデックスに関する記事を参照することでより理解が深まります。
テーブルのパーティション化とは
まずは Oracle Partitioning オプションで利用できる本来のパーティション化について簡単に解説します。
パーティション化とは1つのテーブルを小さな複数のテーブルに分割して管理する機能です。
テーブルが分割されていても SELECT や DML は透過的に扱えるので分割された実テーブルを意識する必要なく利用可能です。
例えば注文テーブルを1ヶ月ごとにパーティション化する場合は以下のような partition by句で定義します。
create table orders (
id number(12) not null,
...
order_date date not null,
...
)
partition by range(order_date)
(
partition orders_202001 values less than(to_date('2020-02-01','yyyy-mm-dd')),
partition orders_202002 values less than(to_date('2020-03-01','yyyy-mm-dd')),
...
);
この例では期間の範囲を手動で定義していますがインターバルパーティションを利用すれば自動的にパーティションを作成することも可能です。
パーティション化されたテーブルへの SELECT は前述の通り透過的に扱えますが、より効率的にデータ取得するためにはある程度パーティション化を意識する必要があります。
例えばパーティションキー（この場合は order_date）を WHERE句の条件としてバインド変数を用いずに絞り込むと静的パーティション・プルーニングが行われて実行計画レベルでアクセスするパーティションを確定できます。
では続いて本稿の主題である標準機能でパーティション化を実現する方法の解説に入ります。
標準機能でパーティション化
解説にあたって以下の注文テーブルを作成して1000万件のテストレコードを登録しました。
create table orders (
id number(12) not null,
customer_id number(12) not null,
order_date date not null,
shipped number(1) not null,
prefecture_id number(2) not null,
note varchar2(100)
);
テストレコードの注文日(order_date)は2020年の1年間のほぼ均等なランダムな日時となっています。
まずは注文日が2020年01月の1ヶ月間に含まれるレコードに対する擬似パーティションを作ってみます。
create unique index idx_orders_202001 on orders (
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then id end,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then customer_id end,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then order_date end,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then shipped end,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then prefecture_id end
);
この時点でおおよそネタバレしていますが、擬似パーティションの実態はインデックスです。
そして対象カラムは計算結果に対してインデックスを作成するファンクションインデックスとなっています。
擬似パーティションと呼んでいるので当然ではありますが、このインデックスには2020年01月の注文レコードしか含まれないのでインデックスのサイズを小さく保つことができます。
今回の例では orders テーブルは12ヶ月分のレコードで 984MB ほどありましたが、1ヶ月分のみを持つ擬似パーティション（インデックス）のサイズは 32MB 程度でした。後述する note カラムを落としている点も差の要因として大きいですが、今後運用期間が長くなれば長くなるほど差は広がっていきます。
次にこの擬似パーティションからデータを取得します。
擬似パーティションからデータを取得するためには明示的にインデックスからデータを取得する記述方法が必要になります。
select /*+ index_ffs(orders idx_orders_202001) */
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then id end as id,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then customer_id end as customer_id,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then order_date end as order_date,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then shipped end as shipped,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then prefecture_id end as prefecture_id
from
orders
where
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then id end is not null;
-- 実行計画 --
SELECT STATEMENT
INDEX FAST FULL SCAN IDX_ORDERS_202001
実行計画から INDEX FAST FULL SCAN を用いて高速にデータ取得していることを確認できます。また TABLE ACCESS BY INDEX ROWID が実行計画に登場しないことから取得するデータがインデックスにすべて含まれていたためテーブルへのアクセスが必要なかったことも分かります。
このようにインデックスが持つデータだけで処理を完了してテーブルからの読み込みを省略可能なインデックスのことをカバリングインデックスと呼びます。擬似パーティションはインデックスへのアクセスだけで情報を取得するために必要となるカラムをすべてインデックス内に含めて作成します。
また今回はパーティション化にあたり note カラムをあえて落としています。
なぜならば orders テーブルのレコードを月単位で取得する用途は数値の集計がメインなので、注文時のメモが登録される note カラムを必要とするシーンがなかったからです。
Oracle Partitioning はパーティション化にあたってカラム単位で分離することはできませんが、今回紹介する擬似パーティションではカラム単位で必要なものに絞ることができます。
必要なカラムを限定できればそれだけインデックスのサイズは小さくなって読み込み負荷は減るのでメリットがあります。
今回の例では 32MB まで小さくできたインデックスをさらに INDEX FAST FULL SCAN でマルチブロックリードするので、DBバッファキャッシュに頼らずにストレージから読み込む前提で設計しても痛くもかゆくもない状況を作ることができます。
では次に動作原理を解説します。
インデックスを使った擬似パーティションの動作原理
みなさんも一度くらいはインデックスを作成したカラムに対して is null で検索してしまい、インデックスが使われなくて悩んだことはありませんか？
そういった経験もあってか大抵の方はインデックスに null が入らないことを認識しているかと思います。対象カラムが null の場合はインデックスの構造内にそのレコード自体が登録されないため、必然的に null であるレコードをインデックスで検索することはできません。
複合インデックスでも同様で、対象カラム全てが null だった場合はインデックス対象から外されます。
今回の擬似パーティションはこの仕様を利用して必要なレコードのみをインデックスに登録しています。
まずは先ほどの create index 文の最初のカラムを見てみましょう。
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then id end
case when の条件式は trunc(order_date, 'mm') が2020年01月ならば id を返し、それ以外ならば null となります。つまり2020年01月以外はすべて null になる条件式です。
続く customer_id なども同様の条件で、order_date が2020年01月ならばカラム値をそのまま返し、それ以外なら null です。
つまり order_date が2020年01月以外の場合は全カラムが null になるので、インデックスに含まれるレコードは trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') の条件に合うレコードだけになります。
これで擬似パーティション化されたインデックスを作ることはできましたが、今度はこのインデックスの使い方を解説します。
まずは擬似パーティションに含まれるレコードに絞り込む必要があります。
where
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then id end is not null
WHERE句の case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then id end はインデックス対象になっているカラムですが、この結果が null ではないということは、今回作成した擬似パーティションに含まれるレコードということになります。それはつまり、Oracleのオプティマイザ（＝SQLから実行計画を構築する機能）に対して今回作成したインデックスが使えると示すことができます。
また、trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') が true であるレコードに絞り込めるので、それはつまり2020年01月で絞り込んだのと同じ状態になります。
絞り込みは is not null 相当の意味が含まれていればインデックス対象のどのカラムでも問題ないので、例えば以下のような別の条件にすることもできます。
where
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then order_date end
between to_date('2020-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_date('2020-01-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
この条件は 2020-01-01 から 2020-01-09 までの範囲で指定していますが、当然 is not null でもあるので問題なく動作します。
どのような条件を利用してもかまいませんが、SELECT句や WHERE句に指定するカラムは必ずインデックスを作成した case when 式をそのまま記述してください。インデックスに含まれるデータは case when の実行結果なので、それ以外のデータを取得しようとするとテーブルへアクセスしてしまいます。
また、case when trunc(order_date, 'mm') = ... id end is not null という条件で絞るカラムは、この場合の id カラムのように not null 制約が付いているかどうかも注意しましょう。nullable の場合は、それに対して is not null で絞り込んで望む結果が得られるかどうかは、そのテーブルに対する要件次第です。
もし not null 制約のついた適当なカラムがない場合は以下のように明示的なフラグを追加することもできます。
-- インデックス作成
create unique index idx_orders_202001 on orders (
-- 明示的な not null のフラグとして 1 を返す
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then 1 end,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then id end,
...
-- 検索時の絞り込み
select
...
where
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then 1 end is not null
次は SELECT句での値の取得方法です。
WHERE句と同様に case when で指定したままで指定します。
誤って select id, customer_id … としてしまうとテーブルからのデータ取得になってしまうので、実際に以下のSQLで確認してみましょう。
select /*+ index(orders idx_orders_202001) */
id,
customer_id,
order_date,
shipped,
prefecture_id
from
orders
where
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then id end is not null;
このSQLで指定した SELECT句のカラムはインデックスに含まれていないため以下の実行計画となり、インデックスにアクセスした後に TABLE ACCESS BY INDEX ROWID で元の orders テーブルからデータを取得しています。
-- 実行計画 --
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID ORDERS
INDEX FULL SCAN IDX_ORDERS_202001
次は case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then id end という形式で値を取得します。
select /*+ index_ffs(orders idx_orders_202001) */
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then id end as id,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then customer_id end as customer_id,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then order_date end as order_date,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then shipped end as shipped,
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then prefecture_id end as prefecture_id
from
orders
where
case when trunc(order_date, 'mm') = to_date('2020-01', 'yyyy-mm') then id end is not null;
-- 実行計画 --
SELECT STATEMENT
INDEX FAST FULL SCAN IDX_ORDERS_202001
それぞれの case when の実行結果はインデックスに含まれているのでテーブルへのアクセスを省略できます。そして当然 case when の条件はいずれも true となるので元のカラム値を取得できます。
次に重要なのが INDEX FAST FULL SCAN の利用を指示するヒント句です。
select /*+ index_ffs(orders idx_orders_202001) */
ファンクションインデックスを利用したカバリングインデックスによるテーブルのパーティション化は複雑故にOracleのオプティマイザがインデックスをうまく利用してくれない場合もあります。
または INDEX FAST FULL SCAN ではなく、INDEX FULL SCAN が選択されることもあります。
INDEX FAST FULL SCAN は INDEX FULL SCAN と違ってマルチブロックリードが可能なのでI/Oパフォーマンスが大きく異なります。
パーティション化のメリットで最も大きいのはパーティション単位でマルチブロックリードによるフルスキャンができる点なので、index_ffs ヒント句を使ってマルチブロックリードの恩恵を得られる実行計画に誘導しましょう。
マルチブロックリードとは
複数ブロックを同時取得することで大幅にI/Oパフォーマンスが上昇する機能
テーブルやインデックス全体を取得する場合に利用可能なのでパーティション化と相性が良い
詳しくはこちらの記事を参照ください
値のリストでパーティション化
Oracle Partitioning には値のリストでパーティション化する機能があるので、次はその再現方法も紹介します。
以下のような擬似パーティション（インデックス）を作成します。
create unique index idx_orders_tnof on orders (
case when prefecture_id in (13, 23, 27, 40) then order_date end,
case when prefecture_id in (13, 23, 27, 40) then id end,
case when prefecture_id in (13, 23, 27, 40) then customer_id end,
case when prefecture_id in (13, 23, 27, 40) then shipped end,
case when prefecture_id in (13, 23, 27, 40) then prefecture_id end
);
prefecture_id はJISX0401:1973の都道府県コードが入っているとします。
運営部門からの要件で東京(13)、愛知(23)、大阪(27)、福岡(40)の4都府県を重点的なモニタリング対象地域としてデータ分析したいという相談を受けました。
そこでその4都府県に絞ったパーティションを作りつつ、今回は order_date の範囲検索も同時に実施したいのでインデックス対象カラムの先頭に持ってきました。
ではこの擬似パーティションを利用して4都府県の2020年01月の注文実績を取得してみましょう。
select /*+ index(orders idx_orders_tnof) */
case when prefecture_id in (13, 23, 27, 40) then id end as id,
case when prefecture_id in (13, 23, 27, 40) then order_date end as order_date,
case when prefecture_id in (13, 23, 27, 40) then customer_id end as customer_id,
case when prefecture_id in (13, 23, 27, 40) then shipped end as shipped,
case when prefecture_id in (13, 23, 27, 40) then prefecture_id end as prefecture_id
from
orders
where
case when prefecture_id in (13, 23, 27, 40) then order_date end
between to_date('2020-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_date('2020-01-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
このSQLの実行計画は以下です。
-- 実行計画 --
SELECT STATEMENT
INDEX RANGE SCAN IDX_ORDERS_TNOF
TABLE ACCESS BY INDEX ROWID がなく、カバリングインデックスとして動作しています。しかし INDEX FAST FULL SCAN ではなく INDEX RANGE SCAN になっています。
INDEX FAST FULL SCAN はインデックスをフルスキャンする場合のみ利用可能ですが、今回は case when prefecture_id in (13, 23, 27, 40) then order_date end に対する範囲検索をするSQLなのでこれはしかたありません。擬似パーティション全体を取得しない場合はマルチブロックリードができないデメリットがあります。
とはいえ INDEX RANGE SCAN となっても擬似パーティションを利用した方がパフォーマンスにメリットがあることを次の項で解説します。
擬似パーティションに対する INDEX RANGE SCAN のメリット
前項に引き続き、与えられた要件は4都府県に対する任意の月の販売実績の取得（集計）だとします。
そこでまずは prefecture_id, order_date に対する普通の複合インデックスと比較してみます。
create index idx_orders_pref_order_date on orders (
prefecture_id,
order_date
);
select
id,
order_date,
customer_id,
shipped,
prefecture_id
from
orders
where
prefecture_id in (13, 23, 27, 40)
and order_date
between to_date('2020-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_date('2020-01-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
このSQLの実行計画は以下のようになります。
-- 実行計画 --
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID ORDERS
INDEX RANGE SCAN IDX_ORDERS_PREF_ORDER_DATE
prefecture_id に指定された4都府県を INLIST ITERATOR で繰り返して、それぞれ order_date の範囲検索を実行した上で TABLE ACCESS BY INDEX ROWID でテーブルからデータを取得しています。
前項の擬似パーティション化されたインデックスは1回の INDEX RANGE SCAN でデータを取得できていましたが、普通の複合インデックスの場合は INDEX RANGE SCAN を4回実施するのでその分のオーバーヘッドがあります。
またカバリングインデックスでもないので TABLE ACCESS BY INDEX ROWID で最終的にテーブルからデータを取得する必要があります。この点は大きな違いになります。
次は擬似パーティション化されていない普通のカバリングインデックスと比較してみましょう。
create index idx_orders_pref_order_date on orders (
prefecture_id,
order_date,
id,
customer_id,
shipped
);
-- 実行計画 --
SELECT STATEMENT
INLIST ITERATOR
INDEX RANGE SCAN IDX_ORDERS_PREF_ORDER_DATE
INLIST ITERATOR で4回繰り返してしまう問題は依然として残りますが、これなら TABLE ACCESS BY INDEX ROWID が不要になるので先ほどの擬似パーティションとパフォーマンス差はさほどなくなります。
しかしこのカバリングインデックスには1つ問題があり、それはインデックスのサイズです。
普通のインデックスなのでテーブルに含まれる全レコードに対してインデックスが作成されます。
例えば1000万件のテストデータで実際にインデックスを作成してみると、擬似パーティション化したインデックスは 32MB 程度だったのに比べ、今回作成した普通のカバリングインデックスは 387MB と10倍以上のサイズになってしまいました。
ストレージは大容量でも用意しやすいのでさほど問題ないとしても、DBバッファキャッシュに際限なく潤沢なメモリ割り当てをすることはできません。定義されたインデックスのサイズはそのままDBバッファキャッシュへの圧迫となるので利用用途に対して必要最低限のインデックスにする方がベターです。
このような理由から擬似パーティションに対して INDEX RANGE SCAN が選択される場合でも十分にメリットがあります。
まとめ
さて、いかがでしたか？今回は憧れの機能であるパーティションテーブルを標準機能だけで擬似的に再現する方法を紹介しました。
本来のパーティションテーブルはライセンス費用の都合上から大規模DBでないとなかなか使えないと思われます。しかし実際にはマルチブロックリードを駆使する実行計画やDBバッファキャッシュを節約できる点などから、むしろ中・小規模のギリギリで頑張っているDBでこそ大活躍する機能だったりします。
今回紹介したテクニックは公式パーティションテーブルのおいしいところを再現できるので、是非ここぞというシーンがあれば活用いただけたらと思います。
一方で MySQL や PostgreSQL などは標準でパーティションテーブルに対応しているのと、PostgreSQL の場合は部分インデックスという機能も標準で用意されていて、create index ... where ...; という形式で今回紹介したようなインデックスを作成することができます。
Oracleにもいずれ部分インデックスなどがサポートされればいいのですが、パフォーマンスの特性として Oracle Partitioning と競合する機能になるのでなかなか難しいのでしょうね。
