5. 파티셔닝

■ 파티션 테이블 생성 및 관리

*목차

1. 테이블 파티셔닝

2. 파티션 pruning

3. 인덱스 파티셔닝

 

▒ 파티셔닝이란?

 파티셔닝은 테이블 또는 인덱스를 파티션 단위로 나누어서

저장하는 것을 말한다.

 테이블을 파티셔닝하면 하나의 테이블일지라도

파티션 키에 따라 물리적으로 별도의 세그먼트에 데이터가

저장된다.

 

 

그림의 옷장서랍을 빗대어 설명하면,

봄, 여름, 가을, 겨울 옷을 따로 다른 서랍(partition)에 저장해두면

검색속도가 빨라진다.

해당 파티션만 검색하면 되기 때문에 성능이 좋아진다.

 

파티셔닝을 왜 사용하는 것인가?

            빠르게 data를 검색하기 위하여.

 

 

▒파티셔닝의 장점

 1. 관리적 측면: 보관주기가 지난 data 들을 별도로 백업하고

                     지우는 일이 아주 쉬워진다.

 

 2. 성능적 측면: 파티셔닝 하지 않는 테이블들이 대용량일 때

                    인덱스를 이용해서 data를 검색하더라도

                    data의 양이 많아서 인덱스를 이용해

                    건건이 테이블을 엑세스 하는 방식은

                    일정량이 넘는 순간 full table scan 보다

                     더 못한 결과가 나온다.

                    그렇다고 full table scan 을 해도 부담스럽다면

                    바로 그 때 파티션을 나누면

                    full table scan을 하더라도 일부 파티션 세그먼트만

                    읽고 멈출 수 있다.

 

 

░ 파티션 뷰 생성

문제82. 아래의 수동 파티셔닝을 파티션 뷰로 구현하시오

 

demobld

 

1. 파티션 뷰를 정의할 때 사용할 base 테이블을 만든다.

create table p1

as

select * from emp

where deptno = 10;

 

create table p2

as

select * from emp

where deptno = 20;

 

create table p3

as

select * from emp

where deptno = 30;

 

 

2. 체크 제약을 반드시 설정해야 함

alter table p1

add constraint c_deptno_10 check(deptno < 20);

 

alter table p2

add constraint c_deptno_20 check(deptno >= 20 and deptno < 30);

 

 

alter table p3

add constraint c_deptno_30 check(deptno >=  30 and deptno < 40);

 

 

3. 인덱스를 생성한다

create index p1_empno_idx on p1(empno);

 

create index p2_empno_idx on p2(empno);

 

create index p3_empno_idx on p3(empno);

 

 

4. 3개의 테이블에 대하여 분석작업을 한다

analyze table p1 compute statistics;

 

analyze table p2 compute statistics;

 

analyze table p3 compute statistics;

 

 

 

문제83. p2 테이블에 아래의 data를 입력하시오

empno : 3829

ename: jack

sal        : 4000

deptno: 30

 

insert into p2(empno, ename, sal, deptno)

values(3829, 'jack', 4000, 30);

 

 

응 에러

 

※설명: p1 테이블에는 부서번호 10번만 입력할 수 있고

         p2 테이블에는 부서번호 20번만 입력할 수 있고

         p3 테이블에는 부서번호 30번만 입력할 수 있다.

       

 

 

문제84. 위에서 준비된 3개의 테이블(segment)을 이용해서

 파티션 view 를 생성하시오

 

create or replace view emp_partition

 as

 select * from p1

union all

 select * from p2

union all

 select * from p3;

 

select * from emp_partition;

 

 

 

 

문제85. 부서번호 20번인 사원들의 모든 컬럼을 출력하는

아래의 2개의 SQL 의 성능을 비교해보시오

(BLOCK 의 갯수)

 

SELECT *

FROM EMP_PARTITION

WHERE deptno = 20;

 

 

 

 

 

SELECT *

FROM EMP

WHERE deptno = 20;

 

 

 

 

이 작은 테이블도 이렇게 블럭갯수 차이가 나는데

대용량 처리할 때는 엄청난 시간단축을 도와준다.

