Hybrid Search Architectures Combining Relational Predicates, Text, JSON, and Vectors

Learning outcomes

A ServiceHub user asks for “compressor restart guides in English for my tenant, active HVAC equipment only.” Pure keyword search misses synonyms; pure vector search can retrieve semantically similar but unauthorized or obsolete documents. A production hybrid retrieval path combines security/tenant predicates, relational status/category, JSON metadata, full-text relevance and vector similarity while keeping database row-level security authoritative.

1. Hybrid retrieval has two kinds of filters

Security filters decide which rows the caller is allowed to know exist. They must be enforced before data leaves the database. Relevance filters/scores decide which authorized rows best answer the query. Tenant/VPD policy belongs to the first category; vector distance, text score and business ranking belong to the second.

2. Create a mixed-format ServiceHub document table

BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh25_hybrid_docs PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE sh25_hybrid_docs (  doc_id NUMBER PRIMARY KEY,  tenant_code VARCHAR2(30) NOT NULL,  status_code VARCHAR2(12) NOT NULL,  category_code VARCHAR2(30) NOT NULL,  body_text CLOB NOT NULL,  attributes JSON NOT NULL,  embedding_model VARCHAR2(80) NOT NULL,  embedding_version VARCHAR2(40) NOT NULL,  embedding VECTOR(4,FLOAT32) NOT NULL);INSERT INTO sh25_hybrid_docs VALUES(  1,'SH25_TENANT_A','ACTIVE','HVAC',  'Restart compressor safely after thermal shutdown. Check pressure and temperature.',  JSON('{"language":"en","region":"north","safety":"approved"}'),  'SERVICEHUB_LAB_AXES','1.0',  VECTOR('[0.95,0.82,0.10,0.05]',4,FLOAT32));INSERT INTO sh25_hybrid_docs VALUES(  2,'SH25_TENANT_A','ACTIVE','HVAC',  'Power-cycle procedure for refrigeration compressor and temperature alarm.',  JSON('{"language":"en","region":"north","safety":"approved"}'),  'SERVICEHUB_LAB_AXES','1.0',  VECTOR('[0.89,0.78,0.12,0.08]',4,FLOAT32));INSERT INTO sh25_hybrid_docs VALUES(  3,'SH25_TENANT_A','RETIRED','HVAC',  'Old compressor reset procedure.',  JSON('{"language":"en","region":"north","safety":"retired"}'),  'SERVICEHUB_LAB_AXES','1.0',  VECTOR('[0.94,0.75,0.10,0.04]',4,FLOAT32));INSERT INTO sh25_hybrid_docs VALUES(  4,'SH25_TENANT_B','ACTIVE','HVAC',  'Compressor emergency restart for private Tenant B equipment.',  JSON('{"language":"en","region":"south","safety":"restricted"}'),  'SERVICEHUB_LAB_AXES','1.0',  VECTOR('[0.98,0.84,0.07,0.02]',4,FLOAT32));INSERT INTO sh25_hybrid_docs VALUES(  5,'SH25_TENANT_A','ACTIVE','ELECTRICAL',  'Replace cabinet breaker and verify wiring insulation.',  JSON('{"language":"en","region":"north","safety":"approved"}'),  'SERVICEHUB_LAB_AXES','1.0',  VECTOR('[0.10,0.08,0.88,0.35]',4,FLOAT32));COMMIT;

3. Add Oracle Text for keyword evidence

CREATE INDEX sh25_hybrid_text_ixON sh25_hybrid_docs(body_text)INDEXTYPE IS CTXSYS.CONTEXTPARAMETERS ('SYNC (ON COMMIT)');

Oracle Text CONTAINS provides linguistic/token/indexed text matching and SCORE(label). It answers a different relevance question than vector similarity; one should not be disguised as the other.

4. Create least-privilege tenant users

BEGIN EXECUTE IMMEDIATE 'DROP USER sh25_tenant_a CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP USER sh25_tenant_b CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/CREATE USER sh25_tenant_a NO AUTHENTICATION;CREATE USER sh25_tenant_b NO AUTHENTICATION;GRANT CREATE SESSION TO sh25_tenant_a,sh25_tenant_b;GRANT SELECT ON servicehub_owner.sh25_hybrid_docs  TO sh25_tenant_a,sh25_tenant_b;PASSWORD sh25_tenant_aPASSWORD sh25_tenant_b-- Set lab passwords interactively; never store them in this file.

5. VPD makes the tenant boundary database-enforced

CREATE OR REPLACE FUNCTION sh25_tenant_predicate(  p_schema VARCHAR2,  p_object VARCHAR2)RETURN VARCHAR2AUTHID DEFINERASBEGIN  IF SYS_CONTEXT('USERENV','SESSION_USER')       IN ('SH25_TENANT_A','SH25_TENANT_B') THEN    RETURN      'tenant_code = SYS_CONTEXT(''USERENV'',''SESSION_USER'')';  END IF;  RETURN '1=0';END;/

BEGIN  DBMS_RLS.ADD_POLICY(    object_schema   => 'SERVICEHUB_OWNER',    object_name     => 'SH25_HYBRID_DOCS',    policy_name     => 'SH25_TENANT_VPD',    function_schema => 'SERVICEHUB_OWNER',    policy_function => 'SH25_TENANT_PREDICATE',    statement_types => 'SELECT',    enable          => TRUE  );END;/

The policy returns fail-closed 1=0 for unexpected users. Production administrative/reporting bypass must be deliberate and audited; do not “fix” it by granting EXEMPT ACCESS POLICY to the application.

6. Deliberately wrong: retrieve globally, then filter tenant rows in application code

