25-Oracle 23ai DBMS_SEARCH — Ubiquitous Search(无处不在的搜索)

一. DBMS_SEARCH Package的核心作用​

DBMS_SEARCH 是Oracle 23ai新增的PL/SQL包，用于实现跨多表/视图的统一全文搜索​（Ubiquitous Search）。DBMS_SEARCH 执行 Oracle Text 普适搜索，可以轻松在多个表和视图上创建单一索引。创建 DBMS_SEARCH INDEX并添加表和视图后，所有可搜索值（包括 VARCHAR、CLOB、JSON 和数字列）都将包含在索引中，并在表或视图内容发生更改时自动进行维护。自动将多个表或视图合成为单个索引，而不是创建各种单独的索引或物化视图。

今天测试医疗场景中同时搜索病历描述、患者主诉、检验、检查报告的案例。

在这个之前，默认基本表的索引和混合索引，按照int，number等属性默认各种优化都用过了。

典型场景​：

• - 企业知识库中跨文档、邮件、日志表的联合搜索。
• - 电商平台同时搜索商品描述、用户评论、客服记录。
• - 医疗客户同时搜索病历描述、患者主诉、检验检查报告

​​跨模态搜索融合​

支持向量搜索与传统文本搜索混合执行（结合AI Vector Search）

优势​：在同一个SQL中实现关键词过滤与语义相似度匹配，适应复杂搜索场景。

​事务一致性保障​

所有索引更新与基表DML操作在同一事务中完成，

确保：搜索结果的实时可见性（提交后立即可查）

二. 工作原理​

DBMS_SEARCH通过以下流程实现统一搜索：

1.​索引创建​：

• - 调用DBMS_SEARCH.CREATE_INDEX定义索引范围（指定表/视图及文本列）。
• - 自动调用Oracle Text引擎生成倒排索引，存储词项与源数据的映射关系

2.​查询处理​：

• - 用户输入自然语言查询（如“诊疗意见”）。
• - 包将查询解析为SQL语句，在统一索引中检索相关文档。

3.​结果返回​：

• - 返回包含匹配结果的表名、行ID及相关性评分，支持分页和排序

三. 与历史版本的对比

Oracle 23ai前​,依赖CTXCAT或CTXRULE索引，需手动为每个表创建独立全文索引.跨表搜索需复杂SQL联合查询，维护成本高，性能瓶颈明显。索引碎片化：需定期CTX_DDL.SYNC_INDEX维护，性能瓶颈：百万级数据联合查询超5秒，开发复杂：需管理多个索引一致性任务

​Oracle 23ai,通过DBMS_SEARCH实现单索引覆盖多表，简化架构并提升查询效率,避免跨索引合并结果的性能损耗。仅限23ai及以上版本可用。

--23ai以前版本需要做UNION

-- 需为每个表创建独立索引
CREATE INDEX disease_name_idx ON endocrine_diseases(disease_name) INDEXTYPE IS CTXSYS.CONTEXT;
CREATE INDEX symptom_desc_idx ON symptoms(symptom_desc) INDEXTYPE IS CTXSYS.CONTEXT;

-- 跨表查询需手动UNION
SELECT 'Disease' AS type, disease_id FROM endocrine_diseases 
WHERE CONTAINS(disease_name, '糖尿病')>0
UNION ALL
SELECT 'Symptom' AS type, symptom_id FROM symptoms 
WHERE CONTAINS(symptom_desc, '并发症')>0;

四、电子病历场景实战验证 

0.建库建表 

CREATE TABLESPACE tbs1
DATAFILE 'tbs5.dbf' SIZE 2G AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
--
DROP USER docuser cascade;
CREATE USER docuser identified by Oracle_4U default tablespace tbs1;
--
GRANT DB_DEVELOPER_ROLE TO docuser;
GRANT DBA TO docuser;
--
[oracle@OL96 ~]$ sql docuser/Oracle_4U@localhost:1521/FREEPDB1;

 1. 环境搭建（内分泌案例）

-- 1. 创建医疗数据模型
CREATE TABLE endocrine_diseases (
    disease_id NUMBER PRIMARY KEY,
    icd_code VARCHAR2(10),
    name VARCHAR2(100),
    description CLOB,
    risk_factors VARCHAR2(200)
);

CREATE TABLE patients (
    patient_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    gender CHAR(1),
    age NUMBER,
    bmi NUMBER(5,2)
);

CREATE TABLE medical_records (
    record_id NUMBER PRIMARY KEY,
    patient_id NUMBER REFERENCES patients(patient_id),
    disease_id NUMBER REFERENCES endocrine_diseases(disease_id),
    visit_date DATE,
    symptoms CLOB,
    diagnosis CLOB,
    treatment CLOB,
    lab_results CLOB
);

