06 IOT 클러스터 테이블 활용

h3.(1) IOT(lndex-Organized Table)

• Random 액세스가 발생하지 않도록 테이블을 아예 인텍스 구조로 생성
• 오라클은 그런 식으로 테이블을 생성하는 방법을 제공
• 테이블을 찾아가기 위한 rowid를 갖는 일반 인텍스와 달리 IOT는 모든 행 데이터를 리프 블록에 저장

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

IOT의 장점

• IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법중 하나이다
• 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있기 때문에 Random 액세스가 아닌 Sequential 방식으로 데이터를액세스할 수 있다. 넓은 범위를 액세스할 때 유리
• RANGE SEARCH, EXACT MATCH를 수행하는 경우 일반적인 TABLE보다 빠른 KEY-BASED ACCESS가 가능 합니다.
• FULL TABLE SCAN 시 PRIMARY KEY에 대한 FULL INDEX SCAN이 이루어지므로 자동적인 ORDERING이 이루어 집니다.
• INDEX KEY COLUMN과 ROWID에 대한 STORAGE 중복을 피할 수 있어 STORAGE가 절약 됩니다.

IOT의 단점

• 데이터 입력시 성능이 느리다는 점
• 둘 간에 성능 차이가 클 때는 인텍스 분할(Split) 발생량 차이 때문이다.
• IOT는 인텍스 구조이므로 중간에 꽉 찬 블록에 새로운 값을 입력할때는 인텍스 분할(Split)이 발생한다. IOT가 PK 이외에 많은 컬럼을 갖는다면 리프 블록에 저장해야할 데이터량 이 늘어나 그만람 인텍스 분할 발생빈도도 높아진다.
• 컬럼 수가 그렇게 많은 테이블이라면 부적합
• Direct Path Insert 작동하지 않음
• 추가적인 index 생성이 불가능(IOT는 오직 primary key에 대한 indexing만 가능)
• cluster table로 이용되지 못함.
• 병렬 작업이 불가능
• 분산, 복제, 분할이 불가능
• long, long raw, lob이 지원이 안 됨

h3.(2) IOT, 언제 사용할 것인가?

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

• 코드성 테이블이 주로 여기에 속한다.
• NL 조인에서 Inner 쪽 룩업 테이블로서 액세스되는 동안 건건이 인텍스와 테이블 블록을 다 읽는다면 비효율적
• IOT 구성 시 PK 이외 속성의 크기 때문에 인텍스 높이 (heighl)가 증가한다면 역효과

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

• 두 테이블 간 M:M 관계를 해소하기 위한 Association(=lnlerseclion) 테이블이 주로 여기에 속한다.
  
• 그림 1-27의 고객/컨텐츠 테이블은 PK이외에도 많은컬럼으로 구성되지만 컨텐츠방문 테이블은 ( 고객ID , 컨텐츠ID, 방문일시)로 구성된다.

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

• 주로 Between , Like 같은 조건으로 넓은 범위를 검색하는 테이블
• PK이외 컬럼이 별로 없는 통계성 테이블(주로 넓은 범위 조건으로 검색 )

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

• 테이블 구조
• 회사에 100명의 영업사원이 있다.
• 영업사원들의 일별 실적을 집계하는 테이블이 있는데， 한 블록에 100개 레코드가 담긴다.
• 매일 한 블록씩 1년이면 365개 블록이 생긴다.
• 실적등록은 일자별로 진행되지만 실적조회는 주로 사원별로 이루어진다.

 
select substr( 일자,1,6) 월도
     , sum(판매금액) 총판매금액， avg( 판매금액) 평균판매금액
  from 영업실적
 where 사번 = 'S1234'
   and 일자 between '20090101' and '20091231'
 group by substr( 일자， 1, 6)

• 위 쿼리에서 인텍스를 경유해 사원마다 365개 테이블 블록을 읽어야 한다.

 
create tab1e 영업실적 ( 사번 varchar2 (5) , 일자 varchar2(8) , ....
          , constraint 영업실적_PK primary key 사번,일자) ) orgaanization index;

• 사번이 첫 번째 정렬 기준이 되도록 IOT를구성해 주면 한블록만 읽고 처리할수 있다.

h3.(3) Partitioned IOT

