CREATE HYBRID VECTOR INDEX

Use the CREATE HYBRID VECTOR INDEX SQL statement to create a hybrid vector index, which allows you to index and query documents using a combination of full-text search and vector similarity search.

Purpose

To create a class of specialized Domain Index called a hybrid vector index.

A hybrid vector index is an Oracle Text SEARCH INDEX type that combines the existing Oracle Text indexing data structures and vector indexing data structures into one unified structure. It is a single domain index that stores both text fields and vector fields for a document. Both text search and similarity search are performed on tokenized terms and vectors respectively. The two search results are combined and scored to return a unified result set.

The purpose of a hybrid vector index is to enhance search relevance of an Oracle Text index by allowing users to search by both vectors and keywords. By integrating traditional keyword-based text search with vector-based similarity search, you can improve the overall search experience and provide users with more accurate information.

Usage Notes

To create a hybrid vector index, you can provide minimal information such as:

For cases where multiple columns or tables need to be indexed together, you can specify the MULTI_COLUMN_DATASTORE or USER_DATASTORE preference.

All other indexing parameters are predefined to facilitate the indexing of documents without requiring you to be an expert in any text processing, chunking, or embedding strategies. If required, you can modify the predefined parameters using:

During HVI (Hybrid View Index) creation, any rows that fail to be indexed are logged in the CTX_USER_INDEX_ERRORS table. You can query this table to review details about any rows that could not be indexed, including error messages and relevant row information. This is helpful for easier identification of issues encountered during the index creation.

When your database is configured with MAX_STRING_SIZE=EXTENDED, you can leverage this database setting to index significantly larger text chunks (up to 32,767 bytes) for hybrid vector indexing using the DBMS_VECTOR_CHAIN.CREATE_PREFERENCE PL/SQL function.

When performing IMPDP (Oracle Data Pump Import) of a schema containing a Hybrid Vector Index (IVF) ensure that index preferences (such as datastore, lexer, wordlist, storage, and section group) exist in the target schema prior to import. Always manually create all text index preferences (datastore, lexer, wordlist, storage, section group) in the target schema before running Data Pump Import or index creation DDL for hybrid vector indexes. Failing to do so will cause errors about missing preferences. Review the export SQL file, prepare corresponding ctx_ddl.create_preference statements, and execute them before import.

For detailed information on the creation process of a hybrid vector index or in general about what hybrid vector indexes are, see Understand Hybrid Vector Indexes.

Note: There are some key points to note when creating and using hybrid vector indexes. See Guidelines and Restrictions for Hybrid Vector Indexes.

Syntax

CREATE HYBRID VECTOR INDEX [schema.]index_name ON
  [schema.]table_name(column_name)
  [FILTER BY filter_column[, filter_column]...]
  [ORDER BY oby_column[desc|asc][, oby_column[desc|asc]]...]
  PARAMETERS ('paramstring')
  [LOCAL [PARTITION [partition] ][, PARTITION [partition] ]]
  [PARALLEL n];

Here is an example DDL specified with only the minimum required parameters.

CREATE HYBRID VECTOR INDEX my_hybrid_idx on
  doc_table(text_column)
  PARAMETERS('MODEL my_embed_model');

Setting an explicit memory and parallel degree is highly recommended as the default memory is low and the index will take longer to create.

Here is an example DDL which specifies the memory and the parallel degree.

CREATE HYBRID VECTOR INDEX my_hybrid_idx on
  doc_table(text_column)
  PARAMETERS('MODEL my_embed_model MEMORY 1G') PARALLEL 4;

Note: The total PGA memory used will be the value of the MEMORY parameter multiplied by the PARALLEL degree. In this example, the total PGA memory used would be 1G * 4 = 4GB. This means that up to 4GB of PGA memory can be used by the hybrid vector index.

More comprehensive examples are given at the end of this section.

Let us explore all the required and optional indexing parameters:

[schema.]index_name

Specify the name of the hybrid vector index to create.

[schema.]table_name(column_name)

Specify the name of the table and column on which you want to create the hybrid vector index. You can create a hybrid vector index on one or more text columns with VARCHAR2, CLOB, and BLOB data types.

