フルテキスト検索

フルテキスト検索とは、テキストデータ内で全文を対象に検索または調査を行う操作です。特定のキーワード、フレーズ、またはテキスト表現を含むテキスト内容を見つけるために使用されます。フルテキスト検索は、テキスト全体をより包括的に検索し、検索条件に一致する結果を返します。

構文

以下の構文を使用して全文インデックスクエリを実行すると、指定された列とキーワードまたはフレーズに基づいて全文検索を実行できます。特定の検索修飾子を使用して検索パターン（つまり、検索条件やルール）を調整することもできます。

MATCH (column_name [, column_name ...]) AGAINST (expr [search_modifier])

search_modifier:
    IN NATURAL LANGUAGE MODE
    | IN BOOLEAN MODE
    | IN MATCH PHRASE MODE

関連パラメータの説明は以下のとおりです：

• column_name：全文検索を実行する列を指定します。複数の列を指定する場合は、英字のカンマで区切ります。

• expr：検索するキーワードまたはフレーズを指定します。

• search_modifier：オプションです。検索パターンを指定するために使用します。値と適用シナリオの詳細は次の表を参照してください。

  サーチモード	パラメータ説明	適用シナリオ
  IN NATURAL LANGUAGE MODE	デフォルト値。自然言語検索モードを使用して検索することを指定します。	自然言語、トップkシナリオでのあいまい一致、関連性の出力。単語はテーブルで設定されたトークナイザーによってデフォルトで分割されます。
  IN BOOLEAN MODE 説明 OceanBaseデータベースV4.3.5バージョンでは、V4.3.5 BP1バージョンから IN BOOLEAN MODE がサポートされています。	ブールモードを使用して検索することを指定します。現在のバージョンでサポートされているブール演算子および演算のネストは以下のとおりです： +：AND、共通部分を表します。 -：否定、差集合を表します。 操作記号なし：単独で使用した場合、OR、和集合を表します。例えば、A B は A OR B を表します。和記号と混在させると、存在する文の関連性が高くなりますが、OR の意味が失われます。例えば、+A B の場合、A が必要であり、文中の A と B の関連性を計算します。 ()：演算のネストを表します。外側に記号がない場合、OR の意味を持ちます。例えば、+A (ネストされた句) は、A が必要であるか、ネストされた句が必要であることを表します。	語意のフィルタリング、厳密一致、ユーザーの出力構文に基づいてフィルタリングし、関連性の出力を選択できます。各単語はスペース、+、-、() によってのみ分割されます。
  IN MATCH PHRASE MODE 説明 OceanBaseデータベースは V4.4.0 バージョンから IN MATCH PHRASE MODE をサポートしています。	Phrase Query（フレーズクエリ）を使用することを指定します。	フレーズマッチング、完全に分割せず、検索結果は必ず完全一致します。

  例：

  1. 出力される文にはcomputerが含まれていなければなりません。

     obclient> SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer" IN BOOLEAN MODE);
     

  2. 出力される文にはcomputerが含まれていなければならず、weatherは含まれてはなりません。

     obclient> SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer -weather" IN BOOLEAN MODE);
     

  3. 出力される文にはcomputerが含まれていなければならず、oceanbaseが含まれている方がより一致します。

     obclient> SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer oceanbase" IN BOOLEAN MODE);
     

  4. フレーズマッチングモードを使用してクエリを実行します。

     • MATCH AGAINST を SELECT 句として使用します。

       obclient> SELECT id, MATCH (title, body) AGAINST ('some words' IN MATCH PHRASE MODE) AS score
           FROM test;
       

     • MATCH AGAINST を WHERE 句として使用します。

       obclient> SELECT * FROM test WHERE MATCH (title, body) AGAINST ('some words' IN MATCH PHRASE MODE);
       

MATCH AGAINST 式の詳細については、MATCH AGAINSTを参照してください。

ベクトル化クエリ

全文インデックスを含むクエリでは、ベクトル化または非ベクトル化を選択できます。/*+ opt_param('rowsets_enabled', '[true | false]')*/ ヒントを使用して、ベクトル化を有効または無効にすることができます。

例：

• ベクトル化クエリを有効にします。

  obclient> SELECT /*+ opt_param('rowsets_enabled', 'true') */ title, body
      FROM articles
      WHERE MATCH(title, body) AGAINST('tutorial');
  

• ベクトル化クエリを無効にします。

  obclient> SELECT /*+ opt_param('rowsets_enabled', 'false') */ title, body
      FROM articles
      WHERE MATCH(title, body) AGAINST('tutorial');
  

例

1. テーブル tbl1 を作成し、全文インデックス full_idx1_tbl1 も同時に作成します。

   obclient> CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(100), col3 TEXT, FULLTEXT INDEX full_idx1_tbl1(col2, col3));
   

2. テーブル tbl1 にテストデータを追加します。

   obclient> INSERT INTO tbl1 (col1, col2, col3) VALUES (1, 'Hello World', 'This is a test'),
       (2, 'OceanBase', 'OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team'),
       (3, 'Database Management', 'Learn about SQL and database administration'),
       (4, 'Full Text Searching', 'Master the art of full text searching');
   

   実行結果は次のとおりです：

   Query OK, 4 rows affected
   Records: 4  Duplicates: 0  Warnings: 0
   

3. MATCH 句で、テーブル tbl1 の col2 列と col3 列を検索することを指定し、検索キーワードは 'OceanBase' とします。同時に、検索修飾子 IN NATURAL LANGUAGE MODE を使用して、自然言語検索モードで検索を実行します。

   obclient> SELECT * FROM tbl1
       WHERE MATCH (col2, col3) AGAINST ('OceanBase' IN NATURAL LANGUAGE MODE);
   

   実行結果は次のとおりです：

   +------+-----------+---------------------------------------------------------------------------------------------------------------------+
   | col1 | col2      | col3                                                                                                                |
   +------+-----------+---------------------------------------------------------------------------------------------------------------------+
   |    2 | OceanBase | OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team |
   +------+-----------+---------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