• 테이블의 건수가 수억건식 발생하면 단일IOT로 구성하는것은 관리상 부담스럽다.
• 그럴경우 Partitioned I0T 를 구성하자

 
 - 예시
 CREATE TABLE APP_STATS 
  (STAT_ID NUMBER(10,0), 
   ACTION_DATE DATE, 
   VAL FLOAT(126) , 
   STATUS VARCHAR2(1), 
   MARKER VARCHAR2(1), 
   primary key(stat_id,action_date) 
  ) 
   organization index 
   partition by range(action_date) 
   ( 
   PARTITION part1 VALUES LESS THAN (to_date('13-sep-2004','dd-mon-yyyy')), 
   PARTITION part2 VALUES LESS THAN (to_date('20-sep-2004','dd-mon-yyyy')), 
   PARTITION part3 VALUES LESS THAN (to_date('27-sep-2004','dd-mon-yyyy')), 
   PARTITION part4 VALUES LESS THAN (to_date('04-oct-2004','dd-mon-yyyy')) 
 ) 

h3.(4) Overflow 영역

• PK 이외 컬럼이 많은 태이블일수록 IOT로 구성하기에 부적합하다.
• 그럼에도 성능향상을 위해 IOT가 필요하다면 OVERFLOW 기능을 이용하자

• PK컬럼과 시스템관리속성을 분리 저장할수 있다면 IOT 활용성을 높일 수 있다.

OVERFLOW 옵션

• OVERRFLOW TABLESPACE : Overflow 세그먼트가 저장될 테이블스페이스를 지정한다.
  ( ※ SYS_IOT_OVEr38645 등의 이름을 가진 세그먼트가 지동으로 생성됨)
• PCTTHRESHOLD : DEFAULT 값은 50이다. 예를 들어 이 값이 30이면， 블록 크기의 30% 를 초과하기 직전 컬럼까지만 인텍스 블록에 저장하고 그 뒤쪽 컬럼은 모두 Overflow 세그먼트에 저장한다.
• INCLUDING : Including에 지정한 컬럼까지만 인텍스 블록에 저장하고 나머지는 Overflow 세그먼트에 저장한다.

h3.(5) Secondary 인덱스

• IOT는 secondary 인덱스 추가 가능성이 크지 않을 때만 선택하자.

h5.오라클 Logical Rowid

• 오라클은 secondary 인텍스로부터 IOT 레코드를 가리킬때 물리적 주소 대신 logical rowid 를 사용한다.
• logical rowid는 PK와 physical guess로 구성된다.
• physical guess는 seconddary 인텍스를 "최초 생성하거나 재생성(Rebuild)한 시점" IOT레코드가 위치했던 데이터 블록주소(DBA)다
• 인텍스분할에 의해 IOT 레코드가 다른블록으로 이동하더라도 secondary 인텍스에 저장된 physical guess 값은 갱신되지 않는다.
  
• ①은 physical guess를 통해 IOT 레묘드를 직접 액세스하는 것을 표현한 것- ①은 physical guess를 통해 IOT 레묘드를 직접 액세스하는 것을 표현한 것
• ②는 PK를 통해 IOT 를탐색하는것을 표현한 것

h5.PCT_DIRECT_ACCESS

• dba/all/user_indexes 테이블을 조회하면 pct_direct_access 값을 확인할 수 았다.
• secondary 인텍스가 유효한 physical guess를 가진 비율(Direct 액셰스 성공 비율)을 나타내는 지표
• 통계정보 수집을 통해 얻어지는 이 값이 100% 미만이면 오라클은 바로 PK를 이용해 IOT를 탐색한다.100% 일 때만 physical guess를 이용
• 인텍스를 최초 생성하거나 재생성(Rebuild)하고 나면 (통계정보를 따로 수집해 주지 않더라도) pct_ direct _ access 값은 100 이다

h5.비휘발성 IOT에 대한 Secondary 인텍스 튜닝 방안

• pct_direct_access 값이 100을 가리 키도록 유지하는 것이 효과적인 튜닝 방안
• 읽기전용 태이블이면 pct_direct_access 값이 100을 가리키도록 한 상태에서 더 이상 통계정보를 수집하지 않으면 되겠지만， 맨 우측에 지속적으로 값이 입력되는 경우라면 통계정보 수집이 필수적이다.

-- Right- Growing 10T이더라도 pct_direct_access 값이 100이 아닐수 있다
-- 통계정보 수집후 아래 프로시져 호출
exec dbms_stats.set_index_stats (user,'t1_x1' , guessq => 100) ;

• physical guess에 의한 Direct 액세스 성공률이 100%에 가깝다면 일반 테이블을 인텍스 rowid로 액세스할 때와 거의 같은 수준의 성능을 보인다.

h5.휘발성 IOT에 대한 Secondary 인텍스 튜닝 방안
1.주기적으로 physical guess를 정확한 값으로 갱신 - secondary 인텍스 크기가 작을 때