-- 2. 生成10万+内分泌疾病测试数据
INSERT INTO medical_records 
SELECT
    ROWNUM AS record_id,
    p.patient_id,
    d.disease_id,
    TRUNC(SYSDATE) - DBMS_RANDOM.VALUE(1, 1825) AS visit_date,
    TO_CLOB('症状描述：' || DBMS_RANDOM.STRING('X', 150)) AS symptoms,
    TO_CLOB('诊断结果：' || d.name || '，' || DBMS_RANDOM.STRING('X', 100)) AS diagnosis,
    TO_CLOB('治疗方案：' || 
        CASE MOD(ROWNUM, 5)
            WHEN 0 THEN '药物治疗'
            WHEN 1 THEN '手术干预'
            ELSE '生活方式调整'
        END || '，' || DBMS_RANDOM.STRING('X', 120)) AS treatment,
    TO_CLOB('检验结果：' || 
        ROUND(DBMS_RANDOM.VALUE(3, 15), 1) || 'mmol/L，' ||  
        ROUND(DBMS_RANDOM.VALUE(60, 180), 0) || 'mg/dL') AS lab_results  
FROM 
    (SELECT patient_id FROM patients ORDER BY DBMS_RANDOM.VALUE) p
CROSS JOIN 
    (SELECT disease_id, name FROM endocrine_diseases ORDER BY DBMS_RANDOM.VALUE) d
CROSS JOIN 
    (SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10)
WHERE 
    ROWNUM <= 100000;
COMMIT;
--100,000 rows inserted
-- 3. 创建DBMS_SEARCH索引
EXEC DBMS_SEARCH.CREATE_INDEX('MED_SEARCH_IDX');
EXEC DBMS_SEARCH.ADD_SOURCE('MED_SEARCH_IDX', 'ENDOCRINE_DISEASES');
EXEC DBMS_SEARCH.ADD_SOURCE('MED_SEARCH_IDX', 'MEDICAL_RECORDS');
--查询 USER_DBMS_SEARCH_INDEXES 字典视图
SELECT IDX_NAME FROM USER_DBMS_SEARCH_INDEXES;

IDX_NAME
__________________
MY_SEARCH_INDEX
MED_SEARCH_IDX
--查询 USER_DBMS_SEARCH_INDEX_SOURCES 字典视图。
SELECT IDX_NAME,SRC_OWNER,SRC_NAME,SRC_TYPE
FROM USER_DBMS_SEARCH_INDEX_SOURCES
ORDER BY IDX_NAME, SRC_OWNER,SRC_NAME;

IDX_NAME           SRC_OWNER    SRC_NAME              SRC_TYPE
__________________ ____________ _____________________ ___________
MED_SEARCH_IDX     DOCUSER      ENDOCRINE_DISEASES    T
MED_SEARCH_IDX     DOCUSER      MEDICAL_RECORDS       T
MY_SEARCH_INDEX    DOCUSER      CUSTOMERS             T

-- 4. 性能测试对比 (索引前 vs 索引后)
-- 测试前清理缓存
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
--查询索引内容（使用JSON_SERIALIZE）
SELECT JSON_SERIALIZE(DBMS_SEARCH.GET_DOCUMENT('MED_SEARCH_IDX', metadata)) AS document
FROM MED_SEARCH_IDX;


-- 5. 使用JSON_TEXTCONTAINS精确搜索
SELECT JSON_SERIALIZE(metadata) 
FROM MED_SEARCH_IDX
WHERE JSON_TEXTCONTAINS(
  data, 
  '$.medical_records.DIAGNOSIS',
  '肾上腺疾病'
);

-- 测例1: 关键词搜索
SET TIMING ON
-- 无索引传统查询 (LIKE搜索)
SELECT COUNT(*) 
FROM medical_records 
WHERE symptoms LIKE '%多饮多尿%' OR diagnosis LIKE '%糖尿病%';
--Elapsed: 00:00:02.536
-- DBMS_SEARCH查询
SELECT COUNT(*) 
FROM med_search_idx 
WHERE CONTAINS(DATA, '多饮多尿 AND 糖尿病') > 0;
SET TIMING OFF
Elapsed: 00:00:00.425

-- 测例2. 索引维护监控
-- 查看索引大小
SELECT segment_name, bytes/1024/1024 MB 
FROM user_segments 
WHERE segment_name = 'MED_SEARCH_IDX';

五、性能优化建议

1.混合索引策略​：结合关系型索引

CREATE INDEX idx_patient_age ON patients(age);
CREATE INDEX idx_disease_name ON endocrine_diseases(name);

2.缓存优化​：配置结果集缓存 

ALTER SYSTEM SET result_cache_mode = FORCE;

System altered.

3.查询优化技术​：

SELECT /*+ RESULT_CACHE */ COUNT(*) 
FROM med_search_idx 
WHERE CONTAINS(DATA, '糖尿病');