728x90
반응형
SMALL
■ Range partition
1. 개요
- Oracle 8 버전부터 제공되는 파티션 방법 중 하나.
- 데이터의 범위에 따라 파티션을 나누는 방식.
- 이력성 데이터 조회에 특히 유리.
2. 주요 특징
1) 8버전 이후 도입: Range 파티션은 Oracle 8 버전부터 처음 도입.
2) 다양한 파티션 키: 여러 컬럼을 파티션 키로 지정 가능하며 최대 16개의 컬럼을 활용
3) maxvalue 파티션:
- Range 파티션에서는 범위를 벗어나는 데이터는 maxvalue 파티션에 저장
- 범위가 정의되지 않은 데이터를 수용하는 역할
3. 예시
- 날짜 기준으로 범위를 나누는 경우, 각 날짜 범위에 해당하는 파티션을 생성.
- 예시) 연도별로 파티션을 나누어 데이터를 저장하는 경우 등.
4. 활용- 주로 시간에 따른 데이터의 이력성이 있는 테이블에서 범위 파티션이 적합하게 활용 - 조회 성능 향상 및 관리 용이성을 제공.
5. 제약 사항- Range 파티션의 주요 제약사항은 특정 범위에 해당하는 데이터를 빠르게 조회하려면 적절한 파티션 키를 선택해야 한다.
#) 생성
[해석]
#1) partition by range(hire_date) 'hire_date'의 컬럼으로 기준.
#2) less then(to_data('2005-01-01', 'yyyy-mm-dd')): (=:check제약조건), 2005년 이전 값
#3) p2004: 'p2004'라는 세그먼트에 저장
#4) p2006 이후로 저장되지 않는 그 외 데이터 값은 별도로 설정하지 않으면 저장 되지 않으니, maxvalue 를 지정해서 저장해준다.
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 employees;=> Table create.
#) 파티션 확인
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_YEAR';PARTITION_ HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
---------- -------------------------------------------------------------------------------- -------------------- ---------- ---------- -----------
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
#) 파티션 타입, 갯수 확인
select partitioning_type, partition_count from user_part_tables where table_name = 'EMP_YEAR';PARTITION PARTITION_COUNT
--------- ---------------
RANGE 4
#) 파티션 key 확인
select * from user_part_key_columns where name = 'EMP_YEAR';NAME OBJEC COLUMN_NAME COLUMN_POSITION
-------------------- ----- ------------------------------ ---------------
EMP_YEAR TABLE HIRE_DATE 1
#) 전체 보기
select * from hr.emp_year;
#) 특정한 data 보기
=> select문 뒤에 partition(특정한 파티션그룹이름)
select * from hr.emp_year partition(p2004);
select * from hr.emp_year partition(p2005);
select * from hr.emp_year partition(p2006);
select * from hr.emp_year partition(pmax);
#) 삭제
drop table hr.emp_year purge;=> Table dropped.
#) maxvalues 없이 생성
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'))
)
as
select employee_id, last_name, salary, hire_date, department_id from employees;=> 오류 발생
=> ORA-14400: inserted partition key does not map to any partition
#) max 꼭 생성해줘야 한다!
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 employees;
# interval 파티셔닝(11g)
- interval 기준을 정의함으로써 정해진 간격으로 파티션이 자동 추가되도록 할 수 있다.
#) 삭제
drop table hr.emp_year purge;
#) interval 생성
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 employees;=> Table created.
#) 자동생성된 파티션 확인.
=> 기존 partition 외 SYS_P47, SYS_P48 생성된 것을 확인.
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_YEAR';PARTITION_ HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
---------- -------------------------------------------------------------------------------- -------------------- ---------- ---------- -----------
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
SYS_P47 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
SYS_P48 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
#) 조회
select * from hr.emp_year partition(p2004);
select * from hr.emp_year partition(p2005);
select * from hr.emp_year partition(p2006);
select * from hr.emp_year partition(pmax);
select * from hr.emp_year partition(SYS_P47);
select * from hr.emp_year partition(SYS_P48);
#) interval 확인해보기 => SYS_P47, SYS_P48: 'YES'
select partition_name, high_value, tablespace_name, num_rows, blocks, interval
from user_tab_partitions
where table_name = 'EMP_YEAR';PARTITION_ HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS INT
---------- -------------------------------------------------------------------------------- -------------------- ---------- ---------- ---
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_P47 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS YES
SYS_P48 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS YES
#) count 확인
select partitioning_type, partition_count from user_part_tables where table_name = 'EMP_YEAR';PARTITION PARTITION_COUNT
--------- ---------------
RANGE 1048575
■ Hash partition
1. 개요
- Oracle 8i 버전부터 제공되는 파티션 방법 중 하나.
- Partition Key 값에 해시 함수를 적용하여 데이터를 분할하는 방식.
2. 주요 특징
1) 8i 버전 이후 도입: 해시 파티션은 Oracle 8i 버전부터 도입되었다.
2) 해시 함수 활용: Partition Key 값에 해시 함수를 적용하여 데이터를 분할한다.
3) 중복성 및 유일키: 중복성보다는 유일키 값을 사용하는 것이 적합하다.
4) 데이터 분포 균등성: 파티션 컬럼으로 선정되는 데이터 분포가 고른 컬럼이어야 한다.
5) 파티션 개수: 보편적으로 2의 제곱(2, 4, 8, 16, ...)으로 설정하여 특정 파티션에 데이터가 몰리지 않도록 한다.
6) 조건절에 따른 최적화: =, IN 조건 등의 검색 시에만 Partition Pruning 기능이 수행된다.
7) 용도 및 성능 향상:
- 대용량 테이블에서 '동시에 입력이 많은' 상황이나 인덱스 경합을 줄이기 위해 주로 활용된다.
- 병렬 쿼리 수행 시에도 성능 향상을 기대할 수 있다.
3. 예시:
- 주문 테이블에서 주문번호를 기준으로 해시 파티션을 생성하는 경우.
4. 제약사항:
- 효율적인 파티셔닝을 위해 데이터 분포가 고르고, 파티션 개수가 적절하게 설정되어야 한다.
#) 생성
[해석]
#1) partition by hash(employee_id): 중복성이 없는 컬럼으로 생성
#2) partitions 4: 4개로 생성
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.
#) 확인
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_HASH';PARTITION_ HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -------------------- ---------- ---------- -----------
SYS_P43 USERS
SYS_P44 USERS
SYS_P45 USERS
SYS_P46 USERS
#) count 확인
select partitioning_type, partition_count from user_part_tables where table_name = 'EMP_HASH';PARTITION PARTITION_COUNT
--------- ---------------
HASH 4
■ List partition
1. 개요:
- Oracle 9i 버전부터 도입된 파티션 방법.
- 미리 정해진 그룹을 기준으로 데이터를 분할하는 방식으로 값 목록을 활용한다.
2. 주요 특징
1) 9i 버전 이후 도입: 리스트 파티션은 Oracle 9i 버전부터 사용 가능하다.
2) 미리 정해진 그룹 기준: 미리 정해진 값 목록을 기준으로 데이터를 분할한다.
3) 값 목록 활용: 특정 컬럼의 값 목록을 파티션 기준으로 사용한다.
4) 단일 컬럼만 가능: 하나의 컬럼만을 기준으로 파티션을 나눌 수 있다.
3. 적용 예시:
- 특정 컬럼의 값 목록을 정의하여 그룹을 형성하고, 해당 그룹을 기준으로 파티션을 생성하는 경우.
4. 제약사항:
- 단일 컬럼만을 기준으로 파티션을 나눌 수 있다.
5. 용도:
- 값의 목록을 기준으로 데이터를 파티션하고자 할 때 사용된다.
#) 생성
[해석]
#1) partition by list(department_id): 기준
#2) partition p_dept_1 values (10,20,30,40): values 안에 삽입된 값을 p_dept_1에 넣는다.
#3) partition p_dept_4 values (default): 그 외 데이터를 넣어야 한다. 생성할 때, 기본으로 넣고 생성해주는것이 좋다.
#) list partition 생성
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.
#) 확인
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_LIST';PARTITION_ HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ------------------------ -------------------- ---------- ---------- -----------
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
#) 생성된 테이블의 key 확인
select * from user_part_key_columns where name = 'EMP_LIST';NAME OBJEC COLUMN_NAME COLUMN_POSITION
-------------------- ----- ------------------------------ ---------------
EMP_LIST TABLE DEPARTMENT_ID 1
#) 파티션타입 및 갯수 확인
select partitioning_type, partition_count from user_part_tables where table_name = 'EMP_HASH';PARTITION PARTITION_COUNT
--------- ---------------
HASH 4
#) 조회
select * from hr.emp_list partition(p_dept_1);
■ composite partition(조합 파티션)
1. 개요
- 주 파티션키와 서브 파티션키를 조합하여 데이터를 분할하는 방식.
- 주 파티션키에 따라 데이터를 먼저 분배하고, 서브 파티션키에 따라 최종 세그먼트 위치를 결정한다.
- 서브 파티션 각각에 대해 세그먼트를 할당하고, 서브 파티션 단위로 데이터를 저장한다.
2. 조합 예시
| 주 파티션 | version | 서브 파티션 |
| Range Partition | 8i | Hash Partition |
| Range Partition | 9i | List Partition |
| Range Partition | 11g | Range Partition |
| List Partition | 11g | Hash Partition |
| List Partition | 11g | List Partition |
| List Partition | 11g | Range Partition |
3. 주요 특징
- 각 서브 파티션에 대해 별도의 세그먼트가 할당된다.
- 주 파티션키에 따라 1차적으로 데이터를 분배하고, 서브 파티션키에 따라 최종 세그먼트 위치를 결정한다.
4. 용도
- 여러 파티션 방법을 유연하게 조합하여 사용하고자 할 때 활용된다.
5. 버전별 지원
- 8i 버전 이후에는 여러 파티션 방법을 조합할 수 있는 기능이 도입되었다.
#) 생성
[해석1]
#1) subpartition by hash() subpartitions 4: 4개 생성
#2) partition p4999 values less than(5000): 급여가 5000미만은 p4999 에 저장
#3) partition by range(salary): 급여를 범위 정함. 주 파티션
#4) subpartition by hash(employee_id) subpartitions 4: 서브 파티션
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.
=========================================================================================
[해석2]
1차(주 파티션)로 salary로 지정된 범위 나눔 → 2차(서브파티션)로 employee_id로 4개의 그룹으로 나눔.
p4999 => h1,h2,h3,h4 (임의로 지정된 이름)
p9999 => h1,h2,h3,h4
p9999 => h1,h2,h3,h4
pmax => h1,h2,h3,h4
=========================================================================================
#) 확인
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_COMP';PARTITION_ HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ----------- -------------------- ---------- ---------- -----------
P4999 5000 USERS
P9999 10000 USERS
P19999 20000 USERS
PMAX MAXVALUE USERS
#) 주 파티션 키 컬럼 확인
select * from user_part_key_columns where name = 'EMP_COMP';NAME OBJEC COLUMN_NAME COLUMN_POSITION
-------------------- ----- ------------------------------ ---------------
EMP_COMP TABLE SALARY 1
#) 서브 파티션 키 컬럼 확인
select * from user_subpart_key_columns;NAME OBJEC COLUMN_NAME COLUMN_POSITION
-------------------- ----- ------------------------------ ---------------
EMP_COMP TABLE EMPLOYEE_ID 1
#) 서브 파티션 확인
select partitioning_type, subpartitioning_type, partition_count, def_subpartition_count
from user_part_tables
where table_name = 'EMP_COMP';PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT
--------- --------- --------------- ----------------------
RANGE HASH 4 4
#) 주 파티션, 서브파티션 확인 위치
select partition_name, subpartition_name, high_value, subpartition_position
from user_tab_subpartitions
where table_name = 'EMP_COMP';PARTITION_ SUBPARTITION_NAME HIGH_VALUE SUBPARTITION_POSITION
---------- ------------------------------ ------------ ---------------------
P19999 SYS_SUBP57 1
P19999 SYS_SUBP58 2
P19999 SYS_SUBP59 3
P19999 SYS_SUBP60 4
P4999 SYS_SUBP49 1
P4999 SYS_SUBP50 2
P4999 SYS_SUBP51 3
P4999 SYS_SUBP52 4
P9999 SYS_SUBP53 1
P9999 SYS_SUBP54 2
P9999 SYS_SUBP55 3
P9999 SYS_SUBP56 4
PMAX SYS_SUBP61 1
PMAX SYS_SUBP62 2
PMAX SYS_SUBP63 3
PMAX SYS_SUBP64 4
#) i/o 확인, 주 파티션만 읽음.
select /*+ gather_plan_statistics */ * from hr.emp_comp where salary < 5000;
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 | | 49 |00:00:00.01 | 16 | 1 |
| 1 | PARTITION RANGE SINGLE| | 1 | 43 | 49 |00:00:00.01 | 16 | 1 |
| 2 | PARTITION HASH ALL | | 1 | 43 | 49 |00:00:00.01 | 16 | 1 |
| 3 | TABLE ACCESS FULL | EMP_COMP | 4 | 43 | 49 |00:00:00.01 | 16 | 1 |
------------------------------------------------------------------------------------------------------
#) i/o 확인, 주파티션과 서브파티션 같이 확인
select /*+ gather_plan_statistics */ *
from hr.emp_comp
where salary < 5000 and employee_id = 200;
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)
#) 주 파티션이 없다면?
select /*+ gather_plan_statistics */ * from hr.emp_comp where employee_id = 200;
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)=> 주 파티션이 4개이니, 다 check 해야 한다. (4번의 i/o)
#) 삭제
drop table hr.emp_year purge;
#) range() + list()
create table hr.emp_year
partition by range(hire_date)
subpartition by list(employee_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.
#) 확인
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 NUM_ROWS BLOCKS AVG_ROW_LEN
--------------- ----------------------------------------------------------------------------------- ---------- ---------- ---------- -----------
P2004 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USERS
P2005 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USERS
P2006 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USERS
PMAX MAXVALUE USERS
#) 주 파티션 키 컬럼 확인
select * from user_part_key_columns where name = 'EMP_YEAR';NAME OBJEC COLUMN_NAME COLUMN_POSITION
-------------------- ----- ------------------------------ ---------------
EMP_YEAR TABLE HIRE_DATE 1
#) 서브 파티션 키 컬럼 확인
select * from user_subpart_key_columns where name = 'EMP_YEAR';NAME OBJEC COLUMN_NAME COLUMN_POSITION
-------------------- ----- ------------------------------ ---------------
EMP_YEAR TABLE EMPLOYEE_ID 1
#) 서브 파티션 확인
select partitioning_type, subpartitioning_type, partition_count, def_subpartition_count
from user_part_tables
where table_name = 'EMP_YEAR';PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT
--------- --------- --------------- ----------------------
RANGE LIST 4 4
#) 주 파티션, 서브파티션 확인 위치
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
16 rows selected.
#) 주 파티션 + 서브 파티션 확인
select /*+ gather_plan_statistics */ * from hr.emp_year
where hire_date > to_date('2005-05-02', 'yyyy-mm-dd') and 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 | | 1 |00:00:00.01 | 13 |
| 1 | PARTITION RANGE ITERATOR| | 1 | 1 | 1 |00:00:00.01 | 13 |
| 2 | PARTITION LIST ALL | | 3 | 1 | 1 |00:00:00.01 | 13 |
|* 3 | TABLE ACCESS FULL | EMP_YEAR | 12 | 1 | 1 |00:00:00.01 | 13 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("DEPARTMENT_ID"=20 AND "HIRE_DATE">TO_DATE(' 2005-05-02 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
#) 주 파티션만 확인
select /*+ gather_plan_statistics */ * from hr.emp_year
where hire_date > to_date('2005-05-02', 'yyyy-mm-dd');
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 | | 71 |00:00:00.01 | 16 |
| 1 | PARTITION RANGE ITERATOR| | 1 | 80 | 71 |00:00:00.01 | 16 |
| 2 | PARTITION LIST ALL | | 3 | 80 | 71 |00:00:00.01 | 16 |
|* 3 | TABLE ACCESS FULL | EMP_YEAR | 12 | 80 | 71 |00:00:00.01 | 16 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("HIRE_DATE">TO_DATE(' 2005-05-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
#) 서브 파티션만 확인
select /*+ gather_plan_statistics */ * from hr.emp_year 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 | 19 |
| 1 | PARTITION RANGE ALL| | 1 | 5 | 2 |00:00:00.01 | 19 |
| 2 | PARTITION LIST ALL| | 4 | 5 | 2 |00:00:00.01 | 19 |
|* 3 | TABLE ACCESS FULL| EMP_YEAR | 16 | 5 | 2 |00:00:00.01 | 19 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPARTMENT_ID"=20)
#) 주 파티션 조회
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');
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 | 7 |
| 1 | PARTITION RANGE SINGLE| | 1 | 7 | 7 |00:00:00.01 | 7 |
| 2 | PARTITION LIST ALL | | 1 | 7 | 7 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL | EMP_YEAR | 4 | 7 | 7 |00:00:00.01 | 7 |
---------------------------------------------------------------------------------------------
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')))
#) 주 파티션, 서브 파티션 같이 조회
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;
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 | PARTITION RANGE SINGLE| | 1 | 1 | 1 |00:00:00.01 | 7 |
| 2 | PARTITION LIST ALL | | 1 | 1 | 1 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL | EMP_YEAR | 4 | 1 | 1 |00:00:00.01 | 7 |
---------------------------------------------------------------------------------------------
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')))
#) 삭제
drop table hr.emp_year purge;
#) nologging 모드 생성
create table hr.emp_year
partition by range(hire_date)
subpartition by list(employee_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.
#) nologgin 모드 확인
select partition_name, subpartition_count, high_value, tablespace_name, logging
from user_tab_partitions
where table_name = 'EMP_YEAR';PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE TABLESPACE LOGGING
--------------- ------------------ -------------------------------------------------------------------------------- ---------- -------
P2004 4 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS NONE
P2005 4 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS NONE
P2006 4 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS NONE
PMAX 4 MAXVALUE USERS NONE
#) logging 생성
alter table hr.emp_year logging;=> Table altered.
#) 다시 확인
select partition_name, subpartition_count, high_value, tablespace_name, logging
from user_tab_partitions
where table_name = 'EMP_YEAR';PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE TABLESPACE LOGGING
--------------- ------------------ -------------------------------------------------------------------------------- ---------- -------
P2004 4 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS YES
P2005 4 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS YES
P2006 4 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS YES
PMAX 4 MAXVALUE USERS YES
#) ★ logging 모드로 변경 작업!
=> daily check 중 하나!
create table hr.no_emp
nologging
as select * from hr.employees;=> Table create.
select logging from user_tables where table_name = 'NO_EMP';LOG
---
NO
alter table hr.no_emp logging;=> Table altered.
select logging from user_tables where table_name = 'NO_EMP';LOG
---
YES
drop table hr.no_emp purge;=> Table dropped.
#) 생성
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.
#) 생성 확인
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 25000 USERS
#) 파티션 확인
select partitioning_type, partition_count from user_part_tables where table_name = 'SAL_EMP';PARTITION PARTITION_COUNT
--------- ---------------
RANGE 3
| 개념 설명과 예시 | |
| Range Partition | - 특정 범위의 값을 기준으로 데이터를 분할하는 파티션 방법. - 주로 날짜, 숫자 등의 범위 기준으로 사용된다. ex) 날짜별로 파티션을 나누거나, 숫자 범위에 따라 파티션을 생성하는 경우. |
| Hash Partition | - 해시 함수를 사용하여 데이터를 분할하는 파티션 방법. - 해시 함수 결과에 따라 데이터가 균등하게 분산된다. ex) 해시 함수를 통해 생성된 값에 따라 파티션을 나누는 경우. |
| List Partition | - 명시적인 값 목록을 기준으로 데이터를 분할하는 파티션 방법. - 특정 값 목록에 속하는 데이터가 해당 파티션에 저장된다. ex) 지정된 목록의 값에 따라 파티션을 나누는 경우. |
| Composite Partition | - 여러 파티션 기법을 조합하여 데이터를 분할하는 방법. - 주 파티션키와 서브 파티션키를 조합하여 세분화된 파티션을 생성한다. ex) 범위와 목록, 해시와 목록 등 여러 파티션 기법을 조합하여 사용하는 경우. |
728x90
반응형
LIST
'Data Base > SQL 튜닝' 카테고리의 다른 글
| Data Pump_expdp, impdp (1) | 2024.02.26 |
|---|---|
| PARTITION 옵션_나누기, 추가, 수정, 삭제, 통계 수집 (0) | 2024.02.26 |
| PARTITION, 수동 파티셔닝(manual partitioning) (1) | 2024.02.26 |
| 통계 수집 (0) | 2024.02.26 |
| join 조건 pushdown, BLOOM FILTER, 조건절 pushdown (0) | 2024.02.26 |