06 IOT, 클러스터 테이블 활용
(1) IOT란? ( Page.99 그림 1-26 )
-- IOT
create table index_org_t ( a number primary key, b varchar(10) )
organization index;
-- heap
create table heap_org_t( a number primary key, b varchar(10))
organization heap;
IOT의 장점과 단점
- 장점
- 넓은 범위를 액세스할 때 유리 ( Random 액세스 X )
- PK 인덱스를 위한 별도의 세그먼트를 생성하지 않아도 돼 저장공간을 절약하는 부수적인 이점
- 단점
- 데이터 입력 시 성능이 느림
- heap 테이블에 PK 인덱스를 두고 비교해 보면 차이가 거의 없음
(2) IOT, 언제 사용할 것인가?
- 크기가 작고 NL 조인으로 반복 룩업(Lookup == Inner)하는 테이블 ( 코드성 테이블 )
- 인덱스 높이(height)가 증가한다면 역효과가 날수 있으므로 반드시 확인 필요
- 폭이 좁고 긴(=로우 수가 많은) 테이블
- 넓은 범위를 주로 검색하는 테이블
- 데이터 입력과 조회 패턴이 서로 다른 테이블
폭이 좁고 긴(=로우 수가 많은) 테이블 ( Page.101 그림 1-27 )
넓은 범위를 주로 검색하는 테이블 ( Page.101 그림 1-28 )
- BETWEEN, LIKE, PK 이외 컬럼이 별로 없는 통계성 테이블에는 최적의 솔류션
- RANDOM 액세스가 전형 발생하지 않는다 ( 장점 )
- 인덱스 높이 증가 우려 : 파티션 IOT으로 해결
데이터 입력과 조회 패턴이 서로 다른 테이블
- 영업사원 : 100명
- 한 블럭에 100개 레코드 : 1년이면 365개 블록
select substr( 일자, 1, 6 ) 월도
, sum( 판매금액 ) 총판매금액, avg( 판매금액 ) 평균판매금액
from 영업실적
where 사번='S1234'
and 일자 between '20090101' and '20091231'
group by substr( 일자, 1, 6 )
create table 영업실적 ( 사번 varchar2( 5 ), 일자 varchar2( 8 ), ...
, constraint 영업실적_pk primary key ( 사번, 일자 ) organization index;
(3) Partitioned IOT
- 상품별 거래 건수가 워낙 많아 쿼리 1에 인덱스를 사용하면 Random 액세스 부하가 심하게 발생
- 거래일자 기준으로 월별 Range 파티션닝돼 있다면 인덱스를 이용하기 보다 필요한 파티션만 FULL SCAN하는 편이 빠르지만 비효율
- 상품번호 + 거래일자 순으로 정렬되도록 IOT를 구성하면 읽기 성능이야 획기적으로 개선되겠지만,
수억 건에 이르는 테이블을 단일 IOT로 구성하는 것은 관리상 부담스럽지 않다.
쿼리 1
select 거래일자, 지점번호, 계좌번호, sum( 거래량 ), sum( 거래금액 )
from 일별상품별계좌거래
where 상품번호 = 'P7006050009'
and 거래일자 between '20080101' and '20080630'
group by 거래일자, 지점번호, 계좌번호
쿼리 2
- 단일 IOT를 구성하면 쿼리는 2는 수억 건에 이르는 테이터를 FULL SCAN 해야한다.
select 상품번호, 거래일자, sum( 거래량 ), sum( 거래금액 )
from 일별상품계좌거래
where 거래일자 between '20080101' and '20080630'
group by 상품번호, 거래일자
Page.104 그림 1-29
(4) Overflow 영역
- OVERFLOW TABLSPACE : Overflow 세그먼트가 저장될 테이블스페이스를 지정한다.
- PCTTHRESHOLD : DEFAULT 값은 50이다. 예를 들어 이 값이 30이면, 블록 크기의 30%
를 초과하기 직전 컬럼까지만 인덱스 블록에 저장하고 그 뒤쪽 컬럼은 모두
Overflow 세그먼트에 저장한다. 물론 로우 전체 크기가 지정된 비율 크기
보다 작다면 모두 인덱스 블록에 저장한다.
테이블 생성하는 시점에 모든 컬럼의 데이터 타입 Max 길이를 합산한
크기가이 비율 크기보다 작다면 Overflow 세그먼트는 불필요하지만
만약 초과한다면 오라클은 Overflow Tablespace 옵션을 반드시 지정하도록
강제하는 에러를 던진다. - INCLUDING : Including에 지정한 컬럼까지만 인덱스 블록에 저정하고 나머지는 무조건 Overflow 세그먼트에 저장한다.
create table 불공정거래 적출(
종목코드 varchar2( 12 ) NOT NULL
, 적출일자 varchar2( 8 ) NOT NULL
, 적출일자 varchar2( 5 ) NOT NULL
, 적출일자 varchar2( 5 ) NOT NULL
, 적출일자 varchar2( 12 ) NOT NULL
, 적출일자 varchar2( 2 ) NOT NULL
, 적출일자 varchar2( 1 ) NOT NULL
, 적출일자 varchar2( 15 ) NOT NULL
, 적출일자 varchar2( 14 ) NOT NULL
, 적출일자 varchar2( 14 ) NOT NULL
, 적출일자 varchar2( 14 ) NOT NULL
, 적출일자 varchar2( 14 ) NOT NULL
, CONSTRAINT 불공정거래적출_PK PRIMARY KEY
( 종목코드, 적출일자, 회원번호, 지점번호, 계좌번호, 적출유형코드 )
)
ORGANIZATION INDEX
OVERFLOW TABLESPACE TBS_OVREL01
PCTTHRESHOLD 30
INCLUDING 적출건수;
Page.107 그림 1-30
- Overflow 영역에도 버퍼 Pinning 효과
(5) Secondary 인덱스
- IOT는 secondary 인덱스 추가 가능성이 크지 않을 때만 선택하는 것이 바람직하다.
MS-SQL 서버의 비클러스터형 인덱스 진화 과정
- 클러스터형 인덱스 : IOT 처럼 인덱스 구조로 생성한 테이블
- 비클러스터형 인덱스 : 비클러스터형 인덱스
Page.107 그림 1-31
- SQL 서버 6.5 이전 : 비클러스터형 인덱스가 클러스터형 인덱스 레코드를 직접 가리키는 rowid
- 문제 : 인덱스 분할에 의해 클러스터형 인덱스 레코드 위치가 변경될 때마다
비클러스터형 인덱스가 갖는 rowid 정보를 모두 갱신해 주어야 하는 문제( DML 부하 )
- SQL 서버 7.0 : 비클러스터형 인덱스가 rowid 대신 클러스터형 인덱스의 키 값을 갖도록 구조를 변경
- 키 값을 갱신하지 않는한, 인덱스 분할 때문에 비클러스터형 인덱스를 갱신할 필요가 없어진 것이다.
- DML 부하가 줄어든 대신 이전보다 더 많은 I/O가 발생하는 부작용
- 그림 1-31 우측처럼 비클러스터형 인덱스에서 읽히는 레코드마다 건건이 클러스터형 인덱스 수직 탐색을
인덱스에서 읽히는 레코드마다 건건이 클러스터형 인덱스 수직 탐색을 반복하기 때문이다.
오라클 Logical Rowid
- SQL 서버 6.5 와 7.0 이후 버전의 갖는 두 가지 액세스 방식을 모두 사용할 수 있도록 설계
Page.109 그림 1-32
- 물리적 주소 대신 logical rowid 사용
- logical rowid = PK + physical guess
- physical guess는 secondary 인덱스를 "최조 생성하거나 재생성한 시점"에
IOT 레코드가 위치했던 데이터 블록 주소다 ( 픽스 )- 1 : physical guess 통해 IOT 레코드를 직접 액세스
- 2 : PK를 통해 IOT를 탐색하는 것을 표현
PCT_DIRECT_ACCESS
- dba/all/user_indexes : PCT_DIRECT_ACCESS 값을 확인 가능
- 100% 미만 : PK를 이용해 IOT를 탐색
- 100% : physical guess를 이용하는데, 레코드를 찾아 갔을 때 해당 레코드가 다른 곳으로 이동하고 없으면 PK로 다시 IOT를 탐색
alter index iot_second_idx rebuild;
alter index iot_second_idx update block references;
-- 통계 변화량 확인
create table t1( c1 number not null
, c2 number
, c3 nubmer
, c4 number
, constraint t1_pk primary key( c1 ) )
orgnization index; --> IOT 생성
-- 인덱스 생성 ( pct_direct_accss : 100 )
create index t1_x1 on t1( c2 ); --> secondary 인덱스 생성
insert into t1
select rownum, rownum, rownum, rownum
from all_objects
where rownum <= 1000;
commit;
-- physical guess 활용
select index_name, pct_direct_access
from user_indexes
where index_name = 'T1_X1';
INDEX_NAME PCT_DIRECT_ACCES
------------------- ----------------
T1_X1 100
-- 통계 수집 ( PK로 IOT를 탐색 )
EXEC DBMS_STATS.GETHER_INDEX_STATS( USER, 'T1_X1' );
select index_name, pct_direct_access
from user_indexes
where index_name = 'T1_X1';
INDEX_NAME PCT_DIRECT_ACCES
------------------- ----------------
T1_X1 64
ALTER INDEX T1_X1 UPDATE BLOCK REFERNCES;
select index_name, pct_direct_access
from user_indexes
where index_name = 'T1_X1';
INDEX_NAME PCT_DIRECT_ACCES
------------------- ----------------
T1_X1 64
EXEC DBMS_STATS.GETHER_INDEX_STATS( USER, 'T1_X1' );
-- physical guess 이용
select index_name, pct_direct_access
from user_indexes
where index_name = 'T1_X1';
INDEX_NAME PCT_DIRECT_ACCES
------------------- ----------------
T1_X1 100
비휘발성 IOT에 대한 Secondary 인덱스 튜닝 방안
- 비휘발성 ( 일기 전용 or IOT 업데이특 ) 테이블 : PCT_DIRECT_ACCESS 값이 100을 가리키도록 유지 ( 효과적인 튜닝 )
- 데이터가 쌓이는 양에 따라 한 달에 한 번 또는 일년에 한 번 정도만 physical guess를 갱신
- Right-Growing IOT : pct_direct_access 값이 100이 아닐수 있다
-- 통계정보 수집 직후에 아래 프로시저를 이용해 값을 직접 설정해 주면 된다.
exec dbms_status.set_index_stats( user, 't1_x1', guessq=>100 );
{code:sql}
h3. 휘발성 IOT에 대한 Secondary 인덱스 튜닝 방안
* physical guess에 의한 Direct 액세스 성공률이 낮다면 두 가지 선택을 할 수 있다.
** 1 : 주기적으로 physical guess를 정확한 값으로 갱신해 주는 것 ( secondary 인덱스 크기가 작을 때 )
** 2 : physical guess가 사용되지 못하도록 pct_diect_access 값을 100 미만으로 떨어뜨리는 것( secondary 인ㄷ게스 크기가 클 때 _
*** 선택도가 매우 낮은 secondary 인덱스 위주로 구성해 주면 큰 비효율은 없다
h3. Right-Growing IOT에서 pct_direct_access가 100 미만으로 떨어지는 이유 ( Page.114 그림 1-33, 그림 1-34, 그림 1-35 )
* 인덱스 높이가 2단계로 증가하면서 생기는 현상
h4. IOT_REDUNDANT_PKEY_ELIM
{code:sql}
create table emp_iot
( empno, ename, job, mgr, hiredate, sal, comm, deptno
, constraint pk_emp_iot primary key ( empno ) )
organization index
as
select * from scott.emp;
create index iot_secondary_index on emp_iot( ename );
set autotrace traceonly explain
select * from emp_iot_where ename = 'SMITH';
Execution Plan
---------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=87)
1 0 INDEX (UNIQUE SCAN) OF 'PK_EMP_IOT' (UNIQUE) (Cost=1 Card=1 Bytes=87)
2 1 INDEX (RANGE SCAN) OF 'IOT_SECONDARY_INDEX' (NON-UNIQUE) (Cost=1 Card=1)
- dba/all/user_indexes를 조회하면 iot_redundant_pkey_elim 통계치를 볼수 있는데,
이 값이 'YES' 이면 secondary 인덱스 키와 pk 컬럼 간에 하나 이상 중복 컬럼이 있어
오라클이 이를 재거했음을 의미한다.
(6) 인덱스 클러스터 테이블
인덱스 클러스터 ( Page.118 그림 1-36 )
- 클러스터 인덱스 : 일반적인 B*Tree 인덱스 구조를 사용하지만, 해당 키 값을
저장한는 첫 번재 데이터 블록만을 가리킨다는 점에서 다르다.- 키 값은 항상 Unique 하며 1:M 관계를 갖는다
- Random 액세스가 값 하나당 한 번씩 발생 클러스터에 도달해서는 Sequential 방식으로 스캔하기 때문에
넓은 범위를 읽더라도 비효율이 없다는 게 핵심 원리
-- 클러스터
create cluster c_deptno# ( deptno number(2) ) index;
-- 클러스터 인덱스
create index i_deptno# on cluster c_deptno#;
create table emp
cluster c_deptno# ( deptno )
as
select * from scott.emp;
create table dept
cluster c_deptno# (deptno )
as
select * from scott.dept;
select owner, table_name from dba_tables where cluster_name = 'C_DEPTNO#';
OWNER TABLE_NAME
--------------- ------------------
ORAKING DEPT
ORAKING EMP
break on deptno skip 1
select d.deptno, e.empno, e.name
, dbms_rowid.rowid_block_number( d.rowid ) dept_block_no
, dbms_rowid.rowid_block_number( e.rowid ) emp_block_no
from dept d, emp e
where e.deptno = d.deptno
order by d.deptno;
DEPTNO DEMPNO ENAME DEPT_BLOCK_NO EMP_BLOCK_NO
------ ------ -------- ------------- ------------
10 7934 MILIER 524 524
7839 KING 524 524
7782 CLARK 524 524
20 7788 SCOTT 527 527
7566 JONES 527 527
7369 SMITH 527 527
- deptno가 같은 dept, emp 레코드가 같은 블록에 담긴 것을 알 수 있다.
인덱스 클러스터는 넓은 범위를 검색할 때 유리
create cluster objs_cluster# ( object_type varchar2( 19 ) ) index;
create index objs_cluster_idx on cluster objs_cluster#;
-- 클러스터 테이블
create table objs_cluster
cluster objs_cluster#
as
select * from all_objects
order by dbms_random.value;
-- 일반 테이블
create table objs_reqular
as
select * from objs_cluster
order by rdbms_random.value;
create inded objs_reqular_idx on objs_reqular( object_type );
alter table objs_regular modify object_name null;
alter table objs_cluster modify object_name null;
select /*+ index( t objs_reqular_idx ) */ count( object_name )
from objs_regular t
where object_type = 'TABLE'
Rows Row Source Operation
------ --------------------------------------------------------
1 SORT AGGREGATE (cr=642 pr=0 time=5774 us)
1763 TABLE ACCES BY INDEX ROWID OBJS_REGULAR (cr=642 pr=0 pw=0 time=38863 us)
1763 INDEX RANGE SCAN OBJS_REGUAR_IDX (cr=6 pr=0 pw=0 time=8922 us)
select count( object_name )
from objs_cluster t
where object_type = 'TABLE'
Rows Row Source Operation
------ --------------------------------------------------------
1 SORT AGGREGATE (cr=23 pr=0 time=638 us)
1763 TABLE ACCES CLUSTER OBJS_CLUSTER (cr=23 pr=0 pw=0 time=7075 us)
1 INDEX UNIQUE SCAN OBJS_CLUSTER_IDX (cr=1 pr=0 pw=0 time=18 us)
- heap TABLE : 1,763번 Random 액세스 하는 동안 636( 642-6 )개의 블록 I/O가 발생
- cluster index TABLE : Random 액세스는 단 1회만 발생
클러스터 테이브로가 관련한 성능 이슈
- 단점
- DML 부하
- IOT 처럼 정렬 상태를 유지하지는 않지만 정해진 블록을 찾아서 갑을 입력해야 하기 때문에 DML 성능이 다소 떨어진다.
- 특히, 전에 없던 값을 입력할 때는 블록을 새로 할당 받아야 하기 때문에 더 느리다.
- Truncate table 문장 X
- DROP : 내부적으로 건건이 delete가 수행된다
- 전체 데이터를 빠르게 지우고 싶을 때는 아래와 같이 클러스터를 Truncate 하거나 Drop하는것이 가장 빠르다
- Direct Path Loading을 수행할 수 없다
- 파티셔닝 기능을 함께 적용 할 수 없다.
- 다중 테이블 클러스터를 Full Scan할 때는 다른 테이블 데이터까지 스캔하기 때문에 불리하다.
truncate cluster objs_cluster#;
dro cluster objs_cluster# including tables;
SIZE 옵션
- 클러스터 키 하나당 레코드 개수가 많지 않을 때 클러스터마다 한 블록씨 통째로
할당하는 것은 낭비 - 하나의 블록에 담을 최대 클러스터 키 개수를 졀정짓는다고 할 수 있다.
- 8kb : SIZE 옵션을 2000바이트로 지정하면 한 블록당 최대 4개 클러스터 키만을 담을 수 있다.
show parameter block_size
NAME TYPE VALUE
-------------------------- --------- -------
db_block_size interger 8192
drop cluster emp_cluster# including tables;
create cluster emp_cluster#( empno number(4) ) pctfree 0 size 2000 index;
create index emp_cluster_idx on cluster emp_cluster#;
create table emp
cluster emp_cluster#( empno )
as
select * from scott.emp;
select emp.empno, emp.ename, dbms_rowid.rowid_block_number( rowid ) block_no
from emp;
EMPNO ENAME BLOCK_NO
------- ---------- --------
7902 FORD 68404
7934 MILLER 68404
7369 SMITH 68406
7499 ALLEN 67406
7521 WARD 68406
7566 JONES 68406
...
(7) 해시 클러스터 테이블
- 해시 클러스터 테이블은 해시 함수에 반환된 값이 같은 테이터를 물리적으로 함께 저정하는 구조다.
- 클러스터 키로 테이터를 검색하고 저장할 위치를 찾을 때는 해시 함수로를 사용한다.
- 해시 함수가 인덱스 역할을 대신하는 것이며, 해싱 알고리즘을 이용해 클러스터 키 값을
데이터 블록 주소로 변환해 준다. - 단일 테이블 해시 클러스터
- 다중 테이블 해시 클러스터
create cluster username_cluster#( username varchar2( 30 ) )
hashkeys 100 size 50;
create table user_cluster
cluster username_cluster#( username )
as
select * from all_users;
create table user_regular as select * from all_users;
create unique index user_regular_idx on user_regular( username );
alter table user_reular modify user_id null;
alter table user_cluster modify user_id null;
alter session set sql_trace = true;
declare
l_user_id user_regular.user_id%type;
begin
for c in (select owner from objs_regular where owner <> 'PUBLIC' )
loop
select user_id into l_user_id from user_regular where username = c.owner;
select user_id into l_user_id from user_cluster where username = c.owner;
end loop;
end;
/
SELECT USER_ID FROM USER_REGULAR WHERE USERNAME = :B1
call count cpu elapsed disk query current rows
------- ----- ----- ------- ------ ------- --------- -------
Parse 1 0.00 0.00 0 0 0 0
Execute 30101 0.75 0.64 0 0 0 0
Fetch 30101 0.81 0.81 0 60202 0 30101
------- ----- ----- ------- ------ ------- --------- -------
total 60203 1.56 1.45 0 60202 0 30101
SELECT USER_ID FROM USER_CLUSTER WHERE USERNAME = :B1
call count cpu elapsed disk query current rows
------- ----- ----- ------- ------ ------- --------- -------
Parse 1 0.00 0.00 0 0 0 0
Execute 30101 0.75 0.64 0 0 0 0
Fetch 30101 0.81 0.81 0 30104 0 30101
------- ----- ----- ------- ------ ------- --------- -------
total 60203 1.56 1.45 0 30104 0 30101
Rows Row Source Operation
------- --------------------------------------
30101 TABLE ACCESS HASH USER_CLUSTER (cr=3010 pr=0 time=410828 us)
(8) IOT와 클러스터 테이블을 동시에 적용한 튜닝 사례
select a.카드번호, a.고객id, a.고객명, a.발급일자
, a.성별, a.생년월일, a.결혼기념일, a.핸드폰번호, a.전화번호
, a.우편번호, a.우편번호주소, a.상세주소
, a.최종구매일자, a.구매횟수, a.구매금액 구매금액1, x.구매금액 구매금액2
, a.누적포인트, a.실사용가능포인트
FROM (select x.고객id, x.관리지점, sum( 구매금액 ) 구매금액
from 고객별품목별구매내역 x
where x.관리지점 = 'A6123E'
and x.구매일자 between '20010201' and '20090430'
and x.구매일자 not in ( 'AAAAA', 'BBBBB' )
AND ( X.품목 = '0001' OR X.품목 = '0004' )
GROUP BY X.고객id, x.관리지점) x
, 고객마스터 a
where x.관리지점 = 'A6123E'
and a.고객id = x.고객id
and a.관리지점 = x.관리지점
and a.전화확인 = '01'
call count CPU Time elapsed disk query current rows
------- ----- --------- ------- ------ ------- --------- -------
Parse 1 0.000 0.005 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 1108 12.970 292.657 81233 121574 0 11062
------- ----- --------- ------- ------ ------- --------- -------
total 1108 12.970 292.657 81233 121574 0 11062
Rows Row Source Operation
----- --------------------------------------
11062 NESTED LOOPS (cr=121571 r=81233 w=0 time=292379730 us)
14661 VIEW (cr=76364 r=68123 w=0 time=252514157 us)
14661 SORT GROUOP BY (cr=76364 r=68123 w=0 time=252503412 us)
19099 TABLE ACCESS BY INDEX ROWID 고객별품목별구매내역 (cr=76364 r=68123 ...)
75773 INDEX RANGE SCAN 고객별품목별구매내역_PK (cr=853 r=853 w=0 time ...)
11062 TABLE ACCESS BY INDEX ROWID 고객마스터 (cr=45207 r=13110 w=0 time ...)
14661 INDEX UNIQUE SCAN 고객마스터_PK (cr=30431 r=10849 w=0 time ...)
고객마스터_pk : 고객ID
고객별품목구매내역_PK : 관리지점 + 구매일자 + 품목 + 고개ID + 구매지점
고객별품목별구매내역_X01 : 고객ID + 구매일자
고객마스터_pk : 고객ID
고객마스터_X01 : 관리지점 ( -> 클러스터 키 인덱스 )
고객별품목구매내역_PK : 관리지점 + 구매일자 + 품목 + 고개ID + 구매지점 ( -> IOT )
고객별품목별구매내역_X01 : 고객ID + 구매일자
SELECT /*+ LEADING( A ) USE_HASH( X ) */
...
call count CPU Time elapsed disk query current rows
------- ----- --------- ------- ------ ------- --------- -------
Parse 1 0.000 0.005 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 1108 1.120 2.803 2995 2995 0 11062
------- ----- --------- ------- ------ ------- --------- -------
total 1108 1.120 2.803 2995 2995 0 11062
Rows Row Source Operation
----- --------------------------------------
11062 HASH JOIN (cr=2995 r=2995 w=0 time=269887 us)
61185 TABLE ACCESS CUSTER 고객마스터 (cr=1241 r=2142 w=0 time=1776145 us)
1 INDEX UNIQUE SCAN 고객마스터_IDX01 (cr=1 r=1 w=0 time=647 us)
14661 VIEW (cr=853 r=853 w=0 time=537164 us)
14661 SORT GROUP BY (cr=853 r=853 w=0 time=527150 us)
19099 INDEX RANGE SCAN 고객별품목별구매내역_PK (cr=853 r=853 w=0 time=... )
- 고객마스터 : 관리지점 기준으로 클러스터 테이블을 구성하는 것이 가장 최적의 솔류션
- 고객별품목별구매내역 : 관리지점, 구매일자 정렬 기준 선두 컬럼이 되도록 IOT를 구성
문서에 대하여
- 최초작성자 : 이재현
- 최초작성일 : 2016년 06월 11일
- 이 문서는 오라클클럽 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법 I'를 참고하였습니다.*