6. IOT, 클러스터 테이블 활용

조회 수 13124 추천 수 0 2011.03.02 21:41:04
오예스 *.182.62.197
06  IOT, 클러스터 테이블 활용
   (1) IOT란?

? RANDOM 액세스가 발생하지 않도록 테이블을 아예 인덱스 구조로 생성 하는것으로,

오라클은 이런 식으로 테이블을 생성하는 방법을 제공하는데, 이를 'IOT(Index-Organized Table)' 라고 부른다.

테이블을 찾아가기 위한 rowid를 갖는 일반 인덱스와 달리 IOT는 모든 행 데이터를 리프 블록에 저장하고 있다.

IOT에서는 "인덱스 리프 블록이 곧 데이터 블록"인 셈이다.


테이블을 인덱스 구조로 만드는 구문은 아래와 같다.

create table index_org_t ( a number primary key, b varchar(10))

organization index;



우리가 일반적으로 사용하는 테이블은 '힙 구조 테이블' 이라고 부르며, 테이블 생성 시 대개 생략하지만 아래와 같이 organization 옵션을 명시할 수도 있다.

create table heap_org_t ( a number primary key, b varchar(1) )

organization heap;


일반적인 힙 구조 테이블로의 데이터 삽입은 Random방식으로 이루어 진다. 즉 Freelist로 부터 할당 받은 블록에 정해진 순서 없이 값을 입력한다. 반면, IOT는 인덱스 구조 테이블 이므로 정렬 상태를 유지하며 데이터를 삽입한다.


heap : 컴퓨터의 기억장소에서 그 일부분이 프로그램들에 할당되었다가 회수되는 작용이 되풀이되는 영역. 스택 영역은 엄격하게 후입선출(LIFO) 방식으로 운영되는 데 비해 히프는 프로그램들이 요구하는 블록의 크기나 요구/횟수 순서가 일정한 규칙이 없다는 점이 다르다.

freelist :  세그먼트를 통괄하여 신규삽입이 가능한 블럭의 정보를 가지는 또다른 블럭.







IOT 장점
1.  IOT는 윈위적으로 클러스터링 팩터를 좋게 만다는 방법 중 하나다. 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있기 때문에 Random 액세스가 아닌 Sequential 방식으로 데이터를 엑세스할 수 있고 , 이 때문에 넓은 범위를 액세스할 때 유리하다.
2. PK 컬럼 기준으로 데이터가 모여 있더라도 선행 컬럼이 '=' 조건이 아니면 조회 대상 레코드들이 서로 흩어져 많은 스캔을 유발하지만, 적어도 테이블 Random 액세스는 발생하지 않아 빠른 성능을 낼 수 있다.
3. 테이블을 IOT로 생성하면 PK 인덱스를 위한 별도의 세그먼트를 생성하지 않아도 돼 저장공간을 절약하는 부수적인 이점도 생긴다.
4. full table scan 시 primary key에 대한 full index scan이 이루어지므로 자동적인 ordering이 이루어 진다.







IOT 단점
1. 데이터 입력 시 성능이 느리다는 점을 주로 꼽는다. 이 때문에 IOT를 기피하는 설계자 또는 DBA를 자주 만나는데, 실제 테스트해 보면 그럴 정도로 늦지는 않다. 일반 힙 구조 테이블에 PK 인덱스를 생성하지 않았을 때와 비교하면 당연히 많은 차이가 나겠지만 똑같이 PK 인덱스를 두고 비교해 보면 차이가 거의 없음을 발견할 것이다.
 그럼에도 둘 간에 성능 차이가 클 때는 인덱스 분할(Spilt) 발생량 차이 때문이다. IOT는 인덱스 구조이브로 중간에 꽉 찬 블록에 새로운 값을 입력할 일이 종종 생기고 그럴 때 인덱스 분할이 발생한다. 그런데 IOT가 PK 이외에 많은 컬럼을 갖는다면 리프 블록에 저장해야 할 데이터량이 늘어나 그만큼 인덱스 분할 발생빈도도 높아 진다. 컬럼 수가 그렇게 많은 테이블이라면 인덱스 스캔효율 때문이라도 IOT 대상으로는 부적합하다.
IOT에 Direct Path Insert 가 작동하지 않는 것도 큰 제약중 하나이며, 이 때문에 성능이 느린 것은 어쩔수가 없다.
2. Unique 제약 조건을 설정할 수 없다.


   

Direct Path Insert :  SGA메모리 영역의 데이터 버퍼 캐시 영역을 사용하지 않고 바로 버퍼영역을 통해 직접 테이블로 저장.  redo를 생성하지 않음.  

