CREATE SEARCH INDEX

Use the CREATE SEARCH INDEX statement to create a search index for indexing and querying structured, unstructured, or semi-structured data, such as textual, JSON, and XML documents.

Purpose

The SEARCH INDEX is an index type that supports the CONTEXT index functionality along with sharded databases and system-managed partitioning for index storage. Using the CREATE SEARCH INDEX syntax, you can create search indexes on textual, JSON, and XML columns.

Note: Shadow index is not supported for search indexes.

Overview

The CREATE SEARCH INDEX syntax automatically determines the type of search index to create based on the data type of the column, as follows:

Column Data Type FOR Clause Syntax Description
Text FOR TEXT

The CREATE SEARCH INDEX statement on a textual column creates an Oracle Text search index.

If required, you can explicitly specify the FOR TEXT clause in the CREATE SEARCH INDEX statement to create an Oracle Text search index. If you omit the FOR TEXT clause on a textual column, then the FOR TEXT settings are automatically picked up.

If a column has the JSON data type, an IS JSON check constraint, or an XMLType data type using TBX, then you can override the settings and create a full-text search index by specifying the FOR TEXT clause.

JSON data type

or

Column with an IS JSON check constraint

FOR JSON

The CREATE SEARCH INDEX statement on a column with the JSON data type or an IS JSON check constraint creates a JSON search index.

If required, you can explicitly specify the FOR JSON clause in the CREATE SEARCH INDEX statement to create a JSON search index. If you omit the FOR JSON clause and the column has the JSON data type or an IS JSON check constraint, then the FOR JSON settings are automatically picked up.

XMLType data type of TRANSPORTABLE BINARY XML FOR XML

The CREATE SEARCH INDEX statement on an XMLType column of TRANSPORTABLE BINARY XML (TBX) creates an XML search index.

If required, you can explicitly specify the FOR XML clause in the CREATE SEARCH INDEX statement to create an XML search index. If you omit the FOR XML clause on an XMLTYPE column that uses the TBX storage option, then the FOR XML settings are automatically picked up. If you omit the FOR XML clause on an XMLTYPE column but the storage option is not TBX, then it creates an Oracle Text index. To create an XML search index, you must ensure that the document is stored as TBX.

XML search indexes also support XQuery Full Text search features. You can index XML data that is not stored using the TBX option by creating an XQuery Full Text CONTEXT index. See Oracle XML DB Developer's Guide.

Here is the detailed syntax for each type of search index:

Syntax for Oracle Text Search Index

CREATE SEARCH INDEX [schema.]index ON [schema.]table(txt_column)
 [ONLINE]
 [FILTER BY filter_column[, filter_column]...]
 [ORDER BY oby_column[desc|asc][, oby_column[desc|asc]]...]
 [LOCAL [PARTITION [partition] ]
 [, PARTITION [partition] ])]
 [PARAMETERS(paramstring)] [PARALLEL n] [UNUSABLE]];

ONLINE, FILTER BY, ORDER BY, PARTITION, PARALLEL, and UNUSABLE are described in “Syntax for CONTEXT Index Type”.

[schema.]index

Specifies the name of the Oracle Text search index to create.

[schema.]table(index_column)

Specifies the names of table and column to index. index_column is the name of the column on which the index is created.

LOCAL

Creates a local partitioned 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), hash-composite (range, list, and hash), and automatic-list partitioning schemes. You can create a local 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 search index on an interval-composite partitioned table.

Query the views CTX_INDEX_PARTITIONS or CTX_USER_INDEX_PARTITIONS to find out index partition information, such as index partition name and index partition status.

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

PROMPT create partitioned table and populate it

CREATE TABLE part_tab (a int, b varchar2(40)) 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 search index
CREATE SEARCH INDEX part_idx ON part_tab (b) LOCAL;

See Also:

PARAMETERS(paramstring)

Optionally specify indexing parameters in paramstring. You can specify preferences owned by another user using the user.preference notation.

The syntax for paramstring is as follows:

