oracle index

 

 

 

인덱스 테크닉

- 인덱스 테크닉의 종류
1. B*Tree
2. Reverse Key Index
3. Descending Index
4. IOT
5. B*Tree Cluster Index
6. BITMAP Index
7. Function Based Index
8. Domain Index

 FBI 사용하기

 - FBI에서 가능한 기능
1. 대소문자와 무관한 검색 또는 정렬
2. 복잡한 방정식에 대한 검색
3. 독자적인 함수와 연산자를 구현하고 이들에 대해 검색함으로써 SQL 언어 확장

- B*Btree 인덱스는 NULL 엔트리를 포함하지 않음으로 아래에 소개하는 유용한 기능을 사용할 수 있음
1. 선택적으로 인덱스 생성하기
2. 선택적인 유일성 구현하기

선택적으로 인덱스 생성하기

- 테이블의 일부 행에만 인덱스 생성하는 것이 가능하다. 아래의 예제는 한 테이블 내의 한 컬럼 내에서 인덱싱 조건이 포함되는 경우임. 

Ex) 어떤 테이블에 'Y' 또는 'N'으로만 저장되는 컬럼이 있는데, 'N'으로 설정된 컬럼에 대해서만 인덱스를 생성하는 것에 대한 예제임

1. 테이블 ,인덱스 및 조회용 뷰 생성

SQL> create table t as   2  select 'Y' processed_flag, a.* from all_objects a; 테이블이 생성되었습니다. 경   과: 00:00:03.57 SQL> set timing on SQL> set timing off SQL> create or replace view v   2  as   3  select t.*,   4  case when processed_flag = 'N' then 'N'   5  else NULL   6  end processed_flag_indexed   7  from t; 뷰가 생성되었습니다. SQL> create index t_idx on   2  t( case when processed_flag = 'N' then 'N'   3  else NULL   4  end ); 인덱스가 생성되었습니다.

2. 데이터 변경 전과 후의 해당 인덱스 통계 확인

SQL> -- Before updating data 'N' in 100 rows SQL> select name,del_lf_rows,lf_rows,lf_blks from index_stats; NAME                           DEL_LF_ROWS    LF_ROWS    LF_BLKS ----------------------------- --------- -------- --------- T_IDX                                    0          0          1 SQL> update t set processed_flag='N' where rownum <= 100; 100 행이 갱신되었습니다. SQL> commit; 커밋이 완료되었습니다. SQL> analyze index t_idx validate structure; 인덱스가 분석되었습니다. SQL> -- After updating data 'N' in 100 rows SQL> select name,del_lf_rows,lf_rows,lf_blks from index_stats; NAME                           DEL_LF_ROWS    LF_ROWS    LF_BLKS ----------------------------- --------- -------- --------- T_IDX                                    0        100          1

   - 처음 인덱스 생성 후, 내부 통계를 살펴 보면 한 건의 인덱스 row가 존재하지 않는다.
      이는 데이터가 'N'인 경우에만 선택적으로 인덱스를 생성하게 했기 때문이다. 'Y'의 경우는 null로 대체되므로 인덱스 데이터가 생성되지 않는다.
   - 그러나, 데이터의 일부를 'N'으로 수정 후 통계를 확인해 보면 그 수만큼의 인덱스 row가 존재하게 된다.
       모든 데이터에 대해서 인덱스를 가지고 있는 것이 아니기 때문에 아주 안정적인 상태를 유지하게 된다.

3. 데이터 변경 후, 인덱스에 대한 통계
  * FBI로 참조하고 있는 일부 테이블 행에 대해 변경을 가한 경우임('N' -> 'Y')

SQL> update t set processed_flag='Y' where rownum <= 2; 2 행이 갱신되었습니다. SQL> commit; 커밋이 완료되었습니다. SQL> analyze index t_idx validate structure; 인덱스가 분석되었습니다. SQL> select name,del_lf_rows,lf_rows,lf_blks from index_stats; NAME                           DEL_LF_ROWS    LF_ROWS    LF_BLKS ----------------------------- --------- -------- --------- T_IDX                                    2        100          1

   - DEL_LF_ROWS 값이 늘어났고, 이 값은 변경된 행이 새로운 인덱싱 조건에 맞는다면 여기에 확보된 공간을 사용한다

