如何优化Oracle UTL_MATCH.Jaro_wrinkler_similarity函数的批量匹配性能？

你当前嵌套游标逐行调用UTL_MATCH.Jaro_wrinkler_similarity的方式会产生大量上下文切换，且无法利用Oracle的执行计划优化，这是性能极差的核心原因。以下是具体优化手段：

1. 用SQL关联查询替代嵌套游标循环 #

直接在SQL层完成相似度计算，让Oracle优化器负责执行计划的生成，避免逐行处理的开销，这是提升性能最显著的手段。

SELECT 
    t1.string1, 
    t2.string2, 
    UTL_MATCH.JARO_WRINKLER_SIMILARITY(t1.string1, t2.string2) AS similarity_score
FROM 
    table1 t1, 
    table2 t2
-- 按需添加相似度阈值过滤，减少结果集大小
WHERE 
    UTL_MATCH.JARO_WRINKLER_SIMILARITY(t1.string1, t2.string2) > 80;

2. 预过滤减少无效计算 #

在调用相似度函数前，通过字符串基础特征筛选掉不可能匹配的记录，大幅减少需要计算的配对数：

• 过滤长度差异过大的字符串（比如长度差超过固定值或比例）
• 用SOUNDEX/DIFFERENCE做发音初步匹配，过滤发音差异大的记录

示例：

SELECT 
    t1.string1, 
    t2.string2, 
    UTL_MATCH.JARO_WRINKLER_SIMILARITY(t1.string1, t2.string2) AS similarity_score
FROM 
    table1 t1
JOIN table2 t2 
    ON ABS(LENGTH(t1.string1) - LENGTH(t2.string2)) <= 3 -- 长度差不超过3
    AND DIFFERENCE(t1.string1, t2.string2) >= 3 -- 发音相似度达标
WHERE 
    UTL_MATCH.JARO_WRINKLER_SIMILARITY(t1.string1, t2.string2) > 80;

3. 启用并行查询加速 #

针对百万级数据，开启并行查询让Oracle利用多CPU资源并行处理任务：

SELECT /*+ PARALLEL(8) */ -- 8为并行度，根据服务器CPU核心数调整
    t1.string1, 
    t2.string2, 
    UTL_MATCH.JARO_WRINKLER_SIMILARITY(t1.string1, t2.string2) AS similarity_score
FROM 
    table1 t1
JOIN table2 t2 
    ON ABS(LENGTH(t1.string1) - LENGTH(t2.string2)) <= 3
WHERE 
    UTL_MATCH.JARO_WRINKLER_SIMILARITY(t1.string1, t2.string2) > 80;

4. 优化索引（辅助预过滤） #

由于UTL_MATCH.JARO_WRINKLER_SIMILARITY是双参数函数，无法直接创建有效索引，但可以针对预过滤条件创建索引，提升筛选效率：

CREATE INDEX idx_table1_len_soundex ON table1(LENGTH(string1), SOUNDEX(string1));
CREATE INDEX idx_table2_len_soundex ON table2(LENGTH(string2), SOUNDEX(string2));

5. 批量处理（若必须用PL/SQL） #

如果业务逻辑无法完全用SQL实现，改用批量Fetch集合的方式减少上下文切换，同时加入预过滤：

DECLARE
    TYPE str_tab IS TABLE OF table1.string1%TYPE;
    TYPE str_tab2 IS TABLE OF table2.string2%TYPE;
    v_tab1 str_tab;
    v_tab2 str_tab2;
BEGIN
    -- 批量读取数据到集合
    SELECT string1 BULK COLLECT INTO v_tab1 FROM table1;
    SELECT string2 BULK COLLECT INTO v_tab2 FROM table2;
    
    FOR i IN 1..v_tab1.COUNT LOOP
        FOR j IN 1..v_tab2.COUNT LOOP
            -- 先做预过滤，跳过不可能匹配的记录
            IF ABS(LENGTH(v_tab1(i)) - LENGTH(v_tab2(j))) <= 3 THEN
                -- 执行相似度计算
                DBMS_OUTPUT.PUT_LINE(UTL_MATCH.JARO_WRINKLER_SIMILARITY(v_tab1(i), v_tab2(j)));
            END IF;
        END LOOP;
    END LOOP;
END;
/

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