【ORACLE】サブパーティションプルーニングのバグ

2014-11-10

有識者との問い合わせのやり取りの中で発覚した、結論からいうとOracleのバグらしいのだが、
俺自身がよくわかっていないので情報整理する意味で書いてみる。
内容としては、厄介なことに同じSQLでも結果が異なるケースをもたらすようだ。

以下のようなテーブルがあったとする。
■TEST_TRN

IDNAMEVALUEFLGSUM_ID

01	テスト１	100	0	A
02	テスト２	150	1	A
03	テスト３	750	1	B
10	テスト１０	1150	0	B
11	テスト１１	950	1	B
12	テスト１２	1350	1	A

かつ、このテーブルが「ID」項目でパーティショニングされているとする。

PARTITION BY RANGE(ID) (   
	PARTITION SYS_P001 VALUES LESS THAN('09') ,  
	PARTITION SYS_P002 VALUES LESS THAN('19') ,  
	PARTITION SYS_P003 VALUES LESS THAN('19') ,  
…  
)

このとき、TEST_TRNテーブルに対して例えば以下のような集計SQLを投げるとする

SELECT  
    SUM_ID  
   ,SUM(VALUE) as SUM_VALUE  
FROM TEST_TRN  
WHERE FLG = '1'  
GROUP BY  
    SUM_ID

イメージとしては「集計対象フラグが"1"のデータを対象に集計用のID「SUM_ID」でVALUEを集計」というものであるが、
この結果は普通に考えて

SUM_IDSUM_VALUE

A	1500
B	1700

になるはずである。
※SUM_KEY=AでFLG='1'なのはID=02（VALUE150）、ID=12（VALUE1350）、
　SUM_KEY=BでFLG='1'なのはID=03（VALUE750）、ID=11（VALUE950）

だが、このSUM_VALUEがNULLになったり、この通りの数値にならないケースがある。
これが冒頭の「サブパーティションプルーニングによる結果不正」のバグである。

====

これは厳密には「サブパーティションプルーニング機能」という、
パーティションとして指定された項目とは別の項目へのアクセスの方が最適（速い）と判断された場合に発動するOracleの機能らしく、
発動しているかどうかは実行計画（正確にはトレース）を見る事で判断ができる。
⇒実行計画上、「PARTITION RANGE SUBQUERY PARTITION:」が含まれていると、この機能が発動しているという判断がつくらしい。

ただ、いろいろ聞いたところ、
●どういう条件でこの「サブパーティションプルーニング機能」が発動するかはわからない
●仮に「サブパーティションプルーニング機能」が発動したとして、どのようなケースで「結果不正」になるかがわからない
●「サブパーティションプルーニング機能」のバグがもたらす「結果不正」の程はわからない
　ただ、実行計画が変わることによる性能劣化の報告もああがっている

ということらしく、バグの詳細がわかっていない。
私も有識者から聞いた話をもとに「恐らくこういうケースだと起き得るんだろうな」と思って
今回の記事を書いているだけである。（よってこのケースにおいても絶対起きるかはわからない）
これは、「ID」という列でパーティショニングしているにも関わらず、全然違う項目（この例だと「SUM_ID」）を使って集計している、
というケースの簡易イメージとして書き上げている。
実際、このイメージは、構造こそ簡略化しているものの、実体験のそれに近くなっている。

ちなみに回避方法は公開されていて、SQL発行前に下記のALTER文を発行することで回避できる。（実際、回避できた）

alter session set "_subquery_pruning_enabled" = false;

この"_subquery_pruning_enabled"は「サブパーティションプルーニング機能」を使用するか（True）しないか（False）の指定になる。
alter文によってその指定を使用しない（False）に変更する。

ここまでの話を総合するとOracleの「サブパーティションプルーニング機能」は単なるありがた迷惑にすぎず、
発動するだけ損なので是非発動しないようにしてしまいたいものである。
これはテーブルを設計したり、SQLをコーディングした人間からはわからない（同じ実装でも起きる時と起きない時がある）ところで、
裏でOracleが勝手にやっていることのバグなので、
「バグ」という観点だけ切り出してみればもうありがた迷惑以外の何物でもないのだが、
ただ運用中ともなるとこういった全体に関わりそうなパラメータを変更することの方が億劫でやりたくなくなる。
実際、バグが内在していたとはいえ、この機能による恩恵に授かっている箇所も中にはあるのかもしれない。
（変更によって逆に別の箇所に不具合が生じる可能性も否定できない）
経験上、こういうのはとても厄介な案件になる。