엑기스 | Context Index의 이해와 활용방법

 

개요

보편적으로 오라클을 포함한 다양한 관계형 데이터베이스는 B*Tree Index를 사용한 인덱싱 기법을 도입하여 사용했다. B*Tree Index는 일부 데이터 타입 (numbers, string, etc)에 대하여 다양한 접근 방법을 제공함으로써 DBMS에서 가장 많이 사용하는 Index가 되었다.

 

B*Tree Index의 확장성 부족이라는 단점을 채우기 위하여 Function based Index, Reverse Index, 그리고 Bitmap Index와 같은 다양한 Index를 만들었지만, 사용자들의 요구를 완벽하게 만족시킬 수는 없었다. 이에 사용자의 요구를 만족시키기 위하여 오라클은 8i version부터 사용자가 직접 인덱스의 타입을 생성하고 적용할 수 있는 Domain Index를 해결책으로 제시하였다.

 

Oracle에서 제공하는 Domain Index 중에서도 Oracle Text Index는 SQL 와일드카드 매칭 기능을 보완하는 기능을 제공하며, 구조형 / 비구조형 문서를 검색하는 용도로 활용된다. 또한, Oracle Text Index는 대량의 텍스트에서 키워드 검색을 수행함에서 성능적인 부분에서 상당한 이점을 기대할 수 있다. 해당 문서는 이러한 Oracle Text Index 중 일반적으로 자주 사용하는 Context Index 대해서 정확하게 이해하고 효과적으로 사용하고자 하는 목적으로 만들어졌다.

 

 

Oracle Text Index 구성 요소

Oracle Text Index는 논리적으로 context type, ctxcat type, ctxrule type으로 이루어지며 그중에서 Context Index 물리적으로 $I, $K, $R, $N table로 구성되어 있다. 각각의 구성 요소에 대해서 아래에서 자세하게 확인해보자.

Oracle Text Index Types

Oracle Text Index는 각각의 성격 및 Operator에 따라서 4가지 Type으로 분류할 수 있다. Oracle Text Index의 논리적 구성요소 4가지에 대해서 간략히 정리하여 보면 아래와 같다.

• Context type : 크기가 큰 문서의 full – text 검색에 유리
• Ctxcat type : full-text 검색과 함께 가격, 수량, 위치 등의 구조적인 검색에 유리
• Ctxrule type : 문서를 분석하여 분류에 유리

각각의 Oracle Text Index는 특성 및 사용 형태, 사용 방법이 다르므로 정확하게 동작 방식에 대해서 이해하고 상황에 적절하게 사용해야 한다. 본 글에서는 가장 일반적으로 사용하는 Context Type Index에 대해서 중점적으로 다루었다.

Context Index Objects

Oracle Text Index 중 Context Index는 저장하는 구조에 따라서 물리적으로 4가지 구성요소로 나누어진다. Context Text Index의 물리적 구성요소 4가지에 대해서 간략히 정리하여 보면 아래와 같다.

• $I 테이블 : 나누어질 수 있는 문자열을 “토큰(token)”이라고 불리는 텍스트의 작은 단위로 분할하여 저장
• $K 테이블 : 테이블 각행에 임의로 부여한 DOCID를 통해 ROWID를 저장
• $R 테이블 : 테이블 각행의 ROWID를 통해 임의로 부여된 DOCID를 저장
• $N 테이블 : DELETE 된 DOCID를 저장

해당 테이블 및 인덱스는 Context Index 생성 시 자동으로 생성이 되며, 상황에 따라서 생성에서 제외되는 Segment도 있다. 가령 예를 들어 특정 테이블에 Insert만 수행된다고 가정한다면, Context Index 생성 시 Delete 작업이 없으므로 $R 테이블과 $N 테이블은 필요하지 않기에 생성되지 않고, $I 테이블, $R 테이블, 그리고 각 테이블에 해당하는 Index만 생성될 것이다. 각 테이블의 성격에 대해서 정확하게 이해하고 있다면 테이블의 성격에 따라서 중점적으로 모니터링 해야 할 테이블에 대해 명확하게 판단할 수 있다.

 

 