(개념의 이해를 위해 하셨다고 함.

  현업에서는 partition view 안쓴다고 함)

 

 

 

░ 파티션 테이블 생성

 

86. 이번에는 파티션 뷰 말고

실제로 파티션 테이블을 생성하시오

(pdborcl 에서만 됩니다!!!!!!!!!)

 

create table emp_partition2

partition by range(deptno)

 (

   partition p1 values less than (20),

   partition p2 values less than (30),

   partition p3 values less than (40)

  )

 as

 select * from emp;

 

select * from emp_partition2;

 

 

 

 

이제 아래 2개의 테이블의 블럭수를 비교해보자

select * from emp_partition2

where deptno = 20;

 

select * from emp

 where deptno = 20;

 

emp_partition2 가 블럭수가 더 적다.

 

 

 

문제87. 우리반 테이블을 12c database 에 구현하시오.

 

  12c database(orcl) --------------> 11g database(xe)

             ↓

create public database link link_11g

 connect to scott

 identified by tiger

 using 'localhost:1521/xe';

 

select * from tab@link_11g;

 

create table emp2

 as

 select * from emp2@link_11g;

 

 

 

문제88. 우리반 테이블로 range 파티션 테이블을 생성하시오

 

파티션 테이블명: emp2_partition

파티션 키 컬럼  : age

파티션 나누는 기준: p1 (26살 이하)

                          p2 (28살 이하)

                          p3 (30살 이하)

                          p4 (45살 이하)

 

create table emp2_partition

partition by range(age)

 (

   partition p1 values less than (27),

   partition p2 values less than (29),

   partition p3 values less than (31),

   partition p4 values less than (46)

 )

 as

 select * from emp2;

 

select * from emp2_partition;

 

 

 

문제89. 나이가 26살인 학생들의 이름, 나이를 출력하는데

 

select ename, age

from emp2_partition

where age = 26;

 

select ename, age

from emp2

where age=26;

 

 

※파티션 테이블의 효과를 보려면

   파티션 마다 data가 골고루 분포가 되어야 한다.

 

*emp2_partition 테이블에 대해서 분석 작업을 수행

 

exec dbms_stats.gather_table_stats('SCOTT', 'EMP2_PARTITION');

 

 select table_name, partition_name, num_rows

 from user_tab_partitions

 where table_name = 'EMP2_PARTITION' ;

 

 

 

화면 캡처: 2018-11-13 오전 11:19

 

이러면 잘못 만든 파티션이다.

데이터가 골고루 분포되어 있어야 한다.

 

 

░파티션의 종류

1. range 파티션 ----> 날짜 컬럼, 숫자 컬럼을 기준으로 나눈

                              파티션 테이블(범위를 지정할 수 있는 컬럼)

 예: sk 텔레콤의 요금 테이블의 월별 파티션

      또는 emp의 deptno, emp2의 age

 

2. hash 파티션 -----> 해시함수를 이용해서 오라클이 알아서 data를

                            정해진 파티션 갯수만큼 골고루 분배하는

                            파티션

  장점: 파티션 테이블을 생성하기 편하다.

  단점: 어느 데이터가 어느 파티션에 들어가 있는지

           알기 어려움.

                         괜찮다. 알아서 잘 정리 되있으니까.

                         현업에서 많이 사용한다.

 

3. list 파티션---------> 사용자에 의해 미리 정해진 그룹핑 기준에 따라

                        데이터를 분할하는 파티션(range랑 비슷)

   예: 우리반 테이블을 통신사별로 파티셔닝 하고 싶다.

       sk, kt, lg

 

4. 복합 파티션

   range - hash 파티션

   range - list   파티션

   range - range파티션

   list    -  hash 파티션

   list    -  list   파티션

   list    -  range파티션

 

 

░ hash 파티션 생성 방법

 

create table emp195

 partition by hash(deptno) partitions 3

as

  select * from emp;

 

골고루 분배됬나 확인해보기

exec dbms_stats.gather_table_stats('SCOTT', 'EMP195');

 

 select table_name, partition_name, num_rows

 from user_tab_partitions

 where table_name = 'EMP195' ;

 

hash 는 우리가 옷장서랍 갯수만 정해주면

