파티셔닝

 

파티셔닝이란?

• 큰 테이블이나 인덱스를 관리하기 쉬운 조각으로, 물리적으로 분할하는 것을 의미한다.
• 물리적인 데이터 분할이 있더라도, DB에 접근하는 애플리케이션의 입장에서는 이를 인식하지 못한다.
• Data Partitioning은 큰 테이블과 인덱스의 관리를 보다 쉽게 하기 위해 설계된다.

---

파티셔닝 사용의 장점

1. 가용성(Availability) : 물리적인 파티셔닝으로 인해 전체 데이터의 훼손 가능성이 줄어들고 데이터 가용성이 향상된다.
   • 파티션은 독립적으로 관리된다.
   • Backup and Restore을 파티션별로 작업할 수 있다.
   • 같은 테이블에서 Unavailable한 파티션은 다른 파티션에 영향을 주지 않는다.
2. 관리용이성(Manageability) : Database의 큰 객체들을 제거하여 관리를 쉽게 해준다.
   • 사용자가 지정한 값으로 파티션이 가능하다.
   • 테이블스페이스간에 파티션 이동이 가능하다.
   • 파티션 레벨에서 SELECT, DELETE, UPDATE가 가능하다.
3. 성능(Performance)
   
   • 데이터를 엑세스할 때 엑세스하는 범위를 줄여 성능 향상에 효율적이다.

---

Range Partition

• 일정한 범위의 값을 갖는 데이터를 동일한 파티션에 배치하는 방법으로, 일별/월별/분기별 등의 데이터에 적합하다.
• Range Partition에서 테이블은 단지 논리적인 구조이며 실제 데이터가 물리적으로 저장되는 곳은 파티션으로 나누어진 Tablespace에 저장된다.

CREATE TABLE part_range (
      ymd VARCHAR2(14),
      empno NUMBER
)
TABLESPACE tbs_part01
PARTITION BY RANGE (ymd) (
      PARTITION p200908 VALUES LESS THAN ('20090901000000')
      TABLESPACE tbs_part05,
      PARTITION VALUES LESS THAN (MAXVALUE)
);

PARTITION BY RANGE (COLUMN_LIST) : 어느 컬럼을 기준으로 분할할지 정한다.

VALUES LESS THAN (VALUE_LIST) : 각 파티션이 어떤 값의 범위를 포함할지 Upper Bound를 정한다.

 

Hash Partition

• Partitioning Column의 Partition Key 값에 Hash 함수를 적용하여 파티셔닝하는 방법으로, 균등한 데이터 분할이 가능하고 파티션의 위한 범위가 없는 데이터에 적합하다.
• SELECT 할 때 조건과 무관하게 병렬처리를 제공한다.(질의 성능 향상)
• 특정 데이터가 어느 Hash Partition에 있는지 판단은 불가능하다.

 

Hash Partition - Named Partitions

• Partition할 컬럼과 파티션의 이름을 직접 지정할 수 있다.

CREATE TABLE part_hash (
  empno NUMBER(4),
  empname VARCHAR2(20)
)
PARTITION BY HASH (empname) (
  PARTITION p_h1 tablespace tbs_part01,
  PARTITION p_h2 tablespace tbs_part02
);

 

Hash Partiton - Quantity of Partitions

• 생성하고자 하는 파티션의 수를 지정한다.
• 파티션의 수는 2의 제곱수만큼 지정한다.
• 파티션의 이름은 SYS_Pnnnn 형태로 자동 지정된다.

CREATE TABLE part_hash2 (
  empno NUMBER(4),
  empname VARCHAR2(20)
)
PARTITION BY HASH (empname)
PARTITIONS 4
STORE IN (tbs_part03, tbs_part04); // 4개의 파티션은 STORE 절에 기술된 TABLESPACE에 균등하게 생성된다.

 

List Partition