ES全文検索

OceanBaseデータベースは、バージョンV4.4.1からES全文検索のSQL構文をサポートしており、MATCH()ステートメントとSCORE()ステートメントが含まれます。

• MATCH()ステートメントはWHERE句の後のフィールドとして使用され、フィルタリングの意味を表します。SCORE()ステートメントと組み合わせて使用する必要があります。

  注意

  現在、WHERE句内のMATCH()ステートメントは1つだけでなければならず、MATCH() AGAINST()と混合して使用することはできません。

• SCORE()ステートメントは、対応するMATCH()ステートメントの関連性を表します。SCORE()ステートメントはMATCH()構文と組み合わせて使用し、単独では意味がありません。

ES全文検索のSQL構文は以下のとおりです：

SELECT [select_expr_list ,] SCORE()
FROM table_name
WHERE
    MATCH(
        A_COLUNM^x, B_COLUNM^y ...,
        'expr',
        '[parameters]'
        )
... ;

parameters:
    operator=or;
    minimum_should_match=int_value;
    boost=number_value;
    score_norm=min-max;
    type={most_fields | best_fields}

関連パラメータの説明は以下のとおりです：

• A_COLUNM^x, B_COLUNM^y ...：列名と重みを表します。重みは正の数値です。

  注意

  MATCH()内の各列には、列の組み合わせインデックスではなく、全文インデックスを作成する必要があります。全文インデックスの作成方法については、インデックスの作成を参照してください。

• expr：クエリステートメントを表します。複数のトークンに分割され、各トークンには重みを含めることができます。重みは正の数値で、デフォルト値は1です。

• parameters：オプションです。キーと値のリストを表します。追加のパラメータを指定しない場合でも、空の二重引用符''を1組残す必要があります。現在使用可能なパラメータは以下のとおりです：

  • operator：パラメータexprの集約アルゴリズムを表します。取り得る値は以下のとおりです：

    • or：少なくとも1つのトークンが存在すれば出力されます。minimum_should_matchパラメータを指定した場合、戻り結果の説明は以下のminimum_should_matchパラメータの説明を参照してください。

  • minimum_should_match：正の整数を取ります。パラメータoperatorがorの場合、トークングループで一致させる必要がある最小のキーワード数を表します。

  • boost：正の数値を取ります。MATCHレベルの重みを表し、関連性に掛け合わせられます。デフォルト値は1です。

  • score_norm：スコア計算時のMATCHの正規化アルゴリズムを表します。min-maxを選択でき、デフォルトでは正規化は行われません。

  • type：MATCHで複数列を扱う場合の関連性計算方法を表します。取り得る値は以下のとおりです：

    • most_fields：各列の関連性を合計します。
    • best_fields：各列の関連性のうち最大値を取ります。

例：

1. テーブル tbl1 の列 col2 に全文インデックスを作成します。

   obclient> CREATE FULLTEXT INDEX ft_idx_tbl1_col2 ON tbl1(col2);
   

2. テーブル tbl1 の列 col3 に全文インデックスを作成します。

   obclient> CREATE FULLTEXT INDEX ft_idx_tbl1_col3 ON tbl1(col3);
   

3. テーブル tbl1 から、「OceanBase」と「Database」の両方を含む行をフィルタリングします（少なくとも2つのキーワードが一致する必要があります、minimum_should_match=2）。フィールド col2（重み2）と col3（重み1）で全文検索を実行し、一致スコア（0-1範囲に正規化）を返します。

   obclient> SELECT *, SCORE() AS relevance_score
       FROM tbl1
       WHERE
           MATCH(col2^2, col3^1,
                 'OceanBase Database',
                 'operator=or;
                 minimum_should_match=2;
                 boost=1;
                 score_norm=min-max;
                 type=most_fields'
               );
   

   実行結果は次のとおりです：

   +------+-----------+---------------------------------------------------------------------------------------------------------------------+--------------------+
   | col1 | col2      | col3                                                                                                                | relevance_score    |
   +------+-----------+---------------------------------------------------------------------------------------------------------------------+--------------------+
   |    2 | OceanBase | OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team | 0.6079027355623101 |
   +------+-----------+---------------------------------------------------------------------------------------------------------------------+--------------------+
   1 row in set
   

4. テーブル tbl1 から、「OceanBase」または「Database」を含む行をフィルタリングします（少なくとも1つのキーワードが一致する必要があります、minimum_should_match=1）。フィールド col2（重み2）と col3（重み1）で全文検索を実行し、一致スコア（0-1範囲に正規化）を返します。

   obclient> SELECT *, SCORE() AS relevance_score
       FROM tbl1
       WHERE
           MATCH(col2^2, col3^1,
                 'OceanBase Database',
                 'operator=or;
                 minimum_should_match=1;
                 boost=1;
                 score_norm=min-max;
                 type=most_fields'
               );
   

   実行結果は次のとおりです：

   +------+---------------------+---------------------------------------------------------------------------------------------------------------------+--------------------+
   | col1 | col2                | col3                                                                                                                | relevance_score    |
   +------+---------------------+---------------------------------------------------------------------------------------------------------------------+--------------------+
   |    2 | OceanBase           | OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team | 0.4208104883442219 |
   |    3 | Database Management | Learn about SQL and database administration                                                                         | 0.2876526910145043 |
   +------+---------------------+---------------------------------------------------------------------------------------------------------------------+--------------------+
   2 rows in set
   

関連ドキュメント

• インデックスの作成
• 全文インデックス