Optimizer Plans for Local HNSW Vector Indexes

Oracle AI Database uses partition pruning to narrow the search and search only the local HNSW graphs for qualifying partitions or sub partitions. Partition pruning narrows the set of local HNSW graphs that need to be searched, and the optimizer plan determines the most efficient execution plan for accessing narrowed graph set during similarity search. When using local HNSW vector indexes, you may see different optimizer plans for similarity searches.

Local HNSW indexes use the same general HNSW vector indexes optimizer plan concepts described in Optimizer Plans for HNSW Vector Indexes topic, such as pre-filtering, in-filtering, and join-back. However, a local HNSW index adds partition pruning to the optimizer decision.

No-filtering is used in the following two scenarios:
- The query does not contain any filtering predicates. In this case, the database searches all applicable partition-local HNSW graphs.
- The query contains only partition-pruning predicates and no other filters. In this case, the database uses the partition-pruning predicates to identify the qualifying partitions or sub-partitions, and searches only the corresponding local HNSW graphs.
In a no-filtering plan, the database does not need to access the base table before identifying the approximate nearest rows. This avoids unnecessary base table I/O resources for evaluating those predicates and limits the vector search to the qualifying partition-local HNSW graphs. The approximate nearest rows are computed within the local HNSW index access.

When the query does not contain a filtering predicate that can restrict the search to a subset of partitions. The database searches across the applicable local HNSW graphs and returns the approximate nearest rows. The following query example illustrates this situation:
```
SELECT product_id,
       vector_distance(vec, vector('[7,0,2,..., 2]')) AS distance
FROM   TT
ORDER BY distance
FETCH APPROX FIRST 8 ROWS ONLY;
```
Oracle AI Database cannot prune the local HNSW graphs because there is no WHERE predicate such as product_id = .... The optimizer plan therefore uses PARTITION LIST ALL, meaning all applicable partitions/local graphs are considered.
```
| Id | Operation                              | Name|
|----|----------------------------------------|-----|
|  0 | SELECT STATEMENT                       |     |
|* 1 |  COUNT STOPKEY                         |     |
|  2 |   VIEW                                 |     |
|* 3 |    SORT ORDER BY STOPKEY               |     |
|  4 |     PARTITION LIST ALL                 |     |
|  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID| TT  |
|  6 |       VECTOR INDEX HNSW SCAN            |VIDXH|
```

Parallel no-filtering is used when the database needs to access more than one partitions, parallel plan typically can speed up query response by using parallel execution to distribute the search work across partitions. In a RAC environment, parallel execution plans for local HNSW indexes do not extend to remote nodes.

Parallel no-filtering plan is used in the following scenarios.

The query does not contain any filtering predicates. In this case, the database searches all applicable partition-local HNSW graphs.
The query contains only partition-pruning predicates and no other filters. In this case, the database uses the partition-pruning predicates to identify the qualifying partitions or sub-partitions, and searches only the corresponding local HNSW graphs.

The database searches across all applicable local HNSW graphs. Since there is no partition-pruning predicate is available, the optimizer plan shows PARTITION LIST ALL. The following query example illustrates this situation:

SELECT product_id,
       vector_distance(vec, vector('[7,0,2,..., 2]')) AS distance
FROM   TT
ORDER BY distance
FETCH APPROX FIRST 8 ROWS ONLY;

Oracle AI Database cannot prune the local HNSW graphs because there is no WHERE predicate such asproduct_id = .... The optimizer plan therefore uses PARTITION LIST ALL. The scan and sort work are distributed through PX operations:

| Id | Operation                                     | Name    |
|----|-----------------------------------------------|---------|
|  0 | SELECT STATEMENT                              |         |
|* 1 |  COUNT STOPKEY                                |         |
|  2 |   PX COORDINATOR                              |         |
|  3 |    PX SEND QC (ORDER)                         | :TQ10002|
|  4 |     VIEW                                      |         |
|* 5 |      SORT ORDER BY STOPKEY                    |         |
|  6 |       PX RECEIVE                              |         |
|  7 |        PX SEND RANGE                          | :TQ10001|
|  8 |         PARTITION LIST ALL                    |         |
|* 9 |          SORT ORDER BY STOPKEY                |         |
| 10 |           TABLE ACCESS BY GLOBAL INDEX ROWID  | TT      |
| 11 |            BUFFER SORT                        |         |
| 12 |             PX RECEIVE                        |         |
| 13 |              PX SEND HASH (BLOCK ADDRESS)     | :TQ10000|
| 14 |               PX SELECTOR                     |         |
| 15 |                VECTOR INDEX HNSW SCAN         | VIDXH   |

