튜닝 - 파티션테이블

대용량 테이블을 위해 사용

어떻게 나누느냐가 중요하다.
1. RANGE Partition : 범위(구간별) 로 나눔. 가장많이 쓰는 기법
장점 : 관리가 쉽다.
단점 : 데이터가 균등하게 들어가지 않는다.
         예를 들어 기간별 판매량으로 테이블을 작성했을경우, 판매량이 일정하지 않으므로 값이 많은 테이블과 적은 테이블이 생기게 되어 테이블마
         다 데이터 량의 불균형이 발생한다. → 성능적으로 떨어짐.

 

2. Hash Partition : 해쉬함수가 사용되어 테이블마다 값이 균등하게 분포된다.
장점 : 데이터들이 테이블마다 균등하게 들어간다. → 성능이 좋다.
단점 : 관리하기가 어렵다.
         예를들어 필요한 값이 들어있는 테이블만 백업하고자 할때, 여러 파티션에 균등하게 분포되어 있기 때문에 뽑기가 어렵다.

3. List Partition : 리스트별로 파티션을 만들어 주어 관리한다. 크기 예상을 잘 해서 파티션을 만들어 줘야 하는데, 현실적으로 불가능하다.
ex) 판매량 예측해서 파티션을 나눴는데, 잘팔릴줄 알았던 품목이 안팔리고, 의외의 품목이 잘팔렸을경우..

---

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-파티션실습1 시작
[1] Range Partition
 
1. 현재 상황 확인
SQL> @dd
select tablespace_name, bytes/1024/1024 MB, file_name
from dba_data_files
 
TABLESPACE_NAME    MB FILE_NAME
--------------- ----- --------------------------------------------------
USERS              11 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            250 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           30 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            440 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE           100 /home/oracle/oradata/testdb/example01.dbf
 
 
2. 판매 저장용 테이블 스페이스 ts_q1,ts_q2,ts_q3,ts_q4 를 생성한다.
 
 
SQL>
create tablespace ts_q1
datafile '/home/oracle/oradata/testdb/ts_q101.dbf' size 1M;
 
Tablespace created.
 
SQL>
create tablespace ts_q2
datafile '/home/oracle/oradata/testdb/ts_q201.dbf' size 1M
 
Tablespace created.
 
SQL>
create tablespace ts_q3
datafile '/home/oracle/oradata/testdb/ts_q301.dbf' size 1M
 
Tablespace created.
 
SQL>
create tablespace ts_q4
datafile '/home/oracle/oradata/testdb/ts_q401.dbf' size 1M
 
Tablespace created.
 
SQL>
create tablespace ts_q5
datafile '/home/oracle/oradata/testdb/ts_q501.dbf' size 1M
 
Tablespace created.
 
 
SQL> @dd
 
TABLESPACE_NAME    MB FILE_NAME
--------------- ----- --------------------------------------------------
USERS              11 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            250 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           30 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            440 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE           100 /home/oracle/oradata/testdb/example01.dbf
TS_Q1               1 /home/oracle/oradata/testdb/ts_q101.dbf
TS_Q2               1 /home/oracle/oradata/testdb/ts_q201.dbf
TS_Q3               1 /home/oracle/oradata/testdb/ts_q301.dbf
TS_Q4               1 /home/oracle/oradata/testdb/ts_q401.dbf
TS_Q5               1 /home/oracle/oradata/testdb/ts_q501.dbf
 
10 rows selected.
 
 
3. 파티션 테이블 panmae 생성
SQL>
 
create table panmae
(pdate varchar2(8),
pcode number(3),
pqty number(5))
partition by range(pdate)
(partition q1 values less than ('20100401') tablespace ts_q1,
partition q2 values less than ('20100701') tablespace ts_q2,
partition q3 values less than ('20101001') tablespace ts_q3,
partition q4 values less than ('20110101') tablespace ts_q4,
partition q5 values less than (maxvalue) tablespace ts_q5)
/
 
 
예제 데이터 입력 후 조회
SQL> insert into panmae
  2  values ('20101001',100,20);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> insert into panmae values ('20100405',200,50);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> insert into panmae values ('20100922',300,30);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
 
