오라클 튜닝(IOT, Table Partition)

1. Index Organized Table(IOT)란? 가. B*Tree 구조로 된 인덱스에 전체 행(row)의 data를 모두 저장 나. Primary Key를 Index key로 사용 다. 인덱스 검색만으로 데이터(row)에 바로 접근이 가능해 빠른 성능제공 라. 잦은 DML 작업 시 인덱스 split가 발생하므로 급격한 성능저하 -> 읽기전용 data에 적합 2. IOT 사용예제

-- IOT 테이블생성
create table iottest
(no number constraints iottest_pk_no primary key,
 title varchar2(10),
 contents varchar2(10))
 organization index tablespace indx
 pctthreshold 40 including title
 overflow tablespace users;

-- 데이터 삽입
insert into iottest values (3,'cccc','cccc');
insert into iottest values (2,'bbbb','bbbb');
insert into iottest values (5,'eeee','eeee');
insert into iottest values (1,'aaaa','aaaa');
insert into iottest values (4,'dddd','dddd');
commit;

-- 데이터 확인(입력한 순서에 상관없이 정렬되어 출력)
select * from iottest;

참고 : – PCTTHRESHOLD : IOT테이블에 큰 데이터(ROW)가 들어올 경우 지정한 비율보다 큰 데이터는 다른 테이블스페이스에 별도로 저장함 – INCLUDING : 위에서 지정한 비율에 따라 데이터의 크기가 클 경우 별도의 테이블스페이스에 저장하게 되는데, 이 때IOT 테이블에서 PRIMARY KEY 이외의 컬럼도 인덱스 테이블에 저장하고자 할 때 사용 – OVERFLOW TABLESPACE : 지정 비율보다 큰 데이터가 저장되는 테이블 스페이스

3. 파티션 테이블(Partition Table) 가. Range Partition – 가장많이 사용하는 파티션으로 DBA가 관리하기 용이하다. – Data가 균등하게 저장되지 않을 가능성이 높다.(성능저하 발생) 나. Hash Partition – Hash 함수를 사용한 파티션으로 데이터 저장 시 각 파티션별로 균등하게 저장 – 검색 성능이 좋다. 다. List Partition – 데이터가 저장된 파티션을 List를 통해 직접지정 – DBA가 예측에 실패해 현실과 다르게 list를 지정하면 큰 성능저하 발생 4. 파티션 사용예제 가. Range Partition

-- 분기별 테이블 스페이스 생성
create tablespace q1 datafile '/home/oracle/oradata/testdb/q1.dbf' size 1m;
create tablespace q2 datafile '/home/oracle/oradata/testdb/q2.dbf' size 1m;
create tablespace q3 datafile '/home/oracle/oradata/testdb/q3.dbf' size 1m;
create tablespace q4 datafile '/home/oracle/oradata/testdb/q4.dbf' size 1m;
create tablespace q5 datafile '/home/oracle/oradata/testdb/q5.dbf' size 1m;

-- 테이블스 생성(range partition)
create table panmae
( pdate varchar2(8),
  pcode number(3),
  pqty number(5))
partition by range(pdate)
( partition q1 values less than ('20100401') tablespace q1,
  partition q2 values less than ('20100701') tablespace q2,
  partition q3 values less than ('20101001') tablespace q3,
  partition q4 values less than ('20110101') tablespace q4,
  partition q5 values less than (maxvalue) tablespace q5);


-- 데이터 입력
insert into panmae values ('20100101', 200, 13);
insert into panmae values ('20100201', 10, 111);
insert into panmae values ('20100301', 150, 10);
insert into panmae values ('20100401', 160, 20);
insert into panmae values ('20100501', 120, 310);
insert into panmae values ('20100601', 90, 10);
insert into panmae values ('20100701', 200, 30);
insert into panmae values ('20100801', 400, 410);
insert into panmae values ('20100901', 410, 15);
insert into panmae values ('20101001', 600, 106);
insert into panmae values ('20101101', 400, 111);
insert into panmae values ('20101201', 11, 101);
commit;

-- 파티션별 데이터 조회
select * from panmae partition (q1);
select * from panmae partition (q2);
select * from panmae partition (q3);
select * from panmae partition (q4);


-- 테이블에 소속된 파티션 검색
select table_name, partition_name, tablespace_name, high_value
from dba_tab_partitions
where table_name='PANMAE';

-- 업데이트 하기
select * from panmae partition (q1);
alter table panmae enable row movement;
update panmae set pdate='20100502' where pcode=10;
select * from panmae partition (q1);
select * from panmae partition (q2);

-- Range Partition 분할하기
create tablespace mon_01 
datafile '/home/oracle/oradata/testdb/mon_01.dbf' size 1m;
create tablespace mon_02 
datafile '/home/oracle/oradata/testdb/mon_02.dbf' size 1m;
create tablespace mon_03 
datafile '/home/oracle/oradata/testdb/mon_03.dbf' size 1m;

alter table panmae split partition q1
at ('20100201')
into (partition m01 tablespace mon_01,
      partition m02 tablespace mon_02);

select * from panmae partition (m01);
select * from panmae partition (m02);

alter table panmae split partition m02
at ('20100301')
into (partition m02 tablespace mon_02,
      partition m03 tablespace mon_03);

select * from panmae partition (m02);
select * from panmae partition (m03);


-- Range Partition 삭제하기
select * from panmae;

alter table panmae drop partition q5;


-- Range Partition 추가하기
create tablespace mon_2011
datafile '/home/oracle/oradata/testdb/mon_2011' size 1m;

alter table panmae 
add partition m2011 values less than ('20121231') tablespace mon_2011;