paramstring =
'[DATASTORE datastore_pref]
 [STORAGE storage_pref]
 [MEMORY memsize]
 [MAINTENANCE AUTO | MAINTENANCE MANUAL]
 [SYNC (MANUAL | EVERY "interval-string"[MEMORY memsize] [PARALLEL n]| ON COMMIT)]
 [OPTIMIZE (MANUAL | AUTO_DAILY [PARALLEL n]| EVERY "interval-string"[PARALLEL n] )]
 [STOPLIST stoplist]
 [LEXER lexer_pref]
 [FILTER filter_pref]
 [WORDLIST wordlist_pref]
 [SECTION GROUP section_group]'

Note: TRANSACTIONAL and ASYNCHRONOUS_UPDATE parameters are not supported for the Oracle Text search index type.

DATASTORE datastore_pref

Specifies the name of your data store preference. Use the data store preference to specify where your text is stored. See Datastore Types .

The default is DIRECT_DATASTORE type.

STORAGE storage_pref

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

MEMORY memsize

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

memsize = number[K|M|G]

K is for kilobytes, M is for megabytes, and G is 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 for Oracle Text search index is 500MB.

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.

MAINTENANCE AUTO | MAINTENANCE MANUAL

Specifies the maintenance type for synchronization of the Oracle Text search index when there are inserts, updates, or deletes to the base table. The maintenance type specified for an index applies to all index partitions.

You can specify one of the following maintenance types:

Table 14 Maintenance Types

Maintenance Type Description
MAINTENANCE AUTO

This is the default method for synchronizing Oracle Text CONTEXT and search indexes.

This method sets your index to automatic maintenance, that is, the index is automatically synchronized in the background at optimal intervals.

You do not need to manually configure a SYNC type or set any synchronization interval. The background mechanism automatically determines the synchronization interval and schedules background SYNC.INDEX operations by tracking the DML queue.

Note: Shadow indexes do not support automatic maintenance. For a complete list of requirements and restrictions for indexes in an automatic maintenance mode, see Oracle Text Application Developer's Guide.

MAINTENANCE MANUAL This method sets your index to manual maintenance. This is a non-automatic maintenance (synchronization) mode in which you can specify SYNC types, such as MANUAL, EVERY, or ON COMMIT.

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

Specifies the SYNC method for synchronization of the Oracle Text search index when there are inserts, updates, or deletes to the base table.

These SYNC settings are applicable only to the indexes that are set to manual maintenance.

Note: By default, the CONTEXT and search indexes run in an automatic maintenance mode (MAINTENANCE AUTO), which means that your DMLs are automatically synchronized into the index in the background at optimal intervals. Therefore, you do not need to manually configure a SYNC method. However, if required, you can do so if you want to modify the default settings for an index.

You can specify one of the SYNC methods as described in Table 1-11.

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. MANUAL sync is the default synchronization method for Oracle Text 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 AI 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 an Oracle Text search index with automatic (EVERY) synchronization:

OPTIMIZE (MANUAL | AUTO_DAILY [PARALLEL n] | EVERY “interval-string[PARALLEL n])

Specify OPTIMIZE to enable automatic background index optimization. You can specify any one of the following OPTIMIZE methods:

Table 15 OPTIMIZE TYPES

OPTIMIZE Type Description
MANUAL Provides no automatic optimization. You must manually optimize the index with CTX_DDL.OPTIMIZE_INDEX.
AUTO_DAILY

This is the default value.

When you specify OPTIMIZE (AUTO_DAILY) in the CREATE INDEX PARAMETERS string, the continuously running optimize token and optimize full jobs are scheduled.

  • The optimize token job is scheduled to run every midnight from 12 A.M. to 3 A.M. except on Saturday night, in order to optimize the top 10 most fragmented tokens. Jobs that are not started before 3 A.M. are suspended until 12 A.M. the next day. These suspended jobs are started before the other jobs that are scheduled to run at 12 A.M. the next day.

  • The optimize full job is scheduled to run weekly from 12 A.M. every Saturday night in order to optimize index tables and clean up $N.

Existing indexes do not have OPTIMIZE (AUTO_DAILY) by default. You need to use ALTER INDEX to enable automatic background index optimization.

EVERY "interval-string"

Automatically runs optimize token at a regular interval specified by the value interval-string, which takes the same syntax as the scheduler jobs.

Ensure that interval-string is set to a considerable time period so that the previous optimize jobs are complete; otherwise, the optimize job might stop responding. interval-string must be enclosed in double quotes, and any single quote within interval-string must be preceded by the escape character with another single quote.

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

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

