이펙티브 오라클 (2008년)
인덱싱 테크닉 0 0 3,235

by 구루비스터디 FBI [2009.04.30]


인덱스 테크닉의 종류

  • 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초로 엄청난 향상을 가져왔음
"구루비 데이터베이스 스터디모임" 에서 2008년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3569

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입