선택적인 고유성 선택하기

- 특정 조건에서만 인덱스의 고유성을 유지하게 하는 경우
1. 선택적으로 행에 인덱스를 생성할 수 있다.
2. 인덱스들은 고유할 수 있으며, 따라서 고유성을 강제하는 데 사용될 수 있다.
3. Multiversioning에서 독자적으로 참조 무결성을 수행하려고 시도하는 경우에는 읽기 일관성 데이터베이스가 재난을 막을 수 있다.

Ex) STATUS가 'ACTIVE' 이라면 TEAMID와 JOB은 고유해야 하는 경우 

SQL> create table project 2 (project_ID number primary key, 3 teamid number, 4 job varchar2(100), 5 status varchar2(20) check (status in ('ACTIVE', 'INACTIVE')) 6 ); Table created. SQL> create UNIQUE index 2 job_unique_in_teamid on project 3 ( case when status = 'ACTIVE' then teamid else null end, 4 case when status = 'ACTIVE' then job else null end 5 ) 6 / Index created. SQL> insert into project(project_id,teamid,job,status) 2 values( 1, 10, 'a', 'ACTIVE' ); 1 row created. SQL> insert into project(project_id,teamid,job,status) 2 values( 2, 10, 'a', 'ACTIVE' ); insert into project(project_id,teamid,job,status) * ERROR at line 1: ORA-00001: unique constraint (OPS$TKYTE.JOB_UNIQUE_IN_TEAMID) violated

    - STATUS가 'ACTIVE'라는 상태에서는 반드시 유일성이 지켜져야 하므로 마지막 INSERT 구문은 실패한다.
     

도메인 인덱스 사용하기

- Domain Index: 오라클이 확장 가능한 인덱싱이라 부르는 것으로서 오라클에 의해 제공되는 인덱스처럼 동작하는 사용자 정의 인덱스 구조임

Ex) 오라클 자체적으로 내장된 Oracle Text의 Domain 인덱스 사용하기
      (Oracle Text는 대용량의 Text 데이터에 대한 복잡한 처리를 위해서 사용하는 기능이다. Oracle Document에 수록되어 있는 내용임)

1. 테스트를 위한 테이블 생성

SQL> create table source as select * from dba_source; 테이블이 생성되었습니다. SQL> select text from source where rownum < 10; TEXT ---------------------------------------------------------------------------------------------------- package STANDARD AUTHID CURRENT_USER is              -- careful on this line; SED edit occurs!   /********* Types and subtypes, do not reorder *********/   type BOOLEAN is (FALSE, TRUE);   type DATE is DATE_BASE;   type NUMBER is NUMBER_BASE;   subtype FLOAT is NUMBER; -- NUMBER(126) 9 개의 행이 선택되었습니다.

2. Domain Index가 없는 상태에서 TEXT 에 대한 LIKE 연산 수행

SQL> select * from source where upper(text) like '%SUBTYPE%'; 195 개의 행이 선택되었습니다. 경   과: 00:00:01.98 Execution Plan ---------------------------------------------------------- Plan hash value: 1389487928 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |   198 |   397K|  1401   (3)| 00:00:17 | |*  1 |  TABLE ACCESS FULL| SOURCE |   198 |   397K|  1401   (3)| 00:00:17 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(UPPER("TEXT") LIKE '%SUBTYPE%') Note -----    - dynamic sampling used for this statement Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets        6272  consistent gets        5096  physical reads           0  redo size       13677  bytes sent via SQL*Net to client         532  bytes received via SQL*Net from client          14  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         195  rows processed

  연산 특성 상 Full Scan을 수행할 수 밖에 없으며 약2초의 시간이 소요

