Partition Table In Oracle

파티션 테이블이란 ?


  – Partitioning(분할)은 용량이 큰(지속적으로 증가하는) Table들을 Partition이라는 보다 작은 단위로 나눔으로써 성능이 저하되는 것을 방지하고 관리를 보다 수월하게 하고자 하는 개념 입니다.


  – 각 Partition 은 별개의 Segment에 저장 되어 개별적으로 관리 가능하며, 다른 partition 에 독립적이기 때문에 더 나은 성능을 가져 옵니다.


  – 각 Partition은 열(Column)과 제약조건에 대한 정의를 공유하지만 별도의 SEGMENT로 저장되어 물리적인
속성인 PCTFREE, PCTUSED, TABLESPACE 및 STORAGE PARAMETER등은 다르게 지정할 수 있습니다.


  – Partition Table과 Index 는 VLDB(Very Large Databases) 즉, Data Warehouse(DW)시스템과 의사결정 시스템의 경우 성능과 관리에 많은 향상을 가져 옵니다.


  – Partition의 종류에는 Range partition, Hash partition, List partition, Composite partition등이 있습니다.


 


파티션 테이블 장점


  – 데이터 액세스시 범위를 액세스 범위를 줄여 Performance 향상을 가져올 수 있습니다.


  – 여러 분할 영역으로 관리되어 데이터 훼손 가능성이 감소 되고, I/O 성능 향상을 가져 올 수 있습니다.


  – 각 분할 영역을 독립적으로 백업하고 복구 할 수 있습니다.


사용자 삽입 이미지

사용자 삽입 이미지


Range Partition 이란?


  – Range Partition은 Column 값 의 범위를 기준으로 하여 행을 분할하는 형태로, 달, 분기 등의 logical 한 범위의 분산에 주로 사용 합니다.


  즉, 정해진 범위에 따라 비슷한 크기로 partition 이 예상되는 곳에 효율적입니다.


  – Range Partition을 생성할 때 어느 행을 기준으로 어느 만큼의 값의 범위로 분할 할지를 다음 두 절에서 정의하게 됩니다.


PARTITION BY RANGE (column list..)


  기본 Table에서 어느 Column을 기준으로 분할할지를 정합니다.


VALUES LESS THAN(value list.. ) :


  각 Partition이 어떤 값의 범위를 포함 할지 MAX Value값을 정합니다.


  – PARTITION BY RANGE 절에서 지정 할 수 있는 Column은 한 개의 Column만으로 구성할 할 수도 있고 결합인덱스처럼 여러 개의 Column이 지정될 수 도 있습니다.


 


Range Partition 예제


① 파티션 테이블 생성


  PARTITION BY RANGE 절에 어떤 column들을 기준으로 하여 Partition을 나눌 것인지 지정을 하고, 각 Partition이 나누어 지는 범위는 VALUES LESS THAN 절에서 지정해 주면 됩니다.


 
SQL> CREATE TABLE sales
        (sales_no NUMBER,
         sale_year INT NOT NULL,
         sale_month INT NOT NULL,
         sale_day INT NOT NULL,
         customer_name  VARCHAR2(30),
         price NUMBER)
       PARTITION BY RANGE (sale_year, sale_month, sale_day)
       (PARTITION sales_q1 VALUES LESS THAN (2005, 01, 01) TABLESPACE ASSM_TBS1,
        PARTITION sales_q2 VALUES LESS THAN (2005, 07, 01) TABLESPACE ASSM_TBS2,
        PARTITION sales_q3 VALUES LESS THAN (2006, 01, 01) TABLESPACE ASSM_TBS3,
        PARTITION sales_q4 VALUES LESS THAN (2006, 07, 01) TABLESPACE ASSM_TBS4 );
    


② 데이터 조작


  아래와 같이 INSERT 문장을 실행 하면 파티션 테이블에서 지정한 범위에 따라서 자동으로 파티션이 지정 됩니다.


 
-- Range 파티션 INSERt 예제
INSERT INTO sales VALUES(1, 2004, 06, 12, 'scott', 2500);
INSERT INTO sales VALUES(2, 2005, 06, 17, 'jones', 4300);
INSERT INTO sales VALUES(3, 2005, 12, 12, 'miller', 1200);
INSERT INTO sales VALUES(4, 2006, 06, 22, 'ford', 5200);
INSERT INTO sales VALUES(5, 2005, 01, 01, 'lion', 2200); 
INSERT INTO sales VALUES(6, 2006, 12, 22, 'tiger', 3300);
COMMIT;

