꿈꾸는 개발자, DBA 커뮤니티 구루비

파티셔닝 개요

테이블이나 인덱스를 물리적으로 더 작고 관리하기 쉬운 단위로 나누는 개념이다
분할정복(divide and conquer) 개념을 사용해 매우 큰 테이블과 인덱스를 관리하는데 유용하다
파티션키(Partition key) : 어떤 범위 값, 특정 값의 리스트, 해시 함수의 값으로 데이터를 나누는데 사용되는 값

장점

가용성 증가
관리비용 감소
문장 성능 향상

가용성 증가

각각의 파티션을 독립적으로 사용이 가능하게 됨에 따라 가용성이 증가한다.
오류 발생 시의 복구에 요구되는 작업의 양이 줄어들기 때문에 가동정지 시간(downtime)이 줄어든다.



-- EMPNO 컬럼을 파티션 키로 하고 별도 테이블스페이스에 두개의 해시 파티션을 가지는 EMP 테이블을 생성한다.

CREATE TABLE emp
(
    empno   number,
    ename   varchar2(20)
)
PARTITION BY HASH (empno)
(
    partition part_1 tablespace P1,
    partition part_2 tablespace P2
)
/

Table created.


SQL>insert into emp select empno, ename from scott.emp;

14 rows created.

-- 테이블의 파티션 명(partition-extended table name)을 사용하여 각 파티션의 내용을 조회

SQL>select * from emp partition (part_1) ;

     EMPNO ENAME
---------- --------------------
      7369 SMITH
      7499 ALLEN
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7839 KING
      7876 ADAMS
      7934 MILLER

8 rows selected.

SQL>select * from emp partition (part_2) ;

     EMPNO ENAME
---------- --------------------
      7521 WARD
      7566 JONES
      7788 SCOTT
      7844 TURNER
      7900 JAMES
      7902 FORD

6 rows selected.

-- 해시 키 값의 해싱한 결과를 기준으로 파티션을 결정하기 때문에 데이터를 어느 파티션에 위치할지를 지정할 수 없다.

특정 테이블스페이스를 오프라인으로 변경 (디스크 실패로 가정)
전체 테이블 조회 시 쿼리실패



SQL> alter tablespace p1 offline;
Tablespace altered.


SQL> select * from emp ;
select *from emp
             *
         
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12:
'/data/data01.dbf'



- 바인드변수를 사용하여 실행계획 수립시 어떤 파티션을 액세스할 것인지 알지 못하도록 하더라고, 런타임시 파티션 제외를 수행할 수 있다. 

SQL>variable n number
SQL>exec :n := 7844;

PL/SQL procedure successfully completed.

SQL>select * from emp where empno = :n ;

     EMPNO ENAME
---------- --------------------
      7844 TURNER

관리비용 감소

작은 객체의 작업수행이 큰 객체에서의 같은 작업 수행보다 더 쉽고, 더 빠르고, 더 적은 자원을 수비한다.
테이블 재생성의 예



-- 일반 테이블 생성 : BIG_TABLE1

SQL> CREATE TABLE BIG_TABLE1 
(  ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
   OBJECT_ID, DATA_OBJECT_ID,
   OBJECT_TYPE, CREATED, LAST_DDL_TIME,
   TIMESTAMP, STATUS, TEMPORARY,
   GENERATED, SECONDARY )
TABLESPACE BIG1
AS
SELECT ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
   OBJECT_ID, DATA_OBJECT_ID,
   OBJECT_TYPE, CREATED, LAST_DDL_TIME,
   TIMESTAMP, STATUS, TEMPORARY,
   GENERATED, SECONDARY
FROM BIG_TABLE.BIG_TABLE ;

Table created.


-- 해쉬파티션 테이블 생성 : BIG_TABLE2

SQL> CREATE TABLE BIG_TABLE2
(  ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
   OBJECT_ID, DATA_OBJECT_ID,
   OBJECT_TYPE, CREATED, LAST_DDL_TIME,
   TIMESTAMP, STATUS, TEMPORARY,
   GENERATED, SECONDARY )
