인덱스 테크닉의 종류
- 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초로 엄청난 향상을 가져왔음