Oracle Partition Table(Range, List)

Partition Table

Partition Table이란 특정한 기준으로 데이터를 분할하여 저장해놓은 테이블이다. 논리적인 1개의 테이블에 대해서 물리적으로는 여러개의 파티션 테이블이 생성되는 것이다. 보통 오랜 기간 동안 쌓인 데이터를 수월하게 관리하고 성능저하를 방지하기 위한 용도로 사용한다. 서로 다른 파티션에 데이터를 저장함으로써 노드 간의 디스크 경합을 최소화하여 성능을 향상한다.

 

 

파티션 테이블의 장점

• 고가용성

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

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

 

• 편리한 관리

 - 사용자 지정 값으로 파티션이 가능하다.

 - 테이블스페이스 간 파티션 이동이 가능하다.

 

• 성능 개선

 - 데이터를 조회할 때 액세스하는 범위를 줄여 성능이 향상될 수 있다.

 

 

 

파티션 테이블 종류

RANGE

- 날짜나 숫자 처럼 범위를 가진 데이터(연속된 값)를 기준으로 하여 만든 파티션 테이블이다.

- 실제 데이터가 물리적으로 저장되는 곳은 Partition으로 나누어진 Tablespace이다.

 

INTERVAL

 - Range 파티션과 유사하며 파티션이 추가되는 기준(규칙)을 지정할 때 사용한다.
 - 기존 파티션에 데이터가 있고 새로운 데이터가 입력될 때에만 새로운 파티션을 생성한다.
 - Range 파티션에서 MAXVALUE 파티션 지정 없이 생성한 경우, 후에 데이터가 추가됐을 때 지정한 INTERVAL 만큼의 범위를 가지는 파티션이 생성된다.

 

REFERENCE

 - 자식 테이블 파티션이 부모 테이블 파티션과 일대일 관계인 환경에서 자식 테이블을 파티션할 때 사용한다.
 - 자식 테이블의 파티셔닝을 부모 테이블로부터 상속받는다.
 - 파티셔닝 키는 자식 테이블의 실제 컬럼에 저장되지 않는다.

 

LIST

- 특정값을 가진 데이터로 만든 파티션 테이블이다.

- 데이터가 균등하게 분포되어 있을때 유용하다.

HASH

- 해시 함수에 의해 자동으로 파티셔닝 되는 테이블이다.

- 해시 파티션은 데이터가 어느 파티션에 지정될지 알 수 없기 때문에 관리의 목적보다는, 데이터를 분산시켜 디스크 성능을 개선하는데 목적을 둔다.

 

 

 

 

 

Partition Table 실습

OS 환경 : Oracle Linux 6.3
DB 환경 : Oracle Database 12.1.0.2

 

Range Partition 실습

HR 계정의 EMPLOYEES 테이블을 조회하는데, 커미션이 적용된 연봉을 나타내는 컬럼인 TOT_SALARY 컬럼도 조회한다.

select salary, commission_pct, salary + salary * commission_pct tot_salary
from employees;

 

 

EMPLOYEES 테이블을 기반으로 EMP_PART라는 파티션 테이블을 생성하는데, TOT_SALARY 컬럼을 기준으로 파티셔닝 한다.

create table emp_part(
 EMPLOYEE_ID NUMBER(6),
 FIRST_NAME VARCHAR2(20),
 LAST_NAME VARCHAR2(25),
 EMAIL VARCHAR2(25),
 PHONE_NUMBER VARCHAR2(20),
 HIRE_DATE DATE,
 JOB_ID VARCHAR2(10),
 SALARY NUMBER(8,2),
 COMMISSION_PCT NUMBER(2,2),
 MANAGER_ID NUMBER(6),
 DEPARTMENT_ID NUMBER(4),
 TOT_SALARY AS (NVL(SALARY,0) + NVL(SALARY,0) * NVL(COMMISSION_PCT,0))
)partition by range (tot_salary)
(partition emp_part1 values less than (5000),
partition emp_part2 values less than (10000),
partition emp_part3 values less than (15000),
partition emp_max values less than (maxvalue)
);

 

 

 

EMPLOYEES 테이블의 데이터를 EMP_PART 파티션 테이블에 INSERT 한다. 그러면 자동으로 파티션 키 기준에 맞게 파티셔닝 된다.

insert into emp_part (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, 
SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID) select * from employees;

 

 

 

EMP_PART 테이블의 파티션 종류와 키 값(파티션) 개수를 조회한다.

select partitioning_type, partition_count 
from user_part_tables 
where table_name='EMP_PART';

 

 

