[ORACLE] 오라클 파티셔닝 (ORACLE Partitioning)

오라클 파티셔닝 (ORACLE Partitioning)

파티션이란 테이블에 있는 특정 컬럼값을 기준으로 데이터를 분할해 저장해놓은 테이블. 이때 논리적인 테이블은 1개이지만 물리적으로는 분할한 만큼 파티션이 만들어져 입력되는 컬럼 값에 따라 분할된 파티션별로 데이터가 저장되며 파티션 테이블을 만드는 목적은 대용량 테이블의 경우 데이터 조회 시 효율성과 성능을 높이기 위한 것

 

관리적 측면 이점 : 파티션 단위 백업, 추가, 삭제, 변경

성능적 측면 이점 : 파티션 단위 조회 및 DML 수행

 

- 클러스터, IOT와 마찬가지로 관련 있는 데이터가 흩어지지 않고 물리적으로 인접하도록 저장하는 클러스터링 기술에 속함
- 내부에 몇 개의 세그먼트를 생성하고 그것들이 논리적으로 하나의 오브젝트임을 메타 정보로 딕셔너리에 저장해 두는 것(테이블:세그먼트 = 1:M)

 

파티션 테이블 생성

create table SALES (
  sales_no       number,
  sale_year      number,
  sale_month     number,
  sale_day       number,
  customer_name  varchar2(30),
  birth_date     date,
  price          number,
  state          varchar2(2)
)
partition by hash (birth_date)
subpartition by hash (sales_no) subpartition template
(
  subpartition S1, 
  subpartition S2,
  subpartition S3,
  subpartition S4
)
(
  partition SALES_P1, 
  partition SALES_P2,
  partition SALES_P3,
  partition SALES_P4
);

 

birth_date 컬럼을 파티션키로 하는 Hash 파티션을 메인 파티션으로 만들고, 그 파티션들을 다시 sales_no 컬럼을 서브파티션키로 하는 Hash 서브파티션으로 만들어 복합파티션으로 구성.

서브파티션을 만들지 않고 그냥 파티션만 만들고싶다면 subpartition 부분만 삭제하면 됨.

 

파티션 테이블 조회

--문법
SELECT  * FROM [테이블명] PARTITION ([파티션명])

--예제
SELECT * FROM SALES PARTITION (SALES_P1);

 

파티션 테이블 데이터 변경

--파티션 테이블 UPDATE
UPDATE 테이블명 PARTITION (파티션명)
SET
컬럼1 = 변경값
컬럼2 = 변경값
....
WHERE
조건문;

 

파티션 테이블 데이터 삭제

--파티션 DELETE문
DELETE FROM 테이블명 PARTITION (파티션명)
WHERE
조건;

 

기타 파티션 관련 조회 쿼리

 --전체 테이블 파티션 조회
 SELECT * FROM ALL_TAB_PARTITIONS
 
 --접속계정 테이블 파티션 조회
 SELECT * FROM USER_TAB_PARTITIONS
 
  --파티션 테이블 키 컬럼 조회
 SELECT * FROM ALL_PART_KEY_COLUMNS WHERE NAME = 'SALES'; 
 
 --파티션명 조회
 SELECT * FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES'

 

RNAGE 파티셔닝

- 주로 날짜 컬럼을 기준으로 파티셔닝
- 이력성 데이터 조회 시 성능이 크게 향상됨
- 파티션 키 컬럼 : 1~16개 까지 가능 

CREATE TABLE ORD_RANGE ( 
        ORD_NO NUMER(10)  NOT NULL 
      , ORD_DT VARCHAR2(8) NOT NULL 
      , ORD_HMS VARCHAR2(6) 
      , BRANCH_CD VARCHAR2(10) 
      , ... 
) 
PARTITION BY RANGE(ORD_DT) ( 
        PARTITION P201901 VALUES LESS THAN('201902') 
      , PARTITION P201902 VALUES LESS THAN('201903') 
      , PARTITION P201903 VALUES LESS THAN('201904') 
      , PARTITION P201904 VALUES LESS THAN('201905') 

      , PARTITION P_DEFAULT VALUES LESS THAN(MAXVALUE) 
); 

 

HASH 파티셔닝

CREATE TABLE CUST_HASH( 
        CUST_NO VARCHAR(10) NOT NULL 
      , CUST_ID VARCHAR(30) 
      , CUST_NAME VARCHAR(50) 
      , ... 
) 
PARTITION BY HASH (CUST_NO) PARTITIOS 8; 

 

LIST 파티셔닝

CREATE TABLE PRD_LIST( 
PRODUCT_CD VARCHAR2(10) 
      , PRODUCT_NM VARCHAR2(100) 
      , LOCAL VARCHAR2(20) 
      , ... 
) 
PARTITION BY LIST(LOCAL)  
( 
        PARTITION P_지역1 VALUES ('서울') 
      , PARTITION P_지역2 VALUES ('경기', '인천') 
      , PARTITION P_지역3 VALUES ('부산', '대구', '대전', '광주') 
      , PARTITION P_기타 VALUES (DEFAULT) -> 기타 지역 
); 

 

결합(복합) 파티셔닝

- 서브 파티션마다 세그먼트를 하나씩 할당하고, 서브 파티션 단위로 데이터를 저장
- 주 파티션 키에 따라 1차적으로 데이터를 분배, 서브 파티션 키에 따라 최종적으로 저장할 위치를 결정
- 메인 파티션 : Range와 List만 가능
- 서브 파티션 : 모든 방식 가능
- 보관 기준과 처리 기준이 다를 때 사용하면 유용
  ex) Range(삭제기준컬럼) - Hash(조인기준컬럼)

 

CREATE TABLE 판매 ( 판매점 VARCHAR2(10), 판매일자 VARCHAR2(8) )
PARTITION BY RANGE(판매일자)                     
SUBPARTITION BY LIST(판매점)
SUBPARTITION TEMPLATE  
( 
       SUBPARTITION LST_01 VALUES ('강남지점', '강북지점', '강서지점', '강동지점') 
     , SUBPARTITION LST_02 VALUES ('부산지점', '대전지점')
     , SUBPARTITION LST_03 VALUES ('인천지점', '제주지점', '의정부지점')
     , SUBPARTITION LST_99 VALUES ( DEFAULT ) 
)                    
( 
        PARTITION P2009_Q1 VALUES LESS THAN('20090401')
      , PARTITION P2009_Q2 VALUES LESS THAN('20090701')
      , PARTITION P2009_Q3 VALUES LESS THAN('20091001')
      , PARTITION P2009_Q4 VALUES LESS THAN('20100101') 
);