Use JSON Value Expressions as Included Columns with IVF Indexes

Using JSON_VALUE expressions as included columns in IVF vector indexes provides a mechanism to accelerate query performance and reduce index storage for vector search workloads involving projections of and filters on JSON expressions on the base table.

Syntax and Specifications

Create a base table, which contains house sales data with prices, descriptions, vectors, and a JSON column:

CREATE TABLE houses (
    id            NUMBER,
    zip_code      NUMBER,
    price         NUMBER,
    description   CLOB,
    house_details JSON,
    data_vector   VECTOR
);

The house_details JSON documents are structured as follows:

{
    "rooms": {
        "num_bedrooms": 2,
        "num_bathrooms": 2
    },
    "street_name": "Walnut Street",
    "square_feet" : 15213
}

Then you can create an IVF vector index with included JSON_VALUE expressions as follows:

create vector index vidx_ivf
on houses h (data_vector)
include (price, h.house_details.street_name.string(), h.house_details.rooms.num_bedrooms.number())
organization neighbor partitions
with target accuracy 95
distance EUCLIDEAN
parameters(type IVF, neighbor partitions 2) ONLINE;

The above example uses JSON path expressions with the “simple dot-notation” feature to access nested properties inside each JSON, and mixes traditional included columns (price) with included expressions. These can be provided in any order.

Note: The return type of each included expression should be one of the types supported by the included columns feature. The total number of included columns and expressions combined cannot exceed 31.

Usage Notes

• The ONLINE clause is required when an IVF vector index includes an expression.

  Included expressions are then materialized as columns into the IVF_FLAT_CENTROID_PARTITIONS table of the index. For example, in the above example, the row would contain the string value "Walnut Street" for the first included expression, and the scalar value 2 for the second included expression.

• DML operations on the base table automatically evaluate and maintain these included expressions. Updating a row in the base table that affects an included expression also updates the corresponding row in the index’s centroid partitions table.

• The regular JSON_VALUE syntax is also supported, should it be required:

  create vector index vidx_ivf
  on houses h (data_vector)
  include (price, JSON_VALUE(house_details, '$.street_name' RETURNING VARCHAR2(200)), JSON_VALUE(house_details, '$.rooms.num_bedrooms' RETURNING INTEGER))
  organization neighbor partitions
  with target accuracy 95
  distance EUCLIDEAN
  parameters(type IVF, neighbor partitions 2) ONLINE;

Benefits of Using JSON_VALUE Expressions as Included Columns

• Reduced Index Storage

  The centroid partitions table stores only the evaluated expression results, not each row’s full JSON document. Therefore, the index can use substantially less storage than if it included the entire JSON column. This can greatly reduce the amount of storage occupied by the IVF index compared to including the entire JSON column.

  For example, when the entire HOUSE_DETAILS JSON column is included in the IVF vector index, the centroid partitions table stores the JSON document for each row, as shown here:

  Name               Type
  ------------------ ----------------------------
  BASE_TABLE_ROWID   ROWID
  CENTROID_ID        NUMBER
  DATA_VECTOR        VECTOR(3, FLOAT32, DENSE)
  PRICE              NUMBER
  HOUSE_DETAILS      JSON

  Alternatively, when a JSON_VALUE expression that extracts the number of bedrooms is included, the centroid partitions table stores the JSON_VALUE expression’s numeric result in a system-generated column instead of storing the full JSON document:

  Name               Type
  ------------------ ----------------------------
  BASE_TABLE_ROWID   ROWID
  CENTROID_ID        NUMBER
  DATA_VECTOR        VECTOR(3, FLOAT32, DENSE)
  PRICE              NUMBER
  SYS_NC00008$       NUMBER