옵티마이져가 알아서 넣어준다.

 

 

 

 

문제90. 우리반 테이블을 해시 파티션으로 생성하시오

 (파티션 테이블 이름: emp2_hash_partition)

파티션 key = age

 

create table emp2_hash_partition

partition by hash(age) partitions 4

as

 select * from emp2;

 

exec dbms_stats.gather_table_stats('SCOTT', 'EMP2_HASH_PARTITION');

 

select table_name, partition_name, num_rows

 from user_tab_partitions

 where table_name = 'EMP2_HASH_PARTITION';

 

 

문제91. 사원 테이블의 부서번호로 list 파티션을 생성하시오

 

create table emp_list_partition

 partition by list(deptno)

 (partition p1 values('10'),

  partition p2 values('20'),

  partition p3 values('30') )

 as

 select * from emp;

 

exec dbms_stats.gather_table_stats('SCOTT', 'EMP_LIST_PARTITION');

 

select table_name, partition_name, num_rows

 from user_tab_partitions

 where table_name = 'EMP_LIST_PARTITION';

 

 

문제92.(점심시간 문제)

우리반 테이블을 통신사별로 나눠서 리스트 파티션 테이블을

생성하시오

update emp2

 set telecom = 'sk'

 where telecom = 'SK';

 

create table emp2_list_partition

partition by list (telecom)

(

 partition p1 values('sk'),

 partition p2 values('kt'),

 partition p3 values('lg')

 )

 as

 select * from emp2;

 

exec dbms_stats.gather_table_stats ('SCOTT', 'EMP2_LIST_PARTITION');

 

select table_name, partition_name, num_rows

 from user_tab_partitions

 where table_name = 'EMP2_LIST_PARTITION';

 

 

 

 

 

 

■ 파티션 테이블 생성 및 관리

*목차

1. 테이블 파티셔닝

2. 파티션 pruning

3. 인덱스 파티셔닝

 

 

░ 파티션 pruning

 Prune 의 뜻?    쓸데없는 가지를 치다

                      불필요한 부분을 제거하다

                       (= partition range single)

 

 SQL을 실행하는 시점에서 SQL 의 조건절을 분석해서

읽지 않아도 되는 파티션 세그먼트를 엑세스 대상에서

제외시키는 기능

 

*파티션 프루닝이 되고 있는 실행계획

 

select *

from emp_partition2

where deptno =20;

 

 

partition range single ->

         파티션 프루닝이 되고있다

     즉 옷장의 해당 서랍만 열었다

 

 

문제93. 점심시간 문제로 만들었던

통신사별로 구분한 파티션 테이블의 쿼리문을

작성해서

파티션 프루닝을 하는지 확인하시오.

 

select *

from emp2_list_partition

where telecom = 'sk';

 

**where 있어야지(즉 조건있어야지) single(=prune) 보는지

 알 수 있다. (조건없으면 그냥 풀탐)

 

 

*실행계획 보는 방법 2가지

 1. 예상 실행계획 보는 방법 -> sql gate 에서 F7 누르면 된다.

 2. 실제 실행계획 보는 방법 -> 실제로 실행을 하면서 실행할 때

                                          수행했던 계획 확인

 

select /*+ gather_plan_statistics */ *

from emp_partition2

where deptno = 20;

>>>>>>>실제실행계획 보는거

 

select *

from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +alias +outline +predicate'));

                     

 

 

░ 파티션 프루닝이 안되는 경우

1. where 절의 조건 컬럼을 가공했을 때

2. 등치조건이나 in 조건이 아닐 때(hash 파티션일때만)

 

 

select *

 from emp_partition2

 where trim(deptno) = 20; <<<<whrere절 가공

*******trim 없으면 single(prune) 탑니다.

 

select /*+ gather_plan_statistics */ *

from emp_partition2

where trim(deptno) = 20;

>>>>>>>실제실행계획 보는거

 

all 했다.

 

 

 

select *

 from emp195   <<<<<<<<<<<<hash파티션 걸어놓은 테이블

 where deptno like '2%';

 

 

 

hash all!!!!!!!

