SQLクエリにおいて、結合操作に関連するHintには、特定の結合アルゴリズムを有効または無効にするHintが含まれます。詳細は以下のとおりです。
ヒントタイプ |
説明 |
|---|---|
USE_MERGE |
このヒントで指定されたテーブルが結合の右テーブルとして使用される場合、ソートマージ結合アルゴリズムを使用します。その逆操作は NO_USE_MERGE です。 |
NO_USE_MERGE |
このヒントで指定されたテーブルが結合の右テーブルとして使用される場合、ソートマージ結合アルゴリズムを使用しません。その逆操作は USE_MERGE です。 |
USE_HASH |
このヒントで指定されたテーブルが結合の右テーブルとして使用される場合、HASH-JOIN アルゴリズムを使用します。その逆操作は NO_USE_HASH です。 |
NO_USE_HASH |
このヒントで指定されたテーブルが結合の右テーブルとして使用される場合、HASH-JOIN アルゴリズムを使用しません。その逆操作は USE_HASH です。 |
USE_NL |
このヒントで指定されたテーブルが結合の左テーブルとして使用される場合、NL-JOIN アルゴリズムを使用します。その逆操作は NO_USE_NL です。 |
NO_USE_NL |
このヒントで指定されたテーブルが結合の左テーブルとして使用される場合、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 |
最適化エンジンに対し、HASH JOINで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 ]... ) */
例
以下のクエリ例に示すように、ヒントによりネストループ結合が強制的に実行され、orders テーブルに対してフルテーブルスキャンでアクセスし、フィルター条件 l.order_id = h.order_id を各行に適用しました。フィルター条件を満たす各行について、インデックス order_id を使用して order_items にアクセスします。
-- USE_NL ヒントを使用して、オプティマイザーにネストループ結合アルゴリズム（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 ヒント
NO_USE_NL ヒントを指定すると、テーブルが結合の左テーブルとして使用される場合に、ネストループ結合（NL-JOIN）アルゴリズムを使用しないように指示します。その逆操作は USE_NL です。
構文
/*+ NO_USE_NL ( [ @queryblock ] tablespec [ tablespec ]... ) */
例
-- NO_USE_NL ヒントを使用して、オプティマイザーにネストループ結合アルゴリズムを使用せずにクエリを実行するよう指示します。
-- employees テーブルと departments テーブルの結合操作では、NL-JOIN アルゴリズムの使用が除外されます。
SELECT /*+ NO_USE_NL(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id;
PQ_DISTRIBUTE ヒント
PQ_DISTRIBUTE ヒントは、クエリのパラレル実行時に、オプティマイザーに対して、パラレルクエリのプロデューサー（Producer、クエリ結果の行データを生成する役割）とコンシューマー（Consumer、これらの行データを受信して処理する役割）のサーバー間でデータをどのように分配するかを指示します。PQ_DISTRIBUTE ヒントを使用して、結合操作や負荷操作における行データの分布方法を制御できます。
特に大量のデータを処理する必要があるパラレルクエリのシナリオでは、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つのテーブルは接続キー上で等分である必要があります。 |
例
以下のクエリ例のように、ハッシュ結合を使用してテーブル r と s を結合することを指定します。このクエリには、ハッシュ分散方式を使用するHintが含まれています。
SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s) */ column_list
FROM r, s
WHERE r.c = s.c;
右テーブル r をブロードキャストする場合、Hintを含むクエリステートメントは次のとおりです。
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つの有効な形式があります。優先順位に基づき、次の表のようにマッチングして使用されます。
Hint |
機能 |
|---|---|
| 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 と同じです。