파티셔닝 개요

분할정복(divide and conquer) 개념을 사용해 큰 테이블과 인덱스 관리에 유용
파티션키(Partition key) : 어떤 범위 값, 특정 값의 리스트, 해시 함수의 값으로 데이터를 나누는데 사용되는 값

장점

데이터 가용성 증가
큰 세그먼트 관리의 용이
쿼리 성능 향상
여러 개별 파티션에서 수정이 광범위하게 발생하는 대량 OLTP 환경에서 경합을 줄임

가용성 증가

파티션을 독립적으로 사용이 가능하게 됨에 따라 가용성이 증가.



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

Table created.

Elapsed: 00:00:00.08


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


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

14 rows created.

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.

-- hash partition으로 hash key에 따라 파티셔닝되어 특정 파티션을 지정할 수 없음
-- 다른 파티셔닝 방식으로는 지정이 가능함.

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



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'



- 바인드변수로 조건을 부여하여 조회 시 P2 파티션 내 데이터는 조회가능

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

PL/SQL procedure successfully completed.

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

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

관리비용 감소

Table/Index 재생성 시 자원 소비가 적음.



-- 일반 테이블 생성 : 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
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.

Table move Test



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                                  44000         164
BIG2                                  74000         206



-- 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)
최근 N년 동안의 기간계 데이터를 유지할 경우
일반적인 DML (대량의 DELETE, INSERT) 작업 수행시 대량 UNDO, REDO발생
파티션일 경우
1. 별도 테이블 생성 + 데이터 입력
2. 별도 테이블에 인덱스 생성
3. ALTER TABLE EXCHANGE PARTITION 명령으로 별도 테이블을 파티션 테이블 끝에 추가
4. 파티션 테이블의 다른 끝에서 오래된 파티션 제거

문장 성능 향상

병렬 DML (PDML)

INSERT, UPDATE, DELETE 수행 시 여러 쓰레드나 프로세스를 사용하여 동작
Oracle 9i 까지는 병렬DML 할 경우 파티셔닝이 반드시 필요하였음

쿼리 수행 성능

파티션 제외 : 쿼리 실행 시 일부 파티션은 처리 대상에서 제외됨
병렬 처리 : Table full scan, Index range scan 시 병렬로 처리됨

시스템별 파티셔닝의 장점

OLTP 시스템 : 경합을 줄임으로써 동시사용성을 증가시킬 수 있다.
데이터 웨어하우스 시스템 : 전체데이터 분석 시 full table scan 범위를 파티션으로 줄일 수 있다.

파티셔닝 개요 (by dolphhong) [2012.11.30]

파티셔닝 개요

가용성 증가

관리비용 감소

문장 성능 향상