代码之家 › 专栏 › 技术社区 › MoxGoat

utl_匹配性能

oracle sql

MoxGoat · 技术社区 · 6 年前

我有2个表1有数百万条记录,另一个有200K记录。我正试图用几个连接的列进行UTL匹配,但当然这个过程需要花费大量的时间(将每个记录进行数百万次比较)。x.code只是比较表1的另一个要求有什么办法可以加快速度吗?比较100行花了18分钟

select * 

from table1 x
where exists(select * 
             from largetable y 
             where x.state = y.state and 
                   utl_match.jaro_winkler_similarity(x.address || ' ' || x.city, y.address || ' ' || y.city) > 95 and 
                   x.code like '%a%'
            );

2 回复 | 直到 6 年前

Mitch 6 年前

你正在进行一场长跑比赛。根据这种逻辑,可以索引的字段之间一定有某种相似性。找到一些产生可索引结果的函数,并将其用于每行的搜索空间。

例如,您有一个 City 字段-大多数规范化的城市名称将完全匹配(您可以将城市的不同值规范化为单独的表以修复拼写错误)。一旦你有了一个城市匹配,你可能已经减少了一个公平的地址搜索空间。

如果这还不够,则生成地址第一个字的前两个字母字符、地址第一个字的后两个字母字符、地址中存在的数字的三个“匹配”键。在考虑编辑距离之前,使用三个“匹配”键作为一个筛选功能。

例如,在将“123 Mulberry Lane”与“123 Mlberry Ln”匹配时,步骤如下:

123 Mulberry Lane -> { numbers: '123', first: 'MU', last: 'RY' }
123 Mlberry Ln    -> { numbers: '123', first: 'ML', last: 'RY' }

所以,虽然第一个字符winnow会导致地址不被考虑,但是数字和最后一个启发式仍然会捕获它。

APC 6 年前

“编辑距离匹配”生成每行与其他行的矩阵,因此没有可索引的内容。您所能做的就是减少需要比较的记录集。

有什么办法可以加快速度吗?

性能调整是一门科学。如果没有更多关于数据倾斜和分布的细节,我们可以做的更多的是提供一些猜测。

x.code只是比较表1的另一个要求

所以,随机猜测:最好将其包含在外部查询中。

select * 
from table1 x
where  x.code like '%a%'
and exists(select * 
             from largetable y 
             where x.state = y.state and 
                   utl_match.jaro_winkler_similarity(x.address || ' ' || x.city, y.address || ' ' || y.city) > 95                   
        );

另一个随机猜测。即使 largetable.state 是索引的,它可能不是一个非常有效的访问路径,因此(半)交叉联接可能更有效:

select distinct t1.* 
from table1 x
     join largetable y 
          on x.state = y.state 
where  x.code like '%a%'
and utl_match.jaro_winkler_similarity(x.address || ' ' || x.city, y.address || ' ' || y.city) > 95                   
;

这就是为什么soundex()或metaphone等技术很方便:它们提供了可用于相等操作的近似值,并且可以对其进行索引。然而,它们是粗糙的,尤其是不允许某些拼写错误,所以这也是我们需要其他算法的原因。但是,也许您可以实现一个多步骤的方法,使用标记化方法来识别一些匹配,并编辑距离以赢得剩余部分。