PARTITION, 수동 파티셔닝(manual partitioning)

■ PARTITION
1. 정의

- 테이블 또는 인덱스를 파티션 단위로 나누어서 저장하는 기술.

- 파티션 키에 따라 물리적으로 별도의 세그먼트에 데이터가 저장됨.

- 보관 및 관리, 성능 향상 등을 목적으로 사용됨.

2. 특징 및 장점

1) 보관 관리

- 파티셔닝을 통해 테이블을 파티션 단위로 나누면 보관주기가 지난 데이터를 별도로 백업하고 삭제하는 일이 용이해짐.

2) 성능 향상

- 대용량 테이블에서 인덱스를 이용한 데이터 검색이 부담스러울 때, 파티션을 사용하면 파티션 단위로 부분 스캔이 가능해져 성능이 향상될 수 있음.

- Full Table Scan이 필요할 때도 일부 파티션만 스캔하여 전체 테이블을 효과적으로 읽을 수 있음. (파티션 가지치기)

3. 파티션 가지치기 (Partition Pruning)

- 쿼리에서 조건절에 포함된 파티션 키를 이용하여 필요한 파티션만을 스캔하는 최적화 기법.

- Full Table Scan이 필요할 때도 조건에 맞는 파티션만 스캔하여 효율적으로 읽을 수 있음.

4. 성능적 측면 

- 예를 들어, 날짜를 기준으로 파티셔닝된 테이블에서 특정 기간의 데이터만 필요한 경우, 파티션 가지치기를 통해 해당 기간의 파티션만 스캔하여 Full Table Scan 성능을 개선할 수 있음.

5. 파티션 힌트 

- 쿼리에서 특정 파티션만을 사용하도록 하는 힌트를 제공하기도 함.

---

#) 뼈대만 생성
<hr sess>

create table hr.p10 as select * from hr.employees where 1 = 2;
create table hr.p12 as select * from hr.employees where 1 = 2;
create table hr.p13 as select * from hr.employees where 1 = 2;

=> Table altered.

#) 제약 조건 생성 

alter table hr.p10 add constraint c_deptno_10 check(department_id < 20);
alter table hr.p12 add constraint c_deptno_20 check(department_id >= 20 and department_id < 30);
alter table hr.p13 add constraint c_deptno_30 check(department_id >= 30 and department_id < 40);

=> Table altered.

#) 데이터 삽입 후 저장

insert into hr.p10 select * from hr.employees where department_id < 20;

=> 1 row created.

insert into hr.p12 select * from hr.employees where department_id >= 20 and department_id < 30;

=> 2 row created.

insert into hr.p13 select * from hr.employees where department_id >= 30 and department_id < 40;

=> 6 row created.

commit;

=> Commit complete.

---

■ 수동 파티셔닝(Manual Partitioning)
1. 개요

- Oracle 7 버전 이전에는 테이블 파티셔닝이 기본적으로 제공되지 않았기 때문에 수동으로 파티셔닝을 구현하는 방법으로 파티션 뷰를 사용했다.

- 파티션 뷰는 특정 조건에 부합하는 행만을 뷰에서 읽어오는데, 이를 통해 수동으로 파티션 기능을 모방하며, 이러한 기법을 "파티션 가지치기 (Partition Pruning)"이라고 한다.

2. 주요 특징

1) 파티션 뷰

- 파티션 뷰는 여러 테이블의 데이터를 하나의 뷰로 논리적으로 통합하는 개념.

- 조건절에 따라 해당 파티션에 속하는 테이블만을 읽는다.

2) Partition Pruning (파티션 가지치기)

- 파티션 가지치기는 특정 조건에 따라 불필요한 파티션을 스캔하지 않고 필요한 파티션만을 스캔하여 성능을 향상시키는 최적화 기법.

3. 제약 사항 

- 수동 파티셔닝은 직접 파티션 뷰를 생성하고 관리해야 하므로 자동 파티셔닝에 비해 유지보수 및 관리가 번거로울 수 있다.

 

---

#) 뷰 생성 
<hr sess>

