IOT, 클러스터 테이블 활용,. (by aini79) [2014.06.28]
06. IOT, 클러스터 테이블 활용
(1) IOT란?
- IOT(Index-Organized Table): Random 액세스가 발생하지 않도록 테이들을 아예 인덱스 구조로 생성
- "인덱스 리프블록이 곧 데이터 블록" - 모든 행 데이터를 리프 블록에 저장
- 테이블을 인덱스 구조로 만드는 구문
SQL> create table index_org_t ( a number primary key, b varchar(10) )
organization index ;
- 일반적으로 사용하는 테이블을 '힙 구조 테이블'이라고 부르며, 테이블 생성 시 대개 생략하지만 organization 옵션을 명시할 수도 있다.
SQL> create table heap_org_t ( a number primary key, b varchar(10) )
organization heap ;
- 일반적인 힙 구조 테이블로의 데이터 삽입은 Random 방식 - Freelist로부터 할당 받은 블록에 정해진 순서 없이 값을 입력
- IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터 삽입
SQL 서버나 Sybase에서 말하는 클러스터형 인덱스(Clustered Index) 와 비슷하나 오라클 IOT는 PK 컬럼 순으로만 정렬 가능
IOT의 장점과 단점
- 장점
- CF가 좋음 - 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있기 때문에 Random 액세스가 아닌 Sequential 방식으로 데이터를 액세스 할 수 있고, 넓은 범위를 액세스 할 때 유리
- PK 컬럼 기준으로 데이터가 모여 있더라도 선행 컬럼이 '=' 조건이 아니면 조회 대상 레코드들이 서로 흩어져 많은 스캔을 유발하지만, 적어도 테이블 Random 액세스는 발생하지 않아 빠른 성능을 낼 수 있다.
- PK 인덱스를 위한 별도의 세그먼트를 생성하지 않아도 돼 저장공간을 절약
- 단점
- 데이터 입력 시 성능이 느리다
- 인덱스 분할(Split) 발생에 의한 성능 하락
- IOT는 인덱스 구조이므로 중간에 꽉 찬 블록에 새로운 값을 입력할 일이 종종 생기고 그럴 때 인덱스 분할(Split)이 발생. IOT가 PK 이외에 많은 컬럼을 갖는다면 리프 블록에 저장해야 할 데이터량이 늘어나 그만큼 인덱스 분할 발생빈도도 높아 짐. 컬럼 수가 많은 테이블이라면 인덱스 스캔 효율 때문이라도 IOT 대상으로 부적합.
- Direct Path Insert가 작동하지 않음에 따른 성능 제약
(2) IOT, 언제 사용할 것인가?
IOT가 유용한 상황
- 크기가 작고 NL 조인으로 반복 룩업(Lookup)하는 테이블
- 폭이 좁고 긴(=로우 수가 많은) 테이블
- 넓은 범위를 주로 검색하는 테이블
- 데이터 입력과 조회 패턴이 서로 다른 테이블
크기가 작고 NL 조인으로 반복 룩업(Lookup)하는 테이블
- 주로 코드성 테이블
- NL 조인에서 Inner 쪽 Lookup 테이블로서 액세스되는 동안 건건이 인덱스와 테이블 블록을 다 읽는 다면 비효율적 - IOT로 구성하면 테이블은 반복 액세스하지 않아도 됨
- PK이외 속성의 크기 때문에 인덱스 높이(height)가 증가한다면 역효과가 날 수 있으므로 이를 반드시 확인해야 함
폭이 좁고 긴(=로우 수가 많은) 테이블
- 주로 두 테이블 간 M:M 관계를 해소하기 위한 Association(=intersection) 테이블
- PK 이외의 컬럼이 전혀 없거나 있더라도 아주 소수에 불과(=폭이 좁다)
- PK 인덱스는 어차피 생성해야 하므로 그러면 테이블과 거의 중복된 데이터를 갖게 됨 - IOT로 구성하면 중복 피할 수 있음
넓은 범위를 주로 검색하는 테이블
- 주로 Between, Like 같은 조건으로 넓은 범위를 검색하는 테이블
- 특히 PK 이외 일반 컬럼이 별로 없는 통계성 테이블
데이터 입력과 조회 패턴이 서로 다른 테이블
- 영업사원의 일별 실적 집계 테이블
- 실적등록은 일자별로 진행, 실적조회는 주로 사원별로 수행
select substr(일자, 1, 6) 월도, sum(판매금액) 총판매금액, avg(판매금액) 평균판매금액
from 영업실적
where 사번 = 's1234'
and 일자 between '20090101' and '20091231'
group by substr(일자, 1, 6)
- 사번이 첫번째 정렬기준이 되도록 IOT를 구성해주면 한 블록만 읽고 처리 가능
Create table 영업실적(사번 varchar2(5), 일자 varchar2(8), ...
Constraint 영업실적_PK primary key (사번, 일자) ) organization index;
(3) Partitioned IOT
- 수억건에 이르는 일별상품별계좌별거래 테이블
- 아래 쿼리 처럼 넓은 범위의 거래 일자를 기준으로 특정 상품을 조회하는 쿼리가 가장 자주 수행
select 거래일자,지점번호,계좌번호,sum(거래량),sum(거래금액)
from 일별상품별계좌별거래
where 상품번호 = 'P7006050009'
and 거래일자 between '20080101' and '20080630'
group by 거래일자, 지점번호, 계좌번호
- 상품별 거래건수가 워낙 많아 위의 쿼리1에 인덱스를 사용하면 Random 액세스 부하가 심하게 발생
- 거래일자 기준으로 월별 Range 파티셔닝돼 있다면 인덱스를 이용하기 보다 필요한 파티션만 Full Scan하는 편이 오히려 빠르겠지만 다른 종목의 거래 데이터까지 모두 읽는 비효율이 생긴다.
- 상품번호 + 거래일자 순으로 정렬되도록 IOT를 구성하면 읽기 성능이야 획기적으로 개선되겠지만, 수억 건에 이르는 테이블을 단일 IOT로 구성하는 것은 관리상 부담스럽다.
- 단일 IOT를 구성하면 다음 쿼리는 수억 건에 이르는 데이터를 Full Scan해야만 한다.
select 거래일자,지점번호,계좌번호,sum(거래량),sum(거래금액)
from 일별상품별계좌별거래
where 거래일자 between '20080101' and '20080630'
group by 거래일자, 지점번호, 계좌번호
==> Partitioned IOT 로 구성
- 거래일자 기준 Range 파티셔닝
- 상품번호 + 거래일자 순으로 PK를 정의하고, IOT 구성
(4) Overflow 영역
- PK 이외 컬럼이 많은 테이블일수록 IOT로 구성하기에 부적합. 인덱스 분할에 의한 DML 부하는 물론, 검색을 위한 스캔량도 늘어나기 때문.
- 그럼에도 성능 향상을 위해 IOT가 꼭 필요하다면 Overflow 기능이 도움
- OVERFLOW TABLESPACE: Overflow 세그먼트가 저장될 테이블 스페이스를 지정한다. (* SYS_IOT_38645 등의 이름을 가진 세그먼트가 자동으로 생성됨)
- PCTTHRESHOLD: DEFAULT 값은 50이다. 예를 들어 이 값이 30이면, 블록 크기의 30%를 초과하기 직전 컬럼까지만 인덱스 블록에 저장하고 그 뒤쪽 컬럼은 모두 Overflow세그먼트에 저장한다.
물론 로우 전체 크기가 지정된 비율 크기보다 작다면 모두 인덱스 블록에 저장한다. 테이블을 생성하는 시점에 모든 컬럼의 데이터 타입 Max 길이를 합산한 크기가 이 비율 크기보다 작다면
Overflow 세그먼트는 불필요하지만 만약 초과한다면 오라클은 Overflow Tablespace 옵션을 반드시 지정하도록 강제하는 에러를 던진다. - INCLUDING: Including에 지정한 컬럼까지만 인덱스 블록에 저장하고 나머지는 무조건 Overflow 세그먼트에 저장한다.
- 오라클은 Pctthreshold 또는 Including 둘 중 하나를 만족하는 컬럼을 Overflow 영역에 저장한다.
즉, Including 이전에 위치한 컬럼이더라도 Pctthreshold에 지정된 비율 크기를 초과한다면 Overflow 영역에 저장된다. 반대의 경우도 마찬가지.
SQL> CREATE TABLE IOTTEST (
NO NUMBER CONSTRAINT IOTTEST_PK_NO PRIMARY KEY,
TITLE VARCHAR2(50),
CONTENTS VARCHAR2(500))
ORGANIZATION INDEX TABLESPACE INDX
PCTTHRESHOLD 40
INCLUDING TITLE
OVERFLOW TABLESPACE USERS;
- Overflow 영역을 읽을 때도 건건이 Random 액세스가 발생하므로 Overflow 세그먼트에 저장된 컬럼 중 일부를 자주 액세스해야 하는 상황이 발생한다면 IOT 액세스 효율은 급격히 저하된다.
- 다행히 Overflow 영역에서도 버퍼 Pinning 효과가 나타나기 때문에 연속적으로 같은 Overflow 블록을 읽을 때는 Random 블록 I/O 를 최소화 할 수 있다.
(5) Secondary 인덱스
- IOT는 secondary 인덱스 추가 가능성이 크지 않을 때만 선택하는 것이 바람직 하다.
MS-SQL 서버의 비클러스터형 인덱스 진화 과정
- SQL 서버에서 IOT처럼 인덱스 구조로 생성한 테이블을 '클러스터형 인덱스(Clustered Index)'라 부르고, 여기에 추가로 생성한 2차 인덱스들은 '비클러스터형 인덱스(Non-Clustered index)'라고 부른다.
- SQL Server 6.5 이전에는 좌측처럼 비클러스터형 인덱스가 클러스터형 인덱스 레코드를 직접 가리키는 rowid를 갖도록 하였다.
- 인덱스 분할에 의해 클러스터형 인덱스 레코드 위치가 변경될 때마다 비클러스터형 인덱스(한 개 이상일 수 있음) 가 갖는 rowid 정보를 모두 갱신해 주어야 한다.
- DML 부하가 심하다고 느낀 MS는 SQL Server 7.0 부터 비클러스터형 인덱스가 rowid 대신 클러스터형 인덱스의 키 값을 갖도록 구조를 변경하였다.
- 이제 키 값을 갱신하지 않는 한, 인덱스 분할 때문에 비클러스터형 인덱스를 갱신할 필요가 없어진 것이다.
- DML 부하가 줄어든 대신, 비클러스터형 인덱스를 이용할 대 이전보다 더 많은 I/O가 발생하는 부작용을 떠안게 되었다.
우측 그림처럼 비클러스터형 인덱스에서 읽히는 레코드마다 건건이 클러스터형 인덱스 수직탐색을 반복하기 때문이다. 당연히 클러스터형 인덱스 높이가 증가할수록 블록 I/O도 증가한다.
오라클 Logical rowid
- IOT레코드의 위치는 영구적이지 않기 때문에 오라클은 secondary 인덱스로부터 IOT 레코드를 가리킬 때 물리적 주소 대신 logical rowid를 사용한다. Logical rowid는 PK와 physical guess로 구성된다.
Logical Rowid = PK + Physical Guess
- Physical Guess는 secondary 인덱스를 "최초 생성하거나 재생성(Rebuild)한 시점"에 IOT레코드가 위치했던 데이터 블록 주소(DBA)
- 인덱스 분할에 의해 IOT 레코드가 다른 블록으로 이동하더라도 secondary 인덱스에 저장된 physical guess값은 갱신되지 않는다.
-> SQL 서버 6.5에서 발생한 것과 같은 DML 부하를 없애기 위함이고, 레코드 이동이 발생하면 정확한 값이 아닐 수 있기 대문에 'guess'란 표현을 사용한 것
- 이처럼 두 가지 정보를 다 가짐으로써 오라클은 상황에 따라 다른 방식으로 IOT를 액세스할 수 있게 하였고, 경우에 따라서는 두 가지 방식을 다 사용할 때도 있다.
- ①은 physical guess를 통해 IOT 레코드를 직접 액세스하는 것 (ex. "110페이지를 참조하라" <- 수시로 페이지 번호가 바뀌어 집필과정이 힘듦, 정확하지 않을 수 있음)
- ②는 PK를 통해 IOT를 탐색하는 것을 표현한 것 (ex. "1장 6절 5항을 참조하라" <- SQL 7.0부터의 참조방식에 해당)
- 오라클 IOT 는 두 가지 정보를 다 사용 (ex. "1장 6절 5항을 참조하라(110페이지)" <- 페이지 참조의 성공가능성에 효율성 좌우)
PCT_DIRECT_ACCESS
- DBA_ALL_USER_INDEXES 테이블에서 PCT_DIRECT_ACCESS 값을 확인가능
- secondary 인덱스가 유효한 physical guess를 가진 비율(Direct 액세스 성공 비율)을 나타내는 지표
- secondary 인덱스 탐색 효율을 결정짓는 매우 중요한 값
- 통계정보 수집을 통해 얻어짐
- 이 값이 100%미만이면 오라클은 바로 PK를 이용해 IOT를 탐색
- 100%일 때만 physical guess를 이용하는데 레코드를 찾아갔을 때 해당 레코드가 다른 곳으로 이동하고 없으면(PK 값 비교를 통해 이동 여부를 알 수 있음) PK로 다시 IOT를 탐색 -> 성능 하락
- 인덱스를 최초생성하거나 재생성(Rebuild)하고 나면(통계정보를 따로 수집해 주지 않더라도) pct_direct_access 값은 100
-> 이때는 physical guess로 바로 액세스하고, 성공률도 100%이므로 비효율이 없음
- 휘발성이 강한(-> 레코드 위치가 자주 변하는) IOT의 경우
- 시간이 지나면서 physical guess에 의한 액세스 실패 확률이 높아짐에 따라 성능이 점점 저하됨
- 통계 정보를 다시 수집해 pct_direct_access가 실제 physical guess 성공률(100미만)을 반영하도록 해 주어야 한다. 이 이후로부터 오라클은 physical guess를 거치지 않고 곧바로 PK로 IOT를 탐색할 것이다.
- 인덱스를 Rebuild하거나 update block references 옵션을 이용해 physical guess를 주기적으로 갱신해 준다면 가장 효과적
Alter index iot_second_idx REBUILD;
Alter index iot_second_idx UPDATE BLOCK REFERENCES;
- Secondary 인덱스 physical guess를 갱신하더라도 통계정보를 재수집한 이후부터 Direct 액세스로 전환된다. 인덱스 분할이 발생하더라도 통계정보를 재수집한 이후부터 PK를 이용하는 것과 마찬가지이다.
- Direct 액세스 성공 확률이 비교적 높은 상태에서 통계정보만 재수집하는 바람에 PK액세스로 전환하는 일이 생겨도 문제다. 통계 정보를 수집하는 순간 pct_direct_access가 100미만으로 떨어지기 때문이다.
비휘발성 IOT에 대한 Secondary 인덱스 튜닝 방안
- 비휘발성(읽기전용이거나 맨 우측 블록에만 값이 입력되어 IOT 레코드 위치가 거의 변하지 않는) 테이블 이라면 Direct 액세스 성공률이 높을 것.
-> 따라서 pct_direct_access 값이 100을 가리키도록 유지하는 것이 효과적인 튜닝 방안
-> 데이터가 쌓이는 양에 따라 한 달에 한 번 또는 일년에 한 번 정도만 physical guess를 갱신해 주면 됨
- 읽기 전용 테이블이면 pct_direct_access값이 100을 가리키도록 한 상태에서 더 이상 통계정보를 수집하지 않으면 됨
- 맨 우측에 지속적으로 값이 입력되는 경우라면 통계정보 수집이 필수적. 그럴 때에는 통계정보 수집 직후에 아래 프로시저를 이용해 값을 직접 설정해 주면 된다.
exec dbms_stats.set_index_stats (user, 't1_x1', guessq => 100);
휘발성 IOT에 대한 Secondary 인덱스 튜닝 방안
- 휘발성이 강한(IOT 레코드 위치가 자주 변하는) IOT에 secondary 인덱스를 추가할 때는 각별한 주의가 필요
- 처음 IOT를 설계할 때부터 이에 대한 고려가 있어야 함
- 휘발성이어서 physical guess에 의한 Direct 액세스 성공률이 낮다면 두가지 선택 가능
- 주기적으로 physical guess를 정확한 값으로 갱신 - 주로 secondary 인덱스 크기가 작을 때 가능
- 아예 physical guess가 사용되지 못하도록 pct_direct_guess값을 100 미만으로 떨어뜨림 - 인덱스 크기가 커서 주기적으로 physical guess 를 갱신해 줄 수 없을 때
-> 인덱스 분할이 어느 정도 발생한 상태에서 통계정보를 수집해 준다
- 두 번째 방법을 쓰면 일반 테이블을 인덱스 rowid로 액세스할 대보다 느려지겠지만 선택도가 매우 낮은 secondary 인덱스 위주로 구성해 주면 큰 비효율은 없다.
Right-Growing IOT에서 pct_direct_access가 100미만으로 떨어지는 이유
T1 테이블에 값을 1부터 1,000까지 차례로 입력했기 때문에 기존 레코드 위치가 바뀔 이유가 없다. 그런데 왜 secondary 인덱스의 pct_direct_access 값이 64로 떨어졌는가?
- 맨 우측 블록에만 값이 입력되는 Right-Growing IOT라면 인덱스 분할이 발생하더라도 기존 레코드의 주소 값이 바뀔 이유가 없다
새로운 인덱스 블록을 맨 우측에 추가해 거기에 값을 입력하기 때문 - 이를 가리키는 secondary 인덱스의 physical guess 정확도가 떨어 지는 이유는 인덱스높이가 2단계로 증가하면서 생기는 현상 때문
- 최초 IOT 인덱스 블록이 단 하나인 상태에서 블록이 꽉 차면 기존 100번 블록을 그대로 둔 채 101번 리프 블록과 102번 루트 블록이 새로 생길 것 같지만 실제로는 아래와 같이 처리된다.
- 100번 블록 레코드를 새로 할당한 101번 블록에 모두 복제하고 100번 블록은 루트 레벨로 올라 간다.
- 그리고 나서 새로 추가되는 값들은 102번 리프 블록에 입력된다. 이 때문에 100번 블록을 가리키던 secondary 인덱스 physical guess가 모두 부정확해진 것
- 이후 102번 블록 우측에 리프 블록이 계속 추가될 때는 IOT 레코드 이동이 발생하지 않는다.
- 물론 값이 중간으로 들어와 101번 또는 102번 블록이 50:50으로 분할될 때는 레코드 이동이 발생한다.
- 시간이 흘러 인덱스 레벨이 한 단계 더 올라가는 순간, 아까처럼 100번 블록이 통째로 다른 블록으로 복제된다.
- 100번 블록정보를 새로 할당한 103번 블록에 모두 복제하고, 100번 블록은 다시 루트 레벨로 올라간다.
- 그러나 이 경우 105번 블록이 추가될 뿐 다른 리프 블록에는 변화가 없어 secondary 인덱스 physical guess에도 영향을 주지 않는다.
오라클 인덱스 레벨이 이렇게 조정되는 이유?
- 인덱스 루트블럭은 인덱스를 탐색할 때 항상(Index Fast Full Scan만 제외하고) 시작점으로 사용되는 특별한 블럭
- Table Full Scan 시에는 매번 테이블 세그먼트 헤더로부터 익스텐트 정보를 얻고 스캔을 시작하지만 인덱스 스캔 시에는 실행계획에 담고 있는 루트 블록 주소로 곧바로 찾아감
- 따라서 루트 블록 주소가 바뀌면 해당 인덱스를 참조하는 많은 실행 계획이 영향을 받게 돼 시스템에 엄청난 파급 효과가 일어난다
(물론 실행계획이 만들어지는 하드파싱 시점에는 세그먼트 헤더를 읽어 루트 블록 주소값을 일어내지만 실행될 때마다 세그먼트 헤더를 조회하지는 않는다)
h5 .IOT_REDUNDANT_PKEY_ELIM
- Secondary 인덱스에는 physical guess와 함께 PK 컬럼 값을 저장
- 단점
- 만약 PK 컬럼 개수가 많은 IOT라면 데이터 중복 때문에 저장공간을 낭비하고 스캔 과정에도 많은 비효율을 안게 됨
- Secondary 인덱스가 여러 개면 문제가 더 심각
- 이점
SQL> create table emp_iot
2 ( empno, ename, job, mgr, hiredate, sal, comm, deptno
3 , constraint pk_emp_iot primary key ( empno ) )
4 organization index
5 as
6 select * from scott.emp;
테이블이 생성되었습니다.
SQL> create index iot_secondary_index on emp_iot( ename );
인덱스가 생성되었습니다.
SQL> set autotrace traceonly explain;
SQL> select /*+ index(emp_iot iot_secondary_index) */ * from emp_iot where ename
= 'SMITH';
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 8 (0)|
|* 1 | INDEX UNIQUE SCAN| PK_EMP_IOT | 1 | 87 | 8 (0)|
|* 2 | INDEX RANGE SCAN| IOT_SECONDARY_INDEX | 1 | | 1 (0)|
------------------------------------------------------------------------------
- Secondary 인덱스 컬럼이 ename 조건으로 모든 컬럼을 조회하니까 secondary 인덱스와 IO를 모두 액세스
SQL> select /*+ index(emp_iot iot_secondary_index) */ empno from emp_iot where
name = 'SMITH';
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)|
|* 1 | INDEX RANGE SCAN| IOT_SECONDARY_INDEX | 1 | 20 | 1 (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ENAME"='SMITH')
Note
-----
- 'PLAN_TABLE' is old version
- Secondary 인덱스 키 컬럼(ename)과 PK컬럼(empno)만 읽도록 변경
- Secondary 인덱스만 읽고 처리를 완료함 (이를 통해 secondary 인덱스에 실제 PK 컬럼이 저장돼 있음도 확인되었다.)
- 만약 컨텐츠 방문 테이블을 IOT로 구서하고 컨텐츠 ID + 방문일시를 키(Key)로 갖는 secondary 인덱스 '컨텐츠방문_X01'을 생성하면 아래와 같이 두 컬럼이 중복 저장될까?
- 오라클은 secondary 인덱스의 Logical Rowid가 인덱스키와 중복되면, 이를 제거하고 아래와 같이 고객 ID만을 저장한다.
- DBA_ALL_USER_INDEXES를 조회하면 IOT_REDUNDANT_PKEY_ELIM통계치를 볼 수 있는데, 이 값이 'YES'이면 secondary 인덱스 키와 PK 컬럼 간에 하나 이상 중복 컬럼이 있어 오라클이 이를 제거했음을 의미한다.
(6) 인덱스 클러스터 테이블
- 인덱스 클러스터 테이블은 클러스터 키(여기서는 deptno)값이 같은 레코드가 한 블록에 모이도록 저장
- 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해 클러스터 체인으로 연결
- SQL 서버나 Sybase에서 말하는 클러스터형 인덱스는 IOT에 가깝다.
- 인덱스 클러스터는 키 값이 같은 데이터를 물리적으로 한 곳에 저장해둘 뿐, IOT처럼 정렬하지는 않음
- 여러 테이블 레코드가 물리적으로 같이 저장될 수도 있다. 여러 테이블을 서로 조인된 상태로 저장해두는 것.
일반적으로는 하나의 데이터 블록이 여러 테이블에 의해 공유될 수 없다.
- 인덱스 클러스터 테이블을 구성하는 문장은 아래와 같다.
SQL> create cluster c_deptno# ( deptno number(2) ) index ;
클러스터가 생성되었습니다.
- 클러스터 테이블에 담기 전에 아래와 같이 클러스터 인덱스를 반드시 정의해야 한다. 왜냐하면, 클러스터 인덱스는 데이터 검색용도로 사용될 뿐만 아니라 데이터가 저장될 위치를 찾을 때도 사용되기 때문이다.
SQL> create index i_deptno# on cluster c_deptno#;
인덱스가 생성되었습니다.
- 클러스터 인덱스도 일반적인 B*Tree 인덱스 구조를 사용하지만, 해당 키 값을 저장하는 첫 번째 데이터 블록만을 가리킨다는 점에서 다르다.
- 클러스터 인덱스의 키 값은 항상 Unique하며, 레코드와 1:M 관계를 갖는다.(그림) 일반 테이블에 생성한 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖는다.
- 클러스터 인덱스를 스캔하면서 값을 찾을 때는 Random 액세스가(클러스터 체인을 스캔하면서 발생하는 Random 액세스는 제외하고) 값 하나당 한 번씩 밖에 발생하지 않는다.
- 클러스터에 도달해서 Sequential 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다는 게 핵심 원리
- 인덱스 클러스터 테이블의 종류
- 단일 테이블 인덱스 클러스터
- 다중 테이블 인덱스 클러스터
다중 테이블 인덱스 클러스터 예제
- c_deptno# 클러스터에 dept와 emp두 테이블이 같이 담기도록 정의
SQL> create table emp1
2 cluster c_deptno# (deptno)
3 as
4 select * from scott.emp;
테이블이 생성되었습니다.
SQL> create table dept1
2 cluster c_deptno# (deptno)
3 as
4 select * from scott.dept;
테이블이 생성되었습니다.
SQL> select owner, table_name from dba_tables where cluster_name = 'C_DEPTNO#';
OWNER TABLE_NAME
------------------------------ ------------------------------
SCOTT DEPT1
SCOTT EMP1
SQL> break on deptno skip 1;
SQL> select d.deptno, e.empno, e.ename
2 , dbms_rowid.rowid_block_number(d.rowid) dept_block_no
3 , dbms_rowid.rowid_block_number(e.rowid) emp_block_no
4 from dept1 d, emp1 e
5 where e.deptno = d.deptno
6 order by d.deptno;
DEPTNO EMPNO ENAME DEPT_BLOCK_NO EMP_BLOCK_NO
---------- ---------- ---------- ------------- ------------
10 7839 KING 2352 2352
7782 CLARK 2352 2352
7934 MILLER 2352 2352
20 7902 FORD 2350 2350
7876 ADAMS 2350 2350
7788 SCOTT 2350 2350
7369 SMITH 2350 2350
7566 JONES 2350 2350
30 7900 JAMES 2351 2351
7844 TURNER 2351 2351
7698 BLAKE 2351 2351
7654 MARTIN 2351 2351
7499 ALLEN 2351 2351
7521 WARD 2351 2351
- 위의 결과를 통해 'deptno가 같은 dept, emp 레코드'가 '같은 블록'에 담긴 것을 알 수 있음
인덱스 클러스터는 넓은 범위를 검색할 때 유리
SQL> create cluster objs_cluster# ( object_type VARCHAR2(19) ) index ;
클러스터가 생성되었습니다.
SQL> create index objs_cluster_idx on cluster objs_cluster#;
인덱스가 생성되었습니다.
SQL> create table objs_cluster -- 클러스터 테이블
2 cluster objs_cluster# ( object_type )
3 as
4 select * from all_objects
5 order by dbms_random.value ;
테이블이 생성되었습니다.
SQL> create table objs_regular -- 일반 테이블
2 as
3 select * from objs_cluster
4 order by dbms_random.value;
테이블이 생성되었습니다.
SQL> create index objs_regular_idx on objs_regular(object_type);
인덱스가 생성되었습니다.
SQL> alter table objs_regular modify object_name null;
테이블이 변경되었습니다.
SQL> alter table objs_cluster modify object_name null;
테이블이 변경되었습니다.
select /*+ index(t objs_regular_idx) */ count(object_name)
from objs_regular t
where object_type = 'TABLE';
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=622 pr=0 pw=0 time=4153 us)
1632 TABLE ACCESS BY INDEX ROWID OBJS_REGULAR (cr=622 pr=0 pw=0 time=8220 us)
1632 INDEX RANGE SCAN OBJS_REGULAR_IDX (cr=6 pr=0 pw=0 time=1678 us)(Object ID 53035)
- B*Tree 인덱스와 Heap Table은 테이블을 1.763번 Random 액세스하는 동안 616(=622-6) 개의 블록 I/O가 발생
select count(object_name)
from objs_cluster t
where object_type = 'TABLE';
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=21 pr=0 pw=0 time=943 us)
1632 TABLE ACCESS CLUSTER OBJS_CLUSTER (cr=21 pr=0 pw=0 time=1664 us)
1 INDEX UNIQUE SCAN OBJS_CLUSTER_IDX (cr=1 pr=0 pw=0 time=18 us)(Object ID 53031)
- B*Tree Cluster 인덱스를 통해 클러스터 테이블을 액세스 했더니 Random 액세스는 단 1회만 발생하였고, 클러스터를 스캔하는 동안 20(=21-1)개의 블록 I/O가 발생하였다.
- 클러스터 인덱스를 '=' 조건으로 액세스할 때는 항상 Unique Scan이 나타난다
클러스터 테이블과 관련한 성능 이슈
- 클러스터 테이블은 넓은 범위를 검색할 때 유리
- 하지만 클러스터 테이블이 실무적으로 자주 활용되지 않는 이유는 DML부하 때문(더 큰 이유는 클러스터 테이블 기능을 잘 몰라서..)
- 일반적인 Heap Table에 데이터를 입력할 때는 Freelist로 부터 할당 받은 공간에 정해진 순서없이 값을 입력
- IOT는 정렬 상태를 유지하면서 값을 입력
- 클러스터 테이블은 정해진 블록을 찾아서 값을 입력해야 하기 때문에 DML 성능이 다소 떨어짐.
특히, 전에 없던 값을 입력할 때는 블록을 새로 받아야 하기 때문에 더 느리다.
- 하지만 클러스터를 구성하지 않는 대신 인덱스를 생성할거면 DML부하는 어차피 비슷
- 특히 이미 블록이 할당된 클러스터 키 값을 입력할 때는 별차이가 없고, 만약 계속 새로운 값이 입력돼 많이 느려진다면 클러스터 키를 잘못 선정한 경우
- 클러스터 테이블을 구성하면서 기존에 사용하던 인덱스 두세 개를 없앨 수 있다면 DML부하가 오히려 감소 할 수도 있음
- 수정이 자주 발생하는 컬럼은 클러스터 키로 선정하지 않는 것이 좋지만, 삭제 작업 때문에 클러스터 테이블이 불리할 것은 없다.
- 다만, 전체 데이터를 지우거나 테이블을 통째로 Drop할 때 성능 문제가 생길 수 있다.
- 전체 데이터를 지울 때는 Truncate Table 문장을 쓰는 것이 빠른데, 클러스터 테이블에는 이 문장을 쓸 수가 없다. 단일 테이블 클러스터일 때도 마찬가지이다.
- Drop 하려 할 때도 내부적으로 건건이 delete가 수행된다.
- 전체 데이터를 빠르게 지우고 싶을 때는 아래와 같이 클러스터를 Truncate 하거나 Drop 하는 것이 빠르다.
(다중 테이블 클러스터일 때는 클러스터링된 테이블이 모두 삭제 되므로 주의해야 한다.)
Truncate cluster objs_cluster#;
Drop cluster objs_cluster# including tables;
- DML부하 이외의 고려 사항
- Direct Path Loading을 수행할 수 없다.
- 파티셔닝 기능을 함께 적용할 수 없다. (≠IOT)
- 다중 테이블 클러스터를 Full Scan할 때는 다른 테이블 데이터까지 스캔하기 때문에 불리하다.
SIZE 옵션
- 클러스터 키 하나당 레코드 개수가 많지 않을 때 클러스터마다 한 블록씩 통째로 할당하는 것은 낭비 이므로 하나의 블록에 여러 키 값이 같이 상주할 수 있도록 SIZE옵션을 둠
- 한 블록에 여러 클러스터 키 값이 같이 담기더라도 하나당 가질 수 있는 최소 공간(바이트 단위)를 미리 예약하는 기능
- 하나의 블록에 담을 최대 클러스터 키 개수를 결정
SQL> show parameter block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> drop cluster c_deptno# including tables;
클러스터가 삭제되었습니다.
SQL> create cluster emp_cluster# ( empno number(4) ) pctfree 0 size 2000 index ;
클러스터가 생성되었습니다.
SQL> create index emp_cluster_idx on cluster emp_cluster#;
인덱스가 생성되었습니다.
SQL> create table emp1
2 cluster emp_cluster# ( empno )
3 as
4 select * from scott.emp;
테이블이 생성되었습니다.
SQL> select emp.empno, emp.ename, dbms_rowid.rowid_block_number(rowid) block_no
2 from emp1 emp;
EMPNO ENAME BLOCK_NO
---------- ---------- ----------
7902 FORD 628
7934 MILLER 628
7369 SMITH 630
7499 ALLEN 630
7521 WARD 630
7566 JONES 630
7654 MARTIN 631
7698 BLAKE 631
7782 CLARK 631
7788 SCOTT 631
7839 KING 632
7844 TURNER 632
7876 ADAMS 632
7900 JAMES 632
- 위의 예처럼 블록 크기가 8KB 일 때 SIZE 옵션으로 2000 바이트를 지정하면 한 블록당 최대 4개 클러스터 키 만을 담을 수 있다.
- Emp 레코드 하나가 2,000 바이트에 한참 못 미치지만 각 블록당 4개의 empno를 입력하고는 새로운 블록에 담기 시작한다.
SQL> drop table emp1 purge;
테이블이 삭제되었습니다.
SQL> create table emp1
2 cluster emp_cluster# ( empno )
3 as
4 select empno, ename, lpad('*', 970) data -- 한 로우가 1000 바이트쯤 되도록
5 from scott.emp,(select rownum no from dual connect by level <= 10)
6 where empno = 7900;
테이블이 생성되었습니다.
SQL> select empno, ename, dbms_rowid.rowid_block_number(rowid) block_no
2 from emp1 ;
EMPNO ENAME BLOCK_NO
---------- ---------- ----------
7900 JAMES 630
7900 JAMES 630
7900 JAMES 632
7900 JAMES 632
7900 JAMES 632
7900 JAMES 632
7900 JAMES 632
7900 JAMES 632
7900 JAMES 632
7900 JAMES 632
- 같은 블록내에 공간이 있다면 (최대 클러스터 키 개수를 초과하지 않는 범위 내에서) 계 그곳에 저장하고, 그 블록마저 차면 새로운 블록을 할당해서 계속 저장한다.
- SIZE 옵션은 공간을 미리 예약해 두는 것일 뿐 그 크기를 초과했다고 값을 저장하지 못하도록 하지는 않는다.
- SIZE옵션 때문에 데이터 입력이 방해 받지는 않지만 대부분 클러스터 키 값이 한 블록씩을 초과한다면 굳이 이 옵션을 두어 클러스터 체인이 발생하도록 할 필요는 없다.
- 같은 키 값을 가진 데이터가 물리적으로 서로 모여서 저장되도록 하려고 클러스터 테이블을 사용하는 것인데, 이 옵션을 너무 작게 설정하면 그 효과가 반감된다.
반대로 이 값을 너무 크게 설정하면 공간을 낭비{}{}할 수 있으며, 판단 기준은 클러스터 키마다의 평균 데이터 크기다. - SIZE 옵션을 지정하지 않으면 한 블록에 하나의 클러스터 키만+* 담긴다.
(7) 해시 클러스터 테이블
- 해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조이다.
- 클러스터 키로 데이터를 검색하고 저장할 위치를 찾을 때는 해시 함수를 사용한다.
- 해시 함수가 인덱스 역할을 대신하는 것이며, 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환해 준다.
- 해시 클러스터 테이블의 두 가지 유형
- 단일 테이블 해시 클러스터
- 다중 테이블 해시 클러스터
- 해시 클러스터의 가장 큰 제약사항은 '=' 검색만 가능하다는 점이다.
- 물리적인 인덱스를 따로 갖지 않기 때문에 해시 클러스터 키로 검색할 때는 그만큼 블록 I/O가 덜 발생한다는 이점이 있다.