该表有名字和姓氏字段,目前我正在使用这样的东西：
select id,forename,surname from mytable where upper(forename) like '%JOHN%' and upper(surname) like '%SMITH%';
这是有效的,但它可能非常慢,因为这个表上的索引显然不能说明前面的通配符.此外,用户通常会根据他们通过电话告诉他们 – 包括大量的非英文名称 – 寻找人,因此也可以做一些语音分析.
因此,我一直在尝试使用Oracle Text：
create index forenameFTX on mytable(forename) indextype is ctxsys.context; create index surnameFTX on mytable(surname) indextype is ctxsys.context; select score(1)+score(2) relevance,id,surname from mytable where contains(forename,'!%john%',1) > 0 and contains(surname,'!%smith%',2) > 0 order by relevance desc;
这具有使用Soundex算法以及全文索引的优点,因此应该更有效率. (虽然,我的轶事结果显示它很慢！)我唯一的担心是：
>首先,文本指标需要以有意义的方式进行刷新.在提交时使用会太慢,可能会干扰前端软件(无法控制的)与数据库的交互;所以需要一些思考…
> Oracle返回的结果并不是完全自然的排序;我不太确定这个分数功能.例如,我的发展数据显示“乔纳森·彼得·杰森·史密斯”在顶级,而“简·玛格丽特·辛普森”与“约翰·特伦斯·史密斯”
我认为删除前面的通配符可能会提高性能,而不会降低结果,因为在现实生活中,您永远不会在名称中间搜索一个块.但是,否则,我开放的想法…这种情况必须已经实施了恶心！任何人都可以提出一个更好的做法,我正在做什么/现在考虑？
谢谢 ：)
解决方法
所以我的过程如下：
>创建一个带有列的新表：ID,令牌,声音和位置;主键(ID,声音,位置)和附加索引(ID,声音).
>通过传记表中的每个人：
连接他们的姓和姓.
>将代码页更改为us7ascii,因此重音字符将被归一化.这是因为Soundex算法不适用于重音字符.
>将所有非字母字符转换为空格,并将其视为令牌之间的边界.
>标记此字符串并将表单插入到小写字母表中,令牌的Soundex和令牌来自原始字符串的位置;将此与ID相关联.
像这样：
declare
nameString varchar2(82);
token varchar2(40);
posn integer;
cursor myNames is
select id,forename||' '||surname person_name
from mypeople;
begin
for person in myNames
loop
nameString := trim(
utl_i18n.escape_reference(
regexp_replace(
regexp_replace(person.person_name,'[^[:alpha:]]',' '),'\s+','us7ascii')
)||' ';
posn := 1;
while nameString is not null
loop
token := substr(nameString,1,instr(nameString,' ') - 1);
insert into personsearch values (person.id,lower(token),soundex(token),posn);
nameString := substr(nameString,' ') + 1);
posn := posn + 1;
end loop;
end loop;
end;
/
所以,例如,“Si’n O’Conner”被标记为“sian”(位置1),“o”(位置2)和“conner”(位置3),并且这三个条目与它们的Soundex一起插入到个人搜索以及他们的身份证.
>要搜索,我们执行相同的过程：令牌搜索条件,然后返回Soundexes和相对位置匹配的结果.我们按照位置顺序排列,然后依次对原始搜索的每个令牌的Levenshtein距离(ld)进行排序.
with searchcriteria as (
select 'john' token1,'smith' token2
from dual)
select alpha.id,mypeople.forename||' '||mypeople.surname
from peoplesearch alpha
join mypeople
on mypeople.student_id = alpha.student_id
join peoplesearch beta
on beta.student_id = alpha.student_id
and beta.position > alpha.position
join searchcriteria
on 1 = 1
where alpha.sound = soundex(searchcriteria.token1)
and beta.sound = soundex(searchcriteria.token2)
order by alpha.position,ld(alpha.token,searchcriteria.token1),beta.position,ld(beta.token,searchcriteria.token2),alpha.student_id;
为了搜索任意数量的令牌,我们需要使用动态sql：连续搜索表与令牌一样多次,连接表中的位置字段必须大于先前连接的表的位置.我打算写一个函数来做这个 – 以及搜索字符串tokenisation – 这将返回一个ID表.但是,我刚刚发布在这里,所以你得到的想法:)
正如我所说,这样做很好：它很快返回好的结果.即使是搜索“John Smith”,一旦被服务器缓存,运行时间不到0.2s;返回超过200行…我很高兴与它,并将寻求投入生产.唯一的问题是：
>令牌的预先计算需要一段时间,但这是一个一次性的过程,所以不是太多的问题.然而,相关的问题是,每当对mypeople执行相应的操作时,需要在mypeople表上插入/更新/删除令牌到触发表中.这可能会拖慢系统;但由于这只能在一年的几个时期内发生,也许更好的解决方案是按计划重建搜索表.
>没有干扰,所以Soundex算法只匹配完整的令牌.例如,搜索“克里斯”不会返回任何“克里斯托弗”.一个可能的解决方案是仅存储令牌的杆的Soundex,但计算杆不是一个简单的问题！这将是未来升级,可能使用TeX使用的连字引擎…
无论如何,希望有帮助:)评论欢迎！
编辑我的完整解决方案(写作和实现)现在是here,使用了Metaphone和Damerau-Levenshtein距离.