4. 각 파티션별로 어떤 텡블에 어떤 데이터가 들어가 있는지 조회하기
SQL> select * from panmae partition (q2);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100405                200         50
 
SQL> select * from panmae partition (q3);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100922                300         30
 
SQL> select * from panmae partition (q4);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20101001                100         20
 
SQL>  select * from panmae partition (q5);
 
no rows selected
 
SQL> select * from panmae partition (q1);
 
no rows selected
 
 
 
5. 특정 파티션에만 데이터 입력하는 방법
 
SQL>
insert into panmae partition (q1)
values ('20100406',200,70);
 
insert into panmae partition (q1)
            *
ERROR at line 1:
ORA-14401: inserted partition key is outside specified partition
▶ 지정해준 범위를 벗어나는 값이 들어올 경우 에러가 발생한다.
 
 
SQL>
insert into panmae partition (q1)
values ('20100101',200,70);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from panmae partition (q1);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100101                200         70
 
▶ 지정해준 범위 내의 값을 입력할 경우 제대로 처리된다.
 
 
 
6. 특정 테이블에 속해있는 파티션 찾기
 
SQL> set line 200
SQL> col table_name for a10
SQL> col partition_name for a10
SQL> col high_value for a10
SQL> /
 
select table_name, partition_name,tablespace_name,high_value
from dba_tab_partitions
where table_name='PANMAE'
 
 
TABLE_NAME PARTITION_ TABLESPACE_NAME HIGH_VALUE
---------- ---------- --------------- ----------
PANMAE     Q1         TS_Q1           '20100401'
PANMAE     Q2         TS_Q2           '20100701'
PANMAE     Q3         TS_Q3           '20101001'
PANMAE     Q4         TS_Q4           '20110101'
PANMAE     Q5         TS_Q5            MAXVALUE
 
 
 
 
7. update 수행하여 데이터가 파티션 이동 되는지 확인하기
 
 
SQL> select * from panmae;
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100101                200         70
20100405                200         50
20100922                300         30
20101001                100         20  ← 업데이트해보기
 
 
 
SQL> update panmae
  2  set pdate='20100805'
  3  where pcode=100;
where pcode=100
      *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partiton change
 
 
SQL> alter table panmae enable row movement;
 
Table altered.
 
 
SQL>
update panmae set pdate='20100805' where pcode=100;
 
 
1 row updated.
 
 
SQL>
update panmae
set pdate='20100805'
where pcode=100;
 
1 row updated.
 
SQL> select * from panmae partition(q3);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100922                300         30
20100805                100         20
▶ commit 하지 않아도 데이터가 이동된다.
 
SQL> rollback;
 
Rollback complete.
 
SQL>  select * from panmae partition(q3);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100922                300         30
▶ rollback하면 원래대로 돌아간다.
 
SQL> update panmae set pdate='20100805' where pcode=100;
 
1 row updated.
 
SQL>  select * from panmae partition(q3);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100922                300         30
20100805                100         20
 
SQL> commit;
 
Commit complete.
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-파티션실습1 끝

---

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-파티션실습2 시작
[2] Range partition 추가하기
 
1. 월별 데이터를 저장할 테이블 스페이스 12개 생성
 