Insert into .. select ...문에서 append 힌트를 사용하거나 , sql loder에서 사용 가능.


(2) IOT, 언제 사용할 것인가?
? 크기가 작고 NL 조인으로 반복 룩업(Lookup) 하는 테이블
? 폭이 좁고 긴(=로우 수가 많은) 테이블
? 넓은 범위를 주로 검색하는 테이블
? 데이터 입력과 조회 패턴이 서로 다른 테이블

? 크기가 작고 NL 조인으로 반복 룩업(Lookup) 하는 테이블

코드성 테이블이 주로 여기에 속한다. NL 조인에서 Inner 쪽 룩업 테이블로서 액세스되는 동안 건건이 인덱스와 테이블 블록을 다 읽는다면 비효율적이다. 따라서 그런 테이블을 IOT로 구성해 주면 적어도 테이블은 반복 액세스하지 않아도 된다.

다만, IOT 구성 시 PK 이외 속성의 크기 때문에 인덱스 높이(height)가 증가한다면 역효과가 날수 있으므로 이를 반드시 확인하기 바란다.


? 폭이 좁고 긴(=로우 수가 많은) 테이블

두 테이블간 M:M 관계를 해소하기 위한 Association 테이블이 주로 여기에 속한다.

그림 1-27 에서 고객이나 컨테츠 테이블을 (ERD에 표시하진 않았지만) PK 이외에도 많은 컬럼으로 구성된다. 하지만 컨텐츠방문 테이블은 PK(고객ID, 컨텐츠ID, 방문일시)이외 컬럼이 전혀 없거나 있더라도 아주 소수에 불과하다(=폭이 좁다).

PK인덱스는 어차피 생성해야 할 테고, 그러면 테이블과 거의 중복된 데이터를 갖게 된다. 그럴때 IOT로 구성해 주면 중복을 피할수 있다.


? 넓은 범위를 주로 검색하는 테이블

주로 Between, Like 같은 조건으로 넓은 범위를 검색하는 테이블이라면 IOT 구성을 고려해 볼만하다. 특히 PK 이외 컬럼이 별로 없는 통계성 테이블에는 최적의 솔루션이라고 할 수 있다. 통계성 테이블은 주로 넓은 범위 조건으로 검색하는데다, 일반적으로 1-28처럼  PK 구성컬럼은 많은면서 일반 속성은 몇 개 되질 않는 다. PK 구성  컬럼이 많은 만큼 분석 관점과 액세스 경로가 아주 다양한데, 이를 위해 B*Tree 결합 인덱스를 계속 추가해 나가는 것은 저장공간이나 DML 부하 측면에서 문제가 많다.

그럴 때  테이블을 IOT로 구성하면 효과적이다. 우선 PK 인덱스를 위한 별도 공간이 필요하지 않는다는 점이 맘에 든다. 정렬 순서를 잘 정의하면 인덱스를 여러 개 두지 않고도 다양한 검색 조건에 대응할수 있고, 무엇보다 테이블 Random 액세스가 전혀 발생하지 않는다는 점에서 강력하다.


? 데이터 입력과 조회 패턴이 서로 다른 테이블

회사에 100명의 영업사원이 있다고 하자. 영업사원들의 일별 실적을 집계하는 테이블이 있는데, 한 블록에 100개 레코드가 담긴다.

그러면 매일 한 블록씩 1년이면 365개 블록이 생긴다.

이처럼 실적등록은 일자별로 진행되지만 실적조회는 주로 사원별로 이루어진다. 예를 들어 일상적으로 아래 쿼리가 가장 많이 수행된다고 하자.

 

select substr(일자, 1, 6) 월도

        , sum(판매금액) 총판매금액, avg(판매금액) 평균판매금액

from 영업실적

where 사번 = 'S1234'

  and 일자 beween '20090101' and '20091231'

group by substr(일자, 1, 6);


그러면 인덱스를 경유해 사원마다 365개 테이블 블록을 읽어야 한다. 클러스터링 팩터가 매우 안 좋기 때문이며,입력과 조회 패턴이 서로 달라서 생기는 현상이다. 그럴 때  사번이 첫 번째 정렬 기준이 되도록 IOT를 구성해 주면, 한 블록만 읽고 처리할 수 있다.


(3) Partitioned IOT
? 대용량 테이블을 단일 IOT로 구성하면 관리상 부담스럽다.
    그럴때 Partitioned IOT를 구성하면 관리에 효율적이다. (예 : 월별 Partitioned IOT)

