Oracle遗留系统跨列文本搜索方案优化咨询

一、替换聚合列+LIKE查询，改用Oracle全文索引（Text Index） #

现有LIKE %前缀通配符会触发全表扫描，性能极差，Oracle全文索引是专门针对文本搜索的原生解决方案，能大幅提升查询效率。

• 方案1：基于物化视图的全文索引
  先将两表数据整合为物化视图，再创建全文索引，配置简单易维护：

  -- 创建整合两表所有搜索列的物化视图
  CREATE MATERIALIZED VIEW mv_search_data
  AS
  SELECT t1.id, 
         t1.col1 || ' ' || t1.col2 || ' ' || ... || t2.col1 || ' ' || t2.col2 || ' ' || ... AS search_text
  FROM table_1 t1
  JOIN table_2 t2 ON t1.id = t2.id;
  
  -- 创建全文索引
  CREATE INDEX idx_mv_search_text ON mv_search_data(search_text) INDEXTYPE IS CTXSYS.CONTEXT;
  

  查询时使用CONTAINS函数替代LIKE，性能提升显著：

  SELECT id FROM mv_search_data WHERE CONTAINS(search_text, 'keyword') > 0;
  

• 方案2：跨表多列全文索引（无需物化视图）
  直接在table_1上创建跨表的全文索引，适合不想维护物化视图的场景：

  CREATE INDEX idx_table1_table2_search ON table_1(id) INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('DATASTORE CTXSYS.MULTI_COLUMN_DATASTORE 
              FILTER CTXSYS.NULL_FILTER 
              LEXER CTXSYS.BASIC_LEXER 
              WORDLIST CTXSYS.BASIC_WORDLIST
              MULTI_COLUMN_DATASTORE(COLUMNS = col1, col2, ..., col40, (SELECT col1 FROM table_2 WHERE table_2.id = table_1.id), ...)');
  

• 全文索引自动维护
  无需手动调度器，创建索引时指定自动同步和优化规则：

  CREATE INDEX idx_mv_search_text ON mv_search_data(search_text) INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('SYNC EVERY "SYSDATE+1/1440" OPTIMIZE EVERY "SYSDATE+1/24"');
  

  以上配置为每分钟同步索引、每小时优化索引，可根据实际变更频率调整。

二、优化异步同步机制（替换table_3+轮询调度） #

现有table_3轮询方案存在空跑开销和延迟问题，可替换为消息驱动或批量处理模式：

• 替换为Oracle Advanced Queueing（AQ）消息队列
  用消息触发处理逻辑，避免轮询空耗，延迟更低：

  -- 创建队列表与队列
  BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'search_update_qt', queue_payload_type => 'SYS.XMLTYPE');
    DBMS_AQADM.CREATE_QUEUE(queue_name => 'search_update_q', queue_table => 'search_update_qt');
    DBMS_AQADM.START_QUEUE(queue_name => 'search_update_q');
  END;
  /
  
  -- 修改table_1/table_2的触发器，发送变更消息到队列
  CREATE OR REPLACE TRIGGER trg_table1_update
  AFTER INSERT OR UPDATE OR DELETE ON table_1
  FOR EACH ROW
  DECLARE
    v_payload SYS.XMLTYPE;
  BEGIN
    v_payload := SYS.XMLTYPE.CREATEXML('<update><id>' || :NEW.id || '</id></update>');
    DBMS_AQ.ENQUEUE(queue_name => 'search_update_q', enqueue_options => NULL, message_properties => NULL, payload => v_payload);
  END;
  /
  
  -- 创建消息处理存储过程
  CREATE OR REPLACE PROCEDURE process_search_updates
  IS
    v_dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
    v_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
    v_message_handle RAW(16);
    v_payload SYS.XMLTYPE;
    v_id NUMBER;
  BEGIN
    v_dequeue_options.wait := DBMS_AQ.NO_WAIT;
    LOOP
      BEGIN
        DBMS_AQ.DEQUEUE(queue_name => 'search_update_q', dequeue_options => v_dequeue_options, message_properties => v_message_properties, payload => v_payload, msgid => v_message_handle);
        v_id := v_payload.EXTRACT('/update/id/text()').GETNUMBERVAL();
        -- 批量更新table_4或刷新物化视图
        MERGE INTO table_4 t4
        USING (SELECT t1.id, t1.col1||' '||...||t2.col1||' '||... AS textcolumn FROM table_1 t1 JOIN table_2 t2 ON t1.id=t2.id WHERE t1.id=v_id) src
        ON (t4.id = src.id)
        WHEN MATCHED THEN UPDATE SET t4.textcolumn = src.textcolumn
        WHEN NOT MATCHED THEN INSERT (id, textcolumn) VALUES (src.id, src.textcolumn);
        COMMIT;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN EXIT;
      END;
    END LOOP;
  END;
  /
  
  -- 创建调度作业监听队列
  BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
      job_name => 'JOB_PROCESS_SEARCH_UPDATES',
      job_type => 'STORED_PROCEDURE',
      job_action => 'process_search_updates',
      start_date => SYSTIMESTAMP,
      repeat_interval => 'FREQ=SECONDLY;INTERVAL=1',
      enabled => TRUE,
      auto_drop => FALSE
    );
  END;
  /
  