• 파티셔닝 컬럼의 특정 값으로 분할하는 방식이다.
• 데이터 분포도가 낮지 않고 균등하게 분포되어 있으며, 파티셔닝 컬럼의 조건이 많이 들어오는 경우 유용하다.
• Multi-Column Partition Key 제공이 불가능하다.
• Partition Key 값은 NULL 값을 포함한 어떠한 값이라도 한 번만 명시 가능하며, 대소문자를 구분하고 허용 문자 외 문자 입력 시 에러가 발생한다.
• 파티션 테이블에 정의되지 않는 나머지 데이터를 입력하고 싶다면 DEFALUT 파티션을 생성하면 된다.

CREATE TABLE part_list (
      name VARCHAR2(20),
      location VARCHAR2(20)
)
PARTITION BY LIST(location) (
      PARTITION p_seoul VALUES ('SEOUL')
      TABLESPACE tbs_part06,
      PARTITION p_jeju VALUES ('JEJU', NULL)
      TABLESPACE tbs_part07
);

/*
주의 
DEFALUT 파티션을 생성하면 더 이상 파티션 추가가 불가능하다. 추가하려면 DEFALUT 파티션 제거 후 추가해야 한다.
*/
ALTER part_list ADD PARTITION p_default VALUES ( DEFAULT );

 

Composite Partition

• 파티셔닝 컬럼을 Main-Sub 관계로 나누어 분할하는 방식이다.(두 가지 이상의 파티셔닝(Range-Hash, Range-List) 전략을 조합하여 사용)
• 물리적인 데이터는 Main Partition이 아닌 Sub Partition에 저장된다.
• 큰 파티션의 많은 I/O 요청을 여러 파티션으로 분산시킬 수 있다.

 

Composite Partition - Range Hash Partitioning

CREATE TABLE part_rh (
      empno NUMBER,
      empname VARCHAR2(20)
)
PARTITION BY RANGE (empno)
SUBPARTITION BY HASH (empname)
SUBPARTITIONS 4 STORE IN (tbs_part07, tbs_part08) (
      PARTITION comp_low VALUES LESS THAN (2000),
      PARTITION comp_hi VALUES LESS THAN (4000),
      PARTITION comp_max VALUES LESS THAN (MAXVALUE)
);

 

Reference Partition

• Reference Key로 지정된 경우 부모 테이블의 컬럼이 존재하지 않아도 부모의 Partition Key로 분할하는 방식이다.
• Foreign Key 제약조건이 설정되어 있어야 한다.
• 상속받는 테이블의 Key 값이 NOT NULL이어야 한다.

-- 부모 테이블
CREATE TABLE customers (
   cust_id   NUMBER PRIMARY KEY,
   cust_name    VARCHAR2(200),
   rating    VARCHAR2(1) NOT NULL
)
PARTITION BY LIST (rating) (
   PARTITION pA VALUES ('A'), 
   PARTITION pB VALUES ('B')
);

-- 자식 테이블
CREATE TABLE ref_sales (
  sales_id	NUMBER PRIMARY KEY,
  cust_id 	NUMBER NOT NULL,
  sales_amt 	NUMBER,
  CONSTRAINT fk_sales_01
    FOREIGN KEY (cust_id)
    REFERENCES customers
)
PARTITION BY REFERENCE (fk_sales_01);

 

Interval Partition

• Range Partition에서 특정 범위를 지정하고 관리할때는 미리 Range를 만들어주어야 하고, 생성 이후 분할 또는 병합을 할 때는 추가적인 작업을 해줘야 한다.
• 이러한 Range Partition의 단점을 보완한 기법으로, Partitioning Interval만 지정해주면 오라클이 자동으로 파티션을 생성해준다. 특정 범위에 맞지 않는 데이터가 INSERT 될 경우 새로운 파티션을 생성하여 데이터를 저장한다.