STOPLIST stoplist

Specifies the name of your stoplist. Use stoplist to identify words that are not to be indexed. See CTX_DDL.CREATE_STOPLIST .

The default for Oracle Text search index is CTXSYS.DEFAULT_STOPLIST.

LEXER lexer_pref

Specifies the name of your lexer or multilexer preference. Use the lexer preference to identify the language of your text and how text is tokenized for indexing. See “Lexer Types”.

The default is CTXSYS.DEFAULT_LEXER.

FILTER filter_pref

Specifies the name of your filter preference. Use the filter preference to specify how to filter formatted documents to plain text or HTML. See “Filter Types”.

The default for binary text columns is NULL_FILTER. The default for other text columns is AUTO_FILTER.

WORDLIST wordlist_pref

Specifies the name of your word list preference. Use the word list preference to enable features such as fuzzy, stemming, and prefix indexing for better wild card searching. See “Wordlist Type”.

SECTION GROUP section_group

Specifies the name of your section group. Use section groups to create sections in structured documents. See “CREATE_SECTION_GROUP” in CTX_DDL Package.

The default value for Oracle Text search index is NULL_SECTION_GROUP.

Syntax for JSON Search Index

CREATE SEARCH INDEX [schema.]index ON [schema.]table(json_column) FOR JSON
[LOCAL ([PARTITION [partition] ][, PARTITION [partition] ])]
PARAMETERS(
  [DATAGUIDE ON [CHANGE (ADD_VC | function_name)] | OFF]
  [STORAGE storage_pref]
  [SEARCH_ON NONE]
  [SEARCH_ON (TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)] | TEXT_VALUE_STRING)
             [(path_subsetting_clause)]
  [MEMORY memsize]
  [MAINTENANCE AUTO | MAINTENANCE MANUAL]
  [SYNC (MANUAL | EVERY "interval-string" [MEMORY memsize] [PARALLEL n] | ON COMMIT)]
  [OPTIMIZE (MANUAL | EVERY "interval-string"[PARALLEL n] | AUTO_DAILY [PARALLEL n])]
  [ASYNCHRONOUS_UPDATE | SYNCHRONOUS_UPDATE]
  [POPULATE | NOPOPULATE]
  [DATASTORE datastore_pref]
  [FILTER filter_pref]
  [LEXER lexer_pref]
  [WORDLIST wordlist_pref]
)
[PARALLEL N]
[UNUSABLE];

If you omit the PARAMETERS clause, then the default values for DATAGUIDE and SEARCH_ON are OFF and TEXT_VALUE respectively. The default synchronization method is MAINTENANCE AUTO. Thus, the index is automatically synchronized in the background, and both text and numeric or date-time ranges are indexed.

Note:

[schema.]index

Specifies the name of the JSON search index to create.

[schema.]table(index_column)

Specifies the names of table and column to index. index_column is the name of the column on which the index is created.

The column must have the JSON data type or 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), hash-composite (range, list, and hash), and automatic-list 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 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 AI Database Data Cartridge Developer’s Guide

DATAGUIDE ON | OFF

Specifies data guide support for a JSON search index. The default behavior is to create a JSON search index without data guide support. If you enable data guide support, then you can also define change-trigger procedures.

Note: You use the DATAGUIDE parameter only for JSON search indexes.

Specify one of the following options:

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

Does not enable any indexing features, which 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.

For example:

CREATE SEARCH INDEX json_idx ON json_tab (jsondoc)
  FOR JSON PARAMETERS ('SEARCH_ON NONE DATAGUIDE ON');

SEARCH_ON (TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)] | TEXT_VALUE_STRING) [(path_subsetting_clause)]

Specifies the type of data or attributes to be indexed for efficient searching. You can also specify a path subsetting clause, as explained in the section that follows.

Note: You can use the SEARCH_ON clause only for JSON and XML search indexes.

You can specify one of the following SEARCH_ON options:

Table 16 SEARCH_ON Options

Option Description
TEXT

Enables full-text search component, which indicates that only textual data is indexed for full-text search queries. This also includes queries that rely on path information.

The index is used for JSON_TEXTCONTAINS predicates and for JSON_VALUE or JSON_EXISTS predicates that manipulate strings when using JSON search index.