• 保留table_3的批量优化方案
  若不想改动现有架构，将单条处理改为批量处理，减少轮询开销：

  CREATE OR REPLACE PROCEDURE process_table3_updates
  IS
    TYPE id_list IS TABLE OF NUMBER;
    v_ids id_list;
  BEGIN
    -- 批量锁定并获取待处理ID
    SELECT id BULK COLLECT INTO v_ids
    FROM table_3
    WHERE ROWNUM <= 1000
    FOR UPDATE SKIP LOCKED;
  
    IF v_ids.COUNT > 0 THEN
      -- 批量更新table_4
      MERGE INTO table_4 t4
      USING (SELECT t1.id, t1.col1||' '||...||t2.col1||' '||... AS textcolumn 
             FROM table_1 t1 
             JOIN table_2 t2 ON t1.id=t2.id 
             WHERE t1.id MEMBER OF v_ids) src
      ON (t4.id = src.id)
      WHEN MATCHED THEN UPDATE SET t4.textcolumn = src.textcolumn
      WHEN NOT MATCHED THEN INSERT (id, textcolumn) VALUES (src.id, src.textcolumn);
      
      -- 批量删除已处理ID
      DELETE FROM table_3 WHERE id MEMBER OF v_ids;
      COMMIT;
    END IF;
  END;
  /
  

三、优化索引维护策略 #

现有高频索引同步/优化会消耗大量资源，可调整为按需触发：

• 若保留table_4的普通索引
  • 取消每5秒同步，改为批量处理完table_3数据后手动同步一次：

    ALTER INDEX idx_table4_textcolumn REBUILD ONLINE;
    

  • 降低优化频率，改为每天一次，或根据索引碎片率触发：

    -- 查询索引碎片情况
    SELECT index_name, blevel, leaf_blocks FROM user_indexes WHERE index_name='IDX_TABLE4_TEXTCOLUMN';
    -- 当blevel超过3或碎片率较高时执行优化
    ALTER INDEX idx_table4_textcolumn REBUILD ONLINE;
    

四、其他辅助优化 #

• 分区table_4：按ID范围或数据变更时间分区，减少查询与维护的范围：

  CREATE TABLE table_4 (id NUMBER, textcolumn CLOB)
  PARTITION BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (100000),
    PARTITION p2 VALUES LESS THAN (200000),
    ...
  );
  

• OLTP压缩：对table_4启用OLTP压缩，减少存储空间与IO开销：

  ALTER TABLE table_4 COMPRESS FOR OLTP;
  

• 结果缓存：对频繁搜索的查询添加结果缓存提示，降低数据库压力：

  SELECT id FROM table_4 WHERE textcolumn LIKE '%keyword%' /*+ RESULT_CACHE */;
  

内容的提问来源于stack exchange，提问作者Arun Kumar