3-1. Oracle Text의 Context Domain Index 를 생성하여 조회 한 경우

SQL> create index source_idx on source(text) indextype is stxsys.context; 인덱스가 생성되었습니다.  SQL> select * from source where contains(text,'subtype')>0; 187 개의 행이 선택되었습니다. 경   과: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 1106849809 ------------------------------------------------------------------------------------------ | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT            |            |   160 |   323K|    43   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| SOURCE     |   160 |   323K|    43   (0)| 00:00:01 | |*  2 |   DOMAIN INDEX              | SOURCE_IDX |       |       |     4   (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("CTXSYS"."CONTAINS"("TEXT",'subtype')>0) Note -----    - dynamic sampling used for this statement Statistics ----------------------------------------------------------         138  recursive calls           0  db block gets         539  consistent gets           6  physical reads           0  redo size       13257  bytes sent via SQL*Net to client         532  bytes received via SQL*Net from client          14  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         187  rows processed

    context Domain Index의 사용으로 187개의 행에 대해 rowid 연산으로 빠르게 가져옴
    속도는 기존의 2초에서 0.03초로 엄청난 향상을 가져왔음

문서에 대하여

• 최초작성자 : 김병두
• 이 문서는 오라클클럽 [대용량 데이터베이스 스터디] 모임에서 작성하였습니다.
• 이 문서의 내용은 THOMAS KYTE 저, 박민호 역의 이펙티브오라클을 참고했습니다.

문서정보

• 이 문서는 구루비에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=1507553&
• 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

 

출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=1507553

 

 

 

 

 

 

 

 

 

Oracle Text 인덱스를 사용할 때 주의해야 할 부분이 있습니다.

 

Text Index 를 만들고나서 테이블에 데이터가 추가되는 경우에 인덱스에 자동으로 반영되지 않습니다.

이 부분이 일반 인덱스와 좀 다른 점입니다.

 

일반 인덱스의 경우에는 테이블에 데이터가 추가되면 그 즉시 인덱스에도 함께 반영되는데, Text 인덱스는 아래와 같이 반영되지 않아서 조회가 되지 않는 상황이 발생합니다.

 

 

이걸 일반 인덱스처럼 똑같이 만들어 줄수도 있고, 특정 시점에 비동기적으로 수행되게 할 수도 있고,

완전 수동으로 관리할 수도 있습니다.

 

각각의 구문은 다음과 같습니다.

 

SQL> create index T1_IDX1 on T1(text) indextype is ctxsys.context parameters ('sync(on commit)');
     --> 테이블에 데이터 추가되면 바로 인덱스에 반영되게 할때

SQL> create index T1_IDX1 on T1(text) indextype is ctxsys.context parameters ('sync(every sysdate+1/24/60)');
     --> 1분에 한번씩 동기화 되도록 할때

SQL> exec ctx_ddl.sync_index('T1_IDX1');
     --> 수동으로... 변경이 있을때마다 사용자가 수동으로 실행..

 

Text Index 는 일반 인덱스에 비해 좀더 작업량이 많습니다.

텍스트 데이터를 구문분석해서 내부적으로 키워드 테이블을 만들어야 하기 때문에 매번 데이터가 발생할 때마다 갱신하는 식으로 하면 테이블에 DML 이 느려질 수 있습니다. 그래서 이런식으로 Sync 옵션을 제공하는 것 같습니다.

 

그러면, 수동으로 Sync 해볼까요?

 

 

네, 이제 잘 조회되네요..

 

 

근데, 이거 아래와 같이 에러가 날 수 있습니다. scott 유저에 ctx_ddl 패키지 실행권한이 없어서 에러가 납니다.

 

SQL> exec ctx_ddl.sync_index('T1_IDX1');
BEGIN ctx_ddl.sync_index('T1_IDX1'); END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'CTX_DDL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


/*
   위와 같은 에러 발생시 sys 유저에서 scott 유저에게 아래와 같이 권한을 부여해주면 됩니다.
*/
grant ctxapp to scott;