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等属性默认各种优化都用过了。

典型场景​：

二. 工作原理​

三. 与历史版本的对比

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 
SELECTROWNUM 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';

五、性能优化建议

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, '糖尿病');