If your queries involve only full-text search and not string-range search or numeric search, then you can save some index maintenance time and disk space by specifying this option.

Example:

CREATE SEARCH INDEX json_idx ON json_tab (jsondoc)  FOR JSON PARAMETERS ('SEARCH_ON TEXT');
VALUE[(data_types)]

Enables range-search component for the specified data types.

This allows the index to be picked up for predicates using relational operators (>, <, ==, >=, <=, !=). A JSON search index that is created with only SEARCH_ON VALUE cannot answer full-text queries by using the JSON_TEXTCONTAINS operator.

Supported data types:

  • NUMBER for indexing numeric values.

  • TIMESTAMP for indexing date-time values.

  • VARCHAR2 for indexing complete string values. The string values are indexed as is without tokenization or other transformations. All the strings that are smaller than or equal to 237 bytes are indexed.

If you do not specify any data type, then the index enables range-search indexing on all supported data types.

Note:

The BINARY_DOUBLE data type is allowed only for XML search indexes.

Examples:

  • This example specifies the default behavior: 

    CREATE SEARCH INDEX json_idx ON json_tab (jsondoc)  FOR JSON PARAMETERS ('SEARCH_ON VALUE');

  • These examples explicitly specify data types using the VALUE(data_types) syntax:

    CREATE SEARCH INDEX json_idx ON json_tab (jsondoc)  FOR JSON PARAMETERS ('SEARCH_ON VALUE(TIMESTAMP)');
    CREATE SEARCH INDEX json_idx ON json_tab (jsondoc)  FOR JSON PARAMETERS ('SEARCH_ON VALUE(NUMBER,  TIMESTAMP,  VARCHAR2)');
TEXT_VALUE[(data_types)]

Enables both the full-text and range-search components for the specified data types.

Supported data types:

  • NUMBER for indexing numeric values.

  • TIMESTAMP for indexing date-time values.

  • VARCHAR2 for indexing complete string values. The string values are indexed as is without tokenization or other transformations. All the strings that are smaller than or equal to 237 bytes are indexed.

If you do not specify any data type, then the index enables full-text search and range-search indexing on NUMBER and TIMESTAMP data types.

Examples:

  • This example specifies the default behavior:

    CREATE SEARCH INDEX json_idx ON json_tab (jsondoc)  FOR JSON PARAMETERS('SEARCH_ON TEXT_VALUE');

  • These examples explicitly specify data types using the TEXT_VALUE(data_types) syntax:

    CREATE SEARCH INDEX json_idx ON json_tab (jsondoc)  FOR JSON PARAMETERS('SEARCH_ON TEXT_VALUE(NUMBER)');
    CREATE SEARCH INDEX json_idx ON json_tab (jsondoc)  FOR JSON PARAMETERS('SEARCH_ON TEXT_VALUE(NUMBER,  TIMESTAMP)');
TEXT_VALUE_STRING

Indicates that text and range-based indexes are created for numeric, date-time, and complete string values.

This enables both the full-text and range-search components on the NUMBER, TIMESTAMP, and VARCHAR2 data types. String values are indexed as is without tokenization or other transformations. All the strings that are smaller than or equal to 237 bytes are indexed.

Example:

CREATE SEARCH INDEX json_idx ON json_tab (jsondoc)  FOR JSON PARAMETERS('SEARCH_ON TEXT_VALUE_STRING');

Note: For range-search queries, instead of TEXT_VALUE_STRING, Oracle recommends that you use either the VALUE[(data_types)] or TEXT_VALUE[(data_types)] option.

Creating an index with TEXT_VALUE(NUMBER, TIMESTAMP, VARCHAR2) is equivalent to TEXT_VALUE_STRING.

path_subsetting_clause

You can use path subsetting with SEARCH_ON to identify the fields in a document that you want to include or exclude from indexing. The excluded fields are not indexed, and the JSON search index is not used for them when querying. Filtering out irrelevant paths from documents can reduce the amount of data indexed, thereby minimizing disk space and the index creation or rebuild time.

Syntax for SEARCH_ON with path_subsetting_clause:

SEARCH_ON (
  TEXT | TEXT_VALUE [(data_types)] | VALUE [(data_types)] )
  [(INCLUDE \| EXCLUDE) '(' paths ')']

Note the following:

Table 17 SEARCH_ON Options With Path Subsetting