<등치조건이나 in 조건이 아닐 때(hash 파티션일때만)>

 

 

 

 

 

░ 파티션 와이즈 조인 wise join

                  " 파티션 끼리 조인 하는 것  "

 

 

                       10         20         30        40

emp_partition     □          □          □        □

                       ↓           ↓           ↓         ↓ →파티션끼리 조인

dept_partition     □          □          □        □

 

 

 

 

*파티션 와이즈 조인이 아닌 경우

 

emp    10    20    10    20    30    10    20    20    30    10    20    30   ... ...

            →   →   →   →   →   →   →   →  →   →   →   →  → →

            ↑

dept   10    20    30    40

 

 

문제94. emp_partition 파티션 테이블과

          dept_partition 파티션 테이블을

          deptno 를 파티션 키로 생성하시오 (레인지 파티션)

 

drop view emp_partition;

drop table emp_partition;

 

create table emp_partition

 partition by range(deptno)

(

 partition p1 values less than (20),

 partition p2 values less than (30),

 partition p3 values less than (40),

 partition p4 values less than (50)

 )

 as select * from emp;

 

 

create table dept_partition

partition by range(deptno)

( partition p1 values less than (20),

 partition p2 values less than (30),

 partition p3 values less than (40),

 partition p4 values less than (50)

 )

 as select * from dept;

 

 

 select * from emp_partition;

 select * from dept_partition;

 

 

문제95. emp_partition 과 dept_partition 을

          조인해서 이름, 부서위치, 부서번호를 출력하고

          실행계획을 확인하시오

 

select e.ename, d.loc, e.deptno

 from emp_partition e, dept_partition d

where e.deptno = d.deptno;

 

 

 

 

 

문제96. 위의 조인문을 해시조인으로 유도해서

 파티션 와이즈 조인이 되게 하시오.

select /*+ leading (d e) use_hash(e)

            full(d)     full(e)

             parellel(e 2)  parallel(d 2)        <<해쉬 쓸 때 병렬구조 중요

             pq_distribute(e.none.none) */

 e.ename, d.loc, e.deptno

 from emp_partition e, dept_partition d

where e.deptno = d.deptno;

 

 

*이렇게 실행계획이 나와야

 파티션 와이즈 조인 이다.

 

*더 빠르게 >>>>> parallel

*와이즈 조인유도: pq_distribute(e.none.none)

 *******점 찍는거 주의*****************

 

※ 조인하려는 테이블이 둘 다 파티션 테이블인데

   실행계획 돌렸는데 파티션 와이즈 조인을 안 한다면

pq_distribute(e.none.none) 힌트를 사용해야 한다.

PQ_DISTRIBUTE( Inner, none, none )

• Full-Partition Wise 조인으로 유도할 때 사용한다.

당연히,

양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로

파티셔닝( equi-partitioning ) 돼 있을 때만 작동한다.

 

 

 

↑

위 작업은 2개 테이블에 미리 파티셔닝이 걸려있을 때다.

미리 파티셔닝이 안 걸려 있는 상황에서 쓸 수 있는

힌트가 있다

░ 둘 다 파티션 테이블이 아닌 경우 (동적 파티셔닝)

PQ_DISTRIBUTE( Inner, hash, hash )

• 조인 키 컬럼을 해시 함수에 적용하고 거기서 반환된 값을 기준으로

양쪽 테이블을 동적으로 파티셔닝 하라는 뜻이다.

 

 

         ------------------------------->

  조인:  outer table   :   inner table

             (dept)           (emp)

           파티션x          파티션x

          big table        big table

 

 힌트: pq_distribute(emp.hash.hash)

 

 

 

테이블을 둘 다 파티셔닝 하고 full partition wise 조인해라

*근데 좋지 못함. 메모리 잡아먹으면서 내리는 명령이라서.

 

 

select /*+ leading (d e) use_hash(e)

            full(d)     full(e)

             parellel(e 2)  parallel(d 2)

             pq_distribute(e.hash.hash) */

 e.ename, d.loc, e.deptno

 from emp_partition e, dept_partition d

where e.deptno = d.deptno;

 

 

 

 

