오라클 파티셔닝 테이블(Oracle Partitioning Tables)
오라클의 파티셔닝 테이블partitioning tables은 물리적으로 큰 테이블의 데이터를 파티션 키값에 따라 여러 곳에 나누어 놓은 것 입니다. 테이블이 물리적으로 파티셔닝 되어 있다고 하더라도 논리적으로는 하나의 테이블이므로 테이블에 액세스하기 위해 SQL 쿼리 및 DML 문을 다르게 할 필요는 없습니다.
EMP 테이블의 데이터가 2기가 이상으로 아주 많은 행들을 가지고 있다고 가정을 해보겠습니다. 이 데이터 들이 물리적으로 하나의 데이터 파일에 있다면 수 많은 사용자들이 데이터를 조작하는 경우 I/O 경합으로 인해 성능에 악 영향을 줄수도 있으므로 부서코드를 기준으로 별도의 저장 공간(별도 디바이스의 데이터 파일datafile로 구성된 별개의 테이블스페이스tablespaces)에 저장을 한다면 성능에도 도움이 될 것 입니다. 사실 파티셔닝 테이블이 효과를 발휘하기 위해서는 데이터를 분할된 디바이스의 데이터 파일에 저장을 하도록 하는 것이 효율적 입니다.
파티셔닝 테이블은 Range Partitioning, List Partitioning, Hash Partitioning, Composite Partitioning Tables 여러 종류가 있습니다.
Range Partitioning Tables는 파티션 키값을 기준으로 값의 범위에 따라 데이터를 분할 하는 것인데 주로 날짜 컬럼들이 이용되고 주로 히스토리 테이블 처럼 이력을 저장하는 많은 데이터를 가진 테이블에 주로 사용 됩니다. 파티션 키는 1개에서 16개까지 가능 합니다.
List Partitioning Tables는 미리 정해진 그룹핑 조건에 따라 행들을 분할 저장 하는 것인데 파티션키는 하나의 컬럼만 가능 합니다. 예를들어 EMP 테이블의 컬럼중 직무(job) 컬럼의 값이 ‘CLERK’,’SALESMAN’ 이면 A에, ‘MANAGER’, ‘ANALYST’이면 B에 이런식으로 지정을 하는 것 입니다.
Hash Partitioning Tables는 파티션 키값에 해시함수를 적용하여 해시함수의 값에 따라 데이터를 분할 하는 것입니다.
Composite Partitioning Tables는 주 파티션을 두고 그 아래 서브 파티션을 둬서 주파티션키에 따라 1차적을 데이터를 분배하고 드 안에서 서브 파티션 키에 따라서 실제 저장할 위치를 지정하는 것 입니다.
테이블스페이스 및 데이터파일이 생소하신 분들은 10.1. 테이블 이해를 위한 오라클 구조를 참조하세요.
Range Partitioning, List Partitioning 두 경우를 별도의 테이블스페이스를 만들어서 실제 실습을 해보겠습니다. 디바이스 디스크가 하나밖에 없는 관계로 테이블스페이스를 분리된 데이터 파일에 할당하지는 못했지만 실무에서는 분리된 디바이스의 데이터 파일들에 대해 별도의 테이블스페이스로 만들어서 구성을 하는 것이 좋습니다.
실습
실습을 위해 USERS2 라는 테이블스페이스를 생성 합니다. 실습용 데이터베이스에는 USERS 라는 테이블스페이스는 기본적으로 생성되어 있습니다.
USERS2 테이블스페이스를 C:\app\oradata\ORCL\users02.dbf 파일로 100m 정도로 생성하세요. |
CREATE TABLESPACE USERS2 DATAFILE 'C:\app\oradata\ORCL\users02.dbf' SIZE 100m;
<실행결과>
Tablespace USERS2이(가) 생성되었습니다.
DBA_DATA_FILES 뷰는 DBA 권한이 있는 사용자가 조회할 수 있는 뷰로 데이터파일 및 테이블스페이스의 정보를 조회할 수 있습니다.
생성한 USERS2 테이블스페이스 및 데이터파일을 확인 하세요. |
SELECT FILE_NAME
, TABLESPACE_NAME
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'USER%';
<실행결과>
| FILE_NAME | TABLESPACE_NAME |
1 | C:\APP\ORADATA\ORCL\USERS01.DBF | USERS |
2 | C:\APP\ORADATA\ORCL\USERS02.DBF | USERS2 |
실습용 테이블 EMP10을 입사일을 파티션 키로 해서 Range Partitioning 테이블로 생성하고 EMP 테이블의 데이터를 입력 후 각 파티션별로 조회하여 봅시다.
실습
실습을 위해 EMP10 테이블을 생성 합니다. 이미 존재한다면 삭제 후 실습하세요.
다음과 같은 조건으로 EMP10 테이블을 생성하세요.
|
CREATE TABLE EMP10 (
EMPNO NUMBER CONSTRAINTS PK_EMP10 PRIMARY KEY ,
ENAME VARCHAR2(50),
HIREDATE DATE
)
PARTITION BY RANGE ( HIREDATE )
(
PARTITION P_82 VALUES LESS THAN ( TO_DATE('01/01/1982','dd/mm/yyyy')) TABLESPACE USERS,
PARTITION P_91 VALUES LESS THAN ( TO_DATE('01/01/1991','dd/mm/yyyy')) TABLESPACE USERS2
);
<실행결과>
Table EMP10이(가) 생성되었습니다.
EMP 테이블의 데이터를 파티션 테이블인 EMP10으로 입력 합니다.
INSERT ~ SELECT를 이용하여 EMP 테이블의 데이터를 EMP10으로 입력하세요. |
INSERT INTO EMP10 (EMPNO, ENAME, HIREDATE)
SELECT EMPNO, ENAME, HIREDATE FROM EMP;
<실행결과>
14개 행 이(가) 삽입되었습니다.
각 파티션 별로 데이터를 조회 합니다.
p_82 파티션 영역의 데이터를 조회하세요. |
SELECT * FROM EMP10 PARTITION (P_82);
<실행결과>
| EMPNO | ENAME | HIREDATE |
1 | 7369 | SMITH | 80/12/17 |
... | ... | ... | ... |
10 | 7900 | JAMES | 81/12/03 |
11 | 7902 | FORD | 81/12/03 |
입사일자가 1980년, 1981년 데이터만 조회 됩니다.
p_91 파티션 영역의 데이터를 조회하세요. |
SELECT * FROM EMP10 PARTITION (P_91);
<실행결과>
| EMPNO | ENAME | HIREDATE |
1 | 7788 | SCOTT | 1987-07-13 |
2 | 7876 | ADAMS | 1987-07-13 |
3 | 7934 | MILLER | 1982-01-23 |
입사일자가 1982년부터 1990년사이 데이터만 조회 됩니다.
파티션별이 아닌 전체 EMP10 데이터도 기존 SQL문과 동일한 문법으로 조회 가능 합니다.
EMP10 테이블의 전체 데이터를 조회하세요. |
SELECT * FROM EMP10;
<실행결과>
| EMPNO | ENAME | HIREDATE |
1 | 7369 | SMITH | 80/12/17 |
... | ... | ... | ... |
13 | 7876 | ADAMS | 87/07/13 |
14 | 7934 | MILLER | 82/01/23 |
실습용 테이블 EMP10을 입사일을 파티션 키로 해서 List Partitioning 테이블로 생성하고 EMP 테이블의 데이터를 입력 후 각 파티션별로 조회하여 봅시다.
실습
실습을 위해 EMP10 테이블을 생성 합니다. 이미 존재한다면 삭제 후 실습하세요.
다음과 같은 조건으로 EMP10 테이블을 생성하세요.
|
CREATE TABLE EMP10 (
EMPNO NUMBER CONSTRAINTS PK_EMP10 PRIMARY KEY ,
ENAME VARCHAR2(50),
JOB VARCHAR2(50)
)
PARTITION BY LIST ( JOB )
(
PARTITION P_1 VALUES ('CLERK', 'SALESMAN') TABLESPACE USERS,
PARTITION P_2 VALUES ('MANAGER', 'ANALYST' ,'PRESIDENT') TABLESPACE USERS2
);
<실행결과>
Table EMP10이(가) 생성되었습니다.
EMP 테이블의 데이터를 파티션 테이블인 EMP10으로 입력 합니다.
INSERT ~ SELECT를 이용하여 EMP 테이블의 데이터를 EMP10으로 입력하세요. |
INSERT INTO EMP10 (EMPNO, ENAME, JOB)
SELECT EMPNO, ENAME, JOB FROM EMP;
<실행결과>
14개 행 이(가) 삽입되었습니다.
각 파티션 별로 데이터를 조회 합니다.
직무(job)가 'CLERK', 'SALESMAN'인 데이터를 저장하는 p_1 파티션 영역의 데이터를 조회하세요. |
SELECT * FROM EMP10 PARTITION (P_1);
<실행결과>
| EMPNO | ENAME | JOB |
1 | 7369 | SMITH | CLERK |
2 | 7499 | ALLEN | SALESMAN |
... | ... | ... | ... |
7 | 7900 | JAMES | CLERK |
8 | 7934 | MILLER | CLERK |
직무가 'CLERK', 'SALESMAN'인 데이터만 조회 됩니다.
직무(job)가 'MANAGER', 'ANALYST’, ‘PRESIDENT'인 데이터를 저장하는 p_2 파티션 영역의 데이터를 조회하세요. |
SELECT * FROM EMP10 PARTITION (P_2);
<실행결과>
| EMPNO | ENAME | JOB |
1 | 7566 | JONES | MANAGER |
2 | 7698 | BLAKE | MANAGER |
3 | 7782 | CLARK | MANAGER |
4 | 7788 | SCOTT | ANALYST |
5 | 7839 | KING | PRESIDENT |
6 | 7902 | FORD | ANALYST |
직무가 'MANAGER', 'ANALYST’, ‘PRESIDENT'인 데이터만 조회 됩니다.
파티션별이 아닌 전체 EMP10 데이터도 기존 SQL문과 동일한 문법으로 조회 가능 합니다.
EMP10 테이블의 전체 데이터를 조회하세요. |
SELECT * FROM EMP10;
<실행결과>
| EMPNO | ENAME | JOB |
1 | 7369 | SMITH | CLERK |
2 | 7499 | ALLEN | SALESMAN |
... | ... | ... | ... |
13 | 7839 | KING | PRESIDENT |
14 | 7902 | FORD | ANALYST |
#파티셔닝테이블, #테이블파티션, #오라클PartitioningTable, #오라클파티셔닝테이블, #오라클, #오라클교육, #오라클강좌
