Oracle规则匹配优化：逐列调用EVALUATERULE函数有无更优方案

现有实现存在的问题 #

• 每次调用EVALUATERULE都执行动态SQL，硬解析开销大，数据量高时性能极差
• 动态SQL拼接存在SQL注入风险，输入值包含特殊字符时会报错甚至被恶意利用
• 逐列调用函数产生大量PL/SQL和SQL引擎上下文切换开销
• 数值比较逻辑用临时生成10万条记录的子查询实现，属于冗余设计，性能损耗极大

优化方案 #

方案1：重构校验函数，去掉所有动态SQL #

把所有规则判断逻辑直接用PL/SQL原生运算符实现，完全避免动态SQL执行：

create or replace FUNCTION EVALUATERULE(colvalue varchar2 ,inputvalue varchar2)
return number
as
begin
  -- 通配符/空规则直接匹配
  if colvalue = '*' or colvalue = 'NULL' then
    return 1;
  end if;
  
  -- 数值比较规则
  if regexp_like(colvalue, '^[<>!=]=?|<>') and regexp_like(inputvalue, '^-?\d+(\.\d+)?$') then
    return case when to_number(inputvalue) > to_number(regexp_replace(colvalue, '[^0-9\.]', '')) and colvalue like '>%' then 1
                when to_number(inputvalue) < to_number(regexp_replace(colvalue, '[^0-9\.]', '')) and colvalue like '<%' then 1
                when to_number(inputvalue) = to_number(regexp_replace(colvalue, '[^0-9\.]', '')) and colvalue like '=%' then 1
                when to_number(inputvalue) != to_number(regexp_replace(colvalue, '[^0-9\.]', '')) and regexp_like(colvalue, '^!=|^<>') then 1
                else 0 end;
  end if;
  
  -- in包含规则
  if regexp_instr(colvalue, 'in',1,1,0,'i')=1 then
    return case when regexp_like(colvalue, inputvalue) then 1 else 0 end;
  end if;
  
  -- not排除规则
  if regexp_instr(colvalue, 'not',1,1,0,'i')=1 then
    return case when regexp_like(colvalue, inputvalue) then 0 else 1 end;
  end if;
  
  -- 前后模糊匹配
  if regexp_like(colvalue, '^\*.*|.*\*$') then
    return case when inputvalue like replace(colvalue, '*', '%') then 1 else 0 end;
  end if;
  
  -- 完全匹配
  return case when colvalue = inputvalue then 1 else 0 end;
END EVALUATERULE;

方案2：把校验逻辑直接嵌入WHERE子句，取消逐列函数调用 #

直接在查询条件里写判断逻辑，完全避免函数调用的上下文切换开销，性能提升最明显：

create or replace PROCEDURE GET_CUSTOMER_RULES(
    SCO_CUSTOMER_CD_I IN VARCHAR2,
    MAX_VOL_I IN VARCHAR2,
    MIN_VOL_I IN VARCHAR2,
    MIN_WT_I IN VARCHAR2,
    MAX_WT_I IN VARCHAR2,
    MIN_CARTONS_I IN VARCHAR2,
    MAX_CARTONS_I IN VARCHAR2,
    MIN_PALLETS_I IN VARCHAR2,
    MAX_PALLETS_I IN VARCHAR2,
    PRIORITY_I IN VARCHAR2,
    ORDER_TYPE_I IN VARCHAR2,
    ROUTE_I IN VARCHAR2,
    SHIPTO_COUNTRY_I IN VARCHAR2,
    RULE_SEQ_O OUT VARCHAR2,
    CARRIER_O OUT VARCHAR2,
    SHIP_VIA_O OUT VARCHAR2,
    LOGISTIC_UNIT_O OUT VARCHAR2,
    CARTON_LABEL_TYPE_O OUT VARCHAR2 
) AS 
BEGIN
select RULE_SEQ,CARRIER,SHIP_VIA,LOGISTIC_UNIT,CARTON_LABEL_TYPE 
INTO RULE_SEQ_O,CARRIER_O,SHIP_VIA_O,LOGISTIC_UNIT_O,CARTON_LABEL_TYPE_O
from SCO_CS_CUSTOM_CARRIER_RULESBKP t
where -- 以下为通用匹配逻辑，可按实际规则细化调整
  (SCO_CUSTOMER_CD in ('*','NULL') or SCO_CUSTOMER_CD = SCO_CUSTOMER_CD_I or SCO_CUSTOMER_CD like replace(SCO_CUSTOMER_CD_I, '*', '%'))
  and (MAX_VOL in ('*','NULL') or to_number(MAX_VOL_I) <= to_number(regexp_replace(MAX_VOL, '[^0-9\.]', '')))
  and (MIN_VOL in ('*','NULL') or to_number(MIN_VOL_I) >= to_number(regexp_replace(MIN_VOL, '[^0-9\.]', '')))
  -- 剩余列的判断逻辑按上述规则补全即可，和原函数逻辑保持一致
ORDER BY RULE_SEQ ASC FETCH FIRST 1 ROW ONLY;
END GET_CUSTOMER_RULES;

配套优化项 #

• 给规则表加高频筛选列的联合索引，比如SCO_CUSTOMER_CD, ROUTE, SHIPTO_COUNTRY, RULE_SEQ，可大幅加快筛选速度
• 如果规则更新频率低、查询频率高，可以提前把规则的匹配条件预处理成标准SQL条件存到单独字段，查询时直接调用，避免每次正则匹配开销

内容的提问来源于stack exchange，提问作者Vineeth Gokarn