create or replace view hr.emp_partition
as
select * from hr.p10
union all
select * from hr.p12
union all
select * from hr.p13;

=> View created.

#) 생성되었는지 뷰 확인

select * from hr.emp_partition;

#) 통계 수집

exec dbms_stats.gather_table_stats('hr', 'p10');
exec dbms_stats.gather_table_stats('hr', 'p12');
exec dbms_stats.gather_table_stats('hr', 'p13');

=> PL/SQL procedure successfully completed.

#) hr.p10 확인

select /*+ gather_plan_statistics */* from emp_partition where department_id = 10;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
        200 Jennifer             Whalen                    JWHALEN                   515.123.4444         17-SEP-03 AD_ASST          4400
                      101            10

#) 튜닝 확인

select * from table(dbms_xplan.display_cursor(null, null,'allstats last'));

------------------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |      1 |        |      1 |00:00:00.01 |       6 |
|   1 |  VIEW                | EMP_PARTITION |      1 |      3 |      1 |00:00:00.01 |       6 |
|   2 |   UNION-ALL          |               |      1 |        |      1 |00:00:00.01 |       6 |
|*  3 |    TABLE ACCESS FULL | P10           |      1 |      1 |      1 |00:00:00.01 |       6 |
|*  4 |    FILTER            |               |      1 |        |      0 |00:00:00.01 |       0 |
|*  5 |     TABLE ACCESS FULL| P12           |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  6 |    FILTER            |               |      1 |        |      0 |00:00:00.01 |       0 |
|*  7 |     TABLE ACCESS FULL| P13           |      0 |      1 |      0 |00:00:00.01 |       0 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("DEPARTMENT_ID"=10)
   4 - filter(NULL IS NOT NULL)
   5 - filter("DEPARTMENT_ID"=10)
   6 - filter(NULL IS NOT NULL)
   7 - filter("DEPARTMENT_ID"=10)

1) 3: 파티셔닝 돌아감. →  i/o  확인

2) 4: 파티셔닝 돌아가지 않음. →  i/o  확인되지 않음.

---

#) hr.p12 에서 department_id 확

select /*+ gather_plan_statistics */* from emp_partition where department_id = 20;

#) 파티셔닝  튜닝 확인

select * from table(dbms_xplan.display_cursor(null, null,'allstats last'));

------------------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |      1 |        |      2 |00:00:00.01 |       7 |
|   1 |  VIEW                | EMP_PARTITION |      1 |      3 |      2 |00:00:00.01 |       7 |
|   2 |   UNION-ALL          |               |      1 |        |      2 |00:00:00.01 |       7 |
|*  3 |    FILTER            |               |      1 |        |      0 |00:00:00.01 |       0 |
|*  4 |     TABLE ACCESS FULL| P10           |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  5 |    TABLE ACCESS FULL | P12           |      1 |      2 |      2 |00:00:00.01 |       7 |
|*  6 |    FILTER            |               |      1 |        |      0 |00:00:00.01 |       0 |
|*  7 |     TABLE ACCESS FULL| P13           |      0 |      1 |      0 |00:00:00.01 |       0 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(NULL IS NOT NULL)
   4 - filter("DEPARTMENT_ID"=20)
   5 - filter("DEPARTMENT_ID"=20)
   6 - filter(NULL IS NOT NULL)
   7 - filter("DEPARTMENT_ID"=20)

 

---

#) 바인드 변수 처리 

var b_dept_id number
exec :b_dept_id :=30

=> PL/SQL procedure successfully completed.

#) hr.p12 확인

select /*+ gather_plan_statistics */* from emp_partition where department_id = :b_dept_id;

#) 파티셔닝  튜닝 확인

