꿈꾸는 개발자, DBA 커뮤니티 구루비

인덱스 테크닉의 종류

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초로 엄청난 향상을 가져왔음