CREATE SEARCH INDEX

Use the CREATE SEARCH INDEX statement to create a JSON search index.

Purpose

Note: Search indexes do not support the shadow index.

Search indexes that are partitioned do not support the naming of index partitions because these indexes use system-managed partitioning.

You can create a JSON Search Index on a VARCHAR2, CLOB, or BLOB data type column with an IS JSON check constraint. To index JSON data, you must specify the FOR JSON clause in the CREATE SEARCH INDEX statement.

Note: You can create a JSON Search Index only on a column with an IS JSON check constraint.

Example

create table t1 (txn_date date, po CLOB, constraint c1 check (po is json));
create search index idx on t1(po) for JSON parameters('DATAGUIDE ON sync (on commit)');

Syntax

You can use a simpler alternative syntax to create a search index on JSON.

CREATE SEARCH INDEX [schema.]index ON [schema.]table(json_column) FOR JSON
[LOCAL]
PARAMETERS(
  [DATAGUIDE ON [CHANGE (ADD_VC | function_name)] | OFF]
  [STORAGE storage_pref]
  [SEARCH_ON (NONE | TEXT | TEXT_VALUE)]
  [MEMORY memsize]
  [SYNC (MANUAL | EVERY "interval-string" [MEMORY memsize] [PARALLEL n] | ON COMMIT)]
  [OPTIMIZE (MANUAL | EVERY "interval-string"[PARALLEL n]  | AUTO_DAILY[PARALLEL n] )]
)
[PARALLEL N]
[UNUSABLE];

If the PARAMETERS clause is omitted, then the default values for DATAGUIDE, SEARCH_ON and SYNC are ON, TEXT_VALUE and ON COMMIT respectively.

[schema.]index

Specifies the name of the JSON search index to create.

[schema.]table(json_col)

Specifies the name of the table and the JSON column to index. json_col is the name of the JSON column on which the index is created.

The column must have an IS JSON check constraint.

LOCAL

Creates a local partitioned JSON search index on a partitioned table. The index is partitioned using the partitioning scheme of the base table.

You can partition a table using range, list, hash, interval, range-composite (range, list, and hash), list-composite (range, list, and hash), and hash-composite (range, list, and hash) partitioning schemes. You can create a local JSON search index using reference partitioning if the base table of the reference partitioned table is partitioned using any of the supported schemes.

Note: You cannot create a local JSON search index on an interval-composite partitioned table.

The following example shows how to create a table that is partitioned into three, populate it, and then create a partitioned JSON search index:

PROMPT create partitioned table and populate it

CREATE TABLE part_tab (a int, b CLOB check (b IS JSON)) PARTITION BY RANGE (a)
(partition p_tab1 values less than (10),
 partition p_tab2 values less than (20),
 partition p_tab3 values less than (30));

PROMPT create partitioned JSON search index
CREATE SEARCH INDEX part_idx ON part_tab (b) FOR JSON LOCAL;

See Also:

“Creating a Local Partitioned Index” “System Managed Domain Index - Supported Schemes” in Oracle Database Data Cartridge Developer’s Guide

DATAGUIDE ON | OFF

Specifies data guide support for an existing JSON search index. If you enable the JSON data guide support, then you can also define change-trigger procedures.

Specify one of the following options:

ON: Enables data guide support. If you set the value of DATAGUIDE to ON, then you can also define your own PL/SQL procedure or use the predefined change-trigger procedure ADD_VC.

ADD_VC indicates if virtual columns are created based on the data guide.

function_name specifies the function to be executed when the data guide changes.
OFF: Disables both the data guide support and change-trigger procedures. Provides only general search-index functionality.

Note: You cannot create an index with the SEARCH_ON clause set to NONE when the DATAGUIDE feature is disabled.

See “Change Triggers For Data Guide-Enabled Search Index” in Oracle Database JSON Developer’s Guide.

STORAGE storage_pref

Specifies the name of your storage preference for JSON search index. Use the storage preference to specify how index tables are stored. See “Storage Types”.

SEARCH_ON (NONE | TEXT | TEXT_VALUE)

Specifies search preferences for JSON search index. Enabling a search option rebuilds the index with updated preferences.

Specify one of the following options:

NONE: Indicates that the tables used for full-text and range searches are not populated. Only the index data guide is maintained. The index will not be used by any JSON query operators, including JSON_TEXTCONTAINS.
TEXT: Indicates that full-text search queries use the search index.
TEXT_VALUE: Indicates that both the full-text and range-based indexes are created for numeric and date-time values.

Specifying TEXT_VALUE has a higher cost in indexing time and index space.

MEMORY memsize

Specifies the amount of run-time memory to use for indexing. The syntax for memsize is as follows:

memsize = number[K|M|G]

K stands for kilobytes, M stands for megabytes, and G stands for gigabytes.

The value you specify for memsize must be between 1M and the value of MAX_INDEX_MEMORY in the CTX_PARAMETERS view. To specify a memory size larger than the MAX_INDEX_MEMORY, you must reset this parameter with CTX_ADM.SET_PARAMETER to be larger than or equal to memsize.

The default is the value specified for DEFAULT_INDEX_MEMORY in CTX_PARAMETERS.

The memsize parameter specifies the amount of memory Oracle Text uses for indexing before flushing the index to disk. Specifying a large amount memory improves indexing performance because there are fewer I/O operations and improves query performance and maintenance, because there is less fragmentation.

Specifying smaller amounts of memory increases disk I/O and index fragmentation, but might be useful when run-time memory is scarce.

SYNC (MANUAL | EVERY “interval-string“[MEMORY memsize] [PARALLEL n] | ON COMMIT)

Specifies SYNC for automatic synchronization of the CONTEXT index when there are inserts, updates or deletes to the base table. You can specify one of the SYNC methods as described in Table 1-7.

Each partition of a locally partitioned index can have its own type of sync (ON COMMIT, EVERY, or MANUAL). The type of sync specified in primary parameter strings applies to all index partitions. The ON COMMIT sync is the default synchronization method for JSON search indexes. The ON COMMIT sync can be run only serially and must use the same memory size that was specified at index creation.

With automatic (EVERY) synchronization, you can specify memory size and parallel synchronization. You can define repeating schedules in the interval-string argument using calendaring syntax values. These values are described in Oracle Database PL/SQL Packages and Types Reference.

Syntax:

SYNC [EVERY "interval-string"] MEMORY mem_size PARALLEL paradegree

Example:

SYNC [EVERY "freq=secondly;interval=20"] MEMORY 500M PARALLEL 2

The following examples create a JSON search index with automatic (EVERY) synchronization:

Starting every night at 1:00 A.M.:

CREATE SEARCH INDEX nightly_refreshed ON purchase_orders(json_document) FOR JSON PARAMETERS('SYNC (EVERY "freq=daily; byhour=1")');

Starting every 5 minutes:

CREATE SEARCH INDEX nightly_refreshed ON purchase_orders(json_document) FOR JSON PARAMETERS('SYNC (EVERY "freq=minutely; interval=5")');

OPTIMIZE

Specify OPTIMIZE to enable automatic background index optimization. You can specify the OPTIMIZE methods as described in Table 1-8.

With AUTO_DAILY | EVERY "interval-string" setting, you can specify parallel optimization. That syntax is:

... [AUTO_DAILY | EVERY "interval-string"] PARALLEL paradegree ...

Related Topics