SQL> create tablespace ts_mon_01
  2  datafile '/home/oracle/oradata/testdb/ts_mon_01.dbf' size 1M;
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace ts_mon_02
  2* datafile '/home/oracle/oradata/testdb/ts_mon_02.dbf' size 1M
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace ts_mon_03
  2* datafile '/home/oracle/oradata/testdb/ts_mon_03.dbf' size 1M
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace ts_mon_04
  2* datafile '/home/oracle/oradata/testdb/ts_mon_04.dbf' size 1M
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace ts_mon_05
  2* datafile '/home/oracle/oradata/testdb/ts_mon_05.dbf' size 1M
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace ts_mon_06
  2* datafile '/home/oracle/oradata/testdb/ts_mon_06.dbf' size 1M
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace ts_mon_07
  2* datafile '/home/oracle/oradata/testdb/ts_mon_07.dbf' size 1M
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace ts_mon_08
  2* datafile '/home/oracle/oradata/testdb/ts_mon_08.dbf' size 1M
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace ts_mon_09
  2* datafile '/home/oracle/oradata/testdb/ts_mon_09.dbf' size 1M
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace ts_mon_10
  2* datafile '/home/oracle/oradata/testdb/ts_mon_10.dbf' size 1M
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace ts_mon_11
  2* datafile '/home/oracle/oradata/testdb/ts_mon_11.dbf' size 1M
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace ts_mon_12
  2* datafile '/home/oracle/oradata/testdb/ts_mon_12.dbf' size 1M
SQL> /
 
Tablespace created.
 
 
2. 현재 3개월 단위로 나누어져 있는 파티션 생성
q1,q2,q3,q4
 
SQL> create tablespace q1
  2  datafile '/home/oracle/oradata/testdb/q01.dbf' size 1M;
 
Tablespace created.
 
SQL> create tablespace q2
  2  datafile '/home/oracle/oradata/testdb/q02.dbf' size 1M;
 
Tablespace created.
 
SQL> create tablespace q3
  2  datafile '/home/oracle/oradata/testdb/q03.dbf' size 1M;
 
Tablespace created.
 
SQL> create tablespace q4
  2  datafile '/home/oracle/oradata/testdb/q04.dbf' size 1M;
 
Tablespace created.
 
