SQLクエリにおいて、結合操作に関連するHintには、特定の結合アルゴリズムを有効または無効にするHintが含まれます。詳細は以下のとおりです。
Hintタイプ |
説明 |
|---|---|
USE_MERGE |
このHintで指定されたテーブルが結合の右テーブルとして使用される場合、ソートマージ結合アルゴリズムを使用します。逆操作はNO_USE_MERGEです。 |
NO_USE_MERGE |
このHintで指定されたテーブルが結合の右テーブルとして使用される場合、ソートマージ結合アルゴリズムを使用しません。逆操作はUSE_MERGEです。 |
USE_HASH |
このHintで指定されたテーブルが結合の右テーブルとして使用される場合、HASH-JOINアルゴリズムを使用します。逆操作はNO_USE_HASHです。 |
NO_USE_HASH |
このHintで指定されたテーブルが結合の右テーブルとして使用される場合、HASH-JOINアルゴリズムを使用しません。逆操作はUSE_HASHです。 |
USE_NL |
このHintで指定されたテーブルが結合の左テーブルとして使用される場合、NL-JOINアルゴリズムを使用します。逆操作はNO_USE_NLです。 |
NO_USE_NL |
このHintで指定されたテーブルが結合の左テーブルとして使用される場合、NL-JOINアルゴリズムを使用しません。逆操作はUSE_NLです。 |
PQ_DISTRIBUTE |
結合操作のデータ配分方法を制御します。 |
PQ_MAP |
結合操作でフォロワーマッピング戦略を使用するよう指定します。 |
USE_NL_MATERIALIZATION |
ネストされたループ結合内の左テーブルのマテリアライゼーションを強制します。逆操作はNO_USE_NL_MATERIALIZATIONです。 |
NO_USE_NL_MATERIALIZATION |
ネストされたループ結合内の左テーブルのマテリアライゼーションを防ぎます。逆操作はUSE_NL_MATERIALIZATIONです。 |
PX_JOIN_FILTER |
最適化エンジンにJOIN FILTERの使用を指示します。逆操作はNO_PX_JOIN_FILTERです。 |
NO_PX_JOIN_FILTER |
最適化エンジンにHASH JOINでJOIN FILTERを無効にするよう指示します。逆操作はPX_JOIN_FILTERです。 |
PX_PART_JOIN_FILTER |
最適化エンジンにPART FILTERを手動で有効にするよう指示します。逆操作はNO_PX_PART_JOIN_FILTERです。 |
NO_PX_PART_JOIN_FILTER |
最適化エンジンにPART FILTERを手動で無効にするよう指示します。逆操作はNO_PX_PART_JOIN_FILTERです。 |
USE_MERGE ヒント
USE_MERGE ヒントは、このヒントで指定されたテーブルが結合の右テーブルとして使用される場合に、ソート・マージ結合アルゴリズムを使用するよう指示します。その逆操作は NO_USE_MERGE です。
構文
/*+ USE_MERGE ( [ @queryblock ] tablespec [ tablespec ]... ) */
使用方法と注意点
USE_NLおよびUSE_MERGEヒントは、LEADINGまたはORDEREDヒントと併用することを推奨します。引用されたテーブルが結合の右テーブルの場合、オプティマイザーはこれらのヒントを使用します。
引用されたテーブルが左テーブルの場合、ヒントは無視されます。
USE_MERGEは、テーブルを右テーブルとして指定した場合にMERGE JOINアルゴリズムを使用します。OceanBaseデータベースでは、
MERGE JOINアルゴリズムを使用するには等価条件のjoin-conditionが必要なため、等価条件のない2つのテーブルを結合する場合、USE_MERGEは無効になります。
例
-- USE_MERGE ヒントを使用して、オプティマイザーにソート・マージ結合アルゴリズム（sort-merge join）を使用してクエリを実行するよう指示します。
-- employees テーブルと departments テーブルの結合操作では、employees テーブルが右テーブル、departments テーブルが左テーブルとなります。
SELECT /*+ USE_MERGE(employees departments) */ *
FROM employees, departments
WHERE employees.department_id = departments.department_id;
NO_USE_MERGE ヒント
NO_USE_MERGE ヒントは、指定されたテーブルを左テーブルとして使用し、別の行リソースと結合する際に、USE_MERGE ヒントで使用されている結合を除外するようオプティマイザーに指示します。その逆操作は USE_MERGE です。
構文
/*+ NO_USE_MERGE ( [ @queryblock ] tablespec [ tablespec ]... ) */
例
-- NO_USE_MERGE ヒントを使用して、オプティマイザーにソート・マージ結合アルゴリズムを使用せずにクエリを実行するよう指示します。
-- employees テーブルと departments テーブルの結合操作では、sort-merge join アルゴリズムの使用が除外されます。
SELECT /*+ NO_USE_MERGE(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id;
USE_HASH ヒント
USE_HASH ヒントで指定されたテーブルが結合の右テーブルとして使用される場合、HASH-JOINアルゴリズムを使用します。その逆操作は NO_USE_HASH です。
構文
/*+ USE_HASH ( [ @queryblock ] tablespec [ tablespec ]... ) */
例
-- USE_HASH ヒントを使用して、オプティマイザーにハッシュ結合アルゴリズム（HASH-JOIN）を使用してクエリを実行するよう指示します。
-- orders テーブルと order_items テーブルの結合操作では、orders テーブルが右テーブル、order_items テーブルが左テーブルとなります。
SELECT /*+ USE_HASH(l h) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 2400;
NO_USE_HASH ヒント
NO_USE_HASH ヒントで指定されたテーブルが結合の右テーブルとして使用される場合、HASH-JOIN アルゴリズムは使用されません。その逆操作は USE_HASH です。
構文
/*+ NO_USE_HASH ( [ @queryblock ] tablespec [ tablespec ]... ) */
例
-- NO_USE_HASH ヒントを使用して、オプティマイザーにハッシュ結合アルゴリズムを使用しないように指示してクエリを実行します。
-- employees テーブルと departments テーブルの結合操作では、HASH-JOIN アルゴリズムの使用が除外されます。
SELECT /*+ NO_USE_HASH(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id;
USE_NL ヒント
USE_NL ヒントで指定されたテーブルが結合の左テーブルとして使用される場合、ネストループ結合（NL-JOIN）アルゴリズムを使用します。その逆操作は NO_USE_NL です。
USE_NLヒントとUSE_MERGヒントは、LEADINGまたはORDEREDヒントと併用することを推奨します。引用されたテーブルが結合の左テーブルの場合、オプティマイザーはこれらのヒントを使用します。
引用されたテーブルが右テーブルの場合、ヒントは無視されます。
構文
/*+ USE_NL ( [ @queryblock ] tablespec [ tablespec ]... ) */
例
以下のクエリ例に示すように、Hintによりネストループが強制的に実行され、テーブル全体をスキャンしてordersテーブルにアクセスし、フィルター条件l.order_id = h.order_idを各行に適用します。フィルター条件を満たす各行について、インデックスorder_idを使用してorder_itemsにアクセスします。
-- USE_NL Hintを使用して、オプティマイザーにネストループ結合アルゴリズム（NL-JOIN）でクエリを実行するよう指示します。
-- ordersテーブルとorder_itemsテーブルの結合操作では、ordersテーブルが右テーブル、order_itemsテーブルが左テーブルとなります。
SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
FROM orders h, order_items l
WHERE l.order_id = h.order_id;
NO_USE_NL Hint
NO_USE_NL Hintは、指定されたテーブルが結合の左テーブルとして使用される場合、ネストループ結合（NL-JOIN）アルゴリズムを使用しないよう指示します。その逆操作はUSE_NLです。
構文
/*+ NO_USE_NL ( [ @queryblock ] tablespec [ tablespec ]... ) */
例
-- NO_USE_NL Hintを使用して、オプティマイザーにネストループ結合アルゴリズムを使用せずにクエリを実行するよう指示します。
-- employeesテーブルとdepartmentsテーブルの結合操作では、NL-JOINアルゴリズムの使用を除外します。
SELECT /*+ NO_USE_NL(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id;
PQ_DISTRIBUTE Hint
PQ_DISTRIBUTE Hintは、パラレルクエリ実行時に、オプティマイザーに対して、パラレルクエリのプロデューサー（Producer、クエリ結果の行データを生成する役割）とコンシューマー（Consumer、これらの行データを受信して処理する役割）サーバー間でデータをどのように分配するかを指示するために使用します。PQ_DISTRIBUTE Hintを使用して、結合操作や負荷操作における行データの分散方法を制御できます。
特に大量のデータを処理する必要があるパラレルクエリシナリオでは、PQ_DISTRIBUTE はリソース使用量を最適化し、クエリ性能を向上させることができます。
構文
/*+ PQ_DISTRIBUTE
( [ @queryblock ] tablespec
{ distribution | outer_distribution inner_distribution }
) */
結合の分配を制御する
結合の配布方法を制御するには、2種類の分配方法を指定することができます。
構文の下部の分岐に示すように：
outer_distributionは左テーブルのデータ配布方法を指定します。inner_distributionは右テーブルのデータ配布方法を指定します。
配布方法には HASH、BROADCAST、PARTITION、NONE が含まれます。有効な組み合わせは以下の6種類のみです：
ディストリビューション方式 |
説明 |
|---|---|
| HASH, HASH | 接続キー上のハッシュ関数を使用して、各テーブルの行をクエリサーバーにマッピングします。マッピング完了後、各クエリサーバーは一対の結果パーティション間で結合を実行します。テーブルのサイズが比較可能で、結合操作がハッシュ結合またはソートマージ結合によって実装される場合、このディストリビューション方式を使用することを推奨します。 |
| BROADCAST, NONE | 右テーブルのすべての行が各クエリサーバーにブロードキャストされます。左テーブルの行はランダムにパーティション化されます。 右テーブルが左テーブルに比べて非常に小さい場合、この分散方法を使用することを推奨します。通常、左テーブルのサイズにクエリサーバーの数を掛けたものが右テーブルのサイズを上回る場合も、このディストリビューション方式を使用することを推奨します。 |
| NONE, BROADCAST | 左テーブルのすべての行が各クエリサーバーにブロードキャストされます。右テーブルの行はランダムにパーティション化されます。 左テーブルが右テーブルに比べて非常に小さい場合、この分散方法を使用することを推奨します。通常、左テーブルのサイズにクエリサーバーの数を掛けたものが右テーブルのサイズを下回る場合も、このディストリビューション方式を使用することを推奨します。 |
| PARTITION, NONE | 左テーブルの行は、右テーブルのパーティションを使用してマッピングされます。左テーブルは結合キー上でパーティション化されていなければなりません。 右テーブルのパーティション数がクエリサーバー数の倍数、またはそれに近い場合、このディストリビューション方式を使用することを推奨します。例えば、14個のパーティションと15個のクエリサーバーがある場合です。 注意 左テーブルがパーティション化されていない場合、または結合時にパーティションキーで等分されていない場合、オプティマイザーはこのヒントを無視します。 |
| NONE, PARTITION | 右テーブルの行は、左テーブルのパーティションを使用してマッピングされます。右テーブルは結合キー上でパーティション化されていなければなりません。 右テーブルのパーティション数がクエリサーバー数の倍数、またはそれに近い場合、このディストリビューション方式を使用することを推奨します。例えば、14個のパーティションと15個のクエリサーバーがある場合です。 注意 右テーブルがパーティションキーでパーティション化されていない場合、または結合時に等分されていない場合、オプティマイザーはこのヒントを無視します。 |
| NONE, NONE | 各クエリサーバーは、一対のマッチしたパーティション間で結合操作を実行し、各テーブルには1つずつあります。2つのテーブルは結合キー上で等分されていなければなりません。 |
例
以下のクエリ例のように、ハッシュ結合を使用して2つのテーブル r と s を結合することを指定します。以下のクエリには、ハッシュディストリビューション方式を使用するヒントが含まれています：
SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s) */ column_list
FROM r, s
WHERE r.c = s.c;
右テーブル r をブロードキャストする場合、ヒントを含むクエリステートメントは次のとおりです：
SELECT /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */ column_list
FROM r, s
WHERE r.c = s.c;
USE_NL_MATERIALIZATION ヒント
USE_NL_MATERIALIZATION ヒントは、オプティマイザーに対し、テーブルを左テーブル（サブツリー）として指定する場合に、データをキャッシュするためのマテリアライズドオペレーターを生成するよう強制的に指示します。その逆操作は NO_USE_NL_MATERIALIZATION です。
構文
/*+ USE_NL_MATERIALIZATION ( [ @queryblock ] tablespec [ tablespec ]... ) */
例
-- USE_NL_MATERIALIZATION ヒントを使用して、オプティマイザーにネストされたループ結合でdepartmentsテーブルをマテリアライズするよう指示します。
SELECT /*+ USE_NL_MATERIALIZATION(departments) */ *
FROM employees, departments
WHERE employees.department_id = departments.department_id;
NO_USE_NL_MATERIALIZATION ヒント
NO_USE_NL_MATERIALIZATION ヒントは、オプティマイザーに対し、テーブルを左テーブル（サブツリー）として指定する場合に、データをキャッシュするためのマテリアライズドオペレーターの生成を回避するよう強制的に指示します。その逆操作は USE_NL_MATERIALIZATION です。
構文
/*+ NO_USE_NL_MATERIALIZATION ( [ @queryblock ] tablespec [ tablespec ]... ) */
例
-- NO_USE_NL_MATERIALIZATION ヒントを使用して、オプティマイザーがネストされたループ結合でdepartmentsテーブルをマテリアライズするのを防ぎます。
-- これは、毎回のループ結合でキャッシュされたマテリアライズド結果を使用する代わりに、departmentsテーブルのデータに再アクセスすることを意味します。
SELECT /*+ NO_USE_NL_MATERIALIZATION(departments) */ *
FROM employees, departments
WHERE employees.department_id = departments.department_id;
結合フィルター（Join Filter）ヒント
Join Filter 関連のヒントは合計で4種類あり、最初の2つは通常の Join Filter を制御し、後の2つは部分 Join Filter を制御します：
PX_JOIN_FILTERヒントNO_PX_JOIN_FILTERヒントPX_PART_JOIN_FILTERヒントNO_PX_PART_JOIN_FILTERヒント
注意点として、これら4つのヒントは並列実行環境でのみ有効であり、非並列環境では顕著な効果はありません。
それぞれの構文とパラメータの説明は以下の通りです：
PX_JOIN_FILTER ヒント
並列実行環境において、PX_JOIN_FILTER ヒントは、オプティマイザーにHASH JOINでJOIN FILTERを使用するよう指示します。このヒントを使用すると、特定のテーブルをHASH JOINの右テーブルとして指定した際に、実行時のフィルタリングにJOIN FILTERを使用することができます。その逆操作は NO_PX_JOIN_FILTER です。
構文
/*+ PX_JOIN_FILTER ( [ @qb_name ] filter_table [ left_tables ] [real_filter_table]) */
パラメータの説明
qb_name：ヒントが有効なクエリブロックを指定します。オプションパラメータです。filter_table：JOIN FILTERをプッシュダウンする単一テーブルを記述します。サブクエリの場合、ここではビューの名前を指定する必要があります。left_tables：JOIN FILTERを割り当てる際のHASH-JOINの左テーブルを指定します。オプションパラメータです。real_filter_table：サブクエリ内で実際にJOIN FILTERをプッシュダウンする単一テーブル。
NO_PX_JOIN_FILTER ヒント
NO_PX_JOIN_FILTER ヒントは、オプティマイザーにHASH JOINでJOIN FILTERを無効にするよう指示します。その逆操作は PX_JOIN_FILTER です。
構文
/*+ NO_PX_JOIN_FILTER( table ) */
PX_PART_JOIN_FILTER ヒント
PX_PART_JOIN_FILTER ヒントは、オプティマイザーにPART FILTERを手動で有効にするよう指示します。その逆操作は NO_PX_PART_JOIN_FILTER です。
構文
/*+ PX_PART_JOIN_FILTER ( [ @qb_name ] filter_table [ left_tables ] [real_filter_table]) */
NO_PX_PART_JOIN_FILTER ヒント
NO_PX_PART_JOIN_FILTER ヒントは、オプティマイザーにPART FILTERを手動で無効にするよう指示します。その逆操作は PX_PART_JOIN_FILTER です。
構文
/*+ NO_PX_PART_JOIN_FILTER (table) */
適用シナリオ
ジョインフィルタ（Join Filter）ヒントのこれら4種類のヒント（PX_JOIN_FILTER、NO_PX_JOIN_FILTER、PX_PART_JOIN_FILTER、NO_PX_PART_JOIN_FILTER）は通常、leading と use_hash ヒントと併用されます。leading と use_hash と組み合わせて使用しない場合、他の結合順序や結合アルゴリズムが生成されることで、これらのヒントが無効になる可能性があります。
汎用シナリオ
Join Filterタイプのヒントは一般的に LEADING と USE_HASH と共に使用されます。そうでない場合、異なる結合順序や結合アルゴリズムが生成されたことで無効になる可能性があります。
まず、パーティションテーブルを作成します：
CREATE TABLE t1 (
c1 INT,
c2 INT,
c3 INT,
c4 INT
) PARTITION BY HASH(c1) PARTITIONS 10;
JOIN FILTERの強制使用
以下のSQLを使用して、アプリケーションによるJoin Filterの適用を強制できます。
EXPLAIN SELECT
/*+ PARALLEL(2) LEADING(a b) USE_HASH(b) PQ_DISTRIBUTE(b BC2HOST NONE)
PX_JOIN_FILTER(b)
PX_PART_JOIN_FILTER(b)
*/ *
FROM t1 a, t1 b WHERE a.c1 = b.c1;
または：
EXPLAIN SELECT
/*+ PARALLEL(2) LEADING(a b) USE_HASH(b) PQ_DISTRIBUTE(b BC2HOST NONE)
PX_JOIN_FILTER(b a)
PX_PART_JOIN_FILTER(b a)
*/ *
FROM t1 a, t1 b WHERE a.c1 = b.c1;
出力される実行計画の例は以下のとおりです。
===============================================================
| ID | OPERATOR | NAME | EST. ROWS | COST |
---------------------------------------------------------------
| 0 | PX COORDINATOR | | 1 | 456 |
| 1 | EXCHANGE OUT DISTR | :EX10001| 1 | 456 |
| 2 | SHARED HASH JOIN | | 1 | 455 |
| 3 | JOIN FILTER CREATE | :BF0001 | 1 | 228 |
| 4 | PART JOIN FILTER CREATE | :BF0000 | 1 | 228 |
| 5 | EXCHANGE IN DISTR | | 1 | 228 |
| 6 | EXCHANGE OUT DISTR (BC2HOST)| :EX10000| 1 | 228 |
| 7 | PX BLOCK ITERATOR | | 1 | 228 |
| 8 | TABLE SCAN | a | 1 | 228 |
| 9 | JOIN FILTER USE | :BF0001 | 1 | 228 |
| 10 | PX BLOCK HASH JOIN-FILTER | :BF0000 | 1 | 228 |
| 11 | TABLE SCAN | b | 1 | 228 |
===============================================================
複数テーブルの場合
3テーブルの結合において、左テーブルを a と指定すると、右テーブル c に対する Join Filter を生成できます。
EXPLAIN SELECT
/*+ PARALLEL(2) LEADING(a (b c)) USE_HASH(c (b c)) PQ_DISTRIBUTE((b c) BC2HOST NONE) PQ_DISTRIBUTE(c BC2HOST NONE)
NO_PX_JOIN_FILTER(c)
NO_PX_JOIN_FILTER(b)
NO_PX_PART_JOIN_FILTER(c)
NO_PX_PART_JOIN_FILTER(b)
PX_JOIN_FILTER(c a)
*/ *
FROM t1 a, t1 b, t1 c WHERE a.c1 = c.c1 AND b.c1 = c.c1;
出力される実行計画の例は以下のとおりです。
===============================================================
| ID | OPERATOR | NAME | EST. ROWS | COST |
---------------------------------------------------------------
| 0 | PX COORDINATOR | | 1 | 684 |
| 1 | EXCHANGE OUT DISTR | :EX10002| 1 | 683 |
| 2 | SHARED HASH JOIN | | 1 | 683 |
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 228 |
| 4 | EXCHANGE IN DISTR | | 1 | 228 |
| 5 | EXCHANGE OUT DISTR (BC2HOST) | :EX10000| 1 | 228 |
| 6 | PX BLOCK ITERATOR | | 1 | 228 |
| 7 | TABLE SCAN | a | 1 | 228 |
| 8 | SHARED HASH JOIN | | 1 | 455 |
| 9 | EXCHANGE IN DISTR | | 1 | 228 |
| 10 | EXCHANGE OUT DISTR (BC2HOST) | :EX10001| 1 | 228 |
| 11 | PX BLOCK ITERATOR | | 1 | 228 |
| 12 | TABLE SCAN | b | 1 | 228 |
| 13 | JOIN FILTER USE | :BF0000 | 1 | 228 |
| 14 | PX BLOCK ITERATOR | | 1 | 228 |
| 15 | TABLE SCAN | c | 1 | 228 |
===============================================================
同様に、左テーブルを b と指定した3テーブルの結合においても、右テーブル c に対する Join Filter を生成できます。
EXPLAIN SELECT
/*+ PARALLEL(2) LEADING(a (b c)) USE_HASH(c (b c)) PQ_DISTRIBUTE((b c) BC2HOST NONE) PQ_DISTRIBUTE(c BC2HOST NONE)
NO_PX_JOIN_FILTER(c)
NO_PX_JOIN_FILTER(b)
NO_PX_PART_JOIN_FILTER(c)
NO_PX_PART_JOIN_FILTER(b)
PX_JOIN_FILTER(c b)
*/ *
FROM t1 a, t1 b, t1 c WHERE a.c1 = c.c1 AND b.c1 = c.c1;
出力される実行計画の例は以下のとおりです。
===============================================================
| ID | OPERATOR | NAME | EST. ROWS | COST |
---------------------------------------------------------------
| 0 | PX COORDINATOR | | 1 | 684 |
| 1 | EXCHANGE OUT DISTR | :EX10002| 1 | 683 |
| 2 | SHARED HASH JOIN | | 1 | 683 |
| 3 | EXCHANGE IN DISTR | | 1 | 228 |
| 4 | EXCHANGE OUT DISTR (BC2HOST) | :EX10000| 1 | 228 |
| 5 | PX BLOCK ITERATOR | | 1 | 228 |
| 6 | TABLE SCAN | a | 1 | 228 |
| 7 | SHARED HASH JOIN | | 1 | 455 |
| 8 | JOIN FILTER CREATE | :BF0000 | 1 | 228 |
| 9 | EXCHANGE IN DISTR | | 1 | 228 |
| 10 | EXCHANGE OUT DISTR (BC2HOST) | :EX10001| 1 | 228 |
| 11 | PX BLOCK ITERATOR | | 1 | 228 |
| 12 | TABLE SCAN | b | 1 | 228 |
| 13 | JOIN FILTER USE | :BF0000 | 1 | 228 |
| 14 | PX BLOCK ITERATOR | | 1 | 228 |
| 15 | TABLE SCAN | c | 1 | 228 |
=======================================================================
ヒントの競合処理
PX_JOIN_FILTER と NO_PX_JOIN_FILTER は、左テーブル left_tables が指定されているかどうかに応じて、4つの有効な形式があります。優先順位に基づき、以下の表のようにマッチングして使用されます。
ヒント |
機能 |
|---|---|
| NO_PX_JOIN_FILTER( a (b c) ) | 左テーブルが (b c) の場合、右テーブルの a に join filter の使用を禁止します |
| PX_JOIN_FILTER( a (b c) ) | 左テーブルが (b c) の場合、右テーブルの a に join filter を使用します |
| NO_PX_JOIN_FILTER( a ) | 任意の結合左テーブルに対して、右テーブルの a に join filter の使用を禁止します |
| PX_JOIN_FILTER( a ) | 任意の結合左テーブルに対して、右テーブルの a に join filter を使用します |
PX_PART_JOIN_FILTER と NO_PX_PART_JOIN_FILTER の競合処理は PX_JOIN_FILTER と同じです。