select * from table(dbms_xplan.display_cursor(null, null,'allstats last'));

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |      1 |        |      6 |00:00:00.01 |      19 |
|   1 |  VIEW               | EMP_PARTITION |      1 |      3 |      6 |00:00:00.01 |      19 |
|   2 |   UNION-ALL         |               |      1 |        |      6 |00:00:00.01 |      19 |
|*  3 |    TABLE ACCESS FULL| P10           |      1 |      1 |      0 |00:00:00.01 |       6 |
|*  4 |    TABLE ACCESS FULL| P12           |      1 |      1 |      0 |00:00:00.01 |       6 |
|*  5 |    TABLE ACCESS FULL| P13           |      1 |      6 |      6 |00:00:00.01 |       7 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("DEPARTMENT_ID"=:B_DEPT_ID)
   4 - filter("DEPARTMENT_ID"=:B_DEPT_ID)
   5 - filter("DEPARTMENT_ID"=:B_DEPT_ID)

=> 원래는 돌아가야 하지만, 실질적으로 파티션 튜닝이 돌아가지 않음. 전체 테이블이 i/o 발생

#) 조건절 변경, 상수값 고정

select /*+ gather_plan_statistics */ * from emp_partition 
where department_id = 30 and employee_id = 115;

#) 파티셔닝  튜닝 확인

select * from table(dbms_xplan.display_cursor(null, null,'allstats last'));

 

------------------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |      1 |        |      1 |00:00:00.01 |       7 |
|   1 |  VIEW                | EMP_PARTITION |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   UNION-ALL          |               |      1 |        |      1 |00:00:00.01 |       7 |
|*  3 |    FILTER            |               |      1 |        |      0 |00:00:00.01 |       0 |
|*  4 |     TABLE ACCESS FULL| P10           |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  5 |    FILTER            |               |      1 |        |      0 |00:00:00.01 |       0 |
|*  6 |     TABLE ACCESS FULL| P12           |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  7 |    TABLE ACCESS FULL | P13           |      1 |      1 |      1 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(NULL IS NOT NULL)
   4 - filter(("EMPLOYEE_ID"=115 AND "DEPARTMENT_ID"=30))
   5 - filter(NULL IS NOT NULL)
   6 - filter(("EMPLOYEE_ID"=115 AND "DEPARTMENT_ID"=30))
   7 - filter(("EMPLOYEE_ID"=115 AND "DEPARTMENT_ID"=30))

=> 실질적으로 7번만 돌아간 것을 확인.

---

#) unique index 생성 / drop index는 안돼! => 오류 발생

create unique index hr.p13_id_ix on hr.p13(employee_id);

=> Index created.

#) primary key 생성

alter table hr.p13 add constraint p13_id_pk primary key(employee_id) using index hr.p13_id_ix;

=> Table altered.

#) 다시 확인

select /*+ gather_plan_statistics */ * from emp_partition 
where department_id = 30 and employee_id = 115;

#) 파티셔닝  튜닝 확인

select * from table(dbms_xplan.display_cursor(null, null,'allstats last'));

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  VIEW                         | EMP_PARTITION |      1 |      3 |      1 |00:00:00.01 |       2 |
|   2 |   UNION-ALL                   |               |      1 |        |      1 |00:00:00.01 |       2 |
|*  3 |    FILTER                     |               |      1 |        |      0 |00:00:00.01 |       0 |
|*  4 |     TABLE ACCESS FULL         | P10           |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  5 |    FILTER                     |               |      1 |        |      0 |00:00:00.01 |       0 |
|*  6 |     TABLE ACCESS FULL         | P12           |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  7 |    TABLE ACCESS BY INDEX ROWID| P13           |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  8 |     INDEX UNIQUE SCAN         | P13_ID_IX     |      1 |      1 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(NULL IS NOT NULL)
   4 - filter(("DEPARTMENT_ID"=30 AND "EMPLOYEE_ID"=115))
   5 - filter(NULL IS NOT NULL)
   6 - filter(("EMPLOYEE_ID"=115 AND "DEPARTMENT_ID"=30))
   7 - filter("DEPARTMENT_ID"=30)
   8 - access("EMPLOYEE_ID"=115)

=>i/o 줄어듦. 파티셔닝 발생

#) if, index 삭제해야 한다면?

drop index hr.p13_id_ix;

=>  ORA-02429: cannot drop index used for enforcement of unique/primary key

alter table hr.p13 drop constraint p13_id_pk;

=> Table altered.

drop index hr.p13_id_ix;

=> Table altered.