In-filtering evaluates eligible filters as part of local HNSW index access. If the filter identifies one or more partitions, the database prunes the remaining partitions and searches only the corresponding local HNSW graphs. The following query example illustrates this situation:

SELECT product_id,
       vector_distance(vec, vector('[7,0,2,... 0,2]')) AS distance
FROM   TT
WHERE  product_id = 2
ORDER BY distance
FETCH APPROX FIRST 8 ROWS ONLY;

The predicate product_id = 2 identifies a single partition. The database prunes the other partitions and searches only the local HNSW graph for the matching partition. The optimizer plan therefore uses PARTITION LIST SINGLE.

| Id | Operation                              | Name          |
|----|----------------------------------------|---------------|
|  0 | SELECT STATEMENT                       |               |
|* 1 |  COUNT STOPKEY                         |               |
|  2 |   VIEW                                 |               |
|* 3 |    SORT ORDER BY STOPKEY               |               |
|  4 |     PARTITION LIST SINGLE              |               |
|  5 |      VECTOR INDEX HNSW SCAN IN-FILTER  | VIDXH         |
|  6 |       VIEW                             |VW_HIF_0F5A0514|
|* 7 |        TABLE ACCESS BY USER ROWID      | TT            |

In-filter plans are most effective when filters are highly selective or inexpensive to evaluate. For costlier filters, such as joins, the optimizer may choose a pre-filter or post-filter plan instead.

In-filtering with binds evaluates eligible bind filters as part of local HNSW index access. If the bind values identify one or more partitions, Oracle AI Database prunes the remaining partitions and searches only the corresponding local HNSW graphs. The following query example illustrates this situation:

SELECT product_id,
       vector_distance(vec, vector('[7,0,2,..., 2]')) AS distance
FROM   TT
WHERE  product_id IN (:prod_id, 3)
AND    customer_id > 100
ORDER BY distance
FETCH APPROX FIRST 8 ROWS ONLY;

The product_id IN (:prod_id, 3) filter predicate identifies a list of partitions. The optimizer plan shows PARTITION LIST INLIST, meaning the database searches only the local HNSW graphs for the qualifying partition list.

| Id | Operation                              | Name           |
|----|----------------------------------------|----------------|
|  0 | SELECT STATEMENT                       |                |
|* 1 |  COUNT STOPKEY                         |                |
|  2 |   VIEW                                 |                |
|* 3 |    SORT ORDER BY STOPKEY               |                |
|  4 |     PARTITION LIST INLIST              |                |
|  5 |      VECTOR INDEX HNSW SCAN IN-FILTER  | VIDXH          |
|  6 |       VIEW                             | VW_HIF_0F5A0514|
|* 7 |        TABLE ACCESS BY USER ROWID      | TT             |

In-filtering with join-back and a join evaluates eligible filters as part of local HNSW index access. The database prunes the remaining partitions and searches only the corresponding local HNSW graphs. After local HNSW index access identifies candidate rows, the database joins back to the base table or joined tables to evaluate additional predicates, return projected columns, or complete the join. The plan shows PARTITION LIST INLIST, and join operations such as HASH JOIN or NESTED LOOPS. The following query example illustrates this situation:

SELECT TT_ORDER.amount_sold,
       product_id,
       vector_distance(vec, vector('[7,0,2,2,..., 2]')) AS distance
FROM   TT, TT_ORDER
WHERE  product_id IN (:prod_id, 3)
AND    customer_id > 100
AND    TT.product_id = TT_ORDER.pid
ORDER BY distance
FETCH APPROX FIRST 8 ROWS ONLY;

The predicate product_id IN (:prod_id, 3) enables partition-list pruning. The database uses the local HNSW index to identify candidate rows, then joins back to the base table and joins with TT_ORDER. The corresponding optimizer plan should look like the following:

