スケジュールキャッシュの欠点
前のセクションで説明したように、パラメータ化されたSQLに対して実行計画が生成されると、その計画はスケジュールキャッシュに保存され、以降のSQL実行ではこの実行計画が再利用されます。このような計画の再利用は計画生成時間を節約できますが、データの偏りがあるシナリオでは悪いケースを隠しています。ここで例を挙げて説明します。
テーブルt1の定義は以下のとおりです。c1列には2つの値しかなく、値0が全データの1%、値1が全データの99%を占めていると仮定します。c2列には1から10までの10個の値があり、それぞれ10%ずつを占めています。すると、SELECT * FROM t1 WHERE c1 = ? AND c2 = ?というSQLに対して、計画生成時にオプティマイザーが見た条件がc1 = 0 and c2 = 1だった場合、オプティマイザーはインデックスidx1を使用することを選択します。まずインデックスからc1 = 0を満たす1%のデータを取得し、その後インデックスを使ってテーブルに戻り、C2 = 1で条件を満たさないデータをフィルタリングして返します。この場合、1%のデータをスキャンし、インデックスを使ってテーブルに戻る1%のデータをスキャンするだけでクエリが完了します。しかし、もしその後サーバーが別のSQLを受信し、ユーザーが指定した条件がc1 = 1 and c2 = 1に変わった場合、以前に生成された計画を再利用すると、まずインデックスidx1からC1 = 1を満たす99%のデータを取得し、その後インデックスを使ってテーブルに戻り、C2 = 1で条件を満たさないデータをフィルタリングして返すことになります。この場合、99%のデータをスキャンし、インデックスを使ってテーブルに戻る99%のデータをスキャンする必要があります。明らかに、このSQLに対してはインデックスidx2を使用する方が良い選択です。なぜなら、どのような値を取る場合でも、idx2を使用する場合は常に10%のデータをスキャンし、テーブルに戻る10%のデータをスキャンするだけで済むため、SQLの実行時間がより安定するからです。
create table t1 (
c1 int,
c2 int,
c3 int,
key idx1 (c1),
key idx2 (c2)
);
このような状況に遭遇した場合、ユーザーはoutlineを使用してSQLに実行計画をバインドし、このクエリリクエストがidx2インデックスを使用するよう強制できます。もちろん、このSQLに対しては実際にはもっと良い最適化方法がありますが、これは本記事の議論の焦点ではないため、詳細には触れません。
プランバインディング
プランバインディングは、OceanBaseデータベースが提供するメカニズムであり、SQLを変更することなく、データベース内にoutlineを作成することで実行計画を制御できるようにします。現在、OceanBaseデータベースでは2種類のoutline作成方法を提供しており、構文は以下のとおりです。
/* SQL_TEXTを使用してOutlineを作成 */
CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ]
/* SQL_IDを使用してOutlineを作成 */
CREATE [OR REPLACE] OUTLINE outline_name ON sql_id USING HINT hint;
上記のSQLについて、以下の方法でoutlineを作成し、OceanBaseデータベースに対してこのSQLの実行時にインデックスidx2を使用するよう要求できます。注意点として、OceanBaseデータベースは2つのパラメータ化されたSQLが同じかどうかを判断する際、スペースや大文字小文字を区別しないため、SQL_TEXTを使用してoutlineを作成すると、create outlineステートメントに書かれたSQLと実際に実行されるSQLが完全に一致しないことがあり、結果としてoutlineがバインドできない問題が発生しやすいです。そのため、SQL_IDを使用してoutlineを作成する方が推奨されます。
/* SQL_TEXT方式 */
CREATE OUTLINE otl_idx2 ON SELECT/*+ index(T1 idx2)*/ * FROM t1 WHERE c1 = 1 AND c2 = 1;
/* SQL_ID方式 */
CREATE OUTLINE otl_idx2 ON '27F4FC32407331073407EAA24F5E5FA4'
USING HINT /*+ index(T1 idx2)*/ ;