Context Index 동작 원리

TEXT

EXEM

MFO MFM

DASHBOARD

BIG DATA

(원본 테이블)

 

Context Index는 생성 시에 원본 테이블의 각 ROW에 대하여 DOCID를 부여하고, 각 문자열을 토큰 단위로 분할하여 $I 테이블에 저장한다.

 

TEXT	TOKEN_INFO
EXEM	1-1
MFO	2-1
MFM	2-2
DASHBOARD	3-1
BIG	4-1
DATA	4-2

($| 테이블)

 

“EXEM”에 해당하는 토큰은 1-1, “MFO”에 해당하는 토큰은 2-1, “MFM”에 해당하는 토큰은 2-2, “DASHBOARD”에 해당하는 토큰은 3-1, “BIG”에 해당하는 토큰은 4-1, “DATA”에 해당하는 토큰은 4-2 와 같이 지정된다. 또한, $I테이블에 대하여 성능 향상을 위하여 B*Tree Index 인 X$ 인덱스가 생성된다.

 

사용자가 “TEXT = BIG DATA” 라는 조건으로 검색하게 되면 “BIG”, “DATA”라는 2개의 토큰으로 분해되고 해당 토큰들은 $X 인덱스를 통해 $I 테이블로 ACCESS 하게 된다. $I 테이블로부터 TOEKN_INFO의 정보를 토대로 DOCID를 반환하게 되고, 해당 DOCID를 이용하여 $R 테이블에서 ROWID로 변환되어 원본 테이블을 참조한다.

 

 

Context Index 모니터링 방법

Context Index는 각 문자열에 대해서 토큰이라는 단위로 나누어서 저장하고, 각 토큰에 대한 위칫값, DOCID와 ROWID 변환에 필요한 데이터 등 많은 정보를 저장한다. 그러므로 Context Index를 생성한 후에는 항상 크기를 확인할 필요가 있다. Context Index는 내부적으로 여러 개의 테이블과 인덱스로 구성되기 때문에 각각의 Segments에 대해서 확인해야 한다. 아래 테스트를 통하여 Context Index의 크기에 대해서 모니터링하는 방법을 살펴보자.

 

Context Index 테스트 테이블 생성

<< Context Index 크기 확인 >>   -- TEST TABLE 생성 및 SQL_TEXT COLUMN에 대하여 CONTEXT INDEX 생성 SELECT A.OWNER ,        B.INDEX_NAME ,        B.INDEX_TYPE ,        A.SEGMENT_NAME ,        A.SEGMENT_TYPE ,        A.BYTES/1024 AS "SIZE(KB)" FROM   DBA_SEGMENTS A ,        DBA_INDEXES B WHERE  INSTR( A.SEGMENT_NAME , UPPER( B.INDEX_NAME ) , 1 ) > 0 AND    B.INDEX_TYPE ='DOMAIN' AND    B.OWNER = :B1 AND    B.TABLE_NAME = :B2 ORDER  BY 6 DESC;   -- BIND VARIABLE B1 = 'MFO8MON' B2 = 'DOMAIN_TEST'

 

 

Context Index 동작 방식에 대한 테스트

지금부터 Context Index의 생성 및 확인을 포함하여 동작 요건, 테이블의 데이터에 DML이 발생할 경우, 10046 Trace를 통한 내부 동작에 대해서 테스트를 통하여 확인해보자.

 

우선, TEST 테이블을 생성하고 Context Index를 생성하겠다.

 

Context Index 생성 및 확인

