■ 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.
'Data Base > SQL 튜닝' 카테고리의 다른 글
PARTITION 옵션_나누기, 추가, 수정, 삭제, 통계 수집 (0) | 2024.02.26 |
---|---|
Range partition, Hash partition, List partition, Composite partition (0) | 2024.02.26 |
통계 수집 (0) | 2024.02.26 |
join 조건 pushdown, BLOOM FILTER, 조건절 pushdown (0) | 2024.02.26 |
SEMI JOIN, ANTI JOIN (0) | 2024.02.23 |