오라클 인덱스 개요 (by darkturtle) [2012.11.09]
오라클 인덱스 개요
h1.오라클 제공하는 인덱스 유형
1) B*Tree 인덱스
'B'는 Binary 가 아닌 Balanced 를 의미
키 값을 이용해서 개별적인 로우 또는 로우의 특정 범위를 빠르게 접근할 수 있도록 해주며,
몇 번의 읽는 과정만으로 정확한 로우를 서칭
1.1)인덱스 구조 테이블(IOT, Index oraganized table)
B*Tree 구조로 저장되는 테이블
IOT 데이터는 기본 키에 의해 정렬되어 저장된다.
1.2)B*Tree 클러스터 인덱스(B*Tree cluster index)
클러스터 키를 인덱싱 하는 것
B*Tree 인덱스 - 테이블 로우를 가리키는 인덱스 키 가짐
B*Tree 클러스터 인덱스 - 로우가 포함된 블록을 가리키는 클러스터 키 가짐
1.3)내림차순 인덱스(descending index)
올림차순 인덱스 - 작은 값부터 큰 값으로 오름차순으로 정렬 ( Default )
내림차순 인덱스 - 큰 값부터 작은 값으로 내림차순으로 정렬된 데이터를 위한 인덱스
1.4)리버스 키 인덱스(reverse key index)
키 값을 뒤 칼럼 값에서부터 역순으로 뒤집은 B*Tree 인덱스
계속 증가하는 값에 대한 인덱스를 고르게 분산하기 위한 인덱스
리버스 인덱스 구성을 통해서 근접하던 값들이 다수의 블록에 분산 저장됌
테이블 칼럼 값 - 987500, 987501, 987502
리버스 키 인덱스 - 205789, 105789, 005789
1.5)비트냅 인덱스(Bitmap Index)
B*Tree Index : 인덱스 엔트리 : 테이블 로부 = 1:1
Bitmap Index : 인덱스 엔트리 : 테이블 로부 = 1:M
Cardinality가 작은 읽기 전용 데이터에 적합(DW 용)
INSERT,UPDATE,DELETE 에 따른 비트냅 인덱스 동시성 이슈로 OLTP 에 부적합
1.6)비트맵 조인 인덱스(bitmap join index)
테이블 속성의 반정규화를 대신하여 인덱스 구조를 통해 반정규화하는 것
두 개 이상의 테이블 조인 결과에 대해 비트냅 인덱스 구성(OLTP 에 부적합)
1.7)함수 기반 인덱스(Function-based Index)
칼럼값 자체가 아니라 컬럼에 대한 함수의 계산된 결과를 저장하는 B*Tree 또는 비트앱 인덱스
1.8)애플리케이션 도메인 인덱스(application domain index)
오라클 내부 또는 외부 사용자가 만들어서 저장하는 인덱스 ( 오라클 텍스트 인덱스 )
B*Tree 인덱스 구조
[그림 11-1] B*Tree 인덱스 구조
- Branch Block
: 인덱스 구조를 이동하는 데 사용됨 - Leaf node / Leaf Block
: 모든 인덱스 키와 인덱싱 되는 테이블 로우를 가리키는 rowid 를 포함
양방향 linked list 구조, 리프 노드에서 시작점을 찾은 다음에는 값의 정렬된 순서로 읽어 나가면됌
CREATE INDEX I ON T ( X, Y )
- CREATE INDEX I ON T ( X, Y, ROWID ) 의미
- Unique Index => 인덱스 키 값만으로 정렬
- Non-Unique Inex => 인덱스 키 + Rowid 로 정렬
- 모든 리프 블록이 트리 안에서 모두 동일한 레벨에 존재
즉 어떤 조건이라도 루트 블록부터 리프 블록까지 동일한 숫자의 블록을 방문한다는 의미
- 인덱스 루트 ~ 리브 블록 수를 나타내는 용어
1. HEIGHT - ANALYZE INDEX <NAME> VALIDATE STRUCTURE 를 통해서 INDEX_STATS 통해서 확인
2. BLEVEL - DBMS_STATS 를 통해서 DBA_INDEXES 에서 확인, BLEVEL = HEIGHT - 1
SQL> set autotrace traceonly statistics
SQL> SELECT ID
2 FROM BIG_TABLE WHERE ID = 42 ;
no rows selected
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets <===
0 physical reads
0 redo size
319 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> SELECT ID
2 FROM BIG_TABLE WHERE ID = 12345 ;
no rows selected
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets <===
0 physical reads
0 redo size
319 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> SELECT ID
2 FROM BIG_TABLE WHERE ID = 1234567 ;
no rows selected
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets <==
0 physical reads
0 redo size
319 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select owner, index_name, BLEVEL from dba_indexes where index_name='BIG_TABLE_PK';
OWNER INDEX_NAME BLEVEL
------------------------------ ------------------------------ ----------
OPS$ORACLE BIG_TABLE_PK 2
- 같은 consistent read 를 보임 !!!
- BLEVEL = HEIGHT - 1
- B*Tree 는 큰 테이블과 작은 테이블에서 모두 잘 작동하며,
테이블의 크기가 커짐에도 성능 저하가 거의 없는 범용 목적 인덱스 기법
인덱스 키 압축
- 복합컬럼(다수 컬럼) 인덱스의 중복을 제거하는 것(반복되는 공간에 대한 공간 오버헤드를 줄이는 것)
- 모든 엔트리는 접두사(prefix) 와 접미사(suffix) 두분으로 구분
- 접두사는 복합 컬럼의 앞부분으로 중복되는 값이 많은 존재하는 부분
- 접미사는 인덱스 키 컬럼의 뒷부분으로 같은 접두사앞에서 고유 값을 가짐
- I/O 시간이 줄어드는 반면, CPU 시간이 증가하는 반대급부가 발생한
SQL> create table t
2 as
3 select * from all_objects
4 where rownum <= 50000 ;
Table created.
SQL> create index t_idx on t ( owner, object_type, object_name );
Index created.
SQL> analyze index t_idx validate structure ;
Index analyzed.
SQL> create table idx_stats
2 as
3 select 'noncompressed' what, a.* from index_stats a ;
Table created.
SQL> !cat compress_index_test.sql
drop index t_idx ;
create index t_idx on t ( owner, object_type, object_name ) compress &1 ;
analyze index t_idx validate structure ;
insert into idx_stats select 'compress &1', a.* from index_stats a ;
SQL> @compress_index_test.sql
Index dropped.
Enter value for 1: 1
old 1: create index t_idx on t ( owner, object_type, object_name ) compress &1
new 1: create index t_idx on t ( owner, object_type, object_name ) compress 1
Index created.
Index analyzed.
Enter value for 1: 1
old 1: insert into idx_stats select 'compress &1', a.* from index_stats a
new 1: insert into idx_stats select 'compress 1', a.* from index_stats a
1 row created.
SQL> commit ;
Commit complete.
이하 생략
SQL> set linesize 150
SQL> select what, height, lf_blks, br_blks, btree_space, opt_cmpr_count, opt_cmpr_pctsave
2 from idx_stats ;
WHAT HEIGHT LF_BLKS BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------------- ---------- ---------- ---------- ----------- -------------- ----------------
noncompressed 3 347 3 2793096 2 28
compress 1 3 308 3 2480644 2 19
compress 2 3 247 3 1994108 2 0
compress 3 3 387 3 3110748 2 35
- ANALYZE 명령어는 통계정보 수집이 아닌 인덱스 구조 확인, 체인 Row 확인에 유용
- OPT_CMPR_COUNT => 최적의 압축 칼럼 수 제공
- OPT_CMPR_PCTSAVE => 최적의 압축 시, 절약되는 공간
- 1개 칼럼을 압축할 경우는 압축하지 않은 인덱스의 89% 정도 크기
LEAF_BLOCK 의 수도 줄어듦 ( 347 -> 308 )
HEIGHT 변경 없음 - 2개 컬럼을 압축할 경우는 압축하지 않은 인덱스의 71% 정도 크기
LEAF_BLOCK 의 수도 줄어듬 ( 347 -> 247 )
HEIGHT 변경 없음 ( 책은 3->2 ) - 3개 컬럼을 압축할 경우 압축 하지 않은 인덱스의 111% 정도 크기 (오히려 증가)
LEAF_BLOCK 의 수가 늘어남 ( 347 -> 387 )
HEIGHT 변경 없음- 접두사의 중복은 제거하여 반복되는 공간은 절약할 수 있었지만,
리프 블록에 압축 스키마 정보 바이트 크기의 오버헤드를 추가했지 때문에
압축된 키에 OBJECT_NAME 컬럼을 추가하여, 키를 거의 유일하게 만들어서 실제로 중복된 값이 존재 하지 않게 하였다.
거의 모든 인덱스 키에 4바이트를 추가해서 중복되는 값이 없게 되었다.
- 접두사의 중복은 제거하여 반복되는 공간은 절약할 수 있었지만,
- 공짜는 없다.
압축되어 I/O 가 좋아지는 대신 CPU 사용률이 높아진다.
리버스 키 인덱스
- 인덱스 키 칼럼을 역으로 뒤집어 인덱스 구성
- 먼저 '왜 그것이 필요한가?' 를 생각해보자
오라클 RAC 환경에서 시퀀스 값 또는 타임스탭프 값으로 생성되는 컬럼의 인덱스와
같이 오른쪽(right-hand-side)으로 증가하는 인덱스에 대한 리프 블록의 경합을 줄이기 위해 고안 - RAC 환경에서 공간적인 측면에서는 다소 비효율적이지만 데이터를 모든 리프 블록에 분산하여 저장
[ 싱글 인스턴스 환경에서도 경합을 줄이는데 유용,
오른쪽으로 증가하는 인덱스에서 발생하는 buffer busy wait 이벤트를 경감목적으로 사용 ]
*[ 참고 - http://jjon.tistory.com/24 ]
SQL> select 90101, dump(90101,16) from dual
2 union all
3 select 90102, dump(90102,16) from dual
4 union all
5 select 90103, dump(90103,16) from dual ;
90101 DUMP(90101,16)
---------- ---------------------
90101 Typ=2 Len=4: c3,a,2,2
90102 Typ=2 Len=4: c3,a,2,3
90103 Typ=2 Len=4: c3,a,2,4
- 리버스 키 물리구조 체크 예제
- 이 값을 역순으로 한다면(하나의 칼럼 값이다. 칼럼 값 집합 아님 !!! )
c3,a,2,2 ==> 2,2,a,c3
c3,a,2,3 ==> 3,2,a,c3
c3,a,2,4 ==> 4,2,a,c3 - 인접한 숫자들이 역순으로 뒤집을 경우, 서로 멀리 떨어지게 된다.
- 동일한 블록(가장 오른쪽 블록)을 찾아가는 RAC 인스턴스의 수를 감소 시키고,
- RAC 인스턴스 간에 통신(Cache Fusion)하는 블록의 수 경감
- 단점 : 일반적인 인덱스가 적용되는 모든 경우에 사용할 수 없음
다음과 같은 범위 조건은 사용될 수 없는데,
X 컬럼에 사용한 리버스 키 인덱스는 쓸모 없게 될 것이다.
- Reverse 인덱스 사용 못하는 경우 ( 범위 조건 )
where x > 5 인 경우
인덱스의 데이터가 X 값으로 정렬되어 저장되어 있지 않고 REVERSE(엑스)에 의해 저장됌
X>5 범위 스캔은 리버스 인덱스를 사용할 수 없다. - Reverse 인덱스 사용 가능한 경우 ( = 조건 )
WHERE X = 5 인 경우
리버스 키 인덱스에서 범위 스캔을 사용 가능한 경우
결합 인덱스(X,Y) 이고
WHERE X = 5 인 경우... X 가 리딩 컬럼 이므로.
오라클은 (X||Y)의 바이트를 역순으로 만드는 것이 아니라
REVERSE(X)||REVERSE(Y)로 저장
- 정말 그럴까 검증
SQL> drop table t;
Statement Processed.
Elapsed: 00:00:00.171
SQL> create table t
as
select 0 id, a.*
from all_objects a
where 1=0;
Statement Processed.
SQL> CREATE INDEX XAK1_T ON T ( ID , OBJECT_ID) REVERSE TABLESPACE TOOLS;
Statement Processed.
SQL> SELECT INDEX_TYPE FROM DBA_INDEXES WHERE INDEX_NAME ='XAK1_T';
INDEX_TYPE
---------------------------
NORMAL/REV
1 rows selected.
begin
for x in ( select rownum r, all_objects.* from all_objects )
loop
insert into t
( id, owner, object_name, subobject_name,
object_id, data_object_id, object_type, created,
last_ddl_time, timestamp, status, temporary,
generated, secondary )
values
( s_1.nextval, x.owner, x.object_name, x.subobject_name,
x.object_id, x.data_object_id, x.object_type, x.created,
x.last_ddl_time, x.timestamp, x.status, x.temporary,
x.generated, x.secondary );
if( mod(x.r,100)= 0 )
then
commit ;
end if;
end loop;
commit;
end;
SQL> SELECT MIN(OBJECT_ID), MAX(OBJECT_ID) FROM T;
MIN(OBJECT_ MAX(OBJECT_
----------- -----------
2 954732
1 rows selected.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'OPS$ORACLE', TABNAME=>'T', CASCADE => TRUE)
PL/SQL executed.
SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL;
Statement Processed.
SELECT /*+ gather_plan_statistics */ *
FROM T A
WHERE OBJECT_ID = 954732 ;
select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.02 | 880 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=954732)
SELECT /*+ gather_plan_statistics */ *
FROM T A
WHERE ID = 954730 ;
select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 0 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN | XAK1_T | 1 | 1 | 0 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=954730)
SELECT /*+ gather_plan_statistics INDEX(A) */ *
FROM T A
WHERE OBJECT_ID = 954732 ;
select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 1 |00:00:00.03 | 259 |
|* 2 | INDEX FULL SCAN | XAK1_T | 1 | 1 | 1 |00:00:00.03 | 258 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=954732)
filter("OBJECT_ID"=954732)
*Restrictions on Reverse Indexes Reverse indexes are subject to the following restrictions:
You cannot specify NOSORT with this clause.
You cannot reverse a bitmap index or an index on an index-organized table.
- Reverse Index를 통한 Buffer Busy 검증
SQL> !cat reverse_test.sql
create table t
as
select 0 id, a.*
from all_objects a
where 1=0 ;
alter table t
add constraint t_pk
primary key (id)
using index ( create index t_pk on t(id) &index_type tablespace tools );
create sequence s cache 1000 ;
SQL> @reverse_test.sql
create table t
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Enter value for index_type:
old 4: using index ( create index t_pk on t(id) &index_type tablespace tools )
new 4: using index ( create index t_pk on t(id) tablespace tools )
Table altered.
Sequence created.
SQL> !cat cr_do_sql.sql
create or replace procedure do_sql
as
begin
for x in ( select rownum r, all_objects.* from all_objects )
loop
insert into t
( id, owner, object_name, subobject_name,
object_id, data_object_id, object_type, created,
last_ddl_time, timestamp, status, temporary,
generated, secondary )
values
( s.nextval, x.owner, x.object_name, x.subobject_name,
x.object_id, x.data_object_id, x.object_type, x.created,
x.last_ddl_time, x.timestamp, x.status, x.temporary,
x.generated, x.secondary );
if( mod(x.r,100)= 0 )
then
commit ;
end if;
end loop;
commit;
end;
/
SQL> @cr_do_sql.sql
Procedure created.
SQL> show error
No errors.
SQL>
SQL> set timing on
SQL> exec do_sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.83
SQL>
SQL> drop sequence s ;
Sequence dropped.
Elapsed: 00:00:00.03
SQL> drop table t;
Table dropped.
Elapsed: 00:00:00.05
SQL> @reverse_test.sql
Table created.
Elapsed: 00:00:00.07
Enter value for index_type: reverse
old 4: using index ( create index t_pk on t(id) &index_type tablespace tools )
new 4: using index ( create index t_pk on t(id) reverse tablespace tools )
Table altered.
Elapsed: 00:00:00.02
Sequence created.
Elapsed: 00:00:00.00
SQL> exec do_sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.19
-- 2명이서
SQL> drop table t;
Table dropped.
Elapsed: 00:00:00.13
SQL> drop sequence s ;
Sequence dropped.
Elapsed: 00:00:00.01
SQL> @reverse_test.sql
Table created.
Elapsed: 00:00:00.07
Enter value for index_type:
old 4: using index ( create index t_pk on t(id) &index_type tablespace tools )
new 4: using index ( create index t_pk on t(id) tablespace tools )
Table altered.
Elapsed: 00:00:00.02
Sequence created.
Elapsed: 00:00:00.01
SQL> exec do_sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.38
SQL> set timing on
SQL> exec do_sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.41
SQL> drop sequence s ;
Sequence dropped.
Elapsed: 00:00:00.01
SQL> drop table t;
Table dropped.
Elapsed: 00:00:00.23
SQL> @reverse_test.sql
Table created.
Elapsed: 00:00:00.07
Enter value for index_type: reverse
old 4: using index ( create index t_pk on t(id) &index_type tablespace tools )
new 4: using index ( create index t_pk on t(id) reverse tablespace tools )
Table altered.
Elapsed: 00:00:00.03
Sequence created.
Elapsed: 00:00:00.00
SQL> select index_type from dba_indexes where index_name ='T_PK';
INDEX_TYPE
---------------------------
NORMAL/REV
Elapsed: 00:00:00.03
SQL> exec do_sql;
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.26
SQL> exec do_sql;
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.19
[표11-2] 두명의 사용자 환경에서 리버스 키에 대한 PL/SQL과 Pro*C비교
[표11-2] 5명의 사용자 환경에서 리버스 키에 대한 PL/SQL과 Pro*C비교
[표11-2] 10명의 사용자 환경에서 리버스 키에 대한 PL/SQL과 Pro*C비교
- 두명이 동시에 Insert 작업 시에, 리버스 인덱스는 일반 인덱스보다 추가적인(CPU) 작업 수행함
- 그런, 이 추가적인 작업은 동시 사용자 증가에 따른 Buffer Busy Wait Event를 상쇄함
- PRO*C ==> LOGFILE SYNC (100ROW 마다 발생), BUFFER BUSY 동일하게 영향받음
- PL/SQL ==> LOGFILE SYNC 최적화 , BUFFER BUSY 동일하게 영향받음
- log file sync wait 이벤트가 거의 없는 PL/SQL 은 Reverse Index 를 통해서
Buffer busy wait 이벤트가 제거되면 매우 효과적