Skip to main content

Full-text query

A full-text query is an operation that searches for text content containing specific keywords, phrases, or text expressions in text data. It allows for more comprehensive searching of the entire text and returns results that match the search criteria.

Syntax​

When using the following syntax for a full-text index query, you can specify columns and keywords or phrases to perform a full-text search. You can also use specific search modifiers to adjust the search mode (i.e., search conditions or rules).

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

search_modifier:
    IN NATURAL LANGUAGE MODE
    | IN BOOLEAN MODE

The following table describes the related parameters:

  • column_name: specifies the column to perform the full-text search on. If you want to specify multiple columns, separate them with commas.

  • expr: specifies the keyword or phrase to search for.

  • search_modifier: an optional parameter that specifies the search mode. Valid values are as follows:

    • IN NATURAL LANGUAGE MODE: the default value, which specifies to use the natural language search mode.

    • IN BOOLEAN MODE: specifies to use the boolean mode. The current version supports three most commonly used boolean operators and nested operations, as follows:

      • +: represents AND, which is the intersection.

      • -: represents negation, which is the difference set.

      • No operator: when used alone, it represents OR, which is the union. For example, A B represents A OR B. When used with other operators, it increases the relevance of the existing sentence but loses the OR semantics. For example, +A B means that A must be present, and the relevance of A and B in the sentence is calculated.

      • (): represents nested operations. When there is no operator outside the parentheses, it has the OR semantics. For example, +A (nested clause) means that A or the nested clause must be present.

      Here are some examples:

      1. Output sentences must contain the word "computer".

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

      2. Output sentences must contain the word "computer" and must not contain the word "weather".

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

      3. Output sentences must contain the word "computer", and those containing "oceanbase" are more relevant.

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

For more information about the MATCH AGAINST expression, see MATCH AGAINST.

Vectorized query​

For queries involving full-text indexes, you can choose to use vectorized or non-vectorized queries. You can use the /*+ opt_param('rowsets_enabled', '[true | false]')*/ hint to enable or disable vectorization.

tip

If you do not specify the hint, whether to enable vectorization is determined by the system parameter configuration of the vectorization engine, which is enabled by default.

Here are some examples:

  • Enable vectorized queries.

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

  • Disable vectorized queries.

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

Examples​

  1. Create a table named tbl1 and a full-text index named full_idx1_tbl1.

    CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(100), col3 TEXT, FULLTEXT INDEX full_idx1_tbl1(col2, col3));
    Query OK, 0 rows affected (0.101 sec)

  2. Add test data to the tbl1 table.

    INSERT INTO tbl1 (col1, col2, col3) VALUES (1, 'Hello World', 'This is a test'),
        (2, 'seekdb', 'OceanBase seekdb (referred to as seekdb) is an AI-native search database. It unifies relational, vector, text, JSON and GIS in a single engine, enabling hybrid search and in-database AI workflows.'),
        (3, 'Database Management', 'Learn about SQL and database administration'),
        (4, 'Full Text Searching', 'Master the art of full text searching');

    The return result is as follows:

    Query OK, 4 rows affected (0.003 sec)
    Records: 4  Duplicates: 0  Warnings: 0

  3. Search for the keyword 'seekdb' in the col2 and col3 columns of the tbl1 table. The search modifier IN NATURAL LANGUAGE MODE is used, indicating that the natural language search mode is to be used.

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

The return result is as follows:

+------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| col1 | col2   | col3                                                                                                                                                                                                 |
+------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|    2 | seekdb | OceanBase seekdb (referred to as seekdb) is an AI-native search database. It unifies relational, vector, text, JSON and GIS in a single engine, enabling hybrid search and in-database AI workflows. |
+------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

References​

Contents

Edit this page