| Id | Operation                                        | Name           |
|----|--------------------------------------------------|----------------|
|  0 | SELECT STATEMENT                                 |                |
|* 1 |  COUNT STOPKEY                                   |                |
|  2 |   VIEW                                           |                |
|* 3 |    SORT ORDER BY STOPKEY                         |                |
|* 4 |     HASH JOIN                                    |                |
|  5 |      PARTITION LIST INLIST                       |                |
|  6 |       TABLE ACCESS FULL                          | TT_ORDER       |
|  7 |        TABLE ACCESS BY GLOBAL INDEX ROWID        | TT             |
|  8 |         PARTITION LIST INLIST                    |                |
|  9 |          VECTOR INDEX HNSW SCAN IN-FILTER        | VIDXH          |
| 10 |           VIEW                                   | VW_HI3_837F1853|
| 11 |            NESTED LOOPS                          |                |
| 12 |             TABLE ACCESS BY USER ROWID           | TT             |
| 13 |              PARTITION LIST ALL                  |                |
| 14 |               TABLE ACCESS FULL                  | TT_ORDER       |

A join-back plan can help reduce memory pressure when caching intermediate results, especially when the query selects many columns or includes large values.

In-filtering with join-back and binds evaluates eligible bind filters as part of local HNSW index access. If the bind values identify one or more partitions, the database prunes the remaining partitions and searches only the corresponding local HNSW graphs. After the local HNSW index identifies candidate rows, the database joins back to the base table to evaluate the bind filter, access row data, or return projected columns. The following query example illustrates this situation:

SELECT product_id,
       vector_distance(vec, vector('[7,0,2,2,..., 2]')) AS distance
FROM   TT
WHERE  product_id IN (:prod_id, :prod_id1)
ORDER BY distance
FETCH APPROX FIRST 8 ROWS ONLY;

The predicate product_id IN (:prod_id, :prod_id1) enables partition pruning. The database prunes the remaining partitions and searches only the corresponding local HNSW graphs. The optimizer plan shows PARTITION LIST INLIST:

| Id | Operation                                   | Name            |
|----|---------------------------------------------|-----------------|
|  0 | SELECT STATEMENT                            |                 |
|* 1 |  COUNT STOPKEY                              |                 |
|  2 |   VIEW                                      |                 |
|* 3 |    SORT ORDER BY STOPKEY                    |                 |
|  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID      | TT              |
|  5 |      PARTITION LIST INLIST                  |                 |
|  6 |       VECTOR INDEX HNSW SCAN POST-FILTER    | VIDXH           |
|  7 |        VIEW                                 | VW_HPST_0F5A0514|
|* 8 |         TABLE ACCESS BY USER ROWID          | TT              |

Parallel in-filtering with binds evaluates eligible bind filters as part of local HNSW index access. If the bind values identify one or more partitions, the database prunes the remaining partitions and uses parallel execution to search only the corresponding local HNSW graphs. The following query example illustrates this situation:

SELECT product_id,
       vector_distance(vec, vector('[7,0,2,..., 2]')) AS distance
FROM   TT
WHERE  product_id IN (:prod_id, 3)
AND    customer_id > 100
ORDER BY distance
FETCH APPROX FIRST 8 ROWS ONLY;

The predicate product_id IN (:prod_id, 3) enables partition pruning. The optimizer plan shows PX PARTITION LIST INLIST, which means the selected partition list is processed through parallel execution.

| Id | Operation                                     | Name           |
|----|-----------------------------------------------|----------------|
|  0 | SELECT STATEMENT                              |                |
|* 1 |  COUNT STOPKEY                                |                |
|  2 |   PX COORDINATOR                              |                |
|  3 |    PX SEND QC (ORDER)                         | :TQ10001       |
|  4 |     VIEW                                      |                |
|* 5 |      SORT ORDER BY STOPKEY                    |                |
|  6 |       PX RECEIVE                              |                |
|  7 |        PX SEND RANGE                          | :TQ10000       |
|  8 |         PX PARTITION LIST INLIST              |                |
|* 9 |          SORT ORDER BY STOPKEY                |                |
| 10 |           VECTOR INDEX HNSW SCAN IN-FILTER    | VIDXH          |
| 11 |            VIEW                               | VW_HIF_0F5A0514|
| 12 |             TABLE ACCESS BY USER ROWID        | TT             |

Post-filtering evaluates filters after local HNSW index access. The database first uses the local HNSW index to find approximate nearest-neighbor candidates, then evaluates remaining filters on those candidate rows. The following query example illustrates this situation:

SELECT product_id,
       vector_distance(vec, vector('[7,0,2,..., 2]')) AS distance