• Accelerated Query Performance

  Extend the previous house sales data example to return only homes with more than two bedrooms. The query below returns the four nearest homes that cost 1,400,000 and have more than two bedrooms

  SELECT /*+ VECTOR_INDEX_TRANSFORM(houses, vidx_ivf) */
  price
  FROM houses h
  WHERE price = 1400000 AND h.house_details.rooms.num_bedrooms.number() > 2
  ORDER BY vector_distance(data_vector, :query_vector)
  FETCH APPROX FIRST 4 ROWS ONLY;

  Consider an IVF vector index that includes price and the full house_details JSON column, but not the bedroom-count expression. The plan below avoids a join to the base table, but still evaluates the bedroom filter by reading the JSON document in the selected centroid partitions:

  Plan hash value: 2616695736
  
  ----------------------------------------------------------------------------------------------------------------
  | Id  | Operation                     | Name                                                                   |
  ----------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT              |                                                                        |
  |*  1 |  COUNT STOPKEY                |                                                                        |
  |   2 |   VIEW                        |                                                                        |
  |*  3 |    SORT ORDER BY STOPKEY      |                                                                        |
  |   4 |     VIEW                      | VW_IVENJ_47D4581B                                                      |
  |   5 |      NESTED LOOPS             |                                                                        |
  |   6 |       VIEW                    | VW_IVCR_B5B87E67                                                       |
  |*  7 |        COUNT STOPKEY          |                                                                        |
  |   8 |         VIEW                  | VW_IVCN_9A1D2119                                                       |
  |*  9 |          SORT ORDER BY STOPKEY|                                                                        |
  |  10 |           TABLE ACCESS FULL   | VECTOR$VIDX_DOC_FULL_260930$79903_79910_0$IVF_FLAT_CENTROIDS           |
  |  11 |       PARTITION LIST ITERATOR |                                                                        |
  |* 12 |        TABLE ACCESS FULL      | VECTOR$VIDX_DOC_FULL_260930$79903_79910_0$IVF_FLAT_CENTROID_PARTITIONS |
  ----------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - filter(ROWNUM<=4)
     3 - filter(ROWNUM<=4)
     7 - filter(ROWNUM<=1)
     9 - filter(ROWNUM<=1)
    12 - filter(JSON_VALUE("VTIX_CNPART"."HOUSE_DETAILS" /*+ LOB_BY_VALUE */ FORMAT OSON ,
                '$.rooms.num_bedrooms.number()' RETURNING NUMBER NULL ON ERROR TYPE(LAX) )>2 AND
                "VW_IVCR_B5B87E67"."CENTROID_ID"="VTIX_CNPART"."CENTROID_ID" AND
                "VTIX_CNPART"."PRICE"=1400000)

  Although the join with the base table is avoided, the database still scans each JSON document in the selected centroid partitions and traverses the specified path to evaluate the filter.

  When the IVF vector index includes the JSON_VALUE expression, the execution plan shows that:

  • The query avoids a join to the base table. The included price column serves the price filter, while the included expression serves the bedroom filter.
  • The bedroom filter is evaluated against the stored expression result as a column predicate, so the database does not need to reevaluate JSON_VALUE.

  The plan below shows the filter using its stored result, SYS_NC00008$, alongside PRICE, instead of reevaluating the JSON expression.

  Plan hash value: 2616695736
  
  ----------------------------------------------------------------------------------------------------------------
  | Id  | Operation                     | Name                                                                   |
  ----------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT              |                                                                        |
  |*  1 |  COUNT STOPKEY                |                                                                        |
  |   2 |   VIEW                        |                                                                        |
  |*  3 |    SORT ORDER BY STOPKEY      |                                                                        |
  |   4 |     VIEW                      | VW_IVENJ_47D4581B                                                      |
  |   5 |      NESTED LOOPS             |                                                                        |
  |   6 |       VIEW                    | VW_IVCR_B5B87E67                                                       |
  |*  7 |        COUNT STOPKEY          |                                                                        |
  |   8 |         VIEW                  | VW_IVCN_9A1D2119                                                       |
  |*  9 |          SORT ORDER BY STOPKEY|                                                                        |
  |  10 |           TABLE ACCESS FULL   | VECTOR$VIDX_DOC_EXPR_260930$79933_79940_0$IVF_FLAT_CENTROIDS           |
  |  11 |       PARTITION LIST ITERATOR |                                                                        |
  |* 12 |        TABLE ACCESS FULL      | VECTOR$VIDX_DOC_EXPR_260930$79933_79940_0$IVF_FLAT_CENTROID_PARTITIONS |
  ----------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - filter(ROWNUM<=4)
     3 - filter(ROWNUM<=4)
     7 - filter(ROWNUM<=1)
     9 - filter(ROWNUM<=1)
    12 - filter("VW_IVCR_B5B87E67"."CENTROID_ID"="VTIX_CNPART"."CENTROID_ID" AND
                "VTIX_CNPART"."SYS_NC00008$">2 AND "VTIX_CNPART"."PRICE"=1400000)

Note: To achieve such a plan, the expression used in the query must be an exact match for the expression materialized in the index. For example, if the IVF index includes JSON_VALUE(house_details, '$.street_name' RETURNING VARCHAR2(20)), but the query requests JSON_VALUE(house_details, '$.street_name' RETURNING VARCHAR2(200)), the transformation will not take place, as the expressions have different semantics. Similarly, mixing simplified dot notation with explicit JSON_VALUE notation is not recommended since their semantics can differ in unexpected ways, preventing the optimized plan from being selected.