문제97. 우리반테이블(emp2)과 telecom_price 테이블을 조인해서

 이름, 나이, 텔레콤, month_price 를 출력하는데

 파티션 와이즈 조인이 되게 하시오

 

**지금 12c 니까 dblink 로 telecom_price 갖고 와야함

 

create table telecom_price

 as

 select * from telecom_price@link_11g;

 

(파티셔닝이 안 걸려있다는 전제니까

 pq_distributes(e.hash.hash) )

 

select /*+ leading( t e) use_hash(e) full(t) full(e)

                 parallel(t 2)  parallel(e 2)

                 pq_distribute(e.hash.hash) */

         e.ename, e.age, e.telecom, t.month_price

  from emp2 e, telecom_price t

where e.telecom = t.telecom_name;

 

 

 

 

░ 목차

 1. 테이블 파티셔닝

 2. 파티션 pruning

 3. 인덱스 파티셔닝

 

░ 3. 인덱스 파티셔닝

 인덱스의 크기가 너무 커서 인덱스를 파티션해서 성능을 높이겠다.

 

*인덱스 파티션의 종류

 

1. 로컬 파티션 인덱스

파티션 테이블의 파티션에 각각 로컬 인덱스로 구성된 구조의 인덱스

**각 서랍장마다 인덱스가 별개로 있는 것.

***가장 많이 사용함. 단점은 저장공간을 많이 차지함.

① 각 인덱스 파티션이 테이블 파티션과 1:1 대응 관계를 가지며, 테이블 파티션 속성을 그대로 상속받는다.

② 파티션 키를 사용자가 따로 정의하지 않아도 오라클이 자동으로 관리

③ 테이블 파티션과 1:1 관계가 되도록 사용자가 수동으로 인덱스 파티션을 구성하더라도 이를 로컬 파티션 인덱스라고 부르지 않는다.

④ 테이블이 결합 파티셔닝 돼 있다면 인덱스도 같은 단위로 파티셔닝

⑤ 테이블 파티션 구성에 변경이 생기더라도 인덱스를 재생성할 필요가 없다.

 

 

 

 

 

2. 비 파티션 인덱스 (=글로벌 비 파티션 인덱스)

테이블은 파티션 되어져 있는데 인덱스는 파티셔닝 되지 않은 인덱스

**저장공간을 덜 쓴다.

 

 

① 파티셔닝하지 않은 인덱스

② 1:M 관계 [1:M = 인덱스:여러테이블]

③ 하나의 인덱스 세그먼트가 여러 테이블 파티션 세그먼트와 관계

④기준 테이블의 파티션 구성에 변경(drop, exchange, split등)이

   생길 때마다 인덱스가 unusable 상태로 바뀌고 그때마다 

   인덱스를 재생성REBUILD해야 한다.

   [ '글로벌 비파티션 인덱스'라 부르기도 한다. ]

 

 

 

 

3. 글로벌 파티션 인덱스

테이블도 파티션 되어져 있고 인덱스도 파티션 되어있는데

 테이블 파티션과 인덱스 파티션과의 관계가 서로 독립적 구조로

되어있는 인덱스

① 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝하는 것

② 테이블은 파티셔닝돼 있지 않을 수도 있다.

③ 기준 테이블의 파티션 구성에 변경(drop, exchange, split등)이 생길 때마다 인덱스가 unusable 상태로 바뀌고 그때마다 

인덱스를 재생성해야 한다. (비파티션 인덱스일 때도 동일)

④ 9i(오라클버젼)부터 update global indexes 옵션을 주면

   파티션 DDL 작업에 의해 영향을 받는 인덱스 레코드를

   자동으로 갱신해 준다. (unusable 상태로 빠지지 않는다)

 

 

 

 

테이블 파티션과의 관계

① "오라클이 자동으로 관리해 주는 1:1 관계"가 아닌

   파티션 인덱스는 모두 글로벌 파티션 인덱스

② 하나의 인덱스 파티션이 여러 테이블 파티션과 관계를 갖고,

   반대로 하나의 테이블 파티션이 여러 인덱스 파티션과 관계를 갖는다.

