概要
OpenSearch Retrieval Engine Edition では、SQL ステートメントでヒントを使用して、ビジネス要件に基づいてより効果的な方法で SQL クエリを定義できます。
サポートされているバージョン
HA3 バージョンが V3.7.3 以降の OpenSearch Retrieval Engine Edition
構文
Select:
SELECT [/*+ HintName(params) */]
{ * | projectItem [, projectItem ]* }
FROM tableExpression [/*+ HintName(params) */]
HintName: [a-zA-Z][a-zA-Z_]*
Params:
Identifier[, Identifier]
|
Identifier=Identifier[, Identifier=Identifier]
Identifier: [a-zA-Z_][a-zA-Z_0-9]* 組み込みヒント
OpenSearch Retrieval Engine Edition は、SQL ステートメント用に 4 種類の組み込みヒントを提供します。
ヒント名 | 説明 | 構文 |
HASH_JOIN | (1) HASH_JOIN ヒントを使用してテーブルを結合できます。 (2) HASH_JOIN ヒントを使用してテーブルを結合する場合、システムはデフォルトで HashJoin 演算子を使用します。システムは内部結合操作を実行してテーブルを結合します。 | HASH_JOIN(tableName1, tableName2, ...) |
LOOKUP_JOIN | (1) LOOKUP_JOIN ヒントを使用してテーブルを結合できます。 (2) LOOKUP_JOIN ヒントを使用してテーブルを結合する場合、システムはデフォルトで LookupJoin 演算子を使用します。システムは内部結合操作を実行してテーブルを結合します。 | LOOKUP_JOIN(tableName1, tableName2, ...) |
NORMAL_AGG | (1) デフォルトでは、システムはクエリの最初のフェーズで NORMAL_AGG ヒントを使用して定義した集計操作を実行します。 | NORMAL_AGG( distributionCheck='false' | 'true', propScope='all' ) distributionCheck: データ分散を確認するかどうかを指定します。 distributionCheck='true' を指定し、指定したグループキーがテーブルのパーティションキーでない場合、ヒントは無効です。 propScope: ヒントを他の集計ノードにプッシュするかどうかを指定します。 propSCope='all' を指定すると、システムはヒントを現在の集計ノードとダウンストリームの集計ノードにプッシュします。このパラメータを指定しない場合、システムはヒントを最も近い集計ノードにプッシュします。 |
NO_INDEX | (1) NO_INDEX ヒントを使用して、フィルタフェーズで指定されたフィールドのインデックス最適化機能を無効にできます。 | NO_INDEX( tableName='t1', fields='a, b, c' ) tableName: インデックス最適化機能を無効にするフィールドを含むテーブルの名前を指定します。 fields: インデックス最適化機能を無効にするフィールドを指定します。 |
例
HASH_JOIN
SELECT
/*+ HASH_JOIN(tj_relation)*/
*
FROM
(
SELECT
/*+ HASH_JOIN(tj_item_raw)*/
*
FROM
(
SELECT
sum(tj_item.id) as sum0
FROM
tj_item
GROUP BY
tj_item.id
) B
JOIN tj_item_raw on B.sum0 = tj_item_raw.id --> (1)
) D
JOIN tj_relation on D.sum0 = tj_relation.item_id --> (2)
システムは結合操作をハッシュ結合操作に最適化します。LOOKUP_JOIN
SELECT
/*+ LOOKUP_JOIN(tj_relation)*/
*
FROM
(
SELECT
/*+ LOOKUP_JOIN(tj_item_raw)*/
*
FROM
(
SELECT
sum(tj_item.id) as sum0
FROM
tj_item
GROUP BY
tj_item.id
) B
JOIN tj_item_raw on B.sum0 = tj_item_raw.id ---> (1)
) D
JOIN tj_relation on D.sum0 = tj_relation.item_id ---> (2)
システムは結合操作をルックアップ結合操作に最適化します。NORMAL_AGG
SELECT
/*+ LOOKUP_JOIN(tj_relation), NORMAL_AGG(distributionCheck='false', propScope='all')*/
tj_relation.price
FROM
(
SELECT
*
FROM
(
SELECT
sum(tj_item.id) as sum0
FROM
tj_item
GROUP BY ----> (1)
tj_item.id
) B
JOIN tj_item_raw ON B.sum0 = tj_item_raw.id
) D
JOIN tj_relation ON D.sum0 = tj_relation.item_id
GROUP BY ----> (2)
tj_relation.price
集計操作は通常の集計操作です。システムは集計操作を 2 つのフェーズに分割しません。NO_INDEX
SELECT
/*+ LOOKUP_JOIN(tj_relation), NO_INDEX(tableName='tj_relation', fields='pk') */
*
FROM
(
SELECT
/*+ NO_INDEX(tableName='tj_item', fields='shop_id, reserve_price') */
*
FROM
(
SELECT
SUM(tj_item.id) as sum0
FROM
tj_item
/*+ NO_INDEX(tableName='tj_item', fields='shop_id') */
WHERE
tj_item.id = 100
AND
tj_item.shop_id = 500 ---> (2)
GROUP BY
tj_item.id
) B
JOIN tj_item_raw on B.sum0 = tj_item_raw.id
) D
JOIN tj_relation /*+ NO_INDEX(tableName='tj_relation', fields='item_id') */ on D.sum0 = tj_relation.item_id
WHERE
tj_relation.item_id = 900 AND tj_relation.pk = 100 ---> (1)
tj_relation.item_id = 900 AND tj_relation.pk = 100 では、pk フィールドと item_id フィールドはインデックスではありません。
tj_item.shop_id = 500 では、shop_id フィールドはインデックスではありません。組み込み ATTR ヒント
ATTR ヒントを使用して、特定の補助情報を演算子に渡し、クエリを高速化できます。 ATTR ヒントでは、システムに返してほしいデータエントリの最大数、各バッチのデータエントリ数、その他の情報を指定できます。複数のフィールドからデータをクエリする場合、ATTR ヒントを使用してクエリを高速化できます。 OpenSearch Retrieval Engine Edition は、3 種類の属性ヒントをサポートしています。
SCAN_ATTR
SCAN_ATTR ヒントを使用して、スキャン演算子に情報を渡すことができます。 SCAN_ATTR ヒントには、次のパラメータを含めることができます。
localLimit: スキャン演算子が返すことができるデータエントリの最大数を指定します。
batchSize: スキャン演算子が各バッチで返すことができるデータエントリ数を指定します。ほとんどの場合、このパラメータは localLimit パラメータと一緒に使用する必要があります。デフォルトでは、スキャン演算子は取得されたすべてのデータエントリを返し、システムはテーブルサイズに基づいてバッチサイズ値を自動的に計算します。
nestTableJoinType: ネストされたテーブルの結合方法を指定します。有効な値: inner と left。デフォルト値: left。
SELECT /*+ SCAN_ATTR(localLimit='3',batchSize='2',nestTableJoinType='inner')*/
company_id, company_name
FROM
companyデータをクエリするテーブルの名前の横に SCAN_ATTR ヒントを指定することをお勧めします。
SELECT
company_id, company_name
FROM
company /*+ SCAN_ATTR(localLimit='3',batchSize='2',nestTableJoinType='inner')*/さらに、パーティションベースのクエリのステートメントで SCAN_ATTR ヒントを使用できます。このヒントを使用して、パーティションベースのクエリを実行してアプリケーションをデバッグできます。本番環境でパーティションベースのクエリを実行するためにこのヒントを使用することはできません。
hashValues: システムがハッシュ値を計算して、必要なデータをクエリできるパーティションを識別するために使用するフィールドを指定します。
partitionIds: データをクエリするパーティションの ID を指定します。このパラメータの値として -1 を指定すると、システムはすべてのパーティションをスキャンします。
次のサンプルコードでは、hashValues パラメータが指定されています。システムは、テーブルに指定したハッシュ関数を使用して、a1 値と a2 値に基づいてハッシュ値を計算し、ハッシュ値に基づいてシステムが識別したパーティションの ID を返します。
SELECT /*+ SCAN_ATTR(hashValues='a1,a2')*/ company_id, company_name
FROM company次のサンプルコードでは、partitionIds パラメータが指定されています。システムはパーティション 1 とパーティション 2 からデータをクエリします。
SELECT /*+ SCAN_ATTR(partitionIds='1,2')*/ company_id, company_name
FROM companyAGG_ATTR
AGG_ATTR ヒントを使用して、集計演算子に情報を渡すことができます。 AGG_ATTR ヒントでは、このヒントを使用して処理できるデータグループの最大数を指定する groupKeyLimit パラメータと、制限に達したときにエラーを報告するかどうかを指定する stopExceedLimit パラメータを指定します。 groupKeyLimit のデフォルト値は 200000 で、stopExceedLimit のデフォルト値は true です。 AGG_ATTR ヒントは、SELECT 演算子の後にのみ指定できます。
AGG_ATTR ヒントでは、propScope パラメータを含めて、ヒントを有効にするスコープを指定できます。デフォルトでは、ヒントは現在のノードでのみ有効です。
SELECT /*+ AGG_ATTR(groupKeyLimit='3',stopExceedLimit='false')*/
company_id, company_name
FROM
company
group by
company_id, company_nameJOIN_ATTR
次の例では、AGG_ATTR ヒントを使用してルックアップ結合と、システムが返すことができるルックアップ結合結果の最大数を指定しています。
lookupTurncateThreshold: システムがルックアップ内部結合操作を実行した後に返すことができる結果の最大数を指定します。システムが結果の最大数を取得すると、指定されたフィールドの他の値の結合を停止します。次のシナリオでは、JOIN_ATTR ヒントに lookupTurncateThreshold パラメータを含めることができます。ルックアップ結合操作の入力値が関連性スコアに基づいてソートされており、関連性スコアが高い値のグループに対してシステムが内部結合操作を実行した後に、指定された数の結果のみをシステムに返してほしい場合。
lookupBatchSize: 各バッチのルックアップ結合操作に使用するデータ行の数を指定します。デフォルト値は 500 です。
JOIN_ATTR ヒントでは、propScope パラメータを含めて、ヒントを有効にするスコープを指定できます。デフォルトでは、ヒントは現在のノードで有効になります。
SELECT /*+ JOIN_ATTR(lookupTurncateThreshold='30', lookupBatchSize='50')*/
id, daogou.company_id, company_name
FROM daogou
JOIN company
ON daogou.id = company.company_id同じステートメントで JOIN_ATTR ヒントと SCAN_ATTR ヒントを指定できます。
SELECT /*+ JOIN_ATTR(lookupTurncateThreshold='3', lookupBatchSize='2')*/
id, daogou.company_id, company_name
FROM
daogou /*+ SCAN_ATTR(localLimit='5',batchSize='1')*/
JOIN
company
ON
daogou.id = company.company_id左外部結合操作のデフォルト値
システムが左外部結合操作を実行するときに右側のテーブルの特定のフィールドから値を取得できない場合、システムはこれらのフィールドのデフォルト値を使用します。 JOIN_ATTR ヒントを使用してデフォルト値を指定できます。同じデータ型のすべてのフィールドに指定できるデフォルト値は 1 つだけです。
SELECT /*+ JOIN_ATTR(defaultValue='INTEGER:10,VARCHAR:aa')*/
id, daogou.company_id, company_name
FROM daogou
JOIN company
ON daogou.id = company.company_idLOCAL_PARALLEL
HA3 バージョンが V3.8.0 以降の OpenSearch Retrieval Engine Edition では、並列クエリ機能を有効にして、Searcher ノードでのクエリを最適化できます。ステートメントに LOCAL_PARALLEL ヒントを含めて、テーブルごとに異なるスレッド数を指定できます。システムは、並列で実行できる操作を自動的に決定します。
LOCAL_PARALLEL ヒントでは、次のパラメータを設定できます。
tableName: 並列クエリを実行するテーブルの名前を指定します。このパラメータの値は、catalogName.dbName.tableName 形式で指定できます。 catalgName と dbName を指定しない場合、システムは catalgName と dbName のデフォルト値を使用します。
parallelNum: テーブルで並列クエリを実行するために使用する並列スレッドの数を指定します。
SELECT i1
FROM t1 /*+ LOCAL_PARALLEL(tableName='t1', parallelNum='2') */
SELECT i1, COUNT(*)
FROM t1 /*+ LOCAL_PARALLEL(tableName='t1', parallelNum='2') */
GROUP BY i1
SELECT t1.id, t2.id
FROM tj_item /*+ LOCAL_PARALLEL(tableName='tj_item', parallelNum='2') */ AS t1
JOIN tj_shop AS t2
ON t1.id = t2.id
並列クエリ機能を有効にしてもクエリのパフォーマンスが明らかに向上しない場合は、クエリトレースと実行プランに基づいて問題のトラブルシューティングを行い、システムが実行に最も長い時間を必要とする操作を特定できます。
注: LOCAL_PARALLEL ヒントは、HA3 バージョンが V3.8.0 より前の OpenSearch Retrieval Engine Edition でサポートされている並列クエリメソッドと互換性がありません。 LOCAL_PARALLEL ヒントと、HA3 バージョンが V3.8.0 より前の OpenSearch Retrieval Engine Edition の並列クエリメソッドを同時に使用することはできません。
競合の解決
同じノードに同じタイプのヒントを複数指定した場合、システムは propScope パラメータの値が最も小さいヒントを使用します。同じノードに複数のヒントを指定し、これらのヒントの propScope パラメータの値が同じである場合、システムはヒントを実行しません。
同じタイプのヒントを複数指定し、ヒントの設定に競合情報が含まれている場合、システムはヒントを実行しません。
ヒントによってオプティマイザが正しくない実行プランを作成する可能性があるとシステムが判断した場合、システムはヒントを実行しません。
問題が発生した場合は、実行プランを表示して、システムがヒントを実行したかどうかを確認することをお勧めします。