Option Path Subsetting Example
TEXT

  • This example creates a JSON search index with path subsetting for full-text and string-equality searches. It indexes only the fields located at paths $.SpecialInstructions and $.LineItems.Part.Description of a purchase order document.

    CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc) FOR JSON PARAMETERS ('SEARCH_ON TEXT INCLUDE ($.SpecialInstructions, $.LineItems.Part.Description)');

  • This example creates a JSON search index with path subsetting for full-text and string-equality searches. It excludes the field located at path $.User of a purchase order document.

    CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc) FOR JSON PARAMETERS ('SEARCH_ON TEXT EXCLUDE ($.User');
VALUE[(data_types)]

  • This example creates a JSON search index with path subsetting for full-text and string-equality searches of fields $.SpecialInstructions and $.LineItems.Part.Description of a purchase order document. But it also indexes fields $.PONumber and $.LineItems.Part.UnitPrice for numeric-value ranges, and fields $.Reference, $.User, $.ShippingInstructions.name, and $.ShippingInstructions.Address.zipCode for string-value ranges.

    CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc) FOR JSON PARAMETERS ('SEARCH_ON  TEXT INCLUDE ($.SpecialInstructions, $.LineItems.Part.Description) VALUE(NUMBER) INCLUDE ($.PONumber, $.LineItems.Part.UnitPrice) VALUE(VARCHAR2) INCLUDE ($.Reference,  $.User, $.ShippingInstructions.name, $.ShippingInstructions.Address.zipCode)');

    Alternatively, you can create the same index using the PATHLIST parameter, whose value is a named list of the paths to be included, created using PL/SQL subprograms CTX_DDL.create_path_list and CTX_DDL.add_path, as follows: 

    BEGIN CTX_DDL.create_path_list('json_pl', CTX_DDL.PATHLIST_JSON, CTX_DDL.PATHLIST_INCLUDE); CTX_DDL.add_path('json_pl', 'TEXT', '$.SpecialInstructions'); CTX_DDL.add_path('json_pl', 'TEXT', '$.LineItems.Part.Description'); CTX_DDL.add_path('json_pl', 'NUMBER', '$.PONumber'); CTX_DDL.add_path('json_pl', 'NUMBER', '$.LineItems.Part.UnitPrice'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.Reference'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.User'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.name'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.Address.zipCode'); END; / CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc) FOR JSON PARAMETERS ('PATHLIST json_pl');

  • This example creates a JSON search index with path subsetting for numeric-value ranges, where it excludes the field located at path $.PONumber of a purchase order document.

    CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc) FOR JSON PARAMETERS ('SEARCH_ON VALUE(NUMBER) EXCLUDE ($.PONumber)');
TEXT_VALUE[(data_types)]

  • This example creates a JSON search index with path subsetting for full-text and string-equality searches of fields $."FieldWithNoQuote" and $."FieldWith"Quote"" of a purchase order document. 

    CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc) FOR JSON PARAMETERS ('SEARCH ON  TEXT_VALUE INCLUDE ($."FieldWithNoQuote", $."FieldWith"Quote"")');

    Alternatively, you can create the same index using the PATHLIST parameter, whose value is a named list of the paths to be included, created using PL/SQL subprograms CTX_DDL.create_path_list and CTX_DDL.add_path, as follows: 

    BEGIN CTX_DDL.create_path_list('json_pl', CTX_DDL.PATHLIST_JSON, CTX_DDL.PATHLIST_INCLUDE); CTX_DDL.add_path('json_pl', 'TEXT', '$.SpecialInstructions'); CTX_DDL.add_path('json_pl', 'TEXT', '$.LineItems.Part.Description'); CTX_DDL.add_path('json_pl', 'NUMBER', '$.PONumber'); CTX_DDL.add_path('json_pl', 'NUMBER', '$.LineItems.Part.UnitPrice'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.Reference'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.User'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.name'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.Address.zipCode'); END; / CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc) FOR JSON PARAMETERS ('PATHLIST json_pl');

  • This example creates a JSON search index with path subsetting for numeric-value ranges, where it excludes the field located at path $.PONumber of a purchase order document.

    CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc) FOR JSON PARAMETERS ('SEARCH_ON TEXT_VALUE(NUMBER) EXCLUDE ($.PONumber)');
