Oracle Partition 테이블 종류와 개념

파티션 테이블

- 하나의 테이블을 물리적으로 나눠놓은 것 , 물리적으로 나눠놨지만 논리적으로는 하나의 테이블로 간주된다.

왼쪽 그림 처럼 하나의 테이블에 전체 데이터가 들어가 있는 Non-Partitioned 테이블이 일반적인 테이블이라면

오른쪽 그림은 월 별로 다른 세그먼트에 EMP 데이터를 나눠서 넣을 수 있습니다.

이렇게 나눠서 넣어놔도 사용자는 1~3월치 데이터가 하나의 EMP 테이블에 들어있는 것 처럼 사용할 수 있습니다.

● 저장공간을 가지는 개념이 Segment에 해당

 

파티션을 사용하는 목적

1. 개선된 가용성

- 파티션은 독립적으로 관리된다.

- Backup and Restore를 파티션별로 작업할 수 있다.

- 같은 테이블에서 Unavailable한 파티션은 다른 파티션에 영향을 주지 않는다.

 

2. 관리의 용이성

- 사용자가 지정한 값으로 파티션이 가능

- 테이블스페이스간에 파티션 이동이 가능

- 파티션 레벨에서 SELECT, DELETE. UPDATE가 가능

 

3. 개선된 성능

- 데이터를 액세스할 때 액세스하는 범위를 줄여 성능향상에 도움이 된다

 

파티션 테이블 종류 

1) Range Partition : 특정 기준에 의해서 범위를 나눌 떄 사용

# Range Partition

CREATE TABLE PEMP
(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(10),
JOB   VARCHAR2(9),
MGR   NUMBER(4),
HIREDATE DATE,
SAL   NUMBER(7,2),
COMM  NUMBER(7,2),
DEPTNO NUMBER(2)
)
PARTITION BY RANGE(HIREDATE)
(
PARTITION PEMP1980 VALUES LESS THAN (TO_DATE('19810101','YYYYMMDD')),
PARTITION PEMP1981 VALUES LESS THAN (TO_DATE('19820101','YYYYMMDD')),
PARTITION PEMP1982 VALUES LESS THAN (TO_DATE('19830101','YYYYMMDD')),
PARTITION PEMP1983 VALUES LESS THAN (TO_DATE('19840101','YYYYMMDD')),
PARTITION PEMP1984 VALUES LESS THAN (TO_DATE('19850101','YYYYMMDD')),
PARTITION PEMP1985 VALUES LESS THAN (TO_DATE('19860101','YYYYMMDD')),
PARTITION PEMP1986 VALUES LESS THAN (TO_DATE('19870101','YYYYMMDD')),
PARTITION PEMP1987 VALUES LESS THAN (TO_DATE('19880101','YYYYMMDD'))
-- PARTITION PEMP1999 VALUES LESS THAN (MAXVALUE)
);

MAXVALUE 파티션이 있는 상태는 파티션 키 범위에 없는 데이터도 파티션에 매핑이 가능하다.

MAXVALUE 파티션이 없는 상태는 ADD로 추가해 줄 수 있음

ALTER TABLE PEMP ADD PARTITION PEMP1990 VALUES LESS THAN (TO_DATE('19910101','YYYYMMDD'));

기존에 있던 EMP 테이블의 데이터를 PEMP로 복사

INSERT INTO PEMP SELECT * FROM EMP;

생성 된 파티션 명 조회

SELECT * FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = 'PEMP';

PEMP1984 파티션에 들어있는 데이터 조회

SELECT * FROM PEMP PARTITION(PEMP1984);

● 파티션에 존재하는 데이터를 UPDATE를 하게 된다면 각 범위에 맞게 데이터들이 파티션으로 이동

 

 

2) List Partition : 특정한 값으로 구분되는 파티션 테이블

# List Partition

CREATE TABLE PEMP1
(
	EMPNO NUMBER PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB   VARCHAR2(9),
	MGR   NUMBER(4),
	HIREDATE DATE,
	SAL   NUMBER(7,2),
	COMM  NUMBER(7,2),
	DEPTNO NUMBER(2)
)
PARTITION BY LIST(DEPTNO)
(
	PARTITION PEMP1_10 VALUES(10),
	PARTITION PEMP1_20 VALUES(20),
	PARTITION PEMP1_30 VALUES(30),
	PARTITION PEMP1_40 VALUES(40)
--	PARTITION PEMP1_UNKNWON VALUES(DEFALUT)
--	PARTITION PEMP1_NULL VALUES(NULL)
);

기존에 있던 EMP 테이블의 데이터를 PEMP1 테이블로 복사

INSERT INTO PEMP1 SELECT * FROM EMP;

생성 된 파티션 명 조회

SELECT * FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = 'PEMP1';

PEMP1_20 파티션에 들어가 있는 데이터 조회

SELECT * FROM PEMP1 PARTITION(PEMP1_20);

만약, 파티션 범위외의 데이터를 삽입하게 되면 INSERT 되지 않고 오류가 발생한다.

INSERT INTO PEMP1 VALUES(7489, 'KANE', 'DEVELOPER', 0000, TO_DATE('19830424','YYYYMMDD'), 2200, 0, 50);

파티션을 만들 때 DEFALUT 키워드를 사용하게 되면 범위외의 데이터도 파티션에 매핑 할 수 있게 되고, 값이 아예 안들어오는 경우도 VALUES(NULL)로 지정해 주면 됩니다.

 

3) Hash Partition : 해시함수에 의해 자동으로 파티션 개수만큼 데이터가 분할되는 파티션 테이블

- 관리 목적에는 맞지 않고, 데이터를 여러 위치에 분산배치해서 Disk I/O 성능을 개선하기 위함

# Hash Partition

CREATE TABLE SAMPLE_T1 (
	COL1 NUMBER,
	COL2 NUMBER,
	NAME VARCHAR2(10)
)
PARTITION BY HASH(COL1)
PARTITIONS 4;

COL1 컬럼을 파티션키로 사용해서 4개의 해시 파티션을 만드는 샘플입니다.

파티션 명을 지정하지 않았기 때문에, 파티션명은 DBMS가 알아서 정해줍니다.

 

● 생성된 파티션명 조회

SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_COUNT AS SUB, TABLESPACE_NAME, COMPOSITE
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = 'SAMPLE_T1';

 파티션 조회

SELECT COUNT(*) FROM SAMPLE_T1 PARTITION(SYS_P101);
SELECT COUNT(*) FROM SAMPLE_T1 PARTITION(SYS_P102);
SELECT COUNT(*) FROM SAMPLE_T1 PARTITION(SYS_P103);
SELECT COUNT(*) FROM SAMPLE_T1 PARTITION(SYS_P104);

Hash 파티션 생성시 파티션명을 지정하고자 하는 경우, 아래와 같이 지정할 수 있다

# Hash Partition

CREATE TABLE SAMPLE_T1 (
	COL1 NUMBER,
	COL2 NUMBER,
	NAME VARCHAR2(10)
)
PARTITION BY HASH(COL1)
(
    PARTITION C1,
    PARTITION C2,
    PARTITION C3,
    PARTITION C4
 );