LIKE '%에 대한 PL/SQL 성능 조정...와일드카드 쿼리

LIKE '%에 대한 PL/SQL 성능 조정...와일드카드 쿼리

Oracle 11g 데이터베이스를 사용하고 있습니다.
알 수도 있고 모를 수도 있지만 문자열 앞에 "%"가 있는 와일드카드 쿼리를 사용하면 열 인덱스가 사용되지 않고 전체 테이블 검색이 수행됩니다.

이러한 종류의 쿼리를 개선하는 방법에 대한 명확한 제안은 없는 것 같습니다. 하지만 다음 쿼리를 최적화하는 방법에 대한 경험을 통해 귀중한 정보를 공유할 수 있을 것입니다.

SELECT * 
  FROM myTable 
 WHERE UPPER(CustomerName) like '%ABC%' 
    OR UPPER(IndemnifierOneName) like '%ABC%' 
    OR UPPER(IndemnifierTwoName) like '%ABC%';

...여기서 3개 열은 모두 varchar2(100) 유형이고 ABC는 변수 입력 매개 변수의 값입니다.

CONTEX 인덱스를 제안합니다. 매일 데이터가 언제든지 업데이트되고 이 인덱스는 다시 동기화해야 하므로 150만 행의 테이블에 적합한 옵션이 아닙니다. 죄송합니다.

추신: 제가 모든 답변에 찬성표를 던질 테니 계속해서 답변해 주시기 바랍니다.

이미 언급했듯이 이름 열에 액트x 컨텍스트 인덱스를 추가할 수 있습니다.

소수의 레코드가 업데이트된다고 가정하면 한 가지 옵션은 매일 인덱스를 새로 고치는 것입니다. (그리고 발생한 시간을 기록합니다.)

그런 다음 검색 중인 테이블에 마지막 업데이트 날짜 열 및 색인을 추가합니다.

ctx 인덱스에서 변경되지 않은 기존 데이터의 대부분을 스캔하고 기존 LIKE를 사용하여 업데이트된 데이터의 적은 비율 중에서 선택할 수 있어야 합니다. 예를 들어 다음과 같습니다.

WHERE (lastupdated<lastrefresh AND contains(name,'%ABC%')) 
   OR (lastupdated>lastrefresh AND name like '%ABC%')

참고: 이 경우 쿼리 계획이 약간 정신적으로 진행될 수 있습니다(행 ID로 비트맵 변환이 많음). 이 경우 OR의 두 부분을 UNION ALL 쿼리로 분할합니다. 예:

SELECT id FROM mytable   
    WHERE 
    (lastupdate>lastrefresh and name LIKE '%ABC%')
    UNION ALL
    SELECT id FROM mytable   
    WHERE lastupdate<lastrefresh and CONTAINS(name, '%ABC%', 1) > 0

유일한 최적화 방법은 해당 유형의 쿼리를 사용하지 않고 데이터베이스 플랫폼의 기본 기능을 사용하는 것입니다.

Oracle Text: http://www.oracle.com/technetwork/database/enterprise-edition/index-098492.html 참조

SQL Server 관련 질문에 대한 일반적인 대답은 전체 텍스트 검색입니다.오라클이 더 좋은 것이나 더 좋은 것을 가지고 있는 것을 보니 기쁩니다.

그UPPER()인덱스를 먼저 죽이는 것입니다. regexp를 사용하는 것을 고려해 보십시오. 이셜니.%일반 인덱스 검색을 피할 수 있지만 FTS보다 빠른 전체 테이블 검색이 항상 발생하는 것은 아니지만 전체 인덱스 검색이 발생합니다.

라고 생각이 듭니다.'ABC'변수니다입. 만약 않다면,그렇지 않은 경우에는 함수 인덱스를 사용하는 것이 좋습니다.

때때로 이런 종류의 쿼리는 피할 수 없습니다. URL에서 도메인을 추출하거나 접두사와 접미사가 있는 단어에서 루트를 추출할 수 있습니다.

사용자 정의 토큰화기를 사용하거나 사용하지 않고 전체 텍스트 색인에 의존할 수 있습니다.

또는 검색하는 문자열의 수가 제한되어 있고 미리 알려진 경우(예: URL에서 추출해야 하는 제한된 도메인 이름 집합으로 작업 중) 인덱스를 사용할 수 있습니다.

http://www.akadia.com/services/ora_function_based_index_2.html

Oracle 텍스트를 사용하지만 약간 더 새로운 CTXCAT 버전 - 이 도메인 인덱스는 해당 행을 삽입/업데이트하는 트랜잭션의 일부로 업데이트되므로 항상 최신 상태입니다. 자세한 내용은 Oracle 자체 Oracle 텍스트 문서를 참조하십시오.

언급URL : https://stackoverflow.com/questions/6229445/pl-sql-performance-tuning-for-like-wildcard-queries