TEXT_VALUE_STRING This example creates a JSON search index with path subsetting, where the paths are indexed according to the available data types. For example, if the contents of the field $.PONumber is 145980, then 145980 is indexed as a token for full-text search, as a numeric value (145980) for numeric range search, and as a string ("145980" ) for string range search.
CREATE SEARCH INDEX json_idx ON json_tab (purchase_order_jsondoc) FOR JSON PARAMETERS ('SEARCH_ON TEXT_VALUE_STRING INCLUDE ($.SpecialInstructions, $.LineItems.Part.Description, $.PONumber, $.LineItems.Part.UnitPrice, $.Reference, $.User, $.ShippingInstructions.name, $.ShippingInstructions.Address.zipCode)');

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 is for kilobytes, M is for megabytes, and G is 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 for JSON search index 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.

MAINTENANCE AUTO | MAINTENANCE MANUAL

Specifies the maintenance type for synchronization of the JSON search index when there are inserts, updates, or deletes to the base table. The maintenance type specified for an index applies to all index partitions.

You can specify one of the following maintenance types:

Table 18 Maintenance Types

Maintenance Type Description
MAINTENANCE AUTO

This is the default method for synchronizing Oracle Text CONTEXT and search indexes.

This method sets your index to automatic maintenance, that is, the index is automatically synchronized in the background at optimal intervals.

You do not need to manually configure a SYNC type or set any synchronization interval. The background mechanism automatically determines the synchronization interval and schedules background SYNC.INDEX operations by tracking the DML queue.

Note: Shadow indexes do not support automatic maintenance. For a complete list of requirements and restrictions to follow in an automatic maintenance mode, see Oracle Text Application Developer's Guide.

MAINTENANCE MANUAL This method sets your index to manual maintenance. This is a non-automatic maintenance (synchronization) mode in which you can specify SYNC types, such as MANUAL, EVERY, or ON COMMIT.

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

Specifies the SYNC type for synchronization of the JSON search index when there are inserts, updates, or deletes to the base table.

These SYNC settings are applicable only to the indexes that are set to manual maintenance.

Note: By default, the CONTEXT and search indexes run in an automatic maintenance mode (MAINTENANCE AUTO), which means that your DMLs are automatically synchronized into the index in the background at optimal intervals. Therefore, you do not need to manually configure a SYNC method. However, if required, you can do so if you want to modify the default settings for an index.

You can specify one of the SYNC methods as described in Table 1-11.

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. 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 AI 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:

OPTIMIZE

Specify OPTIMIZE to enable automatic background index optimization. You can specify any of the following OPTIMIZE methods:

Table 19 OPTIMIZE TYPES

OPTIMIZE Type Description
MANUAL

This is the default value.

Provides no automatic optimization. You must manually optimize the index with CTX_DDL.OPTIMIZE_INDEX.

AUTO_DAILY

When you specify OPTIMIZE (AUTO_DAILY) in the CREATE INDEX PARAMETERS string, the continuously running optimize TOKEN_TYPE and optimize full jobs are scheduled as follows:

  • The optimize TOKEN_TYPE job is scheduled to run every midnight from 12 A.M. to 3 A.M., except on Saturday nights, to optimize SDATA sections in the index. Jobs that are not started before 3 A.M. are suspended until 12 A.M. the next day. These suspended jobs are started before the other jobs that are scheduled to run at 12 A.M. the next day.

  • The optimize full job is scheduled to run weekly from 12 A.M. every Saturday night to optimize index tables and clean up $N.

EVERY "interval-string"

Automatically runs the optimize TOKEN_TYPE job at a regular interval specified by the value interval-string, which takes the same syntax as scheduler jobs.

Ensure that interval-string is set to a considerable time period so that the previous optimize jobs are complete; otherwise, the optimize job might stop responding. interval-string must be enclosed in double quotes, and any single quote within interval-string must be preceded by the escape character with another single quote.

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

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

Syntax for XML Search Index

Starting with Oracle Database 26ai, the XML search index provides a simplified syntax for creating XML-enabled indexes. You can create indexes on XML documents that are stored inside an XMLType column or table. This enables you to run textual, path-aware, and range-search queries over XML documents.