Note: You cannot create hybrid vector indexes on a text column that uses the IS JSON check constraint.

Because the system can index most document formats, including HTML, PDF, Microsoft Word, and plain text, you can load a supported type into the text column. For a complete list, see Supported Document Formats.

For cases where multiple columns or tables need to be indexed together, specify a datastore preference (described later in Text search preferences).

PARAMETERS (paramstring)

Specify preferences in paramstring:

A vectorizer preference to enable extended string data type capability for a Hybrid Vector Index:

When your database is configured with MAX_STRING_SIZE=EXTENDED, you can leverage this database setting to index significantly larger text chunks (up to 32,767 bytes) for hybrid vector indexing. This enables you to store and index text segments up to 32,767 bytes or 8,000 words. DBMS_VECTOR_CHAIN.CREATE_PREFERENCE lets you to leverage the extended data type for a hybrid vector index using extended": 1 attribute:

begin
  DBMS_VECTOR_CHAIN.CREATE_PREFERENCE(
    'my_vectorizer_pref',
     dbms_vector_chain.vectorizer,
    json('{"by": "WORDS", "max": 2000, "extended": 1}')
);

Note: If you do not specify "extended": 1 or your database is not configured with MAX_STRING_SIZE=EXTENDED, the previous limits (1,000 words or 4,000 bytes) will apply, and inserting larger chunks will result in errors.

Note: By default, a hybrid vector index runs 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 type for maintaining a hybrid vector index. 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:

SYNC Type Description
MANUAL With this method, automatic synchronization is not provided. You must manually synchronize the index using CTX_DDL.SYNC_INDEX.
EVERY interval-string

Automatically synchronize the index at a regular interval specified by the value of interval-string, which takes the same syntax as that for scheduler jobs. Automatic synchronization using EVERY requires that the index creator have CREATE JOB privileges.

Ensure that interval-string is set to a considerable time period so that any previous synchronization jobs will have completed. Otherwise, the synchronization job may stop responding. The interval-string argument must be enclosed in double quotation marks ('' '').

ON COMMIT

Synchronize the index immediately after a commit. The commit does not return until the sync is complete.

The operation uses the memory specified with the memory parameter.

This sync type works best when the STAGE_ITAB index option is enabled, otherwise it causes significant fragmentation of the main index, requiring frequent OPTIMIZE calls.

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

For example, to sync the index at an interval of 20 seconds:

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

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

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

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 setting. With OPTIMIZE (AUTO_DAILY), the optimize FULL job is scheduled to run midnight from 12 A.M. local time everyday.
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.

Syntax:

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

For example, to optimize the index at an interval of 20 minutes:

OPTIMIZE [EVERY "freq=minutely;interval=20"] PARALLEL 2

FILTER BY filter_column

Specify the structured indexed column on which a range or equality predicate in the WHERE clause of a mixed query will operate. You can specify one or more structured columns for filter_column, on which the relational predicates are expected to be specified along with the CONTAINS() predicate in a query.

ORDER BY oby_column[desc|asc]

Specify one or more structured indexed columns by which you want to sort query results.

You can specify a list of structured oby_columns. These columns can only be of CHAR, NUMBER, DATE, VARCHAR2, or RAW type. VARCHAR2 and RAW columns longer than 249 bytes are truncated to the first 249 bytes. Expressions, for example func(cola), and virtual columns are not allowed.

The order of the specified columns matters. The ORDER BY clause in a query can contain:

DESC sorts the results in a descending order (from highest to lowest), while ASC (default) sorts the results in an ascending order (from lowest to highest).

[PARALLEL n]

Parallel indexing can improve index performance when you have multiple CPUs. To create an index in parallel, use the PARALLEL clause with a parallel degree.

Optionally specifies the parallel degree for parallel indexing. The actual degree of parallelism might be smaller depending on your resources. You can use this parameter on nonpartitioned tables. However, creating a nonpartitioned index in parallel does not turn on parallel query processing. Parallel indexing is supported for creating a local partitioned index.

The indexing memory size specified in the parameter clause applies to each parallel worker. For example, if indexing memory size is specified in the parameter clause as 500M and parallel degree is specified as 2, then you must ensure that there is at least 1GB of memory available for indexing.

Examples

Related Topics