| # 요약 | |
| ■ Partition Partitioning Pruning ■ 수동 파티셔닝(Manual Partitioning) Partitioning Pruning ■ Range Partition less than maxvalue Interval ■ Hash Partition hash() partitions 4 ■ List Partition ■ Composite Partition # Range - Hash Partition # Range - List partition # Logging Mode 상태 확인 # Partition 나누기 : Split # Partition 추가 : Add # Partition 이름 변경 : Rename # Partition 삭제 : Drop # Partition 내 Data 삭제: Truncate # 파티션 키컬럼 값 수정 : Flashback # Maxvalue Partition 추가 # Partition Merge # Partition Exchange (데이터 교환) # Partition Table 통계 수집 : granularity => 'auto' # Data Pump ------------------------------ range 날짜 이력성 hash 동시 insert list 값 목록 |
|
■ Partition
- 테이블 또는 인덱스를 파티션 단위로 나눠서 저장하는 것
- 테이블 파티션하면 하나의 테이블일지라도 파티션 키에 따라 물리적으로 별도의 세그먼트에 데이터가 저장된다.
- 관리적 측면으로 보면 보관주기가 지난 데이터들을 별도로 백업하고 지우는 일이 아주 쉬워진다.
- 성능적 측면으로 파티셔닝하지 않은 테이블이 대용량일 때 인덱스를 이용해서 데이터를 검색하더라도 데이터의 양이
많아서 인덱스를 이용해 건건히 테이블 액세스하는 방식(Random I/O)은 일정량이 넘는 순간 Full Table Scan 보다 더 못한
결과가 나온다. 그렇다고 Full Table Scan도 부담스럽다면 바로 그때 파티션을 나누면 Full Table Scan 을 하더라도 일부
파티션 세그먼트만 읽고 멈출 수 있다.(Partition Pruning)
> Partition 기능은 엔터프라이즈 급 + 별도 라이센스 필요하다
--- Create Table
HR@ora11g> create table hr.p10 as select * from hr.employees where 1 = 2;
Table created.
HR@ora11g> create table hr.p12 as select * from hr.employees where 1 = 2;
Table created.
HR@ora11g> create table hr.p13 as select * from hr.employees where 1 = 2;
Table created.
--- Add Constraint : Check
HR@ora11g> alter table hr.p10 add constraint c_deptno_10 check(department_id < 20);
Table altered.
HR@ora11g> alter table hr.p12 add constraint c_deptno_20 check(department_id >= 20 and department_id < 30);
Table altered.
HR@ora11g> alter table hr.p13 add constraint c_deptno_30 check(department_id >= 30 and department_id < 40);
Table altered.
--- Insert
HR@ora11g> insert into hr.p10 select * from hr.employees where department_id < 20;
1 row created.
HR@ora11g> insert into hr.p12 select * from hr.employees where department_id >= 20 and department_id < 30;
2 rows created.
HR@ora11g> insert into hr.p13 select * from hr.employees where department_id >= 30 and department_id < 40;
6 rows created.
HR@ora11g> commit;
Commit complete.
■ 수동 파티셔닝(Manual Partitioning)
- 7버전까지는 파티션 테이블이 제공되지 않았기 때문에 파티션 뷰를 통해 파티션 기능을 구현했다.
- 파티션 뷰의 핵심기능은 뷰 쿼리에 사용된 조건절에 부합하는 테이블만 읽는다. 이를 Partitioning Pruning이라고 한다.
--- Create View
HR@ora11g> 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.
HR@ora11g> HR@ora11g> select * from hr.emp_partition;
9 rows selected.
--- 통계 수집
HR@ora11g> exec dbms_stats.gather_table_stats('hr','p10')
PL/SQL procedure successfully completed.
HR@ora11g> exec dbms_stats.gather_table_stats('hr','p12')
PL/SQL procedure successfully completed.
HR@ora11g> exec dbms_stats.gather_table_stats('hr','p13')
PL/SQL procedure successfully completed.
# WHERE department_id = 10
HR@ora11g> select /*+ gather_plan_statistics */ * from emp_partition where department_id = 10;
HR@ora11g> 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)
> 10번 부서 : Only P10 Table + Check 제약조건# WHERE department_id = 20
HR@ora11g> select /*+ gather_plan_statistics */ * from emp_partition where department_id = 20;
HR@ora11g> 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)
# 바인더 변수 사용 시
HR@ora11g> var b_dept_id number
HR@ora11g> exec :b_dept_id := 30
PL/SQL procedure successfully completed.
HR@ora11g> select /*+ gather_plan_statistics */ * from emp_partition
where department_id = :b_dept_id;
HR@ora11g> 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)
> 바인더변수는 VIEW를 이용하는 Partitioning Pruning 작동하지 않아 I/O 과다 발생
> 조건제어문을 사용하는게 좋다.# WHERE department_id = 30 AND employee_id = 115
HR@ora11g> select /*+ gather_plan_statistics */ * from emp_partition
where department_id = 30 and employee_id = 115;
HR@ora11g> 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))
> department_id : Check 제약조건
# WHERE department_id = 30 AND demployee_id = 115
--- Create Unique Index
HR@ora11g> create unique index hr.p13_id_ix on hr.p13(employee_id);
Index created.
--- Add Primary Key
HR@ora11g> alter table hr.p13 add constraint p13_id_pk primary key(employee_id) using index hr.p13_id_ix;
Table altered.
HR@ora11g> select /*+ gather_plan_statistics */ * from emp_partition
where department_id = 30 and employee_id = 115;
HR@ora11g> 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)
> department_id : Check 제약조건
> employee_id : unique Index + primary key
# 제약조건 삭제 후 인덱스 삭제 하자.
HR@ora11g> drop index hr.p13_id_ix;
ORA-02429: cannot drop index used for enforcement of unique/primary key
↓
HR@ora11g> alter table hr.p13 drop constraint p13_id_pk;
Table altered.
HR@ora11g> drop index hr.p13_id_ix;
Index dropped.
■ Range Partition
- 8버전부터 제공
- 이력성 데이터 조회에 유리
- 여러 컬럼을 파티션키로 지정 가능(최대 16)
- maxvalue 파티션을 생성
- less than 사용
# 날짜로 파티션 지정
HR@ora11g> create table hr.emp_year
partition by range(hire_date)
(partition p2004 values less than (to_date('2005-01-01','yyyy-mm-dd')),
partition p2005 values less than (to_date('2006-01-01','yyyy-mm-dd')),
partition p2006 values less than (to_date('2007-01-01','yyyy-mm-dd')),
partition pmax values less than (maxvalue)
)
as select employee_id, last_name, salary, hire_date, department_id
from hr.employees;
Table created.
> range + less than 세트
> range(hire_date) : 기준 컬럼 키
> less than(to_date()) : ~ 이전
> less than(maxvalue) : 이후 자료, 안만들면 데이터 누락 및 에러
HR@ora11g> select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_YEAR';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------- -------------------------------------------------------------------------------- ---------------
P2004 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
P2005 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
P2006 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
PMAX MAXVALUE USERS
> less than을 사용하니 해당 날짜 이전까지라고 생각해야한다.
HR@ora11g> select partitioning_type, partition_count
from user_part_tables where table_name = 'EMP_YEAR';
PARTITION PARTITION_COUNT
--------- ---------------
RANGE 4
HR@ora11g> select * from user_part_key_columns where name = 'EMP_YEAR';
NAME OBJEC COLUMN_NAME COLUMN_POSITION
--------------------------------------------- ----- -------------------- ---------------
EMP_YEAR TABLE HIRE_DATE 1
HR@ora11g> select * from hr.emp_year;
107 rows selected.
HR@ora11g> select * from hr.emp_year partition(p2004);
24 rows selected.
HR@ora11g> select * from hr.emp_year partition(p2005);
29 rows selected.
HR@ora11g> select * from hr.emp_year partition(p2006);
24 rows selected.
HR@ora11g> select * from hr.emp_year partition(pmax);
30 rows selected.
# MaxValue 생략 시 나머지 데이터를 넣을 파티션이 없으면 생성불가
HR@ora11g> drop table hr.emp_year purge;
Table dropped.
HR@ora11g> create table hr.emp_year
partition by range(hire_date)
(partition p2004 values less than (to_date('2005-01-01','yyyy-mm-dd')),
partition p2005 values less than (to_date('2006-01-01','yyyy-mm-dd')),
partition p2006 values less than (to_date('2007-01-01','yyyy-mm-dd')),
partition pmax values less than (maxvalue)
)
as
select employee_id, last_name, salary, hire_date, department_id from hr.employees;
ORA-14400: inserted partition key does not map to any partition
# Range 옵션 : Interval Partitioning (11g)
- 기준을 정의함으로써 정해진 간격으로 파티션이 자동 추가되도록 할 수 있다.
HR@ora11g> create table hr.emp_year
partition by range(hire_date) interval(numtoyminterval(1,'year'))
(partition p2004 values less than (to_date('2005-01-01','yyyy-mm-dd')),
partition p2005 values less than (to_date('2006-01-01','yyyy-mm-dd')),
partition p2006 values less than (to_date('2007-01-01','yyyy-mm-dd'))
)
as select employee_id, last_name, salary, hire_date, department_id from hr.employees;
Table created.
> interval(numtoyminterval(1,'year')) : 1년마다
HR@ora11g> select partition_name, high_value, tablespace_name, interval
from user_tab_partitions
where table_name = 'EMP_YEAR';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME INTERVAL
--------------- -------------------------------------------------------------------------------- --------------- --------
P2004 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS NO
P2005 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS NO
P2006 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS NO
SYS_P41 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS YES
SYS_P42 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS YES
> SYS_P41, SYS_P42, ... 1년단위로 계속 생성된다.
HR@ora11g> select * from hr.emp_year;
107 rows selected.
HR@ora11g> select * from hr.emp_year partition(p2004);
24 rows selected.
HR@ora11g> select * from hr.emp_year partition(p2005);
29 rows selected.
HR@ora11g> select * from hr.emp_year partition(p2006);
24 rows selected.
HR@ora11g> select * from hr.emp_year partition(SYS_P41);
19 rows selected.
HR@ora11g> select * from hr.emp_year partition(SYS_P42);
11 rows selected.
HR@ora11g> select partitioning_type, partition_count from user_part_tables where table_name = 'EMP_YEAR';
PARTITION PARTITION_COUNT
--------- ---------------
RANGE 1048575
■ Hash Partition
- 8i 버전 제공
- Partition Key 값에 해시 함수를 적용하여 데이터를 분할하는 방식
- 중복성보다는 유일키 값으로 키값을 정하자.
- 데이터 분포가 고른 컬럼을 파티션 컬럼으로 선정하는게 좋다.
- 파티션 개수는 2의 제곱(2,4,8,16,32,...)으로 하는 이유는 특정 파티션에 몰리지않도록 하기위해서다.
- 조건절 = , in 조건을 검색할 때만 Partition Pruning 기능이 수행된다.
- 병렬쿼리 성능 향상뿐 아니라 동시에 입력이 많은 대용량 테이블이나 인덱스에 발생하는 경합을 줄일 목적으로
해시 파티셔닝을 사용한다.
> 동시 insert에 대한 성능 향상, 분산 목적
예) 주문테이블에 주문번호에 인덱스 걸려있다?
-> 인덱스는 squence -> leaf block에 경합 발생 -> EVENT : Buffer Busy Waits
-> 데이터 흩어지게 해야한다 -> hash partition 활용
: select문 처리단계 흐름 참고
# 파티션을 4개로 생성
HR@ora11g> create table hr.emp_hash
partition by hash(employee_id) partitions 4
as select employee_id, last_name, salary, department_id from hr.employees;
Table created.
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'EMP_HASH';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- ----------- ---------------
SYS_P43 USERS
SYS_P44 USERS
SYS_P45 USERS
SYS_P46 USERS
HR@ora11g> select partitioning_type, partition_count from user_part_tables where table_name = 'EMP_HASH';
PARTITION PARTITION_COUNT
--------- ---------------
HASH 4
HR@ora11g> select * from user_part_key_columns where name = 'EMP_HASH';
NAME OBJEC COLUMN_NAME COLUMN_POSITION
--------------------------------------------- ----- -------------------- ---------------
EMP_HASH TABLE EMPLOYEE_ID 1
HR@ora11g> select * from hr.emp_hash partition(SYS_P43);
25 rows selected.
HR@ora11g> select * from hr.emp_hash partition(SYS_P44);
32 rows selected.
HR@ora11g> select * from hr.emp_hash partition(SYS_P45);
31 rows selected.
HR@ora11g> select * from hr.emp_hash partition(SYS_P46);
19 rows selected.
■ List Partition
- 9i 버전부터 제공
- 미리정해진 그룹을 기준으로 데이터를 분할, 값 목록
- 단일 컬럼만 가능
HR@ora11g> create table hr.emp_list
partition by list(department_id)
(partition p_dept_1 values(10,20,30,40),
partition p_dept_2 values(50),
partition p_dept_3 values(60,70,80,90,100,110),
partition p_dept_4 values(default)
)
as select employee_id, last_name, salary, department_id from hr.employees;
Table created.
> default : 그 외 데이터
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'EMP_LIST';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- ---------------------------- ---------------
P_DEPT_1 10, 20, 30, 40 USERS
P_DEPT_2 50 USERS
P_DEPT_3 60, 70, 80, 90, 100, 110 USERS
P_DEPT_4 default USERS
HR@ora11g> select partitioning_type, partition_count
from user_part_tables where table_name = 'EMP_LIST';
PARTITION PARTITION_COUNT
--------- ---------------
LIST 4
HR@ora11g> select * from user_part_key_columns where name = 'EMP_LIST';
NAME OBJECT_TYPE COLUMN_NAME COLUMN_POSITION
--------------------------------------------- ------------- -------------------- ---------------
EMP_LIST TABLE DEPARTMENT_ID 1
■ Composite Partition (조합 파티션)
- 서브 파티션마다 세그먼트를 하나씩 할당하고 서브 파티션 단위로 데이터를 저장
- 주 파티션 키에 따라 1차적으로 데이터를 분배하고 서브 파티션 키에 따라 최종적으로 저장할 위치(세그먼트)를 결정하다.
Range - Hash Partition (8i)
Range - List Partition (9i)
Range - Range Partition (11g)
List - Hash Partition (11g)
List - List Partition (11g)
List - Range Partiton (11g)
# Range - Hash Partition
HR@ora11g> create table hr.emp_comp
partition by range(salary)
subpartition by hash(employee_id) subpartitions 4
(partition p4999 values less than (5000),
partition p9999 values less than (10000),
partition p19999 values less than (20000),
partition pmax values less than (maxvalue)
)
as select employee_id, last_name, salary, department_id from hr.employees;
Table created.
> range(salary) : 급여를 금액 구간으로 분할 후 각 파티션 내
> hash(employee_id) : 사번번호를 4분할 분산
--- 테이블 내 파티션 개수
HR@ora11g> select partitioning_type, partition_count, subpartitioning_type, def_subpartition_count
from user_part_tables where table_name = 'EMP_COMP';
PARTITION PARTITION_COUNT SUBPARTIT DEF_SUBPARTITION_COUNT
--------- --------------- --------- ----------------------
RANGE 4 HASH 4
--- 주 파티션 컬럼 정보
HR@ora11g> select * from user_part_key_columns where name = 'EMP_COMP';
NAME OBJECT_TYPE COLUMN_NAME COLUMN_POSITION
--------------------------------------------- ------------- -------------------- ---------------
EMP_COMP TABLE SALARY 1
--- 주 파티션 정보
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'EMP_COMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- ------------- ---------------
P4999 5000 USERS
P9999 10000 USERS
P19999 20000 USERS
PMAX MAXVALUE USERS
--- 서브 파티션 컬럼 정보
HR@ora11g> select * from user_subpart_key_columns where name = 'EMP_COMP';
NAME OBJECT_TYPE COLUMN_NAME COLUMN_POSITION
--------------------------------------------- ------------- -------------------- ---------------
EMP_COMP TABLE EMPLOYEE_ID 1
--- 서브 파티션 정보
HR@ora11g> select partition_name, subpartition_name, high_value, subpartition_position
from user_tab_subpartitions
where table_name = 'EMP_COMP';
PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE SUBPARTITION_POSITION
--------------- ------------------------------ ------------- ---------------------
P19999 SYS_SUBP69 1
P19999 SYS_SUBP70 2
P19999 SYS_SUBP71 3
P19999 SYS_SUBP72 4
P4999 SYS_SUBP61 1
P4999 SYS_SUBP62 2
P4999 SYS_SUBP63 3
P4999 SYS_SUBP64 4
P9999 SYS_SUBP65 1
P9999 SYS_SUBP66 2
P9999 SYS_SUBP67 3
P9999 SYS_SUBP68 4
PMAX SYS_SUBP73 1
PMAX SYS_SUBP74 2
PMAX SYS_SUBP75 3
PMAX SYS_SUBP76 4
# Range - Hash + WHERE salary < 5000
HR@ora11g> select /*+ gather_plan_statistics */ * from hr.emp_comp where salary < 5000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 16 | 1 |
| 1 | PARTITION RANGE SINGLE| | 1 | 49 | 50 |00:00:00.01 | 16 | 1 |
| 2 | PARTITION HASH ALL | | 1 | 49 | 50 |00:00:00.01 | 16 | 1 |
| 3 | TABLE ACCESS FULL | EMP_COMP | 4 | 49 | 50 |00:00:00.01 | 16 | 1 |
------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
> 메인 파티션 1번 검색# Range - Hash + WHERE salary < 5000 AND employee_id = 200
HR@ora11g> select /*+ gather_plan_statistics */ * from hr.emp_comp
where salary < 5000 and employee_id = 200;
HR@ora11g> 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 | 4 |
| 1 | PARTITION RANGE SINGLE| | 1 | 1 | 1 |00:00:00.01 | 4 |
| 2 | PARTITION HASH SINGLE| | 1 | 1 | 1 |00:00:00.01 | 4 |
|* 3 | TABLE ACCESS FULL | EMP_COMP | 1 | 1 | 1 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMPLOYEE_ID"=200)
Note
-----
- dynamic sampling used for this statement (level=2)
> 메인 파티션 1개 안에 4분할 서브 파티션 다 검색
# Range - Hash + WHERE employee_id = 200
HR@ora11g> select /*+ gather_plan_statistics */ * from hr.emp_comp where employee_id = 200;
HR@ora11g> 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 | 10 |
| 1 | PARTITION RANGE ALL | | 1 | 1 | 1 |00:00:00.01 | 10 |
| 2 | PARTITION HASH SINGLE| | 4 | 1 | 1 |00:00:00.01 | 10 |
|* 3 | TABLE ACCESS FULL | EMP_COMP | 4 | 1 | 1 |00:00:00.01 | 10 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMPLOYEE_ID"=200)
Note
-----
- dynamic sampling used for this statement (level=2)
> 메인 파티션의 기준이 되는 salary 조건문이 없어 파티션 전체 I/O 발생
# Range - List partition
HR@ora11g> drop table hr.emp_year purge;
--- 테이블 생성
HR@ora11g> create table hr.emp_year
partition by range(hire_date)
subpartition by list(department_id)
subpartition template
(subpartition s_dept_1 values(10,20,30,40),
subpartition s_dept_2 values(50),
subpartition s_dept_3 values(60,70,80,90,100,110),
subpartition s_dept_4 values(default))
(partition p2004 values less than (to_date('2005-01-01','yyyy-mm-dd')),
partition p2005 values less than (to_date('2006-01-01','yyyy-mm-dd')),
partition p2006 values less than (to_date('2007-01-01','yyyy-mm-dd')),
partition pmax values less than (maxvalue))
as
select employee_id, last_name, salary, hire_date, department_id from hr.employees;
Table created.
--- 테이블 내 파티션 개수
HR@ora11g> select partitioning_type, partition_count, subpartitioning_type, def_subpartition_count
from user_part_tables where table_name = 'EMP_YEAR';
PARTITION PARTITION_COUNT SUBPARTIT DEF_SUBPARTITION_COUNT
--------- --------------- --------- ----------------------
RANGE 4 LIST 4
--- 주 파티션 컬럼 정보
HR@ora11g> select * from user_part_key_columns where name = 'EMP_YEAR';
NAME OBJECT_TYPE COLUMN_NAME COLUMN_POSITION
--------------------------------------------- ------------- -------------------- ---------------
EMP_YEAR TABLE HIRE_DATE 1
--- 주 파티션 정보
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'EMP_YEAR';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- -------------------------------------------------------------------------------- ---------------
P2004 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
P2005 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
P2006 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
PMAX MAXVALUE USERS
--- 서브 파티션 컬럼 정보
HR@ora11g> select * from user_subpart_key_columns where name = 'EMP_YEAR';
NAME OBJECT_TYPE COLUMN_NAME COLUMN_POSITION
--------------------------------------------- ------------- -------------------- ---------------
EMP_YEAR TABLE DEPARTMENT_ID 1
--- 서브 파티션 정보
HR@ora11g> select partition_name, subpartition_name, high_value, subpartition_position
from user_tab_subpartitions
where table_name = 'EMP_YEAR';
PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE SUBPARTITION_POSITION
--------------- ------------------------------ ----------------------------- ---------------------
P2004 P2004_S_DEPT_1 10, 20, 30, 40 1
P2004 P2004_S_DEPT_2 50 2
P2004 P2004_S_DEPT_3 60, 70, 80, 90, 100, 110 3
P2004 P2004_S_DEPT_4 default 4
P2005 P2005_S_DEPT_1 10, 20, 30, 40 1
P2005 P2005_S_DEPT_2 50 2
P2005 P2005_S_DEPT_3 60, 70, 80, 90, 100, 110 3
P2005 P2005_S_DEPT_4 default 4
P2006 P2006_S_DEPT_1 10, 20, 30, 40 1
P2006 P2006_S_DEPT_2 50 2
P2006 P2006_S_DEPT_3 60, 70, 80, 90, 100, 110 3
P2006 P2006_S_DEPT_4 default 4
PMAX PMAX_S_DEPT_1 10, 20, 30, 40 1
PMAX PMAX_S_DEPT_2 50 2
PMAX PMAX_S_DEPT_3 60, 70, 80, 90, 100, 110 3
PMAX PMAX_S_DEPT_4 default 4
# Range - List + WHERE hire_date BETWEEN ~ AND
HR@ora11g> select /*+ gather_plan_statistics */ * from hr.emp_year
where hire_date between to_date('2002-01-01','yyyy-mm-dd')
and to_date('2002-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss');
HR@ora11g> 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 | | 7 |00:00:00.01 | 10 |
| 1 | PARTITION RANGE SINGLE| | 1 | 7 | 7 |00:00:00.01 | 10 |
| 2 | PARTITION LIST ALL | | 1 | 7 | 7 |00:00:00.01 | 10 |
|* 3 | TABLE ACCESS FULL | EMP_YEAR | 4 | 7 | 7 |00:00:00.01 | 10 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("HIRE_DATE">=TO_DATE(' 2002-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"HIRE_DATE"<=TO_DATE(' 2002-12-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss')))
Note
-----
- dynamic sampling used for this statement (level=2)
> 메인 파티션의 hire_date 기준으로 검색# Range - List + WHERE hire_date BETWEEN ~ AND + department_id
HR@ora11g> select /*+ gather_plan_statistics */ * from hr.emp_year
where hire_date between to_date('2002-01-01','yyyy-mm-dd')
and to_date('2002-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
and department_id = 30;
HR@ora11g> 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 | 4 |
| 1 | PARTITION RANGE SINGLE| | 1 | 1 | 1 |00:00:00.01 | 4 |
| 2 | PARTITION LIST SINGLE| | 1 | 1 | 1 |00:00:00.01 | 4 |
|* 3 | TABLE ACCESS FULL | EMP_YEAR | 1 | 1 | 1 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("DEPARTMENT_ID"=30 AND "HIRE_DATE">=TO_DATE(' 2002-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "HIRE_DATE"<=TO_DATE(' 2002-12-31 23:59:59',
'syyyy-mm-dd hh24:mi:ss')))
Note
-----
- dynamic sampling used for this statement (level=2)
> 메인 파티션 기준 hire_date + 서브 파티션 기준 department_id 같이 검색
# Logging Mode 상태 확인
HR@ora11g> drop table hr.emp_year purge;
HR@ora11g> create table hr.emp_year
partition by range(hire_date)
subpartition by list(department_id)
subpartition template
(subpartition s_dept_1 values(10,20,30,40),
subpartition s_dept_2 values(50),
subpartition s_dept_3 values(60,70,80,90,100,110),
subpartition s_dept_4 values(default))
(partition p2004 values less than (to_date('2005-01-01','yyyy-mm-dd')),
partition p2005 values less than (to_date('2006-01-01','yyyy-mm-dd')),
partition p2006 values less than (to_date('2007-01-01','yyyy-mm-dd')),
partition pmax values less than (maxvalue))
nologging
as
select employee_id, last_name, salary, hire_date, department_id from hr.employees;
Table created.
HR@ora11g> select partition_name, high_value, tablespace_name, logging
from user_tab_partitions
where table_name = 'EMP_YEAR';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME LOGGING
--------------- -------------------------------------------------------------------------------- --------------- -------
P2004 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS NONE
P2005 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS NONE
P2006 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS NONE
PMAX MAXVALUE USERS NONE
--- Logging Mode로 전환
HR@ora11g> alter table hr.emp_year logging;
Table altered.
HR@ora11g> select partition_name, high_value, tablespace_name, logging
from user_tab_partitions
where table_name = 'EMP_YEAR';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME LOGGING
--------------- -------------------------------------------------------------------------------- --------------- -------
P2004 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS YES
P2005 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS YES
P2006 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS YES
PMAX MAXVALUE USERS YES
> Logging Mode 상태를 꼭 확인하자.# Logging Mode 상태 확인 2
HR@ora11g> create table hr.no_emp
nologging
as select * from hr.employees;
Table created.
HR@ora11g> select logging from user_tables where table_name = 'NO_EMP';
LOGGING
-------
NO
HR@ora11g> alter table hr.no_emp logging;
Table altered.
HR@ora11g> select logging from user_tables where table_name = 'NO_EMP';
LOGGING
-------
YES
HR@ora11g> drop table hr.no_emp purge;
Table dropped.
# 예시 : Partition by Range(salary)
HR@ora11g> create table hr.sal_emp
partition by range(salary)
(partition p1 values less than (5000),
partition p2 values less than (15000),
partition p3 values less than (25000))
as select employee_id, last_name, salary, department_id from hr.employees;
Table created.
HR@ora11g> select partition_name, high_value, tablespace_name, logging
from user_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME LOGGING
--------------- -------------- --------------- -------
P1 5000 USERS YES
P2 15000 USERS YES
P3 25000 USERS YES
HR@ora11g> select partitioning_type, partition_count
from user_part_tables where table_name = 'SAL_EMP';
PARTITION PARTITION_COUNT
--------- ---------------
RANGE 3
# Partition 나누기 : Split
HR@ora11g> alter table sal_emp split partition p3 at(20000) into (partition p3, partition p4);
Table altered.
> p3 25000 -> p3 20000, p4 25000
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- -------------- ---------------
P1 5000 USERS
P2 15000 USERS
P3 20000 USERS
P4 25000 USERS
# Partition 추가 : Add
HR@ora11g> alter table sal_emp add partition p5_1 values less than (30000);
Table altered.
> p5_1 (~30000) 파티션 추가
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- -------------- ---------------
P1 5000 USERS
P2 15000 USERS
P3 20000 USERS
P4 25000 USERS
P5_1 30000 USERS
# Partition 이름 변경 : Rename
HR@ora11g> alter table sal_emp rename partition p5_1 to p5;
Table altered.
> p5_1 -> P5
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- -------------- ---------------
P1 5000 USERS
P2 15000 USERS
P3 20000 USERS
P4 25000 USERS
P5 30000 USERS
# Partition 삭제 : Drop
HR@ora11g> alter table sal_emp drop partition p5;
Table altered.
> ** drop table sal_emp은 테이블 자체를 삭제하는 것이니 사용 주의
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- -------------- ---------------
P1 5000 USERS
P2 15000 USERS
P3 20000 USERS
P4 25000 USERS# Partition 내 Data 삭제: Truncate
HR@ora11g> select * from hr.sal_emp partition (p3);
2 rows selected
--- Partition Data Truncate
HR@ora11g> alter table hr.sal_emp truncate partition p3;
Table truncated.
HR@ora11g> select * from hr.sal_emp partition (p3);
no rows selected
> delete 하면 redo, undo 발생
--- 복구
HR@ora11g> insert into sal_emp select employee_id, last_name, salary, department_id
from employees where employee_id in (101,102);
2 rows created.
HR@ora11g> commit;
Commit complete.
HR@ora11g> select * from hr.sal_emp partition (p3);
2 rows selected
# 파티션 키컬럼 값 수정 : Flashback Table
HR@ora11g> update hr.sal_emp set salary = 24000 where employee_id = 100;
ORA-14402: updating partition key column would cause a partition change
> hr.sal_emp : partition by range(salary)
> p1에 있는 값을 p4에 이동이 필요한 상황 -> 키 컬럼 업데이트는 맘대로 할 수 없다.
↓ p.76 참고
--- 행 이동이 활성화
HR@ora11g> alter table hr.sal_emp enable row movement;
Table altered.
HR@ora11g> update hr.sal_emp set salary = 24000 where employee_id = 100;
1 row updated.
HR@ora11g> commit;
Commit complete.
--- 행 이동이 비활성화
HR@ora11g> alter table hr.sal_emp disable row movement;
Table altered.
HR@ora11g> select * from hr.sal_emp partition (p4);
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King 24000 90
# Maxvalue Partition 추가
--- Add Partition : pmax (~maxvalue)
HR@ora11g> alter table hr.sal_emp add partition pmax values less than (maxvalue);
Table altered.
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- -------------- ---------------
P1 5000 USERS
P2 15000 USERS
P3 20000 USERS
P4 25000 USERS
PMAX MAXVALUE USERS
--- Insert Data
HR@ora11g> insert into sal_emp(employee_id,last_name,salary,department_id)
values(300,'oracle',30000,10);
1 row created.
HR@ora11g> commit;
Commit complete.
HR@ora11g> select * from hr.sal_emp partition (pmax);
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
300 oracle 30000 10
--- Split Partition
HR@ora11g> alter table hr.sal_emp
split partition pmax at(30000) into (partition p5, partition pmax);
Table altered.
> pmax -> p5 ~30000, pmax ~maxvalue
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- -------------- ---------------
P1 5000 USERS
P2 15000 USERS
P3 20000 USERS
P4 25000 USERS
P5 30000 USERS
PMAX MAXVALUE USERS
# Partition Merge
HR@ora11g> select * from hr.sal_emp partition (p4);
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King 24000 90
HR@ora11g> select * from hr.sal_emp partition (p5);
no rows selected
--- Merge
HR@ora11g> alter table hr.sal_emp merge partitions p4, p5 into partition p5;
Table altered.
> p4 Data -> p5 병합
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- -------------- ---------------
P1 5000 USERS
P2 15000 USERS
P3 20000 USERS
P5 30000 USERS
PMAX MAXVALUE USERS
HR@ora11g> select * from hr.sal_emp partition (p5);
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King 24000 90
# Partition Exchange (데이터 교환)
HR@ora11g> create table hr.exch_emp as select employee_id, last_name, salary, department_id
from hr.employees where 1 = 2;
Table created.
HR@ora11g> select * from hr.exch_emp;
no rows selected
HR@ora11g> select * from hr.sal_emp partition (p5);
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King 24000 90
--- Exchange
HR@ora11g> alter table hr.sal_emp exchange partition p5 with table hr.exch_emp;
Table altered.
> 파티션 p5와 테이블 exch_emp의 Data를 교환
HR@ora11g> select * from hr.exch_emp;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King 24000 90
HR@ora11g> select * from hr.sal_emp partition (p5);
no rows selected
--- Re Exchange
HR@ora11g> alter table hr.sal_emp exchange partition p5 with table hr.exch_emp;
Table altered.
HR@ora11g> select * from hr.exch_emp;
no rows selected
HR@ora11g> select * from hr.sal_emp partition (p5);
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King 24000 90
--- rename
HR@ora11g> alter table hr.sal_emp rename partition p5 to p4;
Table altered.
HR@ora11g> select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- -------------- ---------------
P1 5000 USERS
P2 15000 USERS
P3 20000 USERS
P4 30000 USERS
PMAX MAXVALUE USERS
# Partition Table 통계 수집 : granularity => 'auto'
--- 파티션 통계 수집
HR@ora11g> exec dbms_stats.gather_table_stats('hr','sal_emp',granularity=>'auto')
PL/SQL procedure successfully completed.
HR@ora11g> 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_NAME 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 1 19 15
PMAX MAXVALUE USERS 1 46 16
HR@ora11g> select num_rows, blocks, avg_row_len
from user_tables where table_name = 'SAL_EMP';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
108 149 17
HR@ora11g> select * from hr.sal_emp partition (p3);
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
101 Kochhar 17000 90
102 De Haan 17000 90
> 데이터 대비 사용 block이 많다 > 리오브 대상
# Data Pump
# 논리적 디렉터리 위치 확인
SYS@ora11g> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ ---------------------------------------------------------------------------
SYS SUBDIR /u01/app/oracle/product/11.2.0.4/db_1/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0.4/db_1/demo/schema/order_entry/
SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0.4/db_1/demo/schema/log/
SYS MEDIA_DIR /u01/app/oracle/product/11.2.0.4/db_1/demo/schema/product_media/
SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0.4/db_1/demo/schema/sales_history/
SYS XMLDIR /u01/app/oracle/product/11.2.0.4/db_1/rdbms/xml
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0.4/db_1/ccr/hosts/oracle/state
SYS DATA_PUMP_DIR /u01/app/oracle/admin/ora11g/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0.4/db_1/ccr/state
> DATA_PUMP_DIR 사용
# Expdp : 특정 Table 내보내기
[oracle@oracle ~]$ expdp system/oracle directory=data_pump_dir tables=hr.sal_emp dumpfile=hr_sal_emp.pump
Export: Release 11.2.0.4.0 - Production on Mon Feb 26 15:55:51 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=data_pump_dir tables=hr.sal_emp dumpfile=hr_sal_emp.pump
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 40 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."SAL_EMP":"P1" 7.351 KB 49 rows
. . exported "HR"."SAL_EMP":"P2" 7.484 KB 55 rows
. . exported "HR"."SAL_EMP":"P3" 6.390 KB 2 rows
. . exported "HR"."SAL_EMP":"P4" 6.367 KB 1 rows
. . exported "HR"."SAL_EMP":"PMAX" 6.367 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/ora11g/dpdump/hr_sal_emp.pump
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Feb 26 15:56:04 2024 elapsed 0 00:00:07
*/
# Drop Table
SYS@ora11g> drop table hr.sal_emp purge;
Table dropped.
# Impdp : 특정 테이블 가져오기
[oracle@oracle ~]$ impdp system/oracle directory=data_pump_dir tables=hr.sal_emp dumpfile=hr_sal_emp.pump
Import: Release 11.2.0.4.0 - Production on Mon Feb 26 16:00:36 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=data_pump_dir tables=hr.sal_emp dumpfile=hr_sal_emp.pump
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."SAL_EMP":"P1" 7.351 KB 49 rows
. . imported "HR"."SAL_EMP":"P2" 7.484 KB 55 rows
. . imported "HR"."SAL_EMP":"P3" 6.390 KB 2 rows
. . imported "HR"."SAL_EMP":"P4" 6.367 KB 1 rows
. . imported "HR"."SAL_EMP":"PMAX" 6.367 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Feb 26 16:00:38 2024 elapsed 0 00:00:02
*/
[oracle@oracle ~]$ exit
SYS@ora11g> select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from dba_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME 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 1 19 15
PMAX MAXVALUE USERS 1 46 16
SYS@ora11g> select * from hr.sal_emp partition (p4);
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King 24000 90
# p4 파티션 삭
SYS@ora11g> alter table hr.sal_emp drop partition p4;
Table altered.
# Impdp : 특정 Table을 SQL문으로 가져오
[oracle@oracle ~]$ impdp system/oracle directory=data_pump_dir dumpfile=hr_sal_emp.pump sqlfile=sql_emp.sql
Import: Release 11.2.0.4.0 - Production on Mon Feb 26 16:04:59 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** directory=data_pump_dir dumpfile=hr_sal_emp.pump sqlfile=sql_emp.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at Mon Feb 26 16:05:00 2024 elapsed 0 00:00:00
*/
[oracle@oracle ~]$ vi /u01/app/oracle/admin/ora11g/dpdump/sql_emp.sql
> @ sql_emp.sql 실행하면 데이터 생성 가능
# Impdp : 테이블 내 특정 파티션의 데이터만 가져오기
--- 삭제한 파티션 p4 생성 : split 이용
SYS@ora11g> alter table hr.sal_emp
split partition pmax at (30000) into (partition p4, partition pmax);
Table altered.
> p4가 삭제된 상태
> pmax -> p4 ~3000, pmax ~maxvalue
SYS@ora11g> select * from hr.sal_emp partition (p4);
no rows selected
--- 테이블의 p4 파티션의 데이터만 가져오기
[oracle@oracle ~]$ impdp system/oracle directory=data_pump_dir dumpfile=hr_sal_emp.pump tables=hr.sal_emp:p4 content=data_only
Import: Release 11.2.0.4.0 - Production on Mon Feb 26 16:12:04 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=data_pump_dir dumpfile=hr_sal_emp.pump tables=hr.sal_emp:p4 content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."SAL_EMP":"P4" 6.367 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Feb 26 16:12:06 2024 elapsed 0 00:00:02
*/
[oracle@oracle ~]$ exit
SYS@ora11g> select * from hr.sal_emp partition (p4);
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King 24000 90
# p3 데이터만 삭제
SYS@ora11g> alter table hr.sal_emp truncate partition p3;
Table truncated.
SYS@ora11g> select * from hr.sal_emp partition (p3);
no rows selected
# Impdp : 테이블 내 특정 파티션의 데이터만 가져오기
[oracle@oracle ~]$ impdp system/oracle directory=data_pump_dir dumpfile=hr_sal_emp.pump tables=hr.sal_emp:p3 content=data_only
Import: Release 11.2.0.4.0 - Production on Mon Feb 26 16:14:36 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=data_pump_dir dumpfile=hr_sal_emp.pump tables=hr.sal_emp:p3 content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."SAL_EMP":"P3" 6.390 KB 2 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Feb 26 16:14:37 2024 elapsed 0 00:00:01
# 특정 Partition 통계 수집 : granularity => 'partition'
SYS@ora11g> select * from hr.sal_emp partition (p3);
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
101 Kochhar 17000 90
102 De Haan 17000 90
SYS@ora11g> select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from dba_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME 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
PMAX MAXVALUE USERS 1 46 16
--- p4 파티션만 통계 수집
SYS@ora11g> exec dbms_stats.gather_table_stats('hr', 'sal_emp', partname => 'p4', granularity=>'partition')
PL/SQL procedure successfully completed.
SYS@ora11g> select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from dba_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME 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 1 19 15
PMAX MAXVALUE USERS 1 46 16
# 특정 Partition만 Export
[oracle@oracle ~]$ expdp system/oracle directory=data_pump_dir tables=hr.sal_emp:p4 dumpfile=hr_sal_emp_p4.pump
Export: Release 11.2.0.4.0 - Production on Mon Feb 26 16:22:51 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=data_pump_dir tables=hr.sal_emp:p4 dumpfile=hr_sal_emp_p4.pump
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."SAL_EMP":"P4" 6.367 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/ora11g/dpdump/hr_sal_emp_p4.pump
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Feb 26 16:22:54 2024 elapsed 0 00:00:03
# 특정 파티션 Data 삭제 : Truncate
SYS@ora11g> alter table hr.sal_emp truncate partition p4;
Table truncated.
# Impdp : 특정 파티션의 데이터만 가져오기
[oracle@oracle ~]$ impdp system/oracle directory=data_pump_dir dumpfile=hr_sal_emp_p4.pump tables=hr.sal_emp:p4 content=data_only
Import: Release 11.2.0.4.0 - Production on Mon Feb 26 16:26:36 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=data_pump_dir dumpfile=hr_sal_emp_p4.pump tables=hr.sal_emp content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."SAL_EMP":"P4" 6.367 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Feb 26 16:26:38 2024 elapsed 0 00:00:01
*/
[oracle@oracle ~]$ exit
SYS@ora11g> select * from hr.sal_emp partition (p4);
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King 24000 90
> 애당초 원본 hr_sal_emp_p4.pump 파일안에 파티션 p4 데이터만 있어
그냥 tables=hr.sal_emp 테이블만 표기해도 무관하다.
'Oracle 11g > Tuning' 카테고리의 다른 글
| 94 Parallel (3) | 2024.02.28 |
|---|---|
| 93 Arraysize, Partition Pruning, Local/Global Partitioned Indexes (1) | 2024.02.27 |
| 91 통계정보 (0) | 2024.02.23 |
| 90 Pushdown, Pullup, Use_concat, Optimazer (0) | 2024.02.22 |
| 89 Pushing Subquery, View Merging, Pushdown (0) | 2024.02.21 |