Select AI and Vector Search on a Legacy Oracle Schema: What It Actually Takes

Who this is for: DBAs and developers sitting on an Oracle schema that’s been in production for a decade or more, who keep hearing that Select AI and vector search are easy to bolt on, and want to know what that actually looks like before they try it on something that matters.

I spent a Saturday morning trying to get Select AI to answer a simple question against our support ticket schema: “which customers filed more than three tickets about login failures in the last quarter.” The table names were fine (TICKETS, CUSTOMERS, TICKET_CATEGORY), but the columns were a different story. CUST_ID, CUSTOMER_ID, and CUSTID all exist in different tables because three different teams built pieces of this schema between 2013 and 2019. Select AI generated a query that joined on the wrong column, silently returned zero rows, and I burned twenty minutes before I noticed. The fix wasn’t a prompt tweak. It was going back and adding table and column comments that hadn’t existed in eleven years. That’s the part nobody mentions in the demo videos.

Why This Matters

Select AI (built on the DBMS_CLOUD_AI package) and AI Vector Search are two of the headline features of Oracle Database 23ai, carried forward into 26ai. Select AI lets you write a natural-language prompt and have Oracle generate, explain, or run the SQL for you, using an LLM like OpenAI, Cohere, or OCI Generative AI. Vector search gives you a native VECTOR data type plus similarity search operators and indexes, so you can do semantic search or retrieval-augmented generation directly against your existing tables. Both features are genuinely useful. The gap is that the marketing examples all run against clean, well-documented sample schemas like SH or HR. Almost nobody’s production Oracle database looks like that after ten years of feature requests, mergers, and departed developers. Getting real value out of either feature means doing schema cleanup work first, and being honest about what an LLM can and can’t infer from tables that were never designed to be read by a machine, or a human, without tribal knowledge.

Getting Select AI to work on a schema nobody documented

Select AI needs an AI profile before it can do anything. The profile ties a credential (your LLM provider’s API key) to a scoped list of database objects. Scoping matters more than the docs make it sound: if you point it at your whole schema, Select AI has to guess which of forty tables named some variation of ORDER, ORDERS, and ORD_HDR you actually mean. Scoping it to the five or six tables relevant to a given use case cuts down on wrong joins dramatically.

			
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OPENAI_CRED',
    username         => 'openai',
    password         => '<your_api_key>'
  );
END;
/
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'SUPPORT_AI',
    attributes   => JSON_OBJECT(
      'provider'        VALUE 'openai',
      'credential_name' VALUE 'OPENAI_CRED',
      'object_list'     VALUE JSON_ARRAY(
        JSON_OBJECT('owner' VALUE 'APP', 'name' VALUE 'TICKETS'),
        JSON_OBJECT('owner' VALUE 'APP', 'name' VALUE 'CUSTOMERS'),
        JSON_OBJECT('owner' VALUE 'APP', 'name' VALUE 'TICKET_CATEGORY')
      )
    )
  );
END;
/
EXEC DBMS_CLOUD_AI.SET_PROFILE('SUPPORT_AI');
SELECT AI showsql
  How many customers filed more than three tickets about login failures
  in the last quarter?

		

The showsql action is the one to lean on before you ever let it run anything. It shows you the generated SQL without executing it, so you can catch a bad join before it silently returns the wrong answer. Once I added comments to the columns that actually mattered (CUST_ID is the real foreign key, CUSTID on the legacy import table is dead weight), showsql started producing correct joins consistently. The feedback action (or a direct call to DBMS_CLOUD_AI.FEEDBACK) lets you correct a bad generation and have it inform future queries, which is worth using the first few weeks rather than assuming it’ll just get better on its own.

Adding vector search without rebuilding the table

The good news on vector search is that it doesn’t require a schema rewrite. You add a VECTOR column with ALTER TABLE, same as any other column, and populate it in place.

			
ALTER TABLE tickets ADD (ticket_embedding VECTOR(384, FLOAT32));
BEGIN
  DBMS_VECTOR.LOAD_ONNX_MODEL(
    'DM_DUMP',
    'all-MiniLM-L12-v2.onnx',
    'ticket_embed_model'
  );
END;
/
UPDATE tickets
SET ticket_embedding = DBMS_VECTOR.UTL_TO_EMBEDDING(
  description,
  JSON('{"provider":"database","model":"ticket_embed_model"}')
)
WHERE ticket_embedding IS NULL;
CREATE VECTOR INDEX idx_ticket_vec ON tickets(ticket_embedding)
  ORGANIZATION INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE
  WITH TARGET ACCURACY 95;

		

Loading an ONNX model into the database with LOAD_ONNX_MODEL and generating embeddings with UTL_TO_EMBEDDING against a locally hosted model means the ticket text never leaves the database. For a support schema with customer PII in the description field, that mattered more to our security review than the vector search feature itself. The tradeoff is embedding quality: a small ONNX model like all-MiniLM-L12-v2 is fine for ticket similarity but won’t match the quality of a hosted embedding API on more nuanced text. On our ~400,000-row TICKETS table, the batch UPDATE to backfill embeddings took a little under two hours on a modestly sized instance, and it’s a one-time cost since new rows can be embedded via a trigger or a nightly job going forward.

Where This Requires Care

A few things I’d tell anyone before they start:

If you use an external provider (OpenAI, Cohere, OCI GenAI) for Select AI, only schema metadata (table names, column names, comments, and optionally a handful of sample rows if you enable that) goes to the LLM, not your full row data by default. Still, confirm that with your security team before enabling sample rows, since that setting does send actual data out. For a regulated schema, keep it off and rely on good comments instead.

Select AI’s accuracy is directly proportional to how well your schema is documented. Stale or missing comments, ambiguous column names, and tables with the same name prefix across different owners are the most common cause of wrong SQL, not model quality. Budget real time for schema cleanup before you demo this to anyone.

In-memory neighbor graph vector indexes (HNSW) need to fit in the Vector Memory Pool, sized via the VECTOR_MEMORY_SIZE initialization parameter. On a large table this can catch you off guard the first time you build an index without sizing that pool first; the CREATE VECTOR INDEX statement will simply fail or fall back to a slower method.

Backfilling embeddings on a large, actively written table needs a plan for concurrent writes, not just a one-time UPDATE. We ran ours during a maintenance window; a busier table would need batching with commit intervals or a background job.

Quick Reference

Scope Select AI profiles to a small object_list per use case instead of a whole schema; it dramatically improves join accuracy.
Use the showsql action to review generated SQL before running anything against production data.
Add or fix table and column comments before evaluating Select AI’s accuracy. Most “bad” generations trace back to undocumented schema, not the LLM.
Use DBMS_VECTOR.LOAD_ONNX_MODEL for in-database embeddings when row data can’t leave the instance.
Size VECTOR_MEMORY_SIZE before creating an in-memory neighbor graph (HNSW) index on a large table.
Batch large embedding backfills with commit intervals rather than a single UPDATE on actively written tables.

My Take

Both features work as advertised on a well-documented schema, and vector search in particular is genuinely low-friction to add to an existing table. Select AI is the one I’d temper expectations on. It’s not magic that reads your intent through messy column names, it’s an LLM working from whatever metadata you give it, and the quality of that metadata is entirely on you. The upside is that fixing your schema documentation to make Select AI useful is work you probably should have done years ago anyway, so the ROI shows up even before the AI layer does anything. I expect this pattern (AI features that force overdue schema hygiene as a side effect) to keep showing up across every database vendor’s AI push, not just Oracle’s.