3.16 In-Memory Full Text Search and JSON Full Text Search

The queries using CONTAINS() and JSON_TEXTCONTAINS() can be evaluated in SQL predicates when the underlying columns that store the full text documents or JSON documents are enabled for In-Memory full text search.

Normally, to use full-text (keyword) searching against textual columns, you must create an Oracle Text index on that column. For JSON data, you create a JSON search index. Starting with Oracle Database Release 21c, instead of creating an index, you can load the column into memory, using an In-Memory columnar format. This does not require an index, but allows for fast scanning of the text using In-Memory techniques. This is particularly valuable when running queries which combine text searches and structured searches on other In-Memory columns.

You must declare the columns that must be loaded into memory during table creation time, using the INMEMORY TEXT clause. These columns can be searched using the same CONTAINS() and JSON_TEXTCONTAINS() functions that are used with Oracle Text or JSON search indexes, but there are limitations on the types of query operators that can be used. Hence, In-Memory is not a replacement for Oracle Text or JSON search indexes, but an alternative that can be used when required, and when the limitations are not considered to be a problem.

It is possible to have a column which has an Oracle Text index on it and also uses INMEMORY TEXT clause. In this situation, the optimizer chooses the Oracle Text index to execute the query. If there is an Oracle Text index on the column, the query always uses the Oracle Text index. If there is no Oracle Text index, then the optimizer checks if the table is marked as In-Memory. If the table is marked as In-Memory, the In-Memory evaluation is used for the query. If there is no Oracle Text index and the table is not marked as In-Memory, then the "DRG-10599: column is not indexed" error is returned.

Unlike CONTEXT indexes, you can use the INMEMORY TEXT clause with indirect datastore types (NETWORK_DATASTORE and DIRECTORY_DATASTORE) on LOB (large object) or LONG columns.

For detailed information on how to specify an in-memory Text column, see Oracle Database In-Memory Guide.

Supported Data Types

The In-Memory full text search supports the following data types:

  • CHAR

  • VARCHAR2

  • CLOB

  • BLOB

  • JSON

Both JSON and text columns support a custom indexing policy created with the CTX_DDL.CREATE_POLICY procedure. If the column data type is JSON, then the In-Memory full text version of this column enables path-aware search using JSON_TEXTCONTAINS() when the column uses either of the following:

  • A default policy

  • A custom policy with a PATH_SECTION_GROUP having JSON_ENABLED attribute set to TRUE

Usage Notes

You specify an In-Memory full text search column with the INMEMORY TEXT clause. Both CREATE TABLE and ALTER TABLE statements support the INMEMORY TEXT clause. You can use the PRIORITY subclause to control the order of object population. The default priority is NONE. The MEMCOMPRESS subclause is not valid with INMEMORY TEXT.

Specify either the CREATE TABLE or ALTER TABLE statement with the INMEMORY TEXT clause, using either of the following forms:

  • INMEMORY TEXT (col1, col2, …)

  • INMEMORY TEXT (col1 USING policy1, col2 USING policy2, …)

Oracle recommends that you run in-memory repopulate operations after a batch of DML operations or before running any queries. You can use the DBMS_INMEMORY.REPOPULATE procedure that forces immediate repopulation of an object. See Oracle Database PL/SQL Packages and Types Reference.

You must set the following database initialization parameters when using the INMEMORY TEXT clause:

  • MAX_STRING_SIZE: This parameter controls the maximum size of the VARCHAR2, NVARCHAR2, and RAW data types in SQL. You must set MAX_STRING_SIZE to EXTENDED. This setting raises the byte limit to 32767, which requires shutting down or upgrading your database.

  • INMEMORY_EXPRESSIONS_USAGE: This parameter controls the type of IM expression that the database populates. Set INMEMORY_EXPRESSIONS_USAGE to a value other than DISABLE:

    • ENABLE (default) to enable both static and dynamic IM expressions

    • STATIC_ONLY to enable only static IM expressions

  • INMEMORY_VIRTUAL_COLUMNS: This parameter controls which user-defined virtual columns are stored as IM virtual columns. Set INMEMORY_VIRTUAL_COLUMNS to ENABLE, which is the default setting.

Limitations

Examples

Example 3-1 Using In-Memory Full Text Search

The following example shows you how to query from an In-Memory full text search enabled column using the CONTAINS operator. It also shows you how to create a custom policy for text search and apply it on a column.

Create a table named text_docs that is loaded in memory and populate it with an In-Memory full text search column named doc:

CREATE TABLE text_docs(id NUMBER,  docCreationTime DATE, doc CLOB) INMEMORY INMEMORY TEXT(doc);

Query using the CONTAINS operator with your condition: 

SELECT id FROM text_docs WHERE docCreationTime >  to_date('2014-01-01', 'YYYY-MM-DD') 
AND CONTAINS(doc, 'in memory text processing');

You can also create a custom policy for text search, and then apply it to the doc column: 

EXEC CTX_DDL.CREATE_POLICY('first_policy');
ALTER TABLE text_docs INMEMORY TEXT (doc USING 'first_policy');

You can replace an existing custom policy by disabling the In-Memory full text search using the NO INMEMORY TEXT clause and then enabling In-Memory full text search using the INMEMORY TEXT clause: 

EXEC CTX_DDL.CREATE_POLICY('second_policy');
ALTER TABLE text_docs NO INMEMORY TEXT(doc);
ALTER TABLE text_docs INMEMORY TEXT (doc USING 'second_policy');

Example 3-2 Using JSON In-Memory Full Text Search

The following example shows you how to query from an In-Memory full text search enabled column using the JSON_TEXTCONTAINS operator.

Create a table named json_docs that is loaded in memory and populate it with an In-Memory full text search column named doc:

CREATE TABLE json_docs(id NUMBER,  docCreationTime DATE, doc JSON) INMEMORY INMEMORY TEXT(doc);

Query using the JSON_TEXTCONTAINS operator with your condition: 

SELECT id FROM json_docs WHERE docCreationTime >  to_date('2014-01-01', 'YYYY-MM-DD') 
AND JSON_TEXTCONTAINS(doc, '$.abstract', 'in memory text processing');

Example 3-3 Prioritizing In-Memory Population in Full Text Search

The following example shows you how to set the priority level for data population using the PRIORITY subclause.

Create a table named prioritized_docs that is loaded in memory and use the PRIORITY subclause to set the priority level: 

CREATE TABLE prioritized_docs(id NUMBER,  docCreationTime DATE, doc CLOB, json_doc CHECK(json_doc IS json)) 
INMEMORY PRIORITY CRITICAL INMEMORY TEXT(doc, json_doc);

Related Topics