📔설명
파티셔닝
: 물리적 데이터를 논리적으로 나눔
✨테이블 파티셔닝
- 테이블 파티셔닝 시
논리적으로는테이블로 접근하나,물리적으론 테이블 내각각의 파티션으로 접근 - 각각의 파티션은
세그먼트에 해당
-> 파티션 테이블은 파티션과 세그먼트가 1:1 구조 - 데이터를 파티션 단위로 보관하므로 추가, 삭제, 변경이 매우 빠르고 쉬움
Range 파티션
- 파티션 키 컬럼이
값의 범위로 지정 - 데이터가 많이 쌓이는 테이블에서 나눌 기준이 모호할 경우, 주로
쌓이는 날짜기준 파티션 - 파티션 키 컬럼을 정하기 매우 쉬움
- less than 안의 값을
포함하지 않음
create table ord_range (
ord_no number(10) not null,
ord_dt varchar2(8) not null,
ord_hms varchar2(6),
shop_no varchar2(10),
...
)
partition by range (ord_dt)(
partition p201201 values less than ('201202'),
...,
partition p_default values less than (MAXVALUE)
);p201201 파티션에는 201202보다 작은 값이 들어올 수 있다.
- 키 컬럼 자체가 연속성이 있는 데이터를 갖기 때문에,
키 컬럼을 가지고부등호 연산이나between연산을 할 경우 파티션 몇 개만 읽을 수 있음
where ord_dt between '20120115' and '20120125'위 sql은 ord_dt를 월 단위로 나누어 저장하는데, 만약 1월 15일부터 25일까지 데이터를 sql 조회시, 1월 파티션만 읽어 결과 집합을 만든다.
List 파티션
- 파티션 키 컬럼이
값으로 지정 - 키 컬럼의 값이 설정한 값과 일치할 경우
range 파티션과 다르게values()안의 값을 사용
create table bill_list(
bill_no number(10) not null,
bill_ym varchar2(6) not null,
member_id varchar2(10),
...
)
partition by list(bill_ym)(
partition p201201 values('201201'),
...,
partition p_default values(default)
);sql문 조회 시, 일부 파티션만 읽고 처리할 수 있다.
Hash 파티션
- 파티션 키 컬럼의 값을
오라클 내부에서 지정 - 키 컬럼의 값을
해시 함수를 이용해 지정한 파티션 개수로 나누어 저장
create table ord_hash(
ord_no number(10) not null,
ord_dt varchar2(8),
ord_hms varchar2(6),
shop_no varchar2(10),
...
)
partition by hash (ord_no) partitions 8;- 해시 파티션시
같은 값이 많지 않은 컬럼을 사용해야 효과 등치 조건과IN조건으로만 사용 (해시조인과 동일)
복합 파티셔닝
- 복합 파티션 생성 시
삭제와조회기준으로 전략 메인 파티션과서브 파티션으로 구분메인 파티션- 모든 방식의 파티션 가능(12c 이후)서브 파티션- 모든 방식의 파티션 가능
ex) 오래된 데이터를 삭제하는 일이 빈번하고, 특정 컬럼과 자주 조인
=> range-해시 또는 리스트-해시 파티션
🎃인덱스 파티셔닝
- 인덱스도 테이블과 마찬가지로 나누어 보관 가능
비파티션 인덱스
인덱스는 파티션 하지 않음- ex) 사원은 부서별로 배치했으나, 사원 목록(인덱스)는 별도로 나누지 않음
- 일반적으로 PK의 경우, 비파티션 인덱스를 사용해야 완벽하게
무결성유지
로컬 파티션 인덱스
테이블 파티션과같은 기준으로 인덱스 나눔- ex) 사원을 배치한 기준과 같이 사원 목록도 부서별로 나눔
인덱스 생성 구문제일 뒤에LOCAL키워드를 붙이면 생성 가능- 테이블 파티션 삭제시 로컬 인덱스 동시에 삭제 가능
글로벌 파티션 인덱스
- 테이블 파티션과는
다른 기준으로 인덱스 나눔 - ex) 사원은 부서별로 배치하며, 사원 목록은 연도별로 나눔
파티션 키 컬럼을 항상파티션 인덱스의첫 번째 컬럼으로 만들어야 함
🥿파티션 Pruning
테이블 전체를 읽지 않고,필요한 파티션만 읽을 수 있도록 하는 기능
select /*+ gather_plan_statistics */ count(*)
from ord_list -- list 파티션
where ord_ym='201201';
partition list single을 보면 리스트 파티션으로 나뉜 테이블에서 파티션 한 개를 읽은 것이다.
select /*+ gather_plan_statistics */ count(*)
from ord_range --range 파티션
where ord_ym='201201';
처음 sql과 위의 sql의 차이는 조건절이 필터 조건으로 나온 것이다.
ord_ym='201201' 조건 때문인데, 오라클은 파티션 키 컬럼에 저장된 값이 하나라는 것을 알 수 없기 때문에 필터 조건으로 나온 것이다.
- 리스트 파티션을 만들어도, 경우에 따라
between과 같은 범위 조건을 사용할 경우 해당 파티션만 액세스 할 수 있다. - 인덱스 컬럼과 마찬가지로, 가공하면 오라클이 모르기 때문에 값을 가공해야 함
ex) substr(ord_ym,1,6)=:st 대신 ord_ym=substr('20120101',1,6)
merge 구문에서의 파티션 Pruning
merge into ord_list m
using (
select ord_no,shop_no
from ord
where ord_no=7
and ord_dt='20120101') s
on (m.ord_no=s.ord_no)
when matched then update
set m.shop_no=s.shop_no;
-- partition list all로 모든 파티션을 읽게 됨merge into (select * from ord_list where ord_ym=substr('20120101',1,6)) m
using (
select ord_no,shop_no
from ord
where ord_no=7
and ord_dt='20120101') s
on (m.ord_no=s.ord_no)
when matched then update
set m.shop_no=s.shop_no;
-- partition list single로 하나의 파티션만 읽음
MySQL DBA 신입