VARIABLE q VECTORBEGIN  :q := VECTOR('[0.96,0.81,0.09,0.04]',4,FLOAT32);END;/SELECT doc_id,tenant_code,body_text,       VECTOR_DISTANCE(embedding,:q,COSINE) AS distanceFROM sh25_hybrid_docsORDER BY distanceFETCH EXACT FIRST 3 ROWS ONLY;

As the table owner, Tenant B's highly similar private document can rank near the top. If the application fetches this global set and removes other tenants afterward, unauthorized row IDs/text/scores have already crossed the database security boundary and the post-filter may also leave fewer than the requested k.

7. Query as Tenant A: VPD + relational + JSON + text + vector

CONNECT sh25_tenant_a@//localhost:1521/FREEPDB1VARIABLE q VECTORBEGIN  :q := VECTOR('[0.96,0.81,0.09,0.04]',4,FLOAT32);END;/SELECT  doc_id,  category_code,  SCORE(1) AS text_score,  VECTOR_DISTANCE(embedding,:q,COSINE) AS vector_distance,  JSON_VALUE(attributes,'$.language') AS language_codeFROM servicehub_owner.sh25_hybrid_docsWHERE status_code='ACTIVE'  AND category_code='HVAC'  AND JSON_VALUE(attributes,'$.language')='en'  AND CONTAINS(body_text,'compressor AND restart',1) > 0ORDER BY VECTOR_DISTANCE(embedding,:q,COSINE)FETCH EXACT FIRST 5 ROWS ONLY;

Expected visible IDs are Tenant A documents only. No tenant predicate appears in the application SQL because VPD injects it. Semantic/text ranking works only over the authorized row set returned by Oracle policy enforcement.

8. Verify the policy instead of trusting application code

SELECT DISTINCT tenant_codeFROM servicehub_owner.sh25_hybrid_docs;-- Expected for SH25_TENANT_A:-- SH25_TENANT_A

SELECT /*+ gather_plan_statistics */       doc_idFROM servicehub_owner.sh25_hybrid_docsWHERE status_code='ACTIVE'  AND category_code='HVAC'ORDER BY VECTOR_DISTANCE(embedding,:q,COSINE)FETCH EXACT FIRST 5 ROWS ONLY;SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE'  ));

The plan proves what Oracle executed for that session. Depending on policy transformation/plan formatting, the security predicate may appear in predicate information or transformed SQL; the decisive correctness test is that unauthorized rows are unobservable under the tenant identity.

9. Pre-, in- and post-filter implications with ANN

With vector indexes, Oracle can evaluate compatible structured predicates before the vector search (pre-filter), inside certain vector-index paths (in-filter), or after ANN candidates are found (post-filter). A highly selective security/business filter benefits from early enforcement; post-filtering can reduce the final row count if many nearest ANN candidates fail the filter. Security must never depend on “hopefully pre-filter”; VPD remains the authorization policy regardless of optimizer strategy.

10. Score fusion requires a defined ranking contract

Text SCORE and vector distance have different scales/directions. Adding raw values is meaningless. Options include rank-based fusion such as Reciprocal Rank Fusion (RRF), normalized score fusion, or a learned reranker. Record weights/model/version and evaluate relevance with labeled queries rather than guessing constants.

11. Oracle Hybrid Vector Index is a higher-level alternative

26ai can create a CREATE HYBRID VECTOR INDEX over CLOB/VARCHAR2/BLOB text and search it with DBMS_HYBRID_VECTOR.SEARCH, combining Oracle Text and vector results. Current restrictions matter: hybrid vector indexes use in-database ONNX embedding models for the index's embedding generation, and VPD policies must also cover direct queries against the secondary tables created by the hybrid index. The mandatory lab stays manual so it requires no model import.

CREATE HYBRID VECTOR INDEX sh25_hviON sh25_document_source(body_text)FILTER BY tenant_code,status_codePARAMETERS('MODEL servicehub_embed_model');

SELECT JSON_SERIALIZE(  DBMS_HYBRID_VECTOR.SEARCH(    JSON('{      "hybrid_index_name":"SH25_HVI",      "search_text":"compressor restart",      "search_scorer":"rsf",      "return":{"topN":5}    }')  )  PRETTY)FROM dual;

12. Cleanup

BEGIN  DBMS_RLS.DROP_POLICY(    object_schema => 'SERVICEHUB_OWNER',    object_name   => 'SH25_HYBRID_DOCS',    policy_name   => 'SH25_TENANT_VPD'  );END;/DROP USER sh25_tenant_a CASCADE;DROP USER sh25_tenant_b CASCADE;DROP INDEX sh25_hybrid_text_ix;DROP TABLE sh25_hybrid_docs PURGE;

13. Production judgment

Enforce tenant/authorization inside Oracle first, then optimize relevance. Use relational/JSON filters for exact business constraints, Oracle Text for lexical intent, VECTOR for semantic similarity, and a documented fusion/reranking method when both relevance signals matter. Evaluate plans because ANN filtering strategy can change returned k and latency.

The manual hybrid lab is Free-compatible and needs no management pack, external model, restart or COMPATIBLE change beyond the vector prerequisite >=23.4.0. Oracle Hybrid Vector Index can reduce custom plumbing but has model/VPD/secondary-object operational rules. Lesson 4 moves one level up: an LLM can generate the SQL itself, so the generated statement must be governed like untrusted application code.

Authoritative references

• Query Data With Similarity and Hybrid Searches — filtered vector search
• Optimizer Plans for HNSW Filtering — pre/in/post-filter plans
• Understand Hybrid Search — text/vector fusion
• Hybrid Vector Index Restrictions — VPD/model/index restrictions
• DBMS_HYBRID_VECTOR.SEARCH — hybrid query API/scorers