Covering Columns with Hybrid Vector Indexes

Overview

Covering columns in vector indexes facilitate faster searches with attribute filters by incorporating non-vector columns within a hybrid Vector Index.

By bridging the gap between traditional database filters and AI-powered vector searches, covering columns deliver faster, more efficient results. This synergy enhances search performance, especially in use cases requiring fine-grained filtering alongside advanced similarity computations.

Note: The covering column must have the JSON data type. It must be the same source-table column used to build the hybrid vector index.

High-Level Workflow

Configure Covering Columns in the Vectorizer Preference

Define covering columns in the vectorizer preference that you use to create the hybrid vector index. The covering columns configuration in the vectorizer preference helps database identify a source table column and, optionally, its data type. If you omit the data type for a relational column, the database uses the source column’s data type. The covering column uses the source column name unless you specify otherwise.

The vectorizer preference supports relational columns and JSON columns. For JSON columns, set type to JSON. You can copy the whole JSON value or use a transform property to control which parts of a source JSON column are stored into the covering column.

Use KEEP to store only the listed paths. The following example retains $.src and $.deptnum:

{"col":"meta",
 "type":"JSON",
 "transform":["KEEP","$.src","$.deptnum"]}

Use REMOVE to store the JSON value while excluding the listed paths. The following example excludes $.text and $.title:

{"col":"meta",
 "type":"JSON",
 "transform":["REMOVE","$.text","$.title"]}

The supported JSON covering-column types are VARCHAR2, CLOB, NUMBER, DATE, TIMESTAMP, BOOLEAN, and JSON. For relational columns, when type is omitted, the source column’s data type is used.

The following example creates a vectorizer preference for the documents table. It configures the JSON column DATA as a covering column. The hybrid vector index must also be created on DATA.

BEGIN
  DBMS_VECTOR_CHAIN.CREATE_PREFERENCE(
    'docs_vectorizer',
    DBMS_VECTOR_CHAIN.VECTORIZER,
    JSON('{"model":"my_doc_model",
          "covering_columns":[
            {"col":"DATA","type":"JSON"}
          ]}')
  );
END;
/

Create Hybrid Vector Index

Pass the preference name to the VECTORIZER parameter while creating the hybrid vector index. The following example passes docs_vectorizer preference created above :

CREATE HYBRID VECTOR INDEX docs_hybrid_idx
  ON documents(document_text)
  PARAMETERS('VECTORIZER docs_vectorizer');

Filter Vector Searches

The DBMS_HYBRID_VECTOR.SEARCH function uses the vector.filter_on to determine whether to apply filter_by on the source table columns OR on the vector index covering columns during hybrid vector search:

The following query uses the DBMS_HYBRID_VECTOR.SEARCH function to filter vector search results to two categories using the category covering column. The filter_by object is a top-level search parameter. Its op, col, type, and args fields follow the filter syntax documented for the search API; vector.filter_on selects the covering-column filter behavior described here.

SELECT DBMS_HYBRID_VECTOR.SEARCH(
         JSON('{"hybrid_index_name":"docs_hybrid_idx",
                "vector":{
                  "search_text":"quarterly revenue outlook",
                  "filter_on":true
                },
                "filter_by":{
                  "op":"=",
                  "path":"DATA.src",
                  "type":"string",
                  "args":["WSJ"]
                }}'))

The filter_by condition also applies to the text portion of a hybrid search. Text search evaluates the condition against the source table, even when vector.filter_on is TRUE for vector search.

Update JSON Covering-Column Values When Needed

You can change the JSON values stored in the covering_columns configuration by changing the the transform property. To change the paths stored in a JSON covering column, update the covering_columns configuration in the vectorizer preference and then update the index:

BEGIN
  CTX_DDL.SET_ATTRIBUTE(
    'docs_vectorizer',
    'COVERING_COLUMN',
    '{"covering_columns":[
       {"col":"document_data",
        "type":"JSON",
        "transform":["KEEP","$.metadata","$.language"]}
     ]}'
  );
END;
/

ALTER INDEX docs_hybrid_idx
  PARAMETERS('UPDATE COV_COLUMNS docs_vectorizer');

The index update reapplies the preference’s JSON transform to the source data, updates the hybrid vector table, and propagates the new values to the vector index.

When you change the transform property and update the index, the database reapplies the new transform to the source data. The following examples show how the stored JSON changes:

Change Previous covering-column setting New covering-column setting Result
Whole JSON to a subset {"col":"DATA","type":"JSON"} {"col":"DATA","type":"JSON","transform":["KEEP","$.path"]} JSON containing only $.path
Subset to whole JSON {"col":"DATA","type":"JSON","transform":["KEEP","$.path"]} {"col":"DATA","type":"JSON"} Whole JSON value
One path to two {"col":"DATA","type":"JSON","transform":["KEEP","$.path"]} {"col":"DATA","type":"JSON","transform":["KEEP","$.path","$.new"]} JSON containing $.path and $.new
Two paths to one {"col":"DATA","type":"JSON","transform":["KEEP","$.path","$.new"]} {"col":"DATA","type":"JSON","transform":["KEEP","$.path"]} JSON containing only $.path
One path to all but one {"col":"DATA","type":"JSON","transform":["KEEP","$.path"]} {"col":"DATA","type":"JSON","transform":["REMOVE","$.new"]} JSON without $.new

Usage Notes

See Also