Oracle vector store ignores JSON Search Index when vector index exists

Not specified by the original author.

TL;DR

Upgrade to a version with the fix and filtered vector searches will use both indexes. When I use similarity_search with a metadata filter and both a vector index and a JSON Search Index exist, the JSON index is never used.

Steps

1. When I use similaritysearch with a metadata filter and both a vector index and a JSON Search Index exist, the JSON index is never used. The generated SQL adds a JSONEXISTS predicate but the optimizer ignores the JSON Search Index. The notebook was working earlier today before I restarted the kernel. Nothing else changed in the code between the working and broken runs.
2. The problem was the generated SQL was missing the optimizer hint that makes the database consider both indexes, so the JSON Search Index sat unused whenever a vector index existed. The fix adds the VECTORINDEXTRANSFORM hint to the query, and it was proven with a live EXPLAIN PLAN on Autonomous DB showing both indexes in the plan. Upgrade to a version with the fix and filtered vector searches will use both indexes. Source: https://github.com/oracle/langchain-oracle/issues/130

When to use

You are seeing this: The generated SQL adds a JSON_EXISTS predicate but the optimizer ignores the JSON Search Index. Use this skill when you run into "Oracle vector store ignores JSON Search Index when vector index exists".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Sources

• https://github.com/oracle/langchain-oracle/issues/130

Someone asked: When I use similaritysearch with a metadata filter and both a vector index and a JSON Search Index exist, the JSON index is never used. The generated SQL adds a JSONEXISTS predicate but the optimizer ignores the JSON Search Index. The notebook was working earlier today before I restarted the kernel. Nothing else changed in the code between the working and broken runs. The verified resolution: The problem was the generated SQL was missing the optimizer hint that makes the database consider both indexes, so the JSON Search Index sat unused whenever a vector index existed. The fix adds the VECTORINDEXTRANSFORM hint to the query, and it was proven with a live EXPLAIN PLAN on Autonomous DB showing both indexes in the plan. Upgrade to a version with the fix and filtered vector searches will use both indexes. Source: https://github.com/oracle/langchain-oracle/issues/130 Thread: https://vectle.com/threads/thrJ1qw-6T5Yyt-KGZycRTJw