CREATE_INDEX

The DBMS_SEARCH.CREATE_INDEX procedure creates a ubiquitous search index (or DBMS_SEARCH index) to perform full-text and range-based searches across multiple schema objects.

Syntax

The index type is a JSON search index enabled with a predefined set of preferences and settings to perform full text searches on tables, views, and Duality views.

DBMS_SEARCH.CREATE_INDEX(
    index_name       VARCHAR2,
    tablespace       VARCHAR2 DEFAULT NULL,
    datatype         VARCHAR2 DEFAULT NULL,
    lexer            VARCHAR2 DEFAULT NULL,
    stoplist         VARCHAR2 DEFAULT NULL,
    wordlist         VARCHAR2 DEFAULT NULL,
    vectorizer       VARCHAR2 DEFAULT NULL,
    pathlist         VARCHAR2 DEFAULT NULL,
    persist_data     BOOLEAN DEFAULT FALSE
);

Note: The DBMS_SEARCH.CREATE_INDEX procedure has been upgraded in version 23.9 to support lexer, stoplist, wordlist, and vectorizer parameters. These parameters are not available for use in earlier release versions. Additionally, starting from version 23.9 DBMS_SEARCH index will not get created with default indexing preference, WILDCARD_INDEX.

index_name

Specify the name of the DBMS_SEARCH index to create. You can also specify the schema owner name along with the index name as:

[schema].index_name

tablespace

Specify the name of the tablespace to contain the index or index partitions.

datatype

Specify the datatype of the DATA column on which to create the index. The allowed values are JSON and OSON.

The default value is set to JSON, so you need to specify the datatype argument only if you want to override this default.

lexer

Specify 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

wordlist

Specify the name of your wordlist preference. Use the wordlist preference to enable features such as fuzzy, stemming, and prefix indexing for better wildcard searching. See Wordlist Type

stoplist

Specify the name of your stoplist. Use stoplist to identify words that are not to be indexed. See Stoplists

vectorizer

Specify the name of your vectorizer preference. Use the vectorizer preference to customize vector search parameters of a hybrid vector indexing pipeline. The goal of a vectorizer preference is to provide you with a straightforward way to configure how to chunk and embed your documents and create a vector index, without requiring a deep understanding of various chunking or embedding strategies.

A vectorizer preference is a JSON object that collectively holds all indexing parameters related to chunking (UTL_TO_CHUNKS or VECTOR_CHUNKS), embedding (UTL_TO_EMBEDDING, UTL_TO_EMBEDDINGS, or VECTOR_EMBEDDING), and vector index (distance, accuracy, or vector_idxtype). You use the DBMS_VECTOR_CHAIN.CREATE_PREFERENCE PL/SQL function to create a vectorizer preference. To create a vectorizer preference, see DBMS_VECTOR_CHAIN.CREATE_PREFERENCE. After creating a vectorizer preference, you can use the vectorizer parameter to pass the preference name.

persist_data

Specify whether the source data should be copied and stored in the DATA column of the DBMS_SEARCH index:

When set to false (default): The source data is not stored in the DATA column of the DBMS_SEARCH index. Instead, only a reference to the source data is stored in the METADATA column.
When set to true: The source data is copied and stored in the DATA column of the DBMS_SEARCH index.

pathlist

Specifies the name of a pathlist preference to restrict indexing of a JSON document to only those paths explicitly listed in the pathlist preference.

Example: Index only the ‘title’ and ‘body’ paths within a JSON document :

BEGIN
    ctx_ddl.create_pathlist('my_pathlist');
    ctx_ddl.add_path('my_pathlist', '$.title');
    ctx_ddl.add_path('my_pathlist', '$.body');

    ctxsys.dbms_search.create_index(
      index_name => 'my_search_idx',
      pathlist   => 'my_pathlist'
    );
  END;
  /

Notes

You can define which tables or views should be indexed by adding them as data sources into your index. All the columns of those tables or views are indexed. Use the DBMS_SEARCH.ADD_SOURCE and DBMS_SEARCH.REMOVE_SOURCE procedures to manage data sources.
If a column of the data source table is dropped using ALTER TABLE table_name DROP COLUMN column_name then the data source table needs to be removed from the DBMS_SEARCH index using DBMS_SEARCH.REMOVE_SOURCE procedure and added back using DBMS_SEARCH.ADD_SOURCE procedure.
To use a view as a data source for the DBMS_SEARCH index, the view must be defined directly on tables, not on other views. If the view’s definition references another view, then it cannot be used as a data source for the DBMS_SEARCH index.

The DBMS_SEARCH index is created with the following default indexing preferences:

Preference	Description
`WILDCARD_INDEX`	Enables wildcard indexing for a fast wildcard search.
`BASIC_WORDLIST`	Enables stemming and fuzzy matching.
`SEARCH_ON`	Allows both full-text and range-search queries for a specific data type. The supported data types are `NUMBER` (for indexing numeric values) and `TIMESTAMP` (for indexing date-time values).
`SYNC` and `OPTIMIZE`	Creates background jobs at predefined intervals to automatically synchronize the DML changes and optimize the index using the `AUTO_DAILY` mode on all data sources. You do not need to explicitly run any `SYNC_INDEX` and `OPTIMIZE_INDEX` operations on this index.

You can query this index using the CONTAINS(), JSON_TEXTCONTAINS(), and JSON_EXISTS operators on the INDEX_NAME table.
You can use the following DBMS_SEARCH dictionary views to examine these indexes:
- USER_DBMS_SEARCH_INDEXES: To query information about the DBMS_SEARCH indexes that are created in a user’s schema.
- ALL_DBMS_SEARCH_INDEXES: To query information about all existing DBMS_SEARCH indexes, corresponding to each index owner.
- USER_DBMS_SEARCH_INDEX_SOURCES: To query information about the data sources that are added to the DBMS_SEARCH indexes, created in a user’s schema.
- ALL_DBMS_SEARCH_INDEX_SOURCES: To query information about all existing data sources added to the DBMS_SEARCH indexes, corresponding to each index owner.

Example

This example specifies the index_name, tablespace, and datatype arguments. Here, the schema owner name is specified along with the index name as SCOTT.MYINDEX.

CREATE TABLESPACE tbs_02 DATAFILE 'dt.dbf' size 100MB segment space management auto;

exec DBMS_SEARCH.CREATE_INDEX('SCOTT.MYINDEX','tbs_02','JSON');

Related Topics