CREATE SEARCH INDEX [schema.]index ON [schema.]table(xml_column)
FOR XML
[LOCAL]
PARAMETERS(
  [SEARCH_ON (TEXT | TEXT_VALUE(data_types) | VALUE(data_types))]
  [STORAGE storage_pref]
  [PREFIX_NS (prefix_ns_mapping)]
  [MEMORY memsize]
  [MAINTENANCE AUTO | MAINTENANCE MANUAL]
  [SYNC (MANUAL | EVERY "interval-string" | ON COMMIT)]
  [OPTIMIZE (MANUAL | EVERY "interval-string" | AUTO_DAILY)]
)
[PARALLEL N]
[UNUSABLE];

[schema.]index

Specifies the name of the XML search index to create.

[schema.]table(index_column)

Specifies the names of table and column to index. index_column is the name of the column on which the index is created.

You can create the index only on an XMLType column that stores documents using the TRANSPORTABLE BINARY XML (TBX) storage option.

LOCAL

Creates a local partitioned XML 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), hash-composite (range, list, and hash), and automatic-list partitioning schemes. You can create a local XML 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 XML 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 XML search index:

PROMPT create partitioned table and populate it

CREATE TABLE part_tab (a int, b SYS.XMLType) XMLTYPE b STORE AS
  TRANSPORTABLE BINARY XML 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 XML search index
CREATE SEARCH INDEX part_idx ON part_tab (b)
  FOR XML PARAMETERS ('SEARCH_ON TEXT') LOCAL;

STORAGE storage_pref

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

If you do not specify a storage preference, then the default storage preference (CTXSYS.XQFT_MEDIUM) is used.

SEARCH_ON (TEXT | TEXT_VALUE(data_types) | VALUE(data_types))

Specifies the type of data or attributes to be indexed for efficient searching.

Note: You can use the SEARCH_ON clause only for JSON and XML search indexes.

You can specify one of the following SEARCH_ON options:

Table 20 SEARCH_ON Options

Option Description
TEXT

Enables full-text search component, which indicates that only textual data is indexed for full-text search queries. This also includes queries that rely on path information.

The index is used for XMLEXISTS predicates that references the XQuery Full Text operators and clauses.

If your queries involve only full-text search and not string-range search or numeric search, then you can save some index maintenance time and disk space by specifying this option.

For example:

CREATE SEARCH INDEX ex_xml_idx ON ex_tab (xmldoc)  FOR XML PARAMETERS ('SEARCH_ON TEXT');
VALUE(data_types)

Enables range-search component for the specified data types.

This allows the index to be picked up for predicates using relational operators (>, <, ==, >=, <=, !=). An XML search index that only has the SEARCH_ON VALUE component enabled cannot answer full-text queries, if XQuery Full Text operators are present in an XMLEXISTS predicate.

You must specify one or more data types:

  • BINARY_DOUBLE and NUMBER for indexing numeric values.

  • TIMESTAMP for indexing date-time values.

  • VARCHAR2 for indexing complete string values. The string values are indexed as is without tokenization or other transformations. All the strings that are smaller than or equal to 237 bytes are indexed.

For example:

CREATE SEARCH INDEX ex_xml_idx ON ex_tab (xmldoc)  FOR XML PARAMETERS ('SEARCH_ON VALUE(NUMBER)');
CREATE SEARCH INDEX ex_xml_idx ON ex_tab (xmldoc)  FOR XML PARAMETERS ('SEARCH_ON VALUE(BINARY_DOUBLE,  NUMBER,  TIMESTAMP,  VARCHAR2)');
TEXT_VALUE(data_types)

Enables both the full-text and range-search components for the specified data types. For range-search queries, you must specify one or more data types, such as NUMBER (for indexing numeric values) and TIMESTAMP (for indexing date-time values).

For example:

CREATE SEARCH INDEX ex_xml_idx ON ex_tab (xmldoc)  FOR XML PARAMETERS('SEARCH_ON TEXT_VALUE(TIMESTAMP)');
CREATE SEARCH INDEX ex_xml_idx ON ex_tab (xmldoc)  FOR XML PARAMETERS('SEARCH_ON TEXT_VALUE(NUMBER,  TIMESTAMP)');

Note:

You cannot use SEARCH_ON NONE and SEARCH_ON TEXT_VALUE_STRING for an XML search index.

