sql – 查询数据库名称的建议

该表有名字和姓氏字段,目前我正在使用这样的东西：

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距离.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!