sql里做字符串的最佳匹配

之前有个项目要求做最佳匹配,要求如下
输入:字符串 比如31810484
输出:表Test中A字段与31810484最佳匹配的记录

最佳匹配的概念:
比如31810484,如果数据库里有
318
3181
31810
318104
3181048
那么应该选择与31810484最匹配的3181048做为输出

Step 1. 程序处理
接到这个需求之后,马上想到的是在程序里做比较,首先查询出所有的记录,然后拿目标串去匹配,匹配算法想的很简单.
1. 首先将数据库查询出的所有记录放在一个resultSet里面
2. 以某记录R的串长度(N)截取目标串前N位,如果截取后的串等于记录串R
3. 如果下一条匹配上的记录串比上一次的匹配记录串更长,则更新,一直找到最长匹配的那个为止.

这个代码写出来之后,逻辑上是OK的,后来代码检查时,发现这个效率实在是太低了,如果数据量小,OK. 如果数据量达到上千上万,效率就十分差了.

Step 2. 数据库处理
接着想,这个是数据库层面的事,应该由数据库来处理,于是花了一段时间去想写一个sql来处理这个需求,因数据库是oracle,我在这里写oracle版本

select * from (select * from Test where '31810484' like (A || '%') group by length(A) desc) where rownum <=1

这个sql能准确定位到最佳匹配.但是用了函数做为条件,效率也并不高.

Step 3. 加索引
给函数加索引,既是function base index.
于是加上了此索引
create index IDX_A_TEST ON Test (A || '%');
但是执行sql,发现还是很慢.
执行explain plan,发现此时还是全表扫描.
上网搜索才发现,原来需要设置些参数函数索引才会生效
ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;
alter system set query_rewrite_integrity=trusted;
alter system set optimizer_mode=first_rows;
加上之后,速度提升了3-4倍,但是还是有点慢,还需要提高,继续跟进...