이름에 대한 데이터베이스 쿼리에 대한 제안 사항

이름에 대한 데이터베이스 쿼리에 대한 제안 사항

Oracle 데이터베이스에는 많은 데이터베이스와 마찬가지로 전기 정보가 포함된 테이블이 있습니다.그것에 대해, 저는 "자연스러운" 방식으로 이름으로 검색하고 싶습니다.

에 테블은이 .forename그리고.surname필드 및 현재 다음과 같은 것을 사용하고 있습니다.

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,
         forename,
         surname
from     mytable
where    contains(forename,'!%john%',1) > 0
and      contains(surname,'!%smith%',2) > 0
order by relevance desc;

이것은 전체 텍스트 인덱스뿐만 아니라 Soundex 알고리즘을 사용할 수 있다는 장점이 있으므로 조금 더 효율적일 것입니다. (하지만, 제 일화적인 결과는 꽤 느리다는 것을 보여줍니다!)이것에 대해 걱정되는 것은 다음과 같습니다.

• 첫째, 텍스트 색인은 의미 있는 방식으로 새로 고쳐져야 합니다.용사를 합니다.on commit너무 느리고 프런트 엔드 소프트웨어가 데이터베이스와 상호 작용하는 방식에 방해가 될 수 있습니다. 즉, 제가 통제할 수 없는 것입니다. 그래서 생각을 좀 해야 합니다.

• 정렬되지 . 이 Oracle에 잘 . 이에 대해서는 잘 모르겠습니다.score를 들어제는 " Smith " SimpsSmith"와입니다. "Jonathan Peter Jason Smith", "Jonathan Peter Jason Smith"와 같은 수준입니다. -- Jane "Jason" Simps"도 마찬가지입니다.

앞의 와일드카드를 제거하면 실제로는 이름 가운데 청크를 검색하지 않기 때문에 결과가 저하되지 않고 성능이 향상될 수 있다고 생각합니다.하지만, 그렇지 않다면, 나는 아이디어를 받아들일 수 있습니다...이 시나리오는 구역질나게 구현된 것이 틀림없습니다!제가 지금 하고 있는 일에 대해 더 나은 접근법을 제안할 수 있는 사람이 있습니까?

감사합니다 :)

저는 댓글에 있는 제안들을 따라 꽤 잘 작동하는 해결책을 생각해냈습니다.특히, @X-Zero의 Soundexes 테이블 생성 제안:내 경우에는 새 테이블을 만들 수 있지만 기존 스키마를 변경할 수 없습니다!

제 프로세스는 다음과 같습니다.

• 열을 하여 새 .ID,token,sound그리고.position기본 키를 위에 놓고(ID,sound,position및 ()에ID,sound).

• 전기 표의 각 사용자를 살펴봅니다.

  • 이름과 성을 연결합니다.

  • 코드 페이지를 다음으로 변경합니다.us7ascii악센트가 있는 문자는 정규화됩니다.이는 Soundex 알고리즘이 악센트가 있는 문자에서는 작동하지 않기 때문입니다.

  • 알파벳이 아닌 모든 문자를 공백으로 변환하고 이를 토큰 간의 경계로 간주합니다.

  • ), 토큰의 및 에 있는 합니다. 를 이문열을소토 토에큰테블이고다토및큰자자큰토이의원니래, 문자문에열있삽위합는입를치큰화하ex▁token▁this소이▁associate▁with다와 연결합니다. 이를 에 연결합니다.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,instr(nameString,' ') + 1);
      posn := posn + 1;
    end loop;
  end loop;
end;
/

를 들어, " O는 " 1 " "3)로되고, 이 세 O'Conner"(위치 2), "sian"(위치 3)에 삽입됩니다.personsearch그들의 신분증과 함께.

• 검색을 위해 동일한 프로세스를 수행합니다. 검색 기준을 토큰화한 다음 사운드텍스와 상대 위치가 일치하는 결과를 반환합니다.위치를 기준으로 정렬한 다음 레벤슈테인 거리(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:토큰이 하는 동적 . 서 SQL은 다음과 같습니다.position .position이전에 결합된 테이블의...검색 문자열 토큰화와 함께 ID 테이블을 반환하는 함수를 작성할 계획입니다.하지만, 저는 당신이 아이디어를 얻을 수 있도록 이것을 여기에 게시할 뿐입니다 :)

제가 말씀드렸듯이, 이것은 꽤 잘 작동합니다.그것은 좋은 결과를 꽤 빨리 돌려줍니다.서버에 의해 캐시된 "John Smith"를 검색해도 0.2초 미만으로 실행됩니다. 200개 이상의 행이 반환됩니다.저는 그것에 매우 만족하며 생산에 투입할 것을 고려할 것입니다.유일한 문제는 다음과 같습니다.

• 토큰의 사전 계산은 시간이 좀 걸리지만 일회성 과정이라 크게 문제가 되지 않습니다.그러나 관련된 문제는 트리거를 사용해야 한다는 것입니다.mypeople은 해당 이 " " " 에 수행될 마다 검색 을 삽입/업데이트할 수 .mypeople이렇게 하면 시스템 속도가 느려질 수 있습니다. 하지만 이 작업은 1년 중 몇 가지 기간에만 수행되므로 검색 테이블을 예약된 시간에 다시 작성하는 것이 더 나은 해결책이 될 수 있습니다.

• 스템핑이 수행되지 않으므로 Soundex 알고리즘은 전체 토큰에서만 일치합니다.예를 들어, "크리스"를 검색해도 "크리스토퍼"는 반환되지 않습니다.이에 대한 가능한 해결책은 토큰 스템의 사운드덱스만 저장하는 것이지만 스템을 계산하는 것은 간단한 문제가 아닙니다!향후 업그레이드가 될 예정입니다. TeX에서 사용하는 하이픈네이션 엔진을 사용할 수도 있습니다.

어쨌든, 그것이 도움이 되기를 바랍니다 :) 댓글 환영합니다!

---

EDIT Metaone 및 Damerau-Levenshtein Distance를 사용하여 전체 솔루션(작성 및 구현)이 여기에 있습니다.

언급URL : https://stackoverflow.com/questions/7612822/suggestions-for-querying-database-for-names