파티션이란 ?
- 파티션테이블은 하나의 큰 테이블을 물리적으로 나눠놓은 것
- 물리적으로 나눠놨지만, 논리적으로는 하나의 테이블로 간주
파티션 종류
1) RANGE
2) LIST
3) HASH
4) INTERVAL
1) RANGE
- 가장 많이 쓰이는 형식
CREATE TABLE TEST_P(
DAT CHAR(8) NOT NULL,
NUM CHAR(8) NOT NULL,
PRODUCT CHAR(8) NOT NULL)
PARTITION BY RANGE (DAT)
(
PARTITION P_08 VALUES LESS THAN ('20150832') TABLESPACE PBJ,
PARTITION P_09 VALUES LESS THAN ('20150932') TABLESPACE PBJ,
PARTITION P_10 VALUES LESS THAN ('20151032') TABLESPACE PBJ,
PARTITION P_11 VALUES LESS THAN ('20151132') TABLESPACE PBJ,
PARTITION P_12 VALUES LESS THAN ('20151232') TABLESPACE PBJ,
PARTITION P_13 VALUES LESS THAN ('20160132') TABLESPACE PBJ,
PARTITION P_14 VALUES LESS THAN ('20160232') TABLESPACE PBJ,
PARTITION P_15 VALUES LESS THAN ('20160332') TABLESPACE PBJ,
PARTITION P_16 VALUES LESS THAN ('20160432') TABLESPACE PBJ,
PARTITION P_17 VALUES LESS THAN ('20160532') TABLESPACE PBJ,
PARTITION P_18 VALUES LESS THAN ('20160632') TABLESPACE PBJ,
PARTITION P_19 VALUES LESS THAN ('20160732') TABLESPACE PBJ,
PARTITION P_20 VALUES LESS THAN ('20160832') TABLESPACE PBJ,
PARTITION P_21 VALUES LESS THAN ('20160932') TABLESPACE PBJ,
PARTITION P_22 VALUES LESS THAN ('20161032') TABLESPACE PBJ,
PARTITION P_23 VALUES LESS THAN ('20161132') TABLESPACE PBJ,
PARTITION P_24 VALUES LESS THAN ('20161232') TABLESPACE PBJ
);
insert into TEST_P values ('20150821','1','phone');
insert into TEST_P values ('20150921','2','TV');
insert into TEST_P values ('20151022','3','mouse');
insert into TEST_P values ('20151102','4','monitor');
insert into TEST_P values ('20151213','5','computer');
insert into TEST_P values ('20150913','6','tissu');
insert into TEST_P values ('20151012','7','cel');
insert into TEST_P values ('20151111','8','watch');
insert into TEST_P values ('20151217','9','pen');
insert into TEST_P values ('20150808','10','lily');
insert into TEST_P values ('20150909','11','pbj');
insert into TEST_P values ('20151010','12','lmh');
insert into TEST_P values ('20160530','13','pje');
insert into TEST_P values ('20161229','14','ssk');
insert into TEST_P values ('20160821','15','phone');
insert into TEST_P values ('20160921','16','TV');
insert into TEST_P values ('20161022','17','mouse');
insert into TEST_P values ('20161102','18','monitor');
insert into TEST_P values ('20161213','19','computer');
insert into TEST_P values ('20160913','20','tissu');
insert into TEST_P values ('20161012','21','cel');
insert into TEST_P values ('20161111','22','watch');
insert into TEST_P values ('20161217','23','pen');
insert into TEST_P values ('20160808','24','lily');
insert into TEST_P values ('20160909','25','pbj');
insert into TEST_P values ('20161010','26','lmh');
insert into TEST_P values ('20160130','27','pje');
insert into TEST_P values ('20161229','28','ssk');
COMMIT;
# 개별 파티션만 조회
select * from test_p partition (p_08);
select * from test_p partition (p_09);
select * from test_p partition (p_10);
select * from test_p partition (p_11);
select * from test_p partition (p_12);
SELECT * FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME='P_RANGE';
----------------------------------------------------------------------------------------------------
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
----------------------------------------------------------------------------------------------------
PBJ P_RANGE P_08 '20150832' PBJ_D01
PBJ P_RANGE P_09 '20150932' PBJ_D01
PBJ P_RANGE P_10 '20151032' PBJ_D01
PBJ P_RANGE P_11 '20151132' PBJ_D01
PBJ P_RANGE P_12 '20151232' PBJ_D01
PBJ P_RANGE P_13 '20160132' PBJ_D01
PBJ P_RANGE P_14 '20160232' PBJ_D01
PBJ P_RANGE P_15 '20160332' PBJ_D01
PBJ P_RANGE P_16 '20160432' PBJ_D01
PBJ P_RANGE P_17 '20160532' PBJ_D01
PBJ P_RANGE P_18 '20160632' PBJ_D01
PBJ P_RANGE P_19 '20160732' PBJ_D01
PBJ P_RANGE P_20 '20160832' PBJ_D01
PBJ P_RANGE P_21 '20160932' PBJ_D01
PBJ P_RANGE P_22 '20161032' PBJ_D01
PBJ P_RANGE P_23 '20161132' PBJ_D01
PBJ P_RANGE P_24 '20161232' PBJ_D01
2) LIST
- SAP 에서 많이 사용됨
create table list_pt(
NO number not null,
NAME varchar2(10),
JOB varchar2(9),
MGR number(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
PARTITION BY LIST (JOB)
(
PARTITION list_pt_1 values('MANAGER') TABLESPACE TBS_A,
PARTITION list_pt_2 values('SALESMAN') TABLESPACE TBS_A,
PARTITION list_pt_3 values('ANALYST') TABLESPACE TBS_B,
PARTITION list_pt_4 values('PRESIDENT','CLERK') TABLESPACE TBS_B);
INSERT INTO list_pt values(1,'SMITH','CLERK',7902,SYSDATE,800,NULL,20)
INSERT INTO list_pt values(2,'ALLEN','SALESMAN',8921,SYSDATE,700,300,30);
INSERT INTO list_pt values(3,'WARD','SALESMAN',7773,SYSDATE,1200,400,10);
INSERT INTO list_pt values(4,'JONES','MANAGER',7321,SYSDATE,1550,NULL,20);
INSERT INTO list_pt values(5,'BLEAK','ANALYST',8442,SYSDATE,1000,500,20);
INSERT INTO list_pt values(6,'BOMB','CLERK',8884,SYSDATE,900,400,10);
INSERT INTO list_pt values(7,'PPEA','PRESIDENT',7729,SYSDATE,700,NULL,30);
INSERT INTO list_pt values(8,'ABC','MANAGER',7123,SYSDATE,1100,500,10);
INSERT INTO list_pt values(9,'WER','DBA',8563,SYSDATE,1600,700,30);
select * from list_pt partition(list_pt_1);
# 파티션 추가
ALTER TABLE list_pt
add partition list_pt5 values('DBA')
tablespace TBS_A nologging;
# 파티션 드랍
ALTER TABLE list_pt drop partition list_pt5
3) HASH
- 지정된 TABLESPACE 에 데이터를 분포시켜서 저장
- 비효율적임, 특정 TABLESPACE 에 어떤 데이터가 있는지 확인 할 수가 없음
-- 파티션 생성 시 파티션 명을 지정하지 않을 경우
-- 파티션 명이 SYS_P 이런 식으로 자동 생성됨
create table hash_pt(
NO number,
year char(4),
month char(2) not null,
day char(2) not null,
name varchar2(30),
price number)
PARTITION BY hash (no)
partitions 4 --> 2 의 거듭제곱
STORE IN(TBS_A, TBS_B, TBS_C, TBS_D);
SELECT * FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME ='P_HASH';
-------------------------------------------------------------------
TABLE_OWNER TABLE_NAME PARTITION_NAME
-------------------------------------------------------------------
PBJ P_HASH SYS_P1353
PBJ P_HASH SYS_P1354
PBJ P_HASH SYS_P1355
PBJ P_HASH SYS_P1356
-- 파티션 명을 지정해서 생성
create table PBJ.P_HASH(
NO number,
year char(4),
month char(2) not null,
day char(2) not null,
name varchar2(30),
price number)
PARTITION BY hash (no)
(
PARTITION hash_1 tablespace P_HASH_1,
PARTITION hash_2 tablespace P_HASH_2,
PARTITION hash_3 tablespace P_HASH_3,
PARTITION hash_4 tablespace P_HASH_4);
INSERT INTO PBJ.P_HASH values(1,2010,03,12,'scott',2500);
INSERT INTO PBJ.P_HASH values(2,2011,04,22,'jone',4300);
INSERT INTO PBJ.P_HASH values(3,2012,05,30,'miller',2100);
INSERT INTO PBJ.P_HASH values(4,2013,06,06,'lion',1500);
INSERT INTO PBJ.P_HASH values(5,2014,08,18,'tiger',3200);
INSERT INTO PBJ.P_HASH values(6,2017,09,20,'pbj',500);
INSERT INTO PBJ.P_HASH values(7,2018,04,17,'AAA',4000);
INSERT INTO PBJ.P_HASH values(8,2019,10,27,'BBB',2000);
INSERT INTO PBJ.P_HASH values(9,2020,11,02,'CCC',1000);
INSERT INTO PBJ.P_HASH values(10,2021,12,07,'DDD',4000);
COMMIT;
## 데이터가 테이블 스페이스(P_HASH_1 ~ 4) 에 자동 분배가 됐는지 확인
- 테이블스페이스 사이즈 조회
select distinct d.file_id file#,
d.tablespace_name ts_name,
d.bytes/1024/1024 MB,
d.bytes/8192 total_blocks,
sum(e.blocks) used_blocks,
to_char( nvl( round( sum(e.blocks)/(d.bytes/8192), 4 ), 0 )*100, '09.99') || '%' pct_used
from dba_extents e,
dba_data_files d
where d.file_id=e.file_id(+)
and d.tablespace_name like 'P_HASH%'
group by d.file_id,
d.tablespace_name,
d.bytes
order by 1,
2;
| FILE# | TS_NAME | MB | TOTAL_BLOCKS | USED_BLOCKS | PCT_USED |
| 7 | P_HASH_1 | 10 | 1280 | 1024 | 80.00% |
| 8 | P_HASH_2 | 10 | 1280 | 1024 | 80.00% |
| 9 | P_HASH_3 | 10 | 1280 | 1024 | 80.00% |
| 10 | P_HASH_4 | 10 | 1280 | 1024 | 80.00% |
4) INTERVAL
- 만들어져 있는 파티션 외의 날짜로 데이터가 들어올 경우 자동적으로 파티션이 만들어짐
- 파티션 명을 보고 어떤 데이터가 있는지 유추하기가 힘듬
(range 같은 경우. P202201 이런 명칭으로 파티션명으로 생성함, INTERVAL 파티션 명은 SYS_ 이런식.. )
CREATE TABLE PBJ.P_INTERVAL (
MSG_ID VARCHAR2(30) NOT NULL, /* 메시지 일련번호 (메시지에 대해 고유한 값이어야 함), SMS/LMS 즉시발송 시 일련번호 길이는 20byte로 등록 */
REG_DTM DATE DEFAULT SYSDATE NOT NULL
) TABLESPACE PBJ_D01
PARTITION BY RANGE (REG_DTM)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION FIRST_PARTITION VALUES LESS THAN (TO_DATE(' 2018-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE PBJ_D01
,
PARTITION VALUES LESS THAN (TO_DATE(' 2018-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE PBJ_D01
,
PARTITION VALUES LESS THAN (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE PBJ_D01
,
PARTITION VALUES LESS THAN (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE PBJ_D01
,
PARTITION VALUES LESS THAN (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE PBJ_D01
,
PARTITION VALUES LESS THAN (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE PBJ_D01
)
;
CREATE UNIQUE INDEX PBJ.P_INTERVAL_PK
ON PBJ.P_INTERVAL (MSG_ID,REG_DTM)
LOCAL;
ALTER TABLE PBJ.P_INTERVAL
ADD CONSTRAINT P_INTERVAL_PK PRIMARY KEY (MSG_ID,REG_DTM) ;
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME='P_INTERVAL';
| TABLE_OWNER | TABLE_NAME | PARTITION_NAME | HIGH_VALUE | TABLESPACE_NAME |
| PBJ | P_INTERVAL | FIRST_PARTITION | TO_DATE(' 2018-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
| PBJ | P_INTERVAL | SYS_P1370 | TO_DATE(' 2018-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
| PBJ | P_INTERVAL | SYS_P1371 | TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
| PBJ | P_INTERVAL | SYS_P1372 | TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
| PBJ | P_INTERVAL | SYS_P1373 | TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
| PBJ | P_INTERVAL | SYS_P1374 | TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
HIGH VALUE 에 해당되지 않는 데이터 Insert 수행
- 파티션이 자동으로 만들어지는지 확인
insert into pbj.P_INTERVAL values('12345',sysdate);
commit
- SYS_P1380 파티션 신규 생성된 것을 확인 할 수 있음.
| TABLE_OWNER | TABLE_NAME | PARTITION_NAME | HIGH_VALUE | TABLESPACE_NAME |
| PBJ | P_INTERVAL | FIRST_PARTITION | TO_DATE(' 2018-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
| PBJ | P_INTERVAL | SYS_P1370 | TO_DATE(' 2018-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
| PBJ | P_INTERVAL | SYS_P1371 | TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
| PBJ | P_INTERVAL | SYS_P1372 | TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
| PBJ | P_INTERVAL | SYS_P1373 | TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
| PBJ | P_INTERVAL | SYS_P1374 | TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
| pbj | P_INTERVAL | SYS_P1380 | TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') | PBJ_D01 |
'Oracle > partition' 카테고리의 다른 글
| partition index (0) | 2022.01.25 |
|---|