③ 로컬 파티션 인덱스처럼 테이블과 1:1 관계가 되도록 수동으로 구성하더라도      

   글로벌 파티션과 마찬가지로 기준 테이블 구성에 변경이 

    발생할 때마다 인덱스를 재생성해야 한다. 

   (그럼 셋 다 인덱스 변경 될 때 마다 재생성 해야 하네?)

④ 인덱스를 테이블 파티션과

 다른 키 컬럼으로

 글로벌 파티셔닝 (테이블은 주문일자, 인덱스는 배송일자) 할 수도 있는데,

  이때는 테이블 파티션과 인덱스 파티션 간에는 항상 M:M 관계가 형성.

 

 

문제98. emp_partition 테이블의 deptno 에

로컬 파티션 인덱스를 생성하시오

 

create index emp_partition_local

on emp_partition(deptno) local;  <<이렇게 뒤에 local만 붙여주면 된다.

 

select bytes, blocks

 from user_segments

 where segment_name = 'EMP_PARTITION_LOCAL';

 

 

 

 

*비파티션 인덱스

 drop index emp_partition_local;

 

create index emp_partition_index

 on emp_partition(deptno);  <<<뒤에 local만 빼면 된다.

  (local 빼면 비파티션 인덱스)

 

 

░ 로컬 파티션 인덱스와 비파티션 인덱스의 장단점

-로컬 파티션 인덱스 :  장점 : 관리가 쉽다, 검색 성능이 좋다.

                             단점 : 공간을 많이 사용한다.

 

-비파티션 인덱스 : 장점 : 공간을 적게 사용한다.

                         단점 :  관리가 불편하다.

*어떤점이 관리가 불편한가?

 비파티션 인덱스에 관련된 파티션 테이블을 drop 했을 때

 비파티션 인덱스가 invalid 되어서 인덱스를 rebuild 해줘야한다.

 

alter table emp_partition

 drop partition p1;

 

select index_name, status

 from user_indexes

 where index_name = 'EMP_PARTITION_INDEX';

 

 

 

unusable 되면 못 쓴다.

비파티션 인덱스는

파티션과 인덱스가 따로라서

인덱스가 안날라가고 unusable 로 남아있다.

인덱스를 리빌드 해줘야 한다.

 

시신확인

select /*+ index(e emp_partition_index) */

           ename, sal

 from emp_partition e

 where deptno  = 10;

 

 

 

 

alter index emp_partition_index rebuild;

 

select index_name, status

 from user_indexes

 where index_name = 'EMP_PARTITION_INDEX';

 

select /*+ index(e emp_partition_index) */

           ename, sal

 from emp_partition e

 where deptno  = 20;

*****왜 20,.............?????????????????

 

 

 

 

░ 로컬 파티션 인덱스와 비파티션 인덱스의 장단점

-로컬 파티션 인덱스 :  장점 : 관리가 쉽다, 검색 성능이 좋다.

                             단점 : 공간을 많이 사용한다.

 

-비파티션 인덱스 : 장점 : 공간을 적게 사용한다.

                         단점 :  관리가 불편하다.

*어떤점이 불편한가?

 비파티션 인덱스에 관련된 파티션 테이블을 drop 했을 때

 비파티션 인덱스가 invalid 되어서 인덱스를 rebuild 해줘야한다.

 

*로컬 파티션 인덱스

 1. prefixed  파티션 인덱스: deptno + job(결합컬럼 인덱스)

                                        ↑

                                     파티션 키 컬럼

                            **파티션 키가 선두에 있음

 

drop index emp_partition_index;

 

create index emp_partition_index

 on emp_partition(deptno, job)  local;

                                         ↑

                            결합컬럼 인덱스

 

글로벌 파티션 인덱스는 Prefixed 파티션만 지원

 

 

 

 2. non prefixed 파티션 인덱스:  job +  deptno

                                                       ↑

                                                  파티션 키 컬럼

                             **파티션 키가 뒤에 있음

 

 drop index emp_partition_index;

 

create index emp_partition_index

 on emp_partition(job, deptno) local;

 

테이블은 deptno 를 기준으로 파티션 되어있는데

sql 에 where  절에 주로 job 을 검색한다고 하면

non prefixed 파티션 인덱스가 필요하다.