파티션 키값들의 이름과 기준 값을 확인한다.

select partition_name, high_value 
from user_tab_partitions
where table_name='EMP_PART';

 

 

 

 

 

 

 

Range-Interval Partition 실습

SH 계정으로 접속하여 SALES_PART라는 파티션 테이블을 생성한다. 2000년 이전 데이터와 2000년~2002년 데이터로 파티션을 나눈 후, 그 이후의 데이터는 INTERVAL을 사용하여 분기 단위로 자동으로 파티션 되도록 구성한다.

create table sales_part(
prod_id number,
cust_id number,
time_id date,
channel_id number,
promo_id number,
quantity_sold number,
amount_sold number
)partition by range(time_id)
interval(numtoyminterval(3,'YEAR'))
(partition sales_2000_before values less than (to_date('2000-01-01','yyyy-mm-dd')),
partition sales_2002_before values less than (to_date('2002-01-01','yyyy-mm-dd'))
);

 

 

 

ORDER_ITEM_NEW 테이블의 파티션 종류와 키 값(파티션) 개수를 조회한다. INTERVAL 파티션의 경우 파티션의 개수가 다음과 같이 출력된다.

select partitioning_type, partition_count 
from user_part_tables 
where table_name='SALES_PART';

 

 

파티션 키값들의 이름과 기준 값을 확인한다.

SELECT TABLE_NAME, PARTITION_NAME, INTERVAL, HIGH_VALUE
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME ='SALES_PART';

 

 

 

 

 

Range-Reference Partition 실습

OE 계정에 접속하여 ORDER_NEW라는 파티션 테이블을 생성하는데, 파티션 키로 ORDER_DATA 컬럼을 사용한다. 2020년의 분기별로 데이터를 분할한다.

create table order_new (
order_id number primary key,
order_date date,
customer_id number,
order_total number,
order_status number
) partition by range (order_date)
(partition order_part1 values less than (to_date('2020/01/01','yyyy/mm/dd')),
 partition order_part2 values less than (to_date('2020/04/01','yyyy/mm/dd')),
 partition order_part3 values less than (to_date('2020/07/01','yyyy/mm/dd')),
 partition order_max values less than (maxvalue)
);

 

 

ORDER_NEW 테이블의 파티션 종류와 키 값(파티션) 개수를 조회한다.

select partitioning_type, partition_count 
from user_part_tables 
where table_name='ORDER_NEW';

 

 

파티션 키값들의 이름과 기준 값을 확인한다.

select partition_name, high_value 
from user_tab_partitions
where table_name='ORDER_NEW';

 

 

ORDER_ITEM_NEW 파티션 테이블을 생성하는데 파티션 키는 ORDER_NEW 테이블의 파티션 키와 동일하게 설정한다.

create table order_item_new (
order_id number not null,
line_item_id number,
product_id number,
unit_price number,
quantity number,
constraint order_id_fk foreign key (order_id) references order_new (order_id)
)  partition by reference (order_id_fk);

 

 

 

ORDER_ITEM_NEW 테이블의 파티션 종류와 키 값(파티션) 개수를 조회한다.

select partitioning_type, partition_count 
from user_part_tables 
where table_name='ORDER_ITEM_NEW';

 

 

파티션 키값들의 이름과 기준 값을 확인한다.

select table_name, partition_name, high_value 
from user_tab_partitions
where table_name like 'ORDER_%';

 

 

 

 

 

 

LIST PARTITION

SCOTT 계정에 접속하여 dept_part라는 파티션 테이블을 생성한다. 특정 기준 값으로 deptno 컬럼을 사용한다.

testdb> create table dept_part(
deptno number ,dname varchar2(20) 
) partition by list(deptno)
(partition dept_10 values(10), 
partition dept_20 values(20), 
partition dept_30 values(30), 
partition dept_40 values(40));

 

 

 

DEPT 테이블의 데이터를 DEPT_PART 파티션 테이블에 INSERT 한다. 그러면 자동으로 파티션 키 기준에 맞게 파티셔닝 된다.

testdb> insert into dept_part select deptno, dname from dept;

 

 

 

DEPT_PART 테이블의 파티션 종류와 키 값(파티션) 개수를 조회한다.

select partitioning_type, partition_count
from user_part_tables
where table_name = 'DEPT_PART';

 

 

파티션 키값들의 이름과 기준 값을 확인한다.

select partition_name, high_value
from user_tab_partitions
where table_name like 'DEPT_PART';