• 휘발성 이 강한(레코도 위치가 자주 변하는) IOT의 경우 시간이 지나면서 physical guess에 의한 액세스 실패 확률이 높아져 성능이 점점 저하된다 ( 이럴때는 통계정보 수집)
• 아래처럼 인텍스를 Rebuild하거나 update block references 옵션을 이용해 physical guess를 주기적으로 갱신해 준다면 가장 효과

alter index iot_second_idx REBUILD;
alter index iot_second_idx UPDATE BLOCK REFERENCES;

2.physical guess가 사용되지 못하도록 pct_direct_access 값을 100 미만으로 떨어뜨리는 것

• 인텍스 크기가 커서 주기적으로 physical guess를 갱신해 줄 수 없을 때 쓸수 있는 방법
• 인텍스 분할이 어느 정도 발생한 상태에서 통계정보를 수집해 주면 된다.
• 선택도(선두 컬럼의 선택도를 말하는것 같음 - 결합인덱스의 경우 첫번째 컬럼이 = 관계를 가져야 INDEX 활용도가 높음)가 매우 낮은 secondary 인텍스 위주로 구성해 주변 큰 비효율은 없다.

h3.(6) 인텍스 클러스터 테이블

• 클러스터 키(여기서는 deptno) 값이 같은 레코드가 한 블록에 모이도록 저장하는 구조를 사용한다.
• 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해 클러스터 체인으로 연결한다.
• 이미 클러스터값의 기준으로 JOIN 되어있는 구조다.
• 인덱스 클러스터 키 값이 같은데이터를 물리적으로 한곳에 저장해둘뿐 IOT처럼 정렬하지 않는다.
  
• 유형
  1.단일 테이블 인텍스 클러스터
  2.다중 테이블 인텍스 클러스터

• 넓은 범위를 검색할때 유리하다.
• 클러스터 인텍스를 '=' 조건으로 액세스할 때는 항상 Unique Scan ( 클러스트 키값으로 모여있으로 )

• 테이블이 실무적으로 자주 활용되지 않는 이유는 DML 부하(정해진 블록을 찾아서 값을 입력해야 하기 때문에 DML 성능이 다소 떨어진다.)

h3.(7) 해시 클러스터 테이블

• 해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조
• 클러스터 키로 데이터를 검색하고 저장할 위치를 찾을 때는 해시 함수를 사용한다
• 해시함수가 인텍스 역할을 대신하는 것
• 해시 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로변환
• 유형 - 단일 테이블 해시 클러스터 , 다중 테이블 해시 클러스터

 
SQL> create c1uster username cluster# ( username varchar2(30) )
   2 hashkeys 100 size 50;;
SQL> create tab1e user cluster
   2 c1uster username cluster# ( username )
   3 as
   4 se1ect * from all users;
   
SQL> create table user_regular as select * from all_users;

SQL> create unique index user_regular_idx on user_regular( username );

SQL> alter table user_reular modify user_id null;
SQL> alter table user_cluster modify user_id null;

SQL> alter session set sql_trace = true;

SQL> declare
  2 l_user_id user_regular.user_id%type;
  3 begin
  4   for c in (select owner from objs_regular where owner <> 'PUBLIC' )
  5   loop
  6   select user_id into l_user_id from user_regular where username = c.owner;
  7    select user_id into l_user_id from user_cluster where username = c.owner;
  8  end loop;
  9 end;
 10 /  

-- 일반 테이블
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 

Rows    Row Source Operation
------- --------------------------------------
30101   TABLE ACCESS BY INDEX ROWID USER_REGULAR (cr=60202 pr=O pw=O time=924994 .. . )
30101   INDEX UNIQUE SCAN USER_REGULAR_IDX (cr=30101 pr=O pw=O time=383440 us)


-- CLUSTER 테이블

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)

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

• 유형

고객마스터 PK 고객 ID
고객별품목별구매내역 PK 관리지점 + 구매일자 + 품목 + 고객 ID+ 구매지점
고객별품목별구매내역 X01 고객 ID + 구매일자

\

• Range + 리스트로 파티셔닝을 고려해보지만 지점이 추가될때마다 관리(파티션 구성변경)하기 힘듬

• 해결책

고객마스터 PK : 고객 ID
고객마스터 X01 : 관리지점 ( 클러스터 키 인텍스)
고객별품목별구매내역 PK : 관리지점 + 구매일자 + 품목 + 고객 ID + 구매지점 (IOT)
고객별품목별구매내역 X01 고객 ID+ 구매일자

• SQL
  

• 변경전
  

• 변경후