SQL> create table panmae2
  2  (pdate varchar2(8), pcode number(3), pqty number(5))
  3  partition by range(pdate)
  4  (partition q1 values less than ('20100401') tablespace q1,
  5  partition q2 values less than ('20100701') talbespace q2,
  6  partition q3 values less than ('20101001') tablespace q3,
  7
SQL>
SQL>
SQL> create table panmae2
  2  (pdate varchar2(8), pcode number(3), pqty number(5))
  3  partition by range(pdate)
  4  (partition q1 values less than ('20100401') tablespace q1,
  5  partition q2 values less than ('20100701') tablespace q2,
  6  partition q3 values less than ('20101001') tablespace q3,
  7  partition q4 values less than ('20110101') tablespace q4);
 
Table created.
 
SQL> insert into panmae2 values ('20100101',100,10);
 
1 row created.
 
SQL> insert into panmae2 values ('20100201',200,20);
 
1 row created.
 
SQL> insert into panmae2 values ('20100301',300,30);
 
1 row created.
 
SQL> insert into panmae2 values ('20100401',400,40);
 
1 row created.
 
SQL> insert into panmae2 values ('20100501',500,50);
 
1 row created.
 
SQL> insert into panmae2 values ('20100601',600,60);
 
1 row created.
 
SQL> insert into panmae2 values ('20100701',700,70);
 
1 row created.
 
SQL> insert into panmae2 values ('20100801',800,80);
 
1 row created.
 
SQL> insert into panmae2 values ('20100901',900,90);
 
1 row created.
 
SQL> insert into panmae2 values ('20101001',110,11);
 
1 row created.
 
SQL> insert into panmae2 values ('20101101',111,111);
 
1 row created.
 
SQL> insert into panmae2 values ('20101201',120,12);
 
1 row created.
 
 
3. 각 파티션 별 데이터 조회
 
SQL> select * from panmae2 partition(q1);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100101                100         10
20100201                200         20
20100301                300         30
 
SQL> select * from panmae2 partition(q2);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100401                400         40
20100501                500         50
20100601                600         60
 
SQL> select * from panmae2 partition(q3);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100701                700         70
20100801                800         80
20100901                900         90
 
SQL> select * from panmae2 partition(q4);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20101001                110         11
20101101                111        111
20101201                120         12
 
 

4. 각 월별로 파티션 분리하기 (split partition 옵션 사용)
  
SQL>
alter table panmae2 split partition q1
at ('20100201')  ← 이 값을 기준으로 분리한다.
into (partition d01 tablespace ts_mon_01, partition d02 tablespace ts_mon_02)
 
Table altered.
 
 
SQL> select * from panmae2 partition(q1);
select * from panmae2 partition(q1)
                                *
ERROR at line 1:
ORA-02149: Specified partition does not exist
▶ 값이 나오지 않는다. d01,d02에 나뉘어 들어갔음.
 
SQL> select * from panmae2 partition (d01);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100101                100         10
 
SQL> select * from panmae2 partition (d02);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100201                200         20
20100301                300         30
 
 
SQL> alter table panmae2 split partition d02
  2  at ('20100301')
  3  into (partition d02 tablespace ts_mon_02, partition d03 tablespace ts_mon_03);
 
Table altered.
 
SQL> select * from panmae2 partition (d01);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100101                100         10
 
SQL> select * from panmae2 partition (d02);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100201                200         20
 
SQL> select * from panmae2 partition (d03);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100301                300         30
 
 
 
5. 새로운 파티션 추가
 
SQL> create tablespace ts_mon_201101
  2  datafile '/home/oracle/oradata/testdb/201101_01.dbf' size 1M;
 
Tablespace created.
 
SQL> alter table panmae2 add partition p201101 values less than ('20110201')
  2  tablespace ts_mon_201101;
 
Table altered.
 
 
 
SQL> col table_name for a10
SQL> col PARTITION_NAME for a10
SQL> col HIGH_VALUE for a15
SQL> col TABLESPACE_NAME for a40
 
SQL>
select table_name, partition_name, high_value, tablespace_name
from dba_tab_partitions
where table_name='PANMAE2'
order by 2;
 
 
TABLE_NAME PARTITION_ HIGH_VALUE      TABLESPACE_NAME
---------- ---------- --------------- ----------------------------------------
PANMAE2    D01        '20100201'      TS_MON_01
PANMAE2    D02        '20100301'      TS_MON_02
PANMAE2    D03        '20100401'      TS_MON_03
PANMAE2    P201101    '20110201'      TS_MON_201101
PANMAE2    Q2         '20100701'      Q2
PANMAE2    Q3         '20101001'      Q3
PANMAE2    Q4         '20110101'      Q4
 
7 rows selected.
 
 
 
6. 파티션 삭제하기
 
SQL> insert into panmae2 values ('20110101',100,20);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from panmae2 partition(p201101);
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20110101                100         20
 
 
SQL> alter table panmae2 drop partition p201101;
 
Table altered.
 
SQL> select * from panmae2 partition(p201101);
select * from panmae2 partition(p201101)
                                *
ERROR at line 1:
ORA-02149: Specified partition does not exist
 
 
 
SQL> select * from panmae2;
 
PDATE                 PCODE       PQTY
---------------- ---------- ----------
20100101                100         10
20100201                200         20
20100301                300         30
20100401                400         40
20100501                500         50
20100601                600         60
20100701                700         70
20100801                800         80
20100901                900         90
20101001                110         11
20101101                111        111
20101201                120         12
 
12 rows selected.
 
▶ 파티션이 삭제되면 안의 자료도 함께 삭제된다.
 
 
 
 
7. 파티션 이름 바꾸기
 
SQL> alter table panmae2 rename partition d01 to d1;
 
Table altered.
 
SQL> alter table panmae2 rename partition d02 to d2;
 
Table altered.
 
SQL>
select table_name, partition_name, high_value, tablespace_name
from dba_tab_partitions
where table_name='PANMAE2'
order by 2;
 
TABLE_NAME PARTITION_ HIGH_VALUE      TABLESPACE_NAME
---------- ---------- --------------- ----------------------------------------
PANMAE2    D03        '20100401'      TS_MON_03
PANMAE2    D1         '20100201'      TS_MON_01
PANMAE2    D2         '20100301'      TS_MON_02
PANMAE2    Q2         '20100701'      Q2
PANMAE2    Q3         '20101001'      Q3
PANMAE2    Q4         '20110101'      Q4
 
6 rows selected.
 
 
 
 

※ 참고 : RANGE Partition 관련 View
DBA_PART_KEY_COLUMNS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_PART_INDEXES
DBA_IND_PARTITIONS
DBA_PART_TABLES
DBA_TAB_PARTITIIONS
DBA_PART_LOBS
DBA_LOB_PARTITIONS

 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-파티션실습2 끝
 

 

---

 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-파티션실습3 시작
[3] HASH PARTITION
 
 
1. 테이블 스페이스 만들고, 해시파티션 만들어서 값 넣기
 
SQL> create tablespace hash_test01
  2  datafile '/home/oracle/oradata/testdb/hash_test01.dbf' size 1m;
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace hash_test02
  2* datafile '/home/oracle/oradata/testdb/hash_test02.dbf' size 1m
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace hash_test03
  2* datafile '/home/oracle/oradata/testdb/hash_test03.dbf' size 1m
SQL> /
 
Tablespace created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  create tablespace hash_test04
  2* datafile '/home/oracle/oradata/testdb/hash_test04.dbf' size 1m
SQL> /
 
Tablespace created.
 
SQL> create table panmae3
  2  (pdate varchar2(10), pcode number(3), pqty number(5))
  3  partition by hash(pdate) partitions 4
  4  store in (hash_test01,hash_test02,hash_test03,hash_test04);
 
Table created.
 
SQL> insert into panmae3 values ('20101010',100,30);
 
1 row created.
 
SQL> insert into panmae3 values ('20100102',100,40);
 
1 row created.
 
SQL> insert into panmae3 values ('20100401',200,20);
 
1 row created.
 
SQL> insert into panmae3 values ('20100701',300,30);
 
1 row created.
 
 
2. 데이터 분배 상황 보기
 
SQL>
select table_name, partition_name, high_value, tablespace_name
from dba_tab_partitions
where table_name='PANMAE3'
order by 2;
 
TABLE_NAME PARTITION_ HIGH_VALUE      TABLESPACE_NAME
---------- ---------- --------------- ----------------------------------------
PANMAE3    SYS_P21                    HASH_TEST01
PANMAE3    SYS_P22                    HASH_TEST02
PANMAE3    SYS_P23                    HASH_TEST03
PANMAE3    SYS_P24                    HASH_TEST04
 
 
SQL> select * from panmae3;
 
PDATE                     PCODE       PQTY
-------------------- ---------- ----------
20101010                    100         30
20100102                    100         40
20100401                    200         20
20100701                    300         30
 
 
SQL> select * from panmae3 partition(SYS_P21);
 
no rows selected
 
SQL> select * from panmae3 partition(SYS_P22);
 
no rows selected
 
SQL> select * from panmae3 partition(SYS_P23);
 
PDATE                     PCODE       PQTY
-------------------- ---------- ----------
20101010                    100         30
20100102                    100         40
20100401                    200         20
20100701                    300         30
 
SQL> select * from panmae3 partition(SYS_P24);
 
no rows selected
▶ SYS_P23 파티션에 모든 값이 들어갔다??????????
    이 방법은 기준 점 없이 오라클 서버가 랜덤하게 자르는 것이기 때문에 검색을 위해 분할하는 것과는 약간 거리감이 있다.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-파티션실습3 끝