What is the best approach when it comes to execute a semantic search for RAG (Retrieval Augmented Generation) in OllAPEX ?
OllAPEX is an Oracle APEX workbench designed for local LLM integration via Ollama, with semantic search over documents (such as Oracle skills repositories or excerpts from Oracle documentation) amongst other functions.
The main purpose of semantic search report applied to the Oracle Skills or other Oracle documents is to test a search before implementing a RAG mechanism inside OllAPEX itself.
The Real-World Context: Semantic Search in OllAPEX
In OllAPEX, semantic search allows users to query vector-embedded markdown files directly from an APEX interface.
[ User Input Topic / Query ]
│
▼
ollapex.semantic_search(:TOPIC, ...)
│
▼
JSON_TABLE (Extract Top N Chunks)
│
▼
Join with OLLAPI_DOCUMENT_CHUNKS & UPLOADS
│
▼
[ Render Results in a report ] The underlying SQL query calls a PL/SQL vector search function ollapex.semantic_search(), parses the returning JSON payload via JSON_TABLE, and joins the parsed rows with document chunk metadata tables (ollapi_document_chunks and ollapi_uploads_for_embedding). Here is the user-facing application displaying semantic search results for queries over chunks of Oracle skills in markdown format:
 |
| picture #1, semantic search report |
The natural execution:
The initial version of the query behind the report looked like this:
 |
| picture 2, semantic search query |
And its execution plan:
 |
| picture 3, execution plan for the initial semantic search query |
Then I thought: how can I ensure that the CTE is executed only once as I don't want that the prompt embedding function is called more times than necessary ?
The wannabe "optimized" query: forcing MATERIALIZE
To ensure the vector search function in the CTE executes only once, I added a /*+ MATERIALIZE */ hint:
 |
| picture 4, query with MATERIALIZE hint added |
And this is the execution plan that I got:
 |
| picture 5, execution plan for the semantic search query with MATERIALIZE |
As you see the cost is slightly increased and now there is a FULL scan on the temporary table created by the MATERIALIZE hint. No big deal but it doesn't look like an improvement to me.
But wait, is this the best way to tackle this problem?
The good old mantra: keep it easy!
In the end if I want to be sure that the embedding function is executed only once and there are no dependencies from what follows, then I am better off separating the embedding phase from the search phase:
 |
| dynamic action doing the embedding before running the report |
So when the user clicks on the search button, a dynamic action performs the embedding on user's prompt and returns the results as a JSON array (CLOB).
The final query eventually uses the vector to perform the semantic search:
 |
| final semantic search query |
Since there are no vector indexes defined (yet), the execution plan remains identical to that of picture 3, thus performing a so-called "exact" similarity search but now I am 100% sure that the embedding request will be performed just once.
Have a nice #JoelKallmanDay, remembering Joel, Scott and Carl.
