Oracle: 조건을 포함한 전체 텍스트 검색

Oracle: 조건을 포함한 전체 텍스트 검색

다음과 같은 Oracle Text 인덱스를 만들었습니다.

create index my_idx on my_table (text) indextype is ctxsys.context; 

그러면 다음을 수행할 수 있습니다.

select * from my_table where contains(text, '%blah%') > 0;

하지만 이 테이블에 또 다른 컬럼이 있다고 가정해 보겠습니다.group_id, 대신 다음과 같은 질문을 하고 싶었습니다.

select * from my_table where contains(text, '%blah%') > 0 and group_id = 43;

위 인덱스를 사용하면 Oracle은 다음을 포함하는 모든 항목을 검색해야 합니다.'blah', 그리고 그들 모두를 확인해 보세요.group_ids.

이상적으로, 나는 단지 다음과 같은 것들만 검색하는 것을 선호합니다.group_id = 43, 그래서 다음과 같은 지수를 원합니다.

create index my_idx on my_table (group_id, text) indextype is ctxsys.context; 

일반적인 인덱스와 비슷하므로 각각의 텍스트 검색을 별도로 수행할 수 있습니다.group_id.

Oracle에서 이와 같은 작업을 수행할 수 있는 방법이 있습니까(중요한 경우 10g을 사용합니다)?

편집(명확화)

백만 행과 다음 두 열 중에서 다음 두 열이 있는 표를 생각해 보십시오.A그리고.B, 숫자 둘 다500개의 다양한 값이 있다고 가정해 보겠습니다.A그리고 2000개의 다른 값들의.B, 각 행은 독특합니다.

이제 생각해 보겠습니다.select ... where A = x and B = y

에 대한 색인A그리고.B내가 말할 수 있는 한 따로 인덱스 검색을 해봐요B, 500개의 다른 행을 반환한 다음 이 행에 대해 조인/스캔을 수행합니다.어떤 경우에도 최소 500개의 행을 살펴보아야 합니다(데이터베이스가 운이 좋고 필요한 행을 일찍 찾는 것 외에도).

반면에 지수는(A,B)한 인덱스 검색에서 한 행을 찾으면 훨씬 더 효과적입니다.

별도 인덱스를 에 추가하는 중group_id그리고 내가 느끼는 텍스트는 쿼리 생성기에 두 가지 옵션만 남겨놓습니다.

(1) 사용.group_id색인을 만들고 텍스트에 대한 모든 결과 행을 검색합니다.
(2) 텍스트 색인을 사용하고 모든 결과 행을 검색하여 다음 항목에 대해group_id.
(3) 두 인덱스를 모두 사용하고 조인을 수행합니다.

내가 원하는 건

(4) 사용.(group_id, "text")색인을 사용하여 특정 아래의 텍스트 색인을 찾습니다.group_id텍스트 색인에서 필요한 특정 행/행을 검색합니다.에서 인덱스를 사용할 때와 마찬가지로 스캔 및 검사 또는 결합이 필요하지 않습니다.(A,B).

오라클 텍스트

1 - FILTER BY:로 컨텍스트 인덱스를 만들어 성능을 향상시킬 수 있습니다.

create index my_idx on my_table(text) indextype is ctxsys.context filter by group_id;

내 시험에서.filter by성능은 확실히 향상되었지만 group_id에서 btree 인덱스를 사용하는 것이 여전히 약간 빠릅니다.

2 - CTXCAT 인덱스는 "하위 인덱스"를 사용하며 다중 열 인덱스와 유사하게 작동하는 것으로 보입니다.이것이 여러분이 찾고 있는 옵션(4)인 것 같습니다.

begin
  ctx_ddl.create_index_set('my_table_index_set');
  ctx_ddl.add_index('my_table_index_set', 'group_id');
end;
/

create index my_idx2 on my_table(text) indextype is ctxsys.ctxcat
    parameters('index set my_table_index_set');

select * from my_table where catsearch(text, 'blah', 'group_id = 43') > 0

이것이 가장 빠른 접근 방법일 것입니다.당신의 A 및 B 시나리오와 유사한 120MB의 랜덤 텍스트에 대해 위의 쿼리를 사용하면 18개의 일관된 get만 필요합니다.하지만 단점으로는 CTXCAT 지수를 만드는 데 거의 11분이 걸렸고 1.8을 사용했습니다.GB의 공간.

(참고: Oracle Text는 여기서 올바르게 작동하는 것처럼 보이지만, Text에 익숙하지 않고 @NullUserException이 말한 것처럼 이러한 인덱스를 사용하는 것이 부적절하지 않다는 것을 확인할 수 없습니다.)

다중 열 인덱스 대 인덱스 조인

편집에서 설명한 상황의 경우, 일반적으로 (A,B)에서 인덱스를 사용하는 것과 A와 B에서 별도의 인덱스를 결합하는 것 사이에는 큰 차이가 없습니다.당신이 설명한 것과 유사한 데이터로 몇 가지 테스트를 작성했는데 인덱스 결합은 다중 열 인덱스에 대해 7개의 일관된 get과 2개의 일관된 get만 필요합니다.

