Oracle PL/SQL去除字符串重音效率低下，求高效替代方案

针对你遇到的自定义去重音函数拖慢查询的问题，结合老旧Oracle版本不能用collate的限制，给你几个可行的替代方案：

1. 预存储去重音后的字段（最推荐，性能最优） #

自定义函数在查询时需要逐行计算，大数据量下必然拖慢速度。最直接的解决办法是新增一个字段存储去重音后的结果，提前计算好而非查询时临时处理：

• 第一步，新增字段：

ALTER TABLE 你的表名 ADD 字段名_无重音 VARCHAR2(原字段长度);

• 第二步，批量初始化已有数据：

UPDATE 你的表名 
SET 字段名_无重音 = TRANSLATE(原字段名, 'ÁÉÍÓÚÀÈÌÒÙÄËÏÖÜÂÊÎÔÛáéíóúàèìòùäëïöüâêîôû', 'AEIOUAEIOUAEIOUAEIOUaeiouaeiouaeiouaeiou');

（用一次性TRANSLATE替代嵌套REPLACE，比原函数更高效）

• 第三步，用触发器维护新字段（如果数据会频繁更新）：

CREATE OR REPLACE TRIGGER TRG_维护无重音字段
BEFORE INSERT OR UPDATE OF 原字段名 ON 你的表名
FOR EACH ROW
BEGIN
  :NEW.字段名_无重音 := TRANSLATE(:NEW.原字段名, 'ÁÉÍÓÚÀÈÌÒÙÄËÏÖÜÂÊÎÔÛáéíóúàèìòùäëïöüâêîôû', 'AEIOUAEIOUAEIOUAEIOUaeiouaeiouaeiouaeiou');
END;
/

之后查询时直接用字段名_无重音，还能给这个字段建普通索引，性能和查询普通字段完全一致。

2. 用NLSSORT内置函数提取无重音字符 #

老旧Oracle版本（比如10g及以上）可以利用NLSSORT函数的排序规则特性，无需自定义函数就能去除重音：

SELECT SUBSTR(NLSSORT(原字段名, 'NLS_SORT=BINARY_AI'), 1, LENGTH(原字段名)) AS 无重音字段
FROM 你的表名;

NLS_SORT=BINARY_AI表示忽略大小写和重音的二进制排序，NLSSORT返回的字节串中已经去除了重音信息，截取对应长度即可得到无重音的字符串。这个方法无需改表结构，内置函数的执行效率远高于自定义函数。

3. 给自定义函数创建函数索引（如果必须保留函数） #

如果业务上无法新增字段，且必须使用自定义函数，那给函数创建基于函数的索引，让查询能走索引而非全表扫描：

首先确保你的FNC_REMOVE_ACENTO是确定性函数（相同输入必返回相同输出，不能包含SYSDATE这类非确定性操作），然后创建索引：

CREATE INDEX IDX_表名_字段名_无重音 ON 你的表名 (FNC_REMOVE_ACENTO(原字段名));

之后查询中使用FNC_REMOVE_ACENTO(原字段名)作为条件时，Oracle会使用这个索引，大幅提升查询速度。

4. 优化现有TRANSLATE实现（小幅度提升） #

如果之前用的是嵌套REPLACE，换成一次性的TRANSLATE会减少函数调用次数，加上DETERMINISTIC关键字帮助优化器缓存结果：

CREATE OR REPLACE FUNCTION FNC_REMOVE_ACENTO(p_str IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
  RETURN TRANSLATE(p_str, 'ÁÉÍÓÚÀÈÌÒÙÄËÏÖÜÂÊÎÔÛÇáéíóúàèìòùäëïöüâêîôûç', 'AEIOUAEIOUAEIOUAEIOUCAEIouaeiouaeiouaeiouaeiouc');
END;
/

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