GET_SQL 関数は、実際に実行されたSQL文を取得し、文字列として返します。
構文
FUNCTION GET_SQL (IN table_name VARCHAR(65535),
IN search_params LONGTEXT)
RETURN LONGTEXT;
パラメータ説明
パラメータ |
説明 |
必須 |
|---|---|---|
| table_name | テーブル名。ヒープテーブルと主キーのないテーブルをサポートします。 | 必須 |
| search_params | 検索パラメータ。JSON形式の文字列です。 | 必須 |
search_params の構文
search_params はJSON形式の文字列であり、その構文はこのセクションで詳しく説明します。以下のパラメータと例を併せてご理解ください。
構文の説明
このセクションでは、BNF構文記号の意味と使用規則について説明します。
オプションパラメータの表現
[ ]はBNFにおいて複数の要素を含めることができることを示します。例えば、param_list = param [, param]*はparam_listが1つまたは複数のparamを含むことができることを示します。rank_feature、rank_expressionの[ ]もサブパラメータがオプションであることを示します。[, "boost" : boost_value]はboostサブパラメータがオプションであることを示し、すべてのboostサブパラメータはオプションです。
配列の表現
[ ]はJSON構造において配列を示します。例えば、[condition_list]が該当します。
選択関係
|は選択関係を示します。例えば、param = "query" | "knn"は param が "query" または "knn" のいずれかであることを示します。
繰り返しの表現
*は0回以上繰り返すことを示します。例えば、param_list = param [, param]*はparam_listが1つまたは複数のparamを含むことができることを示します。
JSON形式要件
- すべてのJSONフィールド名と文字列値は二重引用符で囲む必要があります。
- 数値は二重引用符で囲む必要はありません。
構文の定義
このセクションでは、search_params の構文を詳しく説明します。パラメータの説明については、下記の詳細なパラメータ説明表を参照してください。
トップレベルパラメータ構造
-- トップレベルキーワードパラメータ
search_params = '{param_list}'
param_list = param [, param]*
-- queryとknnのパラメータは、それぞれ全文検索/スカラー検索およびベクトル検索に使用されます。少なくとも一方は必須です。ハイブリッド検索では両方を同時に使用します。
param = "query" : {query_expression}
| "knn" : {knn_expression}
| "rank" : {rank_expression}
| "_source" : [field_list]
| "from" : number
| "size" : number
| "es_mode" : boolean
クエリ式構造
query_expression = bool_query | single_term
-- bool_query構文
bool_query = "bool" : {bool_condition_list}
bool_condition_list = bool_condition [, bool_condition]*
-- boostサブパラメータはオプションです
bool_condition = "must" : [condition_list]
| "should" : [condition_list]
| "must_not" : [condition_list]
| "filter" : [condition_list]
| "boost" : boost_value
-- bool_queryのネストはサポートされています
condition_list = query_expression [, query_expression]*
単一語句クエリ構造
single_term = range_query | match_query | term_query | query_string | multi_match | rank_feature
-- range_query構文
range_query = "range" : {"field_name" : {range_condition_list}}
range_condition_list = range_condition [, range_condition]*
-- boostサブパラメータはオプションです
range_condition = "gte" : number
| "gt" : number
| "lte" : number
| "lt" : number
| "boost" : boost_value
-- match_query構文
match_query = "match" : {"field_name" : {match_condition}}
-- boostサブパラメータはオプションです
match_condition = "query" : "string_value" [, "boost" : boost_value]
-- term_query構文
term_query = "term" : {term_condition_list}
term_condition_list = term_condition [, term_condition]*
term_condition = "field_name" : scalar_value
| "field_name" : term_value_object
-- boostサブパラメータはオプションです
term_value_object = "value" : scalar_value [, "boost" : boost_value]
-- 全文検索式構造。query_stringとmulti_matchの2種類の検索方式を含みます
query_string = "query_string" : {query_string_condition}
-- fieldsとqueryサブパラメータは必須です
query_string_condition = "fields" : [field_weight_list]
| "query" : "string_value"
| "boost" : boost_value
| "type" : ("best_fields" | "cross_fields" | "most_fields" | "phrase")
| "default_operator" : ("AND" | "OR")
| "minimum_should_match" : number
field_weight_list = field_weight [, field_weight]*
-- field_name、^、numberの間にスペースは置けません
field_weight = "field_name[^number]"
-- multi_match構文。V4.4.1 BP0 HotFix1バージョンからサポートされています
multi_match = "multi_match" : {multi_match_condition}
-- fieldsとqueryサブパラメータは必須です
multi_match_condition = "fields" : [field_weight_list]
| "query" : "string_value"
| "boost" : boost_value
| "type" : ("best_fields" | "cross_fields" | "most_fields" | "phrase")
| "operator" : ("AND" | "OR")
| "minimum_should_match" : number
field_weight_list = field_weight [, field_weight]*
-- field_name、^、numberの間にスペースは置けません
field_weight = "field_name[^number]"
-- 特徴ランキング式構造
rank_feature = "rank_feature" : {"field_name" : {rank_algorithm}}
rank_algorithm = "saturation" : {"pivot" : number[, "positive_score_impact" : boolean]}
| "sigmoid" : {"pivot" : number, "exponent" : number[, "positive_score_impact" : boolean]}
| "log" : {"scaling_factor" : number[, "positive_score_impact" : boolean]}
| "linear" : {["positive_score_impact" : boolean]}
ベクトル検索式の構造
-- knn_expression 構文構造
knn_expression = "knn" : {knn_condition_list}
knn_condition_list = knn_condition [, knn_condition]*
-- field、k、query_vector サブパラメータは必須です。
knn_condition = "field" : "field_name"
| "k" : number
| "query_vector" : [vector_values]
| "filter" : [condition_list]
| "similarity" : number
| "boost" : boost_value
vector_values = float [, float]*
condition_list = {condition [, condition]*}
condition = single_term
-- rank_expression 構文構造。RRFはV4.4.1 BP0 HotFix1バージョンからサポートされています。
rank_expression = "rank" : {rank_strategy}
rank_strategy = "rrf" : {rrf_params}
rrf_params = "rank_window_size" : number [, "rank_constant" : number]
基本型の定義
-- 基本型の定義
field_name = "string_value"
field_list = field_name [, field_name]*
number = integer | decimal
boost_value = integer | float -- boostパラメータの値は0以上である必要があります。
boolean = true | false
scalar_value = "string_value" | number | boolean
パラメータの詳細説明
search_params のパラメータの詳細説明は以下のとおりです。
式のタイプ |
パラメータ名 |
パラメータの説明 |
|---|---|---|
| トップレベルキーワードパラメータ | ||
| query | 全文検索では単独で使用できます。ハイブリッド検索では knn パラメータと併用できます。 |
|
| knn | ベクトル検索では単独で使用できます。ハイブリッド検索では query パラメータと併用できます。 |
|
| rank（オプション） | ハイブリッド検索時のソート戦略を指定するために使用されます。RRF（Reciprocal Rank Fusion）アルゴリズムをサポートします。
注意このパラメータはV4.4.1 BP0 HotFix1バージョンからサポートされています。 |
|
| _source（オプション） | 検索で返す列を指定するために使用されます。指定しない場合、テーブル上のすべてのユーザー定義列が返されます。 | |
| from（オプション） | 検索結果セットの何行目から結果を返すかを指定するために使用されます。指定しない場合、デフォルトで1行目から返されます。size パラメータと併用する必要があります。 |
|
| size（オプション） | 返される結果の数を制限するために使用されます。指定しない場合、制限はありません。 | |
| es_mode | 全文検索をESQLの新しい構文に変換するかどうかを指定するために使用されます。デフォルトは false です。 |
|
| bool | must | 満たす必要があり、スコアが計算されます。内部でブール論理が必要な場合は、bool式をネストする必要があります。bool式内の複数の条件は、デフォルトでAND論理で組み合わされます。 |
| should | 満たすべきであり、ORに類似しており、スコアが計算されます。内部でブール論理が必要な場合は、bool式をネストする必要があります。bool式内の複数の条件は、デフォルトでAND論理で組み合わされます。 | |
| must_not | 満たしてはならず、スコアは計算されません。'NOT' 式に変換され、must_not内の複数の項は 'AND' で結ばれます。内部でブール論理が必要な場合は、bool式をネストする必要があります。bool式内の複数の条件は、デフォルトでAND論理で組み合わされます。 | |
| filter | 満たす必要があり、スコアは計算されません。'AND' 式に変換されます。内部でブール論理が必要な場合は、bool式をネストする必要があります。bool式内の複数の条件は、デフォルトでAND論理で組み合わされます。 | |
| boost（オプション） | クエリの重みです。詳細は下記のboostパラメータの説明を参照してください。 | |
| rank_feature（関連スコア計算パラメータ） | pivot | saturation、sigmoidで必須のスコア計算パラメータです。デフォルト値はこのリスト上のデータの幾何平均です。 |
| positive_score_impact（オプション） | フィールドが最終的な相関に正の影響を与えるか負の影響を与えるかを設定します。 | |
| scaling_factor | logスコア計算式の必須パラメータです。 | |
| exponent | sigmoidアルゴリズムの計算式の必須パラメータです。 | |
| rank_feature（関連スコアアルゴリズム） | saturation | デフォルトの関連スコアアルゴリズム
|
| sigmoid |
|
|
| log |
|
|
| linear |
|
|
| single term（単一語条検索） | ||
| range | 範囲検索。gte、gt、lte、lt、boostと組み合わせて使用します。fieldname は必須です。 |
|
| match | あいまい一致。sqlの 'match' 式に変換し、boostと組み合わせて使用します。 | |
| term | 厳密一致。文字列、数値、ブール値などのスカラー値をサポートします。sqlの '=' 式に変換し、boostと組み合わせて使用します。 | |
| query_string | 全文一致。sqlの複数の 'match' 式の組み合わせに変換します。 | |
| multi_match | 全文一致。sqlの複数の 'match' 式の組み合わせに変換します。query_stringと同様ですが、単一のキーワードには重みを設定できません。
注意このパラメータはV4.4.1 BP0 HotFix1バージョンからサポートされています。 |
|
| fields | テキスト検索のフィールドリストです。各列の重みを設定できます。 | |
| query | 検索キーワードリストです。各キーワードの重みを設定できます。 | |
| minimum_should_match（オプション） | should、query_stringで満たす必要がある条件の数を制御するために使用されます。このパラメータを指定しない場合、デフォルト値は 1 です。注意：bool式にmust/filterが含まれており、このパラメータを指定しない場合、デフォルト値は 0 となり、shouldの任意の条件を満たさなくてもよくなります。 |
|
| boost（オプション） | クエリの重みです。詳細は下記のboostパラメータの説明を参照してください。 | |
| type（オプション） | マッチングモードを指定します。今回はbest_fiedls、cross_fields、most_fields、phraseをサポートしており、指定しない場合はデフォルトで best_fields です。 |
|
| default_operator（オプション） | query_stringのサブフィールドで、複数のキーワード間の組み合わせロジックを指定します。 | |
| operator（オプション） | multi_matchのサブフィールドで、複数のキーワード間の組み合わせロジックを指定します。 | |
| knn（ベクトル検索） | ||
| field | ベクトル検索のフィールド。 | |
| k | ベクトル検索で返される行数を実行します。 | |
| query_vector | 検索ベクトルを指定します。 | |
| filter（オプション） | フィルター条件。 | |
| similarity（オプション） | ベクトル距離のフィルター条件を指定するために使用されます。 | |
| boost（オプション） | クエリの重みです。詳細は下記のboostパラメータの説明を参照してください。 | |
| rank（RRFソート戦略） | rrf | RRF（Reciprocal Rank Fusion）ソート戦略で、ハイブリッド検索時に複数のクエリ結果を統合してソートするために使用されます。
注意このパラメータはV4.4.1 BP0 HotFix1バージョンからサポートされています。 |
| rank_window_size（オプション） | この値は、各クエリが返す個々の結果セットのサイズを指定するために使用されます。値が大きいほど、結果の関連性は高くなりますが、パフォーマンスオーバーヘッドが生じます。最終的なソート結果セットは、検索リクエストで指定された size サイズにトリミングされます。rank_window_sizeは以下の両方を満たす必要があります：
size パラメータの値です。 |
|
| rank_constant（オプション） | この値は、各クエリが返す個々の結果セット内の各ドキュメントが最終的なソート結果に与える影響度を制御するために使用されます。値が大きいほど、ランキングの低いドキュメントが最終結果に与える影響が大きくなります。デフォルト値は60です。 |
boostパラメータの詳細説明
boost パラメータは、クエリ条件が最終的な関連性計算において持つ重みを指定するために使用されます。値は必ず 0 以上でなければならず、指定しない場合はデフォルトで 1 となります。上記の構文では、bool、single_term（rank_feature を除く）、knn はすべて boost パラメータの指定をサポートしています。各検索タイプでサポートされる boost パラメータの使用方法は以下のとおりです：
クエリレベルのboost
クエリ全体に対する重みを指定します。例：
{ "bool": { "must": [{"term": {"category": "Gaming"}}], "boost": 2.0 // boolクエリ全体の重み } }フィールドレベルのboost
特定のフィールドに対するクエリに重みを指定します：
{ "query_string": { "fields": ["product_name", "description"], "query": "gaming keyboard", "boost": 1.5 // query_stringクエリ全体の重み } }一致値レベルのboost
具体的な一致値に対する重みを指定します（matchおよびtermクエリのみサポート）：
{ "match" : { "product_name": { "query" : "gaming keyboard", "boost" : 1.5 } } }フィールド重み構文
query_stringおよびmulti_matchでは、
field_name^weight構文を使用できます：{ "query_string": { "fields": ["product_name^2.0", "description^1.0"], "query": "gaming" } }
例
テーブルを作成し、1つのベクトル列に対してベクトルインデックスを作成します。また、2つのvarchar列に対して、それぞれ全文インデックスを作成します。
CREATE TABLE doc_table(c1 INT, vector VECTOR(3), query VARCHAR(255), content VARCHAR(255),
VECTOR INDEX idx1(vector) WITH (distance=l2, type=hnsw, lib=vsag), FULLTEXT INDEX idx2(query),
FULLTEXT INDEX idx3(content));
検索パラメータを設定します。
SET @parm = '{
"query": {
"bool": {
"should": [
{"match": {"query": "hi hello"}},
{"match": { "content": "oceanbase mysql" }}
],
"filter": [
{"term": { "content" : "postgres" }}
]
}
},
"knn" : {
"field": "vector",
"k": 5,
"query_vector": [1,2,3]
},
"_source" : ["query", "content", "_keyword_score", "_semantic_score"]
}';
クエリを実行し、結果を返します。
SELECT DBMS_HYBRID_SEARCH.GET_SQL('doc_table', @parm);
結果は次のとおりです：
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dbms_hybrid_search.get_sql('doc_table', @parm) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT query, content, _keyword_score, _semantic_score, (ifnull(_fts._keyword_score, 0) + ifnull(_vs._semantic_score, 0)) as _score FROM ((SELECT /*+ opt_param('hidden_column_visible', 'true') union_merge( doc_table idx2 idx3)*/__pk_increment, (match(query) against('hi hello' in boolean mode) + match(content) against('oceanbase mysql' in boolean mode)) as _keyword_score FROM wxj.doc_table WHERE (content = 'postgres') ORDER BY _keyword_score DESC) _fts right join (SELECT /*+ opt_param('hidden_column_visible', 'true') */l2_distance(vector, '[1, 2, 3]') as _distance, __pk_increment, query, content, round((1 / (1 + l2_distance(vector, '[1, 2, 3]'))), 8) as _semantic_score FROM wxj.doc_table ORDER BY _distance APPROXIMATE LIMIT 5) _vs on (_fts.__pk_increment = _vs.__pk_increment)) ORDER BY _score DESC |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+