--> 범위 초과로 ORA-14400 에러 발생
INSERT INTO sales VALUES(6, 2006, 12, 22, 'tiger', 3300); 
1행에 오류:
ORA-14400: 삽입된 분할 영역 키와 매핑되는 분할 영역이 없음  


-- 각 파티션 마다 데이터가 INSERT 되었는지는 
-- 직접 SELECT 문으로 확인 해 보세요.
SELECT sales_no FROM sales PARTITION (sales_q1); --> 1
SELECT sales_no FROM sales PARTITION (sales_q2); --> 2, 5
SELECT sales_no FROM sales PARTITION (sales_q3); --> 3
SELECT sales_no FROM sales PARTITION (sales_q4); --> 4
    


③ 파티션 추가


  MAXVALUE partition 이 존재하면 추가가 불가능 합니다.


 
-- sales 파티션 테이블에 새로운 파티션 sales_q5를 추가하는 예제 입니다.
SQL> ALTER TABLE sales
     ADD PARTITION sales_q5 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE )
     TABLESPACE ASSM_TBS5;
    


④ 파티션 삭제


  Range, List 파티션만 가능 합니다.


  하나의 파티션은 반드시 남아 있어야 합니다.


  한번의 하나의 파티션만 삭제 가능 합니다. 여러 개의 파티션을 삭제하고자 할 때는 삭제 문장을 여러 번 실행 시켜야 합니다.


 
-- sales 테이블의 sales_q5 파티션을 삭제
SQL> ALTER TABLE sales DROP PARTITION sales_q5;
    


⑤ 파티션 이름 변경


  sales 테이블의 sales_q4 파티션 이름을 sales_four로 변경하는 예제 입니다.


 
SQL> ALTER TABLE sales RENAME PARTITION sales_q4 TO sales_four;
    


⑥ 파티션의 병합(MERGE)


  파티션 병합은 두 파티션의 데이터를 합치고, 하나의 파티션을 DROP 합니다.


  Hash Partition, Subpartition은 MERGE 작업을 할 수 없습니다.


 
--  sales_q1 파티션과 sales_q2 파티션을 sales_q2 파티션으로 병합
SQL> ALTER TABLE sales
     MERGE PARTITIONS sales_q1, sales_q2 INTO PARTITION sales_q2
     UPDATE INDEXES; --> Local Index를 갱신
    


⑦ 파티션의 분할(SPLIT)


  SPLIT 작업은 하나의 파티션을 두 개의 새로운 파티션으로 분할 합니다.


  Hash Partition, Subpartition은 SPLIT 작업을 할 수 없습니다


  아래는 sales 파티션 테이블의 sales_q2 파티션을 (2005,01,01) 값을 기준으로 sales_q1 와 sales_q2로 파티션을 분할하는 예제 입니다.


 
SQL> ALTER TABLE sales
     SPLIT PARTITION sales_q2 AT (2005, 01, 01)
     INTO (PARTITION sales_q1 TABLESPACE ASSM_TBS1,
           PARTITION sales_q2 TABLESPACE ASSM_TBS2)
    


⑧ 파티션의 변경(EXCHANGE)


  파티션의 EXCHAGEN는 파티션 데이터를 일반테이블로 생성하는 작업입니다. 물론 일반 테이블의 데이터를 파티션 테이블의 데이터로 생성 할 수도 있습니다.


  아래 예제는 파티션 테이블의 데이터를 일반 테이블로 생성하는 예제 입니다.


 
-- 파티션 데이터를 일반테이블로 이동하기 위한 테이블을 생성 합니다.
SQL> CREATE TABLE sales_ex
       (sales_no NUMBER,
        sale_year INT NOT NULL,
        sale_month INT NOT NULL,
        sale_day INT NOT NULL,
        customer_name  VARCHAR2(30),
        price NUMBER)
     TABLESPACE ASSM_TBS1;


-- 파티션 데이터를 일반 테이블로 변경 합니다.
SQL> ALTER TABLE sales
     EXCHANGE PARTITION sales_q1 
     WITH TABLE sales_ex;


-- 파티션 테이블의 데이터를 조회 해봅니다.
SQL> SELECT sales_no 
     FROM sales PARTITION (sales_q1); 
선택된 레코드가 없습니다.


-- 파티션 데이터를 이동한 일반 테이블의 데이터를 조회 해봅니다.
SQL> SELECT sales_no FROM sales_ex;  --> 1
    


⑨ 파티션의 테이블스페이스 변경


  sales 테이블의 sales_q3 파티션의 테이블스페이스를 ASSM_TBS5로 변경하는 예제 입니다.


 