(4) Overflow영역
?IOT 문제점

PK 이외 컬럼이 많은테이블일수록 IOT로 구성하기에 부적합하다. 인덱스 분할에 의한 DML                      

부하는 물론 , 검색을 위한 스캔량도 늘어나기 때문이다.       

            그럼에도 성능 향상을 위해 IOT가 꼭 필요하다면?

? 해결책

  IOT로 구성하기에 적합하지 않은 컬럼들을 Overflow기능을 사용해서 주요컬럼과 분리 저장할수 있다.

  Overflow 옵션에는 OVERFLOW TABLESPACE, PCTTHRESHOLD, INCLUDING 이 있다.


   
(5) Secondary 인덱스

 ?IOT는 Secondary 인덱스 추가 가능성이 크지 않을 때만 선택하는 것이 바람직하다.
 ?IOT의 Secondary 인덱스는 Primary Key 값과 그것을 기반으로 하는 ‘Universal Rowid’ 즉,  Urowid를 가지고 인덱스가 만들어 진다.


UROWID(Universal Rowid) : 인덱스 구성 테이블(IOT, Index-Organized Table)행의 논리적 위치

(6) 인덱스 클러스터 테이블
? 인덱스 클러스터 테이블 : 클러스터키값이 같은 레코드가 한 블록에 모이도록 저장하는 구조를 사용한다. 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해 클러스터 체인으로 연결한다. 심지어 여러 테이블 레코드가 물리적으로 같이 저장될 수 도 있다. 여러 테이블을 서로 조인된 상태로 저장해 두는 것인데, 일반적으로는 하나의 데이터 블록이 여러 테이블에 의해 공유될수 없음을 상기하기 바란다.
이름 때문에 SQL서버나 Sybase에서 말하는 '클러스터형 인덱스'와 같다고 생각할지 모르지만 클러스터형 인덱스는 IOT에 가깝다. 인덱스 클러스터는 키값이 같은 데이터를 물리적으로 한 곳에 저장해 둘 뿐,  IOT처럼 정렬하지는 않는다.

? 인덱스 클러스터 테이블의 두가지 유형
  - 단일 테이블 인덱스 클러스터
  - 다중 테이블 인덱스 클러스터

? 클러스터 인덱스 테이블 레코드와 1 : M관계.
    일반 테이블의 테이블 레코드는 1 : 1 관계
따라서 클러스터 인덱스를 스캔하면서 값을 찾을 때는 Random 액세스가 값 하나당 한 번씩 밖에 발생하지 않는다.
클러스터에 도달해서는 Sequential 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다는게 핵심 원리다.

? 인덱스 클러스터는 넓은 범위를 검색할 때 유리.

1.)  인덱스 클러스터 테이블과 일반 힙 구조 테이블을 테스트해본 결과 인덱스 클러스터 테이블의 블록 I/O 비중이 현저히 낮았다.

클러스터 인덱스를 '=' 조건으로 액세스할 때는 항상 Unique Sacn이 나타난다는 사실을 기억하기 바란다.


? 클러스터 테이블과 관련한 성능 이슈

1) DML 부하

클러스터 테이블이 실무적으로 자주 활용되지 않는 이유는 DML의 부하 때문이다.

하지만 클러스터를 구성하지 않는 대신 인덱스를 생성할거면 DML 부하는 크게 차이나지 않는다.

2) Direct Path Loading 을 수행할 수 없다.

3) 파티셔닝 기능을 함께 적용할 수 없다. IOT의 경우는 Partitioned IOT가 가능하다.

4) 다중 테이블 클러스터를 Full Scan 할 때는 다른 테이블 데이터까지  스캔하기 때문에 불리하다.


? Size 옵션

클러스터 키 하나당 레코드 개수가 많지 않을 때 클러스터마다 한 블록씩 통째로 할당하는 것은 낭비다. 그래서 오라클은 하나의 블록에 여러 키값이  상주할 수 있도록 SIZE 옵션을 두었다.

SIZE 옵션은 한 블록에 여러 클러스터 키가 같이 담기더라도 하나당 가질 수 있는 최소 공간을 미리 예약하는 기능이다. 반대로 말하면 하나의 블록에 담을 최대 클러스터 키 개수를 결정짓는다고 할 수 있다.


(7) 해시 클러스터 테이블.
? 해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조다. 클러스터 키로 데이터를 검색하고 저장할 위치를 찾을 때는 해시 함수를 사용한다. 해시 함수가 인덱스 역할을 대신하는 것이며, 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환해 준다.