CREATE TABLE part_interval (
      ymd DATE,
      empno NUMBER
)
PARTITION BY RANGE(ymd)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
STORE IN ('TBS_PART05', 'TBS_PART06') (
    PARTITION p200908
    VALUES LESS THAN (to_date('2009-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
);

---

Partition Index

• Index는 테이블과 마찬가지로 파티셔닝이 가능하다.
• Partitioned Index(Partitioning Key와 Leading Index Key 동일) 또는 Non-Partitioned Index(Partitioning Key와 Leading Index Key 불일치)는 Partitioned Table 또는 Non-Partitioned Table과 함께 사용 가능하다.

 

Local Index

• Index와 테이블은 같은 컬럼에 의해 파티션된다.
• 하나의 인덱스 파티션이 테이블 파티션 하나와 대응되며, 대응되는 인덱스 파티션과 테이블 파티션은 각각 동일한 범위를 갖게 된다.
• 인덱스는 각 테이블 파티션 내에서만 작동하며 다른 파티션의 데이터를 참조하지 않는다. 즉, 특정한 하나의 인덱스에 포함된 모든 Key들은 하나의 테이블 파티션 내의 데이터를 가리키게 된다.

 

Prefixed Local Index

• Partitioned Table에만 생성 가능하다.
• Partition Key를 Index Key의 앞부분으로 사용한다. 즉, 인덱스가 테이블의 파티션과 동일한 방식으로 데이터를 분할한다.

 

Non-Prefixed Local Index

• Partitioned Table에만 생성할 수 있다.
• Partition Key를 인덱스의 앞부분으로 사용하지 않는다. Index Key는 Partition Key와 독립적일 수 있다.

 

Global Index

• 하나의 인덱스 파티션이 여러(또는 모든)테이블 파티션을 가리킬 수 있다.
• 인덱스 파티션 수는 테이블 파티션 수와 다를 수 있다.
• 한 테이블 파티션에 대한 변경이 인덱스 전체에 영향을 줄 수 있다.

---

Tablespace

실제 물리적인 공간은 데이터파일이다. 오라클 데이터베이스는 데이터 파일들을 가지고 있으며, 이 파일들에 데이터가 저장된다. 파일들이 데이터가 저장되는 물리적인 공간이다.

 

오라클 내부에서는 Data Block, Extent, Segment, Tablespace라는 논리적인 개념으로 데이터들을 관리한다. 오라클에서 데이터를 저장하는 가장 최소의 논리적인 단위가 데이터 블록이며, 데이터 블록이 모여서 익스텐트가 되고, 익스텐트가 모여 세그먼트, 세그먼트가 모여서 테이블 스테이스가 된다.

 

여기서 테이블 스페이스는 테이블, 인덱스, 프로시저, 뷰 등의 객체들을 저장해놓은 오라클의 논리적인 공간을 의미한다. 물리적인 데이터 파일은 테이블 스페이스와 대응되며 하나의 테이블스페이스는 최소 1개의 데이터 파일로 구성된다.

 

Partition Pruning

파티션 프루닝은 특정 쿼리가 처리해야 하는 데이터의 양을 줄이기 위해 필요하지 않은 파티션(즉, 쿼리의 결과에 영향을 주지 않는 파티션)을 제외하는 과정을 의미한다.

 

예를 들어, 12개의 월별 파티션으로 나누어진 테이블이 존재한다고 할 때, 특정 월에 대한 데이터를 조회하는 경우, 데이터베이스는 해당 월의 파티션만 접근하고 나머지 파티션은 무시(프루닝)한다. 이를 통해 쿼리 성능이 크게 향상된다.

 

장점

• 쿼리 성능 향상(필요한 파티션만 스캔 가능)
• I/O 감소(디스크에서 읽어야 하는 데이터 양 감소)
• 병렬 처리 향상(여러 파티션에 대한 쿼리 동시 실행 가능)
• 인덱스 효율성 향상

 

 

 

참고

파티셔닝 http://wiki.gurubee.net/pages/viewpage.action?pageId=3899999

파티션 프루닝

https://karisma3s.tistory.com/entry/%EC%84%B1%EB%8A%A5%EA%B3%A0%EB%8F%84%ED%99%94-62-%ED%8C%8C%ED%8B%B0%EC%85%98-Pruning