SQL> ALTER TABLE sales 
     MOVE PARTITION sales_q3 
     TABLESPACE ASSM_TBS5;
    


⑩ 파티션 데이터 TRUNCATE


  sales 테이블의 sales_q3 파티션을 TRUNCATE 하는 예제 입니다.


 
SQL> ALTER TABLE sales TRUNCATE PARTITION sales_q3;
    

Hash Partition 이란?

  – Hash Partition은 Partitioning Key 값에 해시 함수를 적용하여 Data를 분할하는 방식으로 History Data의 관리의 목적 보다는 성능 향상의 목적으로 나온 개념 입니다

  – Hash Partition 은 Range Partition 으로 만들기 힘든 사항 즉, 조건을 주기 힘든 경우, 각 파티션 이 고르게 나누어지지 않아 밸런스을 유지하기 힘든 경우라고 판단되는 경우에 유리합니다.

  – Hash Partition 의 경우 각각 다른 파티션에 데이터가 고르게 분산시키기 위해서는 반드시 파티션 의 개수를 명시하여야 하며, 파티션의 수를 2 의 거듭 제곱수 (즉, 2,4,8,16 ….)로 설정하여야 합니다.

  – NULL 값은 첫 번째 파티션에 위치하게 됩니다.

Hash Partition 예제

① 파티션 테이블 생성

  아래 예제 Partition Table 은 입력되는 sales_no의 값을 가지고 Hash 함수를 이용하여 각 Row가 저장될 Partition을 내부적으로 결정하게 된다.

 
 -- 생성 예제 1
 SQL> CREATE TABLE sales_hash_exam
       (sales_no NUMBER,
        sale_year CHAR(4) NOT NULL,
        sale_month CHAR(2) NOT NULL,
        sale_day CHAR(2) NOT NULL,
        customer_name  VARCHAR2(30),
        price NUMBER)
      PARTITION BY HASH (sales_no)
      PARTITIONS 4   --> 2 의 거듭제곱수로 지정
      STORE IN (ASSM_TBS1, ASSM_TBS2, ASSM_TBS3, ASSM_TBS4);
 
 
-- 생성 예제 2
-- sales_hash1, sales_hash2..와 같이 파티션을 직접 지정 할 수 있다.
-- 하지만 각 파티션에  어떤 데이터가 지정될지 모르므로 무의미 할 수 있다.
SQL> CREATE TABLE sales_hash
       (sales_no NUMBER,
        sale_year CHAR(4) NOT NULL,
        sale_month CHAR(2) NOT NULL,
        sale_day CHAR(2) NOT NULL,
        customer_name  VARCHAR2(30),
        price NUMBER)
     PARTITION BY HASH (sales_no) 
       (PARTITION sales_hash1 TABLESPACE ASSM_TBS1,
        PARTITION sales_hash2 TABLESPACE ASSM_TBS2,
        PARTITION sales_hash3 TABLESPACE ASSM_TBS3,
        PARTITION sales_hash4 TABLESPACE ASSM_TBS4);
    
② 데이터 조작

  아래와 같이 데이터를 INSERT 하고, 데이터가 저장된 파티션을 확인해 보자. 데이터가 오라클 내부 해쉬 알고리즘에 위해서 파티션이 지정 되었음을 알 수 있다

 
-- INSERT
INSERT INTO sales_hash VALUES(1, 2004, 06, 12, 'scott', 2500);
INSERT INTO sales_hash VALUES(2, 2005, 06, 17, 'jones', 4300);
INSERT INTO sales_hash VALUES(3, 2005, 12, 12, 'miller', 1200);
INSERT INTO sales_hash VALUES(4, 2006, 06, 22, 'ford', 5200);
INSERT INTO sales_hash VALUES(5, 2005, 01, 01, 'lion', 2200);
INSERT INTO sales_hash VALUES(6, 2006, 12, 22, 'tiger', 3300);
COMMIT;


-- 조회
SELECT sales_no FROM sales_hash PARTITION (sales_hash1); --> 6
SELECT sales_no FROM sales_hash PARTITION (sales_hash2); --> 레코드 없음
SELECT sales_no FROM sales_hash PARTITION (sales_hash3); --> 2,5
SELECT sales_no FROM sales_hash PARTITION (sales_hash4); --> 1,3,4
    
출처 : 
http://oracleclub.com/lecture/1906
http://oracleclub.com/lecture/1908
http://oracleclub.com/lecture/1909

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다