<< Context Index 생성 및 확인 >>   -- TEST TABLE 생성 및 SQL_TEXT COLUMN에 대하여 CONTEXT INDEX 생성 CREATE TABLE DOMAIN_TEST AS SELECT SQL_TEXT FROM   V$SQL;   CREATE INDEX DOMAIN_TEST_IDX ON DOMAIN_TEST( SQL_TEXT ) INDEXTYPE IS CTXSYS.CONTEXT;     -- TEST TABLE에 정상적으로 CONTEXT INDEX 생성 확인   SELECT INDEX_NAME ,        TABLE_NAME ,        STATUS ,        ITYP_OWNER ,        ITYP_NAME ,        DOMIDX_STATUS ,        DOMIDX_OPSTATUS FROM   DBA_INDEXES WHERE  INDEX_NAME ='DOMAIN_TEST_IDX';

 

 

Context Index 조건 절에 따른 사용 테스트

생성된 Context Index에 대해서 다양한 조건에 따른 사용 여부에 대해 아래 테스트를 통해 확인해보자.

 

Context Index 생성 및 확인

<<Query Operator 사용 여부에 따른 Context Index 사용 테스트>>   -- LIKE 조건절만 사용할 경우 조회 SQL 및 실행계획   SELECT SQL_TEXT FROM   DOMAIN_TEST A WHERE  SQL_TEXT LIKE 'UPDATE%';   ----------------------------------------------------------------------------------------------- | Id  | Operation               | Name            | Rows   | Bytes  | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |                     |          |          |     241 (100)|             | |*  1 |  TABLE ACCESS FULL | DOMAIN_TEST |   336  |   164K |      241   (0)| 00:00:01 | -----------------------------------------------------------------------------------------------     -- LIKE 조건절과 DOMAIN INDEX 사용 HINT 사용할 경우 조회 SQL 및 실행계획   SELECT /*+ INDEX(A DOMAIN_TEST_IDX) */ SQL_TEXT FROM   DOMAIN_TEST A WHERE  SQL_TEXT LIKE 'UPDATE%';   ---------------------------------------------------------------------------------------------- | Id  | Operation               | Name            | Rows  | Bytes  | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |                     |         |          |     241 (100)|             | |*  1 |  TABLE ACCESS FULL | DOMAIN_TEST |   336 |   164K |      241   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------     -- Query Operator(contains) 사용할 경우 조회 SQL 및 실행계획 SELECT SQL_TEXT FROM   DOMAIN_TEST A WHERE CONTAINS(SQL_TEXT,'UPDATE')>0;   ---------------------------------------------------------------------------------------------------------------- | Id  | Operation                                | Name                  | Rows  | Bytes | Cost (%CPU)| Time    | ---------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                   |                           |         |         |        6 (100)|            | |   1 |  TABLE ACCESS BY INDEX ROWID | DOMAIN_TEST       |      9 |  4626 |         6   (0)| 00:00:01 | |*  2 |   DOMAIN INDEX                      | DOMAIN_TEST_IDX |         |         |         4   (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------------

 

Text 검색을 위해 일반적으로 사용하는 Like 조건에 대해서는 Context Index가 있음에도 불구하고 Full Table Scan을 통해 결과를 출력한다. Like 조건과 함께 “INDEX(<table_alias> <index_name>)” Hint를 통하여 Context Index를 사용하도록 명시한 결과에서도 마찬가지로 Full Table Scan을 통해 결과를 출력하는 것을 확인할 수 있다.

 

위 결과 중 조건절에 Contains 구문을 사용한 Query에서만 정상적으로 Context Index를 사용하는 것을 확인할 수 있다. 그렇다면, Contains 구문의 사용법에 대해서 아래 테스트를 통하여 명확하게 알아보자.

 

Contains 구문 사용법에 대한 테스트

<<Query Operator 사용 여부에 따른 Context Index 사용 테스트>>   -- LIKE 조건절만 사용할 경우 조회 SQL 및 실행계획   SELECT SQL_TEXT FROM   DOMAIN_TEST A WHERE  SQL_TEXT LIKE 'UPDATE%';   ----------------------------------------------------------------------------------------------- | Id  | Operation               | Name            | Rows  | Bytes  | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |                     |         |          |     241 (100)|             | |*  1 |  TABLE ACCESS FULL | DOMAIN_TEST |   336 |   164K |      241   (0)| 00:00:01 | -----------------------------------------------------------------------------------------------     -- LIKE 조건절과 DOMAIN INDEX 사용 HINT 사용할 경우 조회 SQL 및 실행계획   SELECT /*+ INDEX(A DOMAIN_TEST_IDX) */ SQL_TEXT FROM   DOMAIN_TEST A WHERE  SQL_TEXT LIKE 'UPDATE%';   ---------------------------------------------------------------------------------------------- | Id  | Operation               | Name            | Rows  | Bytes  | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |                     |         |          |     241 (100)|             | |*  1 |  TABLE ACCESS FULL | DOMAIN_TEST |   336 |   164K |      241   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------     -- Query Operator(contains) 사용할 경우 조회 SQL 및 실행계획 SELECT SQL_TEXT FROM   DOMAIN_TEST A WHERE CONTAINS(SQL_TEXT,'UPDATE')>0;   ---------------------------------------------------------------------------------------------------------------- | Id  | Operation                               | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                  |                           |          |         |        6 (100)|            | |   1 |  TABLE ACCESS BY INDEX ROWID| DOMAIN_TEST       |       9 |  4626 |         6   (0)| 00:00:01 | |*  2 |   DOMAIN INDEX                     | DOMAIN_TEST_IDX |          |         |         4   (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------------

 

 

결론

지금까지 Context Index의 구성요소 및 동작 원리, 내부 동작 방식에 대해 확인해 보았다. 테스트를 통해서 확인한 것과 같이 Context Type Index는 이름에서부터 일반 Index와 같이 오해할 수 있지만, 실제로는 다양한 테이블과 인덱스로 구성된 하나의 집합이라고 볼 수 있다. 문자열에 대해서 빠르게 검색할 수 있다는 장점이 있지만, 원본 테이블의 문자열에 대한 값을 토큰 단위로 나누어 저장하고 해당 토큰에 대한 위치정보 및 기타 부수적인 정보들을 저장하고 있으므로, 테이블 스페이스의 여유 공간에 대해서 충분한 확인이 필요하다. 또한, Context Index는 SELECT, DML, DDL 시 일반 Index와는 다르게 동작하기 때문에 정확하게 동작 방식 및 원리에 대해서 이해하고 사용해야 한다.

 

Oracle에서는 8i부터 21c까지 버전이 올라감에 따라서 Oracle Text의 새로운 기능 및 옵션이 추가되고 있다. 그래서 앞으로는 활용도가 더 높아질 것으로 예상한다. 이처럼 계속해서 발전되고 있는 Context Index에 대해서 정확하게 이해하고 필요한 상황에서 적절하게 사용된다면, 문자열 검색 시 성능 개선 부분에서 만족스러운 결과를 얻을 수 있을 것이다.

 

 

 

 

 

 

 

기고 | 컨설팅본부 장기협

편집 | 사업기획팀 박예영

 

 

 

 

 

[NOW엑셈 161회] 다른 이야기도 궁금하시다면?
🎶 엑셈 뉴스룸 | 10 to 5, 일과 삶의 변화   ❗️ 엑셈 인사이트 | 2021년 APM의 현주소와 InterMax  💡 혁신스토리 | 혁신 기업들의 5가지 공통점  👨‍💻 엑기스 | Context Index의 이해와 활용방법  📁 IT뉴스 | 6월 테크뉴스 모음.zip  🤷‍♂️ PHILINNOVATOR | 그래서 '초연결'이 도대체 뭔가요?  💚 이벤트 | 환경 보호 댓글 이벤트