Oracle vector store ignores JSON Search Index when vector index exists

Oracle vector store ignores JSON Search Index when vector index exists

What was going on

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 fix that worked

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

Where this came from

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