partition by hash(id)
( partition part_1 tablespace big2,
  partition part_2 tablespace big2,
  partition part_3 tablespace big2,
  partition part_4 tablespace big2,
  partition part_5 tablespace big2,
  partition part_6 tablespace big2,
  partition part_7 tablespace big2,
  partition part_8 tablespace big2
)
AS
SELECR ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
   OBJECT_ID, DATA_OBJECT_ID,
   OBJECT_TYPE, CREATED, LAST_DDL_TIME,
   TIMESTAMP, STATUS, TEMPORARY,
   GENERATED, SECONDARY
FROM BIG_TABLE.big_table;

Table created.

일반 테이블(BIG TABLE1) 재생성



SQL> select b.tablespace_name,
     mbytes_alloc,
     mbytes_free
    from ( select round(sum(bytes)/1024/1024) mbytes_free,
                  tablespace_name
                from dba_free_space
              group by tablespace_name ) a,
         ( select round(sum(bytes)/1024/1024) mbytes_alloc,
                  tablespace_name
                from dba_data_files
              group by tablespace_name ) b
            where a.tablespace_name(+) = b.tablespace_name
             and b.tablespace_name in ( 'BIG1','BIG2' ) ;

TABLESPACE_NAME                MBYTES_ALLOC MBYTES_FREE
------------------------------ ------------ -----------
BIG1                                   1496         344
BIG2                                   1496         344



-- big_table1 복사본을 저장할 수 있는 공간이 없어서 에러발생

SQL> alter table big_table1 move ;
alter table big_table1 move
            *

ERROR at line 1:
ORA-01652: unable to extend temp segment by 1024 in tablespace BIG1
    
    
    
-- 파티션 테이블은 각 파티션별로 move할 수 있다

SQL> alter table big_table2 move;
alter table big_table2 move;
            *

ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object


SQL> alter table big_table2 move partition part_1 ;

Table altered.

SQL> alter table big_table2 move partition part_2 ;

Table altered.

SQL> alter table big_table2 move partition part_3 ;

Table altered.

SQL> alter table big_table2 move partition part_4 ;

Table altered.

SQL> alter table big_table2 move partition part_5 ;

Table altered.

SQL> alter table big_table2 move partition part_6 ;

Table altered.

SQL> alter table big_table2 move partition part_7 ;

Table altered.

SQL> alter table big_table2 move partition part_8 ;

Table altered.

동적 쿼리를 사용한 partition move script



begin
  for x in ( select partition_name
               from user_tab_partitions
               where table_name ='BIG_TABLES2')
  loop
       execute immediate
       'alter table big_table2 move partition ' ||
       x.partition_name ;
  end loop ;
end;
/

PL/SQL procedure successfully completed.

데이터의 슬라이딩 윈도우(sliding window)
일반적인 DML (대량의 DELETE, INSERT) 작업 수행시 대량 UNDO, REDO발생

파티션일 경우

별도 테이블 생성 + 데이터 입력
별도 테이블에 인덱스 생성
ALTER TABLE EXCHANGE PARTITION 명령으로 별도 테이블을 파티션 테이블 끝에 추가
파티션 테이블의 다른 끝에서 오래된 파티션 제거

문장 성능 향상

병렬 DML (PDML)

PDML 동안 오라클은 INSERT, UPDATE, DELETE를 수행하는데 하나의 serial 프로세스 대신 여러 쓰레드나 프로세스를 사용한다.
Oracle 9i이전 버전에는 병렬DML 할 경우 파티셔닝이 반드시 필요하였음

쿼리 수행 성능

파티션 제외(Partition Elimination): 데이터의 일부 파티션은 쿼리 실행 시 처리 대상에서 제외된다
병렬 처리(Parallel Operation): 전체 테이블 스캔과 인덱스 스캔을 병렬로 처리한다.

시스템 유형별 파티셔닝의 장점

OLTP 시스템 : 경합을 줄임으로써 동시사용성을 증가시킬 수 있다.(성능 향상에 대한 효과는 거의 없다)
데이터 웨어하우스 시스템 : 전체데이터 분석 시 full table scan 범위를 파티션으로 줄일 수 있다. 병렬 쿼리 사용에 따른 시스템 자원을 최대한 활용

14S_파티셔닝 개요 (by feelie) [2015.01.02]

파티셔닝 개요

장점

가용성 증가

관리비용 감소

파티션일 경우

문장 성능 향상

병렬 DML (PDML)

쿼리 수행 성능

시스템 유형별 파티셔닝의 장점