You must explicitly specify a data type with the TEXT_VALUE and VALUE options for an XML search index, otherwise the statement will result in an error.

PREFIX_NS (prefix_ns_mapping)

Specifies prefix-namespace mapping for an XML search index.

An XMLExists query can include XML namespace declarations. While creating the search index, you can separately store qualified names belonging to different XML namespaces.

A prefix-namespace mapping uses this syntax:

xmlns:local_name="URI_string"

xmlns is the default XML namespace declaration attribute. The URI_string value is not mandatory. You can provide an empty string enclosed in double quotation marks. You can also specify a qualified-name with the xmlns prefix. If you do not specify a prefix-namespace mapping, then xmlns is used.

For example:

PREFIX_NS (xmlns="example.com" xmlns:pfx="www.example
1.com" xmlns:pfx2="example2.com"));

Note:

You use the PREFIX_NS clause only for XML search indexes.

You cannot use ALTER INDEX to modify a prefix-namespace mapping specification.

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 is for kilobytes, M is for megabytes, and G is 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 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.

MAINTENANCE AUTO | MAINTENANCE MANUAL

Specifies the maintenance type for synchronization of the XML search index when there are inserts, updates, or deletes to the base table. The maintenance type specified for an index applies to all index partitions.

You can specify one of the following maintenance types:

Table 21 Maintenance Types

Maintenance Type Description
MAINTENANCE AUTO

This is the default method for synchronizing Oracle Text CONTEXT and search indexes.

This method sets your index to automatic maintenance, that is, the index is automatically synchronized in the background at optimal intervals.

You do not need to manually configure a SYNC type or set any synchronization interval. The background mechanism automatically determines the synchronization interval and schedules background SYNC.INDEX operations by tracking the DML queue.

Note: Shadow indexes do not support automatic maintenance. For a complete list of requirements and restrictions to follow in an automatic maintenance mode, see Oracle Text Application Developer's Guide.

MAINTENANCE MANUAL This method sets your index to manual maintenance. This is a non-automatic maintenance (synchronization) mode in which you can specify SYNC types, such as MANUAL, EVERY, or ON COMMIT.

SYNC (MANUAL | EVERY “interval-string” | ON COMMIT)

Specifies the SYNC method for synchronization of the XML search index when there are inserts, updates, or deletes to the base table.

These SYNC settings are applicable only to the indexes that are set to manual maintenance.

Note: By default, the CONTEXT and search indexes run in an automatic maintenance mode (MAINTENANCE AUTO), which means that your DMLs are automatically synchronized into the index in the background at optimal intervals. Therefore, you do not need to manually configure a SYNC method. However, if required, you can do so if you want to modify the default settings for an index.

You can specify one of the SYNC methods as described in Table 1-11.

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. ON COMMIT is the default synchronization method for XML 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 AI 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 an XML search index with automatic (EVERY) synchronization:

OPTIMIZE

Specify OPTIMIZE to enable automatic background index optimization. You can specify one of the following OPTIMIZE methods:

Table 22 OPTIMIZE TYPES

OPTIMIZE Type Description
MANUAL

This is the default value.

Provides no automatic optimization. You must manually optimize the index with CTX_DDL.OPTIMIZE_INDEX.

AUTO_DAILY

When you specify OPTIMIZE (AUTO_DAILY) in the CREATE INDEX PARAMETERS string, the continuously running optimize TOKEN_TYPE and optimize full jobs are scheduled as follows:

  • The optimize TOKEN_TYPE job is scheduled to run every midnight from 12 A.M. to 3 A.M., except on Saturday nights, to optimize SDATA sections in the index. Jobs that are not started before 3 A.M. are suspended until 12 A.M. the next day. These suspended jobs are started before the other jobs that are scheduled to run at 12 A.M. the next day.

  • The optimize full job is scheduled to run weekly from 12 A.M. every Saturday night to optimize index tables and clean up $N.

EVERY "interval-string"

Automatically runs the optimize TOKEN_TYPE job at a regular interval specified by the value interval-string, which takes the same syntax as scheduler jobs.

Ensure that interval-string is set to a considerable time period so that the previous optimize jobs are complete; otherwise, the optimize job might stop responding. interval-string must be enclosed in double quotes, and any single quote within interval-string must be preceded by the escape character with another single quote.

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

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

Related Topics