오라클이 블록 단위로 데이터를 검색하기 때문입니다.블록은 보통 8K이고 인덱스 블록은 이미 정렬되어 있으므로 500에서 2000까지의 값을 몇 개의 블록에 맞출 수 있습니다.성능이 걱정된다면 보통 블록을 읽고 쓰는 IO만 중요합니다.Oracle이 몇 천 개의 행을 결합해야 하는지 여부는 CPU 시간의 의미가 없습니다.

그러나 이는 Oracle Text 인덱스에는 적용되지 않습니다.Bt트리 인덱스("비트맵과?")로 CONCONTIC 인덱스에 가입할 수 있지만 성능이 떨어집니다.

지수를 매기고 싶습니다.group_id그 정도면 충분한가 봐요몇 줄에 대해 이야기하는지, 어떤 성능이 필요한지에 대해서는 말하지 않습니다.

술어가 처리되는 순서가 반드시 쿼리에서 술어를 작성한 순서는 아니라는 것을 기억하십시오.진짜 이유가 없다면 옵티마이저를 능가하려고 하지 마세요.

짧은 버전:그럴 필요 없어요.쿼리 최적화 도구는 데이터를 선택하는 가장 좋은 방법을 결정할 수 있을 정도로 똑똑합니다.bt 트리 인덱스 만들기group_id, 즉:

CREATE INDEX my_group_idx ON my_table (group_id); 

---

긴 버전:136행의 더미 데이터를 삽입하는 스크립트()testperf.sql를 만들었습니다.

DESC my_table;

Name     Null     Type      
-------- -------- --------- 
ID       NOT NULL NUMBER(4) 
GROUP_ID          NUMBER(4) 
TEXT              CLOB      

bt트리 인덱스가 에 있습니다.group_id. 인덱스가 실제로 사용되는지 확인하려면 다음을 dba 사용자로 실행합니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS('<YOUR USER HERE>', 'MY_TABLE', cascade=>TRUE);

여기 각 행의 개수가 있습니다.group_id가 있으며 해당 비율은 다음과 같습니다.

GROUP_ID               COUNT                  PCT                    
---------------------- ---------------------- ---------------------- 
1                      1                      1                      
2                      2                      1                      
3                      4                      3                      
4                      8                      6                      
5                      16                     12                     
6                      32                     24                     
7                      64                     47                     
8                      9                      7         

---

쿼리 최적화 도구는 좋은 아이디어라고 생각되는 경우에만 인덱스를 사용합니다. 즉, 행의 특정 비율까지 검색하고 있는 것입니다.따라서 다음에 대한 쿼리 계획을 요청할 경우:

SELECT * FROM my_table WHERE group_id = 1;
SELECT * FROM my_table WHERE group_id = 7;

첫번째 쿼리의 경우 인덱스를 사용하는 반면 두번째 쿼리의 경우에는 전체 테이블 스캔을 수행합니다. 왜냐하면 다음의 경우 인덱스가 효과적이려면 행이 너무 많기 때문입니다.group_id = 7.

자, 다른 조건을 생각해보세요.WHERE group_id = Y AND text LIKE '%blah%'(내가 잘 모르기 때문에)ctxsys.context).

SELECT * FROM my_table WHERE group_id = 1 AND text LIKE '%ipsum%';

쿼리 계획을 보면 다음의 인덱스를 사용하는 것을 알 수 있습니다.group_id. 조건의 순서는 중요하지 않습니다.

SELECT * FROM my_table WHERE text LIKE '%ipsum%' AND group_id = 1;

동일한 쿼리 계획을 생성합니다.그리고 같은 쿼리를 실행하려고 하면group_id = 7, 전체 테이블 스캔으로 돌아가는 것을 확인할 수 있습니다.

SELECT * FROM my_table WHERE group_id = 7 AND text LIKE '%ipsum%';

Oracle은 쿼리 최적화기의 효율성을 지속적으로 개선하기 위해 통계를 매일 자동으로 수집합니다(매일 밤과 주말에 실행 예정).간단히 말해, Oracle은 최적화를 위해 최선을 다하므로 굳이 최적화할 필요가 없습니다.

테스트할 Oracle 인스턴스를 가지고 있지 않고 Oracle에서 전체 텍스트 인덱싱을 사용한 적은 없지만 인라인 뷰를 사용하면 일반적으로 좋은 성능을 발휘할 수 있습니다. 이는 사용자가 염두에 둔 인덱스를 대체할 수 있습니다.contains()가 포함된 경우 다음 구문이 적합합니까?

이 인라인 뷰를 통해 그룹 43의 행에 대한 PK 값을 얻을 수 있습니다.

             (
             select T.pkcol
             from T
             where group = 43
             )

그룹에 정규 인덱스가 있고 카디널리티가 낮지 않은 경우 이 집합을 빨리 가져올 수 있습니다.그런 다음 해당 집합을 T로 다시 연결합니다.

           select * from T
           inner join
            (
             select T.pkcol
             from T
             where group = 43
             ) as MyGroup

           on T.pkcol = MyGroup.pkcol
           where contains(text, '%blah%') > 0

Optimizer가 PK 인덱스를 사용하여 조인을 최적화한 다음 포함 술어를 그룹 43 행에만 적용할 수 있기를 바랍니다.

언급URL : https://stackoverflow.com/questions/7358137/oracle-full-text-search-with-condition