FROM   TT
WHERE  product_id IN (:prod_id, :prod_id1)
ORDER BY distance
FETCH APPROX FIRST 8 ROWS ONLY;

The predicate product_id IN (:prod_id, :prod_id1) enables partition pruning. The database uses the local HNSW index to get vector candidates, then applies the remaining filter afterward. The optimizer plan shows PARTITION LIST INLIST:

| Id | Operation                                 | Name           |
|----|-------------------------------------------|----------------|
|  0 | SELECT STATEMENT                          |                |
|* 1 |  COUNT STOPKEY                            |                |
|  2 |   VIEW                                    |                |
|* 3 |    SORT ORDER BY STOPKEY                  |                |
|  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID    | TT             |
|  5 |      PARTITION LIST INLIST                |                |
|  6 |       VECTOR INDEX HNSW SCAN POST-FILTER  | VIDXH          |
|  7 |        VIEW                               |VW_HPST_0F5A0514|
|* 8 |         TABLE ACCESS BY USER ROWID        | TT             |

Post-filter plans work well for expensive filters, such as joins or JSON filters, because the expensive filter is evaluated only for candidate rows returned by the local HNSW index scan. However, after applying the post-filter, the query may not be able to return the requested approximate nearest rows. This can happen if the filter removes many or all rows returned from the local HNSW index evaluation.

Pre-filtering evaluates eligible filters before local HNSW vector search. When the filters enable partition pruning, the database can first identify the qualifying partitions or sub partitions, and then search only the local HNSW graphs for those partitions. This can improve performance for selective filtered similarity searches.

The following query example illustrates this situation:

SELECT product_id,
       vector_distance(vec, vector('[7,0,2,..., 2]')) AS distance
FROM   TT
WHERE  product_id = 2
ORDER BY distance
FETCH APPROX FIRST 8 ROWS ONLY;

The predicate product_id = 2 enables partition pruning. The database uses the local HNSW index to get vector candidates. The optimizer plan shows PARTITION LIST SINGLE:

| Id | Operation                                 | Name  |
|----|-------------------------------------------|-------|
|  0 | SELECT STATEMENT                          |       |
|* 1 |  COUNT STOPKEY                            |       |
|  2 |   VIEW                                    |       |
|* 3 |    SORT ORDER BY STOPKEY                  |       |
|  4 |     PARTITION LIST SINGLE                 |       |
|  5 |      VECTOR INDEX HNSW SCAN PRE-FILTER    | VIDXH |

A pre-filter plan evaluates the filter before the local HNSW index scan. Like post-filter plans, pre-filter plans are generally useful for expensive filters. Unlike post-filter plans, pre-filter plans can still return the requested approximate nearest rows because filtering happens before vector index evaluation. The tradeoff is that the filter evaluation cost can be higher than with a post-filter plan. A post-filter plan evaluates the filter only for candidate rows returned by the local HNSW index scan, while a pre-filter plan may need to evaluate the filter earlier in the execution process.

Comparison of In-Filter, Pre-filter, and Post-Filter Plans:

Plan type	When the optimizer typically chooses it	How filtering is applied	TOP N behavior	Filter evaluation cost	Partition-wise processing
In-filter (with or with join back)	Typically chosen when filters are inexpensive or the filters are not highly selective (many rows pass the filters).	The filter is evaluated as part of the local HNSW partition graph scan.	Can return TOP N rows during local HNSW index evaluation.	Usually lower when the filter is inexpensive to evaluate.	The optimizer first determines which partitions to access, then evaluates the filter during each selected partition’s local HNSW graph scan.
Pre-filter (with or with join back)	Typically chosen for expensive filters where preserving TOP N results is important.	The filter is evaluated before the local HNSW partition graph scan.	Can return the requested TOP N rows because filtering happens before vector index evaluation.	Can be higher than a post-filter plan because filtering may occur before candidate rows are reduced by the HNSW scan.	The optimizer first determines which partitions to access, then evaluates the filter before scanning each selected partition’s local HNSW graph.
Post-filter	Typically chosen for expensive filters when evaluating the filter only on candidate rows is more efficient.	The filter is evaluated after the local HNSW partition graph scan.	May not return the requested TOP N rows if the filter removes too many candidate rows returned by the HNSW scan.	Usually lower than a pre-filter plan because the filter is evaluated only for candidate rows returned by the local HNSW index scan.	The optimizer first determines which partitions to access, then applies the filter after each selected partition’s local HNSW graph scan.