? 해시 클러스터 테이블의 두가지 유형.
  - 단일 테이블 해시 클러스터
  - 다중 테이블 해시 클러스터

? 해시 클러스터의 가장 큰 제약사항은 '=' 검색만 가능하다는 점이다. 따라서 거의 대부분 '=' 조건으로 검색되는 컬럼을 해시 키로 선정해야 한다.
물리적인 인덱스를 따로 갖지 않기 때문에 해시 클러스터 키로 검색할 때는 그만큼 블록 I/O가 덜 발생한다는 이점이 생긴다.

(8) IOT와 클러스터 테이블을 동시에 적용한 튜닝  사례

? select a.카드번호, a.고객id, a.고객명,  a.발급일자 a.*,

                x.구매금액 구매금액2

       from (select x.고객id, x.관리지점, sum(구매금액) 구매금액

                  from 고객별품목별구매내역 x

                 where x.관리지점 = 'A6123E'

                     and x.구매일자 between '20010101' and '2090430'

                     and x.구매지점  not in ('AAAAAA', 'BBBBBB')

                     and (x.품목 = '0001' or x.품목 = '0002')

                  group by x.고객id, x.관리지점) x

               , 고객마스터 a

       where x.관리지점 = 'A6123E'

           and a.고객id = x.고객id

           and a.관리지점 = x.관리점

           and a.전화확인 = '01'

           and a.sms거부 = 'N'

           and a.핸드폰번호 is not null

       

      위 쿼리에서 사용된 두 테이블 인덱스 구성은 다음과 같다.

      고객마스터 PK : 고객ID

      고객별품목별구매내역_PK :  관리지점+ 구매일자+ 품목+ 고객ID + 구매지점           

      고객별품목별구매내역_X01 : 고객ID  + 구매일자

 

    문제점 :  1. 고객마스터와 고객별품목별구매내역 모두 지점별로 데이터가 관리. 따라서 관리지점별로 대량의 데이터를 집계하는 경우가 많음.

                    2. 관리지점별 고객수가 수만 명에 이름고 , 구매일자 검색범위에 해당하는 구매내역도 수만 건에  이르기 때문에 NL 조인으로 좋은

                        성능을  기대하기 어려운 상황. 관리지점 조건으로 각각 인덱스를 이용해 테이블을 액세스 하는 과정에서 이미많은  RANDOM

                         I/O가 발생.

                    3.  고객별품목볼구매내역 테이블같은 형태의 거래 테이블은 일자 컬럼 기준으로 Range 파티션을 구성하는 것이 일반적이지만,

                         불필요한 관리지점까지 Full Scan하는 비효율이다 관리지점마다 따로 저장되도록 'Range + 리스트' 결합 파티셔닝을 생각해 볼

                         수 있지만 관리지점이 추가될 때마다 파티션 구성을 변경해 주어야 하는 관리 부담이 생긴다. 게다가 일 단위 파티션이 아닌 한,

                         검색범위에 포함되지 않는 일자까지 모두 Full Scan하는 비용도 적지 않을 것이다.

 

     해결책 : 1. 고객마스터는 관리지점 기준으로 클러스테이블을 구성하는 것이 가장 최적의 솔루션.

                    2.  관리지점, 구매일자가 정렬 기준 선두 컬럼이 되도록 IOT를 구성

 

        고객마스터 PK : 고객ID

        고객마스터 X01 : 관리지점 ( => 클러스터 키 인덱스)

        고객별품목별구매내역_PK :  관리지점+ 구매일자+ 품목+ 고객ID + 구매지점  (=>   IOT)

        고객별품목별구매내역_X01 : 고객ID  + 구매일자


(9) IOT 정보조회

  • SELECT T.TABLE_NAME IOT
          ,O.TABLE_NAME OVERFOLW
          ,I.INDEX_NAME INDEX_NM
          ,O.TABLESPACE_NAME OVERFLOW_TS
          ,I.TABLESPACE_NAME INDEX_TS
          ,I.PCT_THRESHOLD               
      FROM DBA_TABLES T, DBA_TABLES O, DBA_INDEXES I
     WHERE T.TABLE_NAME = O.IOT_NAME
       AND T.OWNER = I.OWNER
       AND T.TABLE_NAME = I.TABLE_NAME
       AND T.OWNER = '';
    • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 2차 스터디)

    • 작성자: 윤용정 (sirason1)

    • 최초작성일: 2011년 2월 28일

    • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net

    • 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^