PARTITION 옵션_나누기, 추가, 수정, 삭제, 통계 수집

# partition 나누기
#) partition p3 values less than(25000)을 나누기

alter table sal_emp split partition p3 at(20000) into (partition p3, partition p4);

=> Table altered.

#) 딕셔너리 확인

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len 
from user_tab_partitions 
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE   TABLESPACE   NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ------------ ---------- ---------- ---------- -----------
P1              5000         USERS
P2              15000        USERS
P3              20000        USERS
P4              25000        USERS

=> p3,p4 로 나누기

# partition 추가: add partition 파티션명

alter table hr.sal_emp add partition p5_1 values less than(30000);

=> Table altered.

#) 추가 확인

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE                     TABLESPACE   NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ------------------------------ ---------- ---------- ---------- -----------
P1              5000                           USERS
P2              15000                          USERS
P3              20000                          USERS
P4              25000                          USERS
P5_1            30000                          USERS

# partition명 수정: renamt partition (예전이름) to (새로운 이름)

alter table hr.sal_emp rename partition p5_1 to p5;

=> Table altered.

#) 추가 확인

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE                     TABLESPACE   NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ------------------------------ ---------- ---------- ---------- -----------
P1              5000                           USERS
P2              15000                          USERS
P3              20000                          USERS
P4              25000                          USERS
P5              30000                          USERS

# partition 삭제: drop partition (삭제할 파티션명)

alter table sal_emp drop partition p5;

=> Table altered

#) 확인

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE                     TABLESPACE   NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ------------------------------ ---------- ---------- ---------- -----------
P1              5000                           USERS
P2              15000                          USERS
P3              20000                          USERS
P4              25000                          USERS

 

partition 나누기	alter table (테이블명) split partition (파티션 나눌 파티션이름) at (범위) into (partition 나눌 파티션명1, partition 파티션명2);
partition 추가	alter table (테이블명)  add partition (파티션 추가할 이름) values less than (범위);
partition 이름 수정	alter table (테이블명) rename partition (파티션 기존 이름) to (파티션 변경할 이름);
partition 삭제	alter table (테이블명) drop partition (삭제할 파티션 이름);

---

#) 파티션으로 데이터 확인: p4, p3

select * from hr.sal_emp partition(p4);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        100 King                           24000            90

 

select * from hr.sal_emp partition(p3);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        101 Kochhar                        17000            90
        102 De Haan                        17000            90

# 특정한 partition data truncate
#) 파티션 내에 있는 data 삭제하려면?

alter table hr.sal_emp truncate partition p3;

=> Table truncated.

#) data 확인

select * from hr.sal_emp partition(p3);

=> no rows selected
=> undo, redo 발생량을 위해 truncate를 하는 것이 좋다.

#) insert문 이후 다시 확인.

insert into hr.sal_emp 
select employee_id, last_name, salary, department_id 
from hr.employees 
where employee_id in (101, 102);

=> 2 rows created.

commit;

=> Commit complete.

select * from hr.sal_emp partition(p3);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        101 Kochhar                        17000            90
        102 De Haan                        17000            90

#) update문 이후 저장 및 확인

update hr.sal_emp set salary = 24000 where employee_id = 100;

=> 1 row updated.

commit;

=> Commit complete.

 

select * from hr.sal_emp partition(p4);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        100 King                           24000            90

 

---

#) if, update문으로 옮기지지 않는 경우!
=> salary = 24000: 파티션 키 컬럼을 수정할 경우 생길 수 있는 오류이다

select salary from hr.sal_emp where employee_id = 100;

    SALARY
----------
     2000

 

update hr.sal_emp set salary = 24000 where employee_id = 100;

=> 오류발생
=> ORA-14402: updating partition key column would cause a partition change

alter table hr.sal_emp enable row movement;

=> Table altered.

update hr.sal_emp set salary = 24000 where employee_id = 100;

=> 1 row updated.

commit;

=> Commit complete.

alter table hr.sal_emp disable row movement;

=> Table altered.

select * from hr.sal_emp partition(p4);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        100 King                           24000            90

---

# partition maxvalue 추가

alter table hr.sal_emp add partition pmax values less than (maxvalue);

=> Table altered.

#) maxvalue 추가 확인

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE                     TABLESPACE   NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ------------------------------ ---------- ---------- ---------- -----------
P1              5000                           USERS
P2              15000                          USERS
P3              20000                          USERS
P4              25000                          USERS
PMAX            MAXVALUE                       USERS

#) insert문 진행

insert into hr.sal_emp(employee_id, last_name, salary, department_id) 
values(290, 'james', 29000, 20);

=> 1 row created.

insert into hr.sal_emp(employee_id, last_name, salary, department_id) 
values(300, 'oracle', 30000, 10);

=> 1 row created.

commit;

=> Commit complete.

#) 확인

select * from hr.sal_emp partition(pmax);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        290 james                          29000            20
        300 oracle                         30000            10

#) pmax 나누어보기

alter table hr.sal_emp split partition pmax at (30000) into (partition p5, partition pmax);

=> Table altered.

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE                     TABLESPACE   NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ------------------------------ ---------- ---------- ---------- -----------
P1              5000                           USERS
P2              15000                          USERS
P3              20000                          USERS
P4              25000                          USERS
P5              30000                          USERS
PMAX            MAXVALUE                       USERS

#) 확인

select * from hr.sal_emp partition(p5);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        290 james                          29000            20

select * from hr.sal_emp partition(pmax);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        300 oracle                         30000            10

 

---

#) p4, p5 확인

select * from hr.sal_emp partition(p4);
select * from hr.sal_emp partition(p5);

# partition merge
→ p4 데이터를 p5에 합치기

alter table hr.sal_emp merge partitions p4, p5 into partition p5;

=> Table altered.

#) p4, p5 확인

select * from hr.sal_emp partition(p4);

=> p5 로 데이터를 옮겨 확인할 수 없다.

=> ORA-02149: Specified partition does not exist

 

select * from hr.sal_emp partition(p5);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        100 King                           24000            90
        290 james                          29000            20

#) 확인

=> p4 가 없어졌는지 확인

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE                     TABLESPACE   NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ------------------------------ ---------- ---------- ---------- -----------
P1              5000                           USERS
P2              15000                          USERS
P3              20000                          USERS
P5              30000                          USERS
PMAX            MAXVALUE                       USERS

 

partition 나누기	alter table (테이블명) split partition (파티션 나눌 파티션이름) at (범위) into (partition 나눌 파티션명1, partition 파티션명2);
partition 추가	alter table (테이블명)  add partition (파티션 추가할 이름) values less than (범위);
partition 이름 수정	alter table (테이블명) rename partition (파티션 기존 이름) to (파티션 변경할 이름);
partition 삭제	alter table (테이블명) drop partition (삭제할 파티션 이름);
partition merge	alter table (테이블명) merge partition (병합할 파티션1, 병합할 파티션2) into partition (병합된 데이터를 저장할 파티션 지정)

---

# partition exchange: 데이터 이관작업

- 데이터를 물리적으로 이동시키지 않고 메타데이터(테이블, 인덱스, 파티션 등과 같은 데이터베이스 객체의 구조, 정의, 속성 등에 대한 정보) 수준에서의 교환 작업이다. oracle에서는 테이블의 파티션 간에 데이터를 물리적으로 이동시키는 대신 해당 파티션들의 메타데이터를 교화하여 효율적인 데이터 이동을 달성한다. 

- 두 개의 테이블 또는 테이블 파티션 간에 데이터 교환을 가능하게 한다. 이때, 데이터으 불리적 이동 없이 오직 메타데이터 교환만이 이루워진다. 이 작업은 큰 테이블이나 파티셔닝된 테이블에서 특정 파티션을 이동하거나 교화하는데 사용된다. 

- 이를 사용하면 빠르게 데이터 이동을 수행할 수 있으며, 특히 대용량 테이블에서 데이터 이관이 필요한 경우 효과적이다.

#) 뼈대만 생성

create table hr.exch_emp as select employee_id, last_name, salary, department_id 
from hr.employees where 1=2;

=> Table created.

#) 확인

select * from hr.exch_emp;

=> no rows selected

#) p5 확인

select * from hr.sal_emp partition(p5);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        100 King                           24000            90
        290 james                          29000            20

#) 각각 테이블의 데이터 교환하기

alter table hr.sal_emp exchange partition p5 with table hr.exch_emp;

=> Table altered.

#) 데이터 이관 되었는지 확인 

select * from hr.sal_emp partition(p5);

=> no rows selected

select * from hr.exch_emp;

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        100 King                           24000            90
        290 james                          29000            20

=> 실제 데이터의 물리적인 이동은 없이 메타데이터만 변경된 것.

#) 반대로 작업, 원상복구

alter table hr.sal_emp exchange partition p5 with table hr.exch_emp;

=> Table altered.

#) 데이터 이관이 되었는지 확인

select * from hr.sal_emp partition(p5);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        100 King                           24000            90
        290 james                          29000            20

select * from hr.exch_emp;

=> no rows selected

#) rename 하기

alter table hr.sal_emp rename partition p5 to p4;

=> Table altered.

#) 확인

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE                     TABLESPACE   NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ------------------------------ ---------- ---------- ---------- -----------
P1              5000                           USERS
P2              15000                          USERS
P3              20000                          USERS
P4              30000                          USERS
PMAX            MAXVALUE                       USERS

 

---

# partition table 통계 수집

exec dbms_stats.gather_table_stats('hr', 'sal_emp', granularity=>'auto')

=> PL/SQL procedure successfully completed.

#) 확인

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';

PARTITION_NAME  HIGH_VALUE                     TABLESPACE    NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- ------------------------------ ---------- ----------- ---------- -----------
P1              5000                           USERS               49         19          17
P2              15000                          USERS               55         19          17
P3              20000                          USERS                2         46          19 
P4              30000                          USERS                2         19          17
PMAX            MAXVALUE                       USERS                1         19          16

=>  data를 2건이나, block는 46개 사용. 나중에 리오그 대상이다.

#) 확인

select num_rows, blocks, avg_row_len from user_tables where table_name = 'SAL_EMP';

   NUM_ROWS     BLOCKS AVG_ROW_LEN
----------- ---------- -----------
        109        122          17

 

select * from hr.sal_emp partition(p3);

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        101 Kochhar                        17000            90
        102 De Haan                        17000            90