파티션 테이블
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 파티션 개요[편집]
- Big Transaction Table을 보다 효율적으로 관리하기 위해 Table을 작은 단위로 나눔으로써 데이터 액세스 작업의 성능 향상을 유도하고 데이터 관리를 보다 수월하게 하고자 하는 개념
2 파티션 테이블의 장점[편집]
- 개선된 가용성
- - 파티션은 독립적으로 관리 가능
- - Backup and Restore을 파티션별로 작업 가능
- - 같은 테이블에서 Unavailable한 파티션은 다른 파티션에 영향을 주지 않음.
- 관리의 용이성
- - 사용자가 지정한 값으로 파티션 가능.
- - 테이블스페이스간에 파티션 이동이 가능.
- - 파티션 레벨에서 SELECT, DELETE, UPDATE가 가능.
- 개선된 성능
- - 데이터를 액세스할 때 액세스하는 범위를 줄여서 성능 향상.
- - RAC(Real Application Clusters) 환경에서 인스턴스간 Block Connection을 감소.
3 파티션 테이블 사용시 주의점[편집]
- 관리적인 관점
- - 하나의 테이블을 세분화하여 관리하기 때문에 보다 세심한 관리가 요구된다.
- - 파티션을 잘못 구성 또는 관리하여 IU(Index Unusable)에 빠지는 것을 주의해야 한다.(12c에서는 기능개선됨. ONLINE UPDATE 기능 )
- 사용하는 관점
- - 파티션 키로 나누어져 있는 테이블에 파티션 키를 조건으로 주지 않아 전체 파티션을 액세스하지 않도록 주의해야 한다.
4 파티션 테이블의 특징[편집]
- 파티션 테이블은 파티션 키값에 의해 구성되며, 한 테이블 당 가능한 파티션은 이론적으로 65,535개 가능.
- 모든 파티션 테이블(또는 인덱스)는 같은 Logical Attribute를 가져야 함.
- ex) Columns, Data Types, Constraints ...
- 모든 파티션 테이블(또는 인덱스)는 다른 Physical Attribute를 가져야 함.
- ex) PCTFREE, PCTUSED, INITTRANS, MAXTRANS, TABLESPACE, STORAGE ...
- 파티션 테이블은 'KEY', 'VALUES LESS THAN Literal', 'Physical Attributes'로 구성.
- 'VALUES LESS THAN Literal' 절에서 'Literal' 값에는 SQL Function 지원.
- 복합컬럼(Composite Column) 구성은 16개까지 가능.
5 파티션 테이블 조회[편집]
SELECT *
FROM DBA_PART_TABLES
WHERE OWNER = ''
;5.1 파티션 테이블의 파티션 목록 조회[편집]
SELECT *
FROM DBA_TAB_PARTITIONS
;5.2 파티션 테이블별 건수 조회[편집]
set serverout on size 1000000
set verify off
declare
sql_stmt varchar2(1024);
row_count number;
cursor get_tab is
select table_name,partition_name
from dba_tab_partitions
where table_owner=upper('&&TABLE_OWNER') and table_name='&&TABLE_NAME';
begin
dbms_output.put_line('Checking Record Counts for table_name');
dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab loop
BEGIN
sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name
||' partition ( '||get_tab_rec.partition_name||' )';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name
||'('||get_tab_rec.partition_name||')',50)
||' '||TO_CHAR(row_count)||' rows.');
exception when others then
dbms_output.put_line
('Error counting rows for table '||get_tab_rec.table_name);
END;
end loop;
end;
/
set verify on5.3 파티션 키 컬럼 조회[편집]
set pagesize 200
set lines 200
set long 999
col owner for a12
col name for a20
col object_type for a20
col column_name for a32
SELECT owner, NAME, OBJECT_TYPE,column_name
FROM dba_part_key_columns
where owner='&OWNER'
ORDER BY owner, NAME;6 파티션 인덱스 조회[편집]
SELECT *
FROM DBA_PART_INDEXES
WHERE OWNER = ''
;6.1 파티션 인덱스의 파티션 목록 조회[편집]
SELECT *
FROM DBA_IND_PARTITIONS
;6.2 인덱스 파티션 조회[편집]
-- 미사용 인덱스 파티션 조회
SELECT A.INDEX_OWNER,A.INDEX_NAME,PARTITION_NAME
, 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD '||' PARTITION '||PARTITION_NAME||' PARALLEL 16;' REBUILD_CMD
FROM DBA_IND_PARTITIONS A
WHERE INDEX_OWNER = 'TBA'
AND STATUS <> 'USABLE'
-- AND INDEX_NAME LIKE 'PK%'
;-- PK 가 아닌 인덱스 파티션 조회
SELECT A.INDEX_OWNER,A.INDEX_NAME,PARTITION_NAME
, 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' UNUSABLE ;' UNUSABLE_CMD
FROM DBA_IND_PARTITIONS A
WHERE INDEX_OWNER = 'TBA'
-- AND STATUS <> 'USABLE'
-- AND INDEX_NAME LIKE 'PK%'
;6.3 파티션 관련 뷰 조회[편집]
SELECT TABLE_NAME
FROM DICT
WHERE TABLE_NAME LIKE '%PART%';7 파티션 테이블 종류[편집]
7.1 RANGE 파티션[편집]
- Column Value의 범위를 기준으로 하여 행을 분할하는 형태.
- Range Partition에서 Table은 단지 논리적인 구조이며 실제 데이터가 물리적으로 저장되는 곳은 Partition으로 나누어진 Tablespace에 저장 된다.
- PARTITION BY RANGE ( column_list ) : 기본 Table에서 어느 Column을 기준으로 분할할지를 정함
- VALUES LESS THAN ( value_list ) : 각 Partition이 어떤 값의 범위를 포함할지 Upper Bound를 정함.
- PARTITION P_DEFAULT VALUES LESS THAN(MAXVALUE) 구문으로 기본값을 항상 설정하도록 한다.
CREATE TABLE ORD_RANGE (
ORD_NO NUMbER(10) NOT NULL
, ORD_DT VARCHAR2(8) NOT NULL
, ORD_HMS VARCHAR2(6)
, BRANCH_CD VARCHAR2(10)
)
TABLESPACE TS_DBCAFE
PARTITION BY RANGE(ORD_DT) (
PARTITION P201901 VALUES LESS THAN('201902')
, PARTITION P201902 VALUES LESS THAN('201903')
, PARTITION P201903 VALUES LESS THAN('201904')
, PARTITION P201904 VALUES LESS THAN('201905')
, PARTITION P_DEFAULT VALUES LESS THAN(MAXVALUE)
);7.2 HASH 파티션[편집]
- Partitioning column의 Partitioning Key 값에 Hash 함수를 적용하여 Data를 분할하는 방식
- 데이터 이력관리의 목적보다 성능 향상이 목적.
- 고객ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준 컬럼으로 선정해야 효과적
- 파티션 키에 hash 함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장
- Hash Partition은 Range Partition에서 범위를 기반으로 나누었을 경우 특정범위의 분포도가 몰려서 각기 Size가 다르게 되는 것을 보완하여, 일정한 분포를 가진 파티션으로 나누고 균등한 데이터 분포도를 이용한 병렬처리로 성능을 보다 향상시킬 수 있다.
- Hash Partition에서 Table은 단지 논리적인 구조이며 실제 데이터가 물리적으로 저장되는 곳은 Partition으로 나누어진 Tablespace에 저장 된다.
- hash 알고리즘 특성상 equal 조건이나 IN-List 조건 검색 시에만 파티션 Pruning 작동
- 특정 파티션에 데이터가 몰리지 않도록 하려면 파티션의 개수를 2의 제곱으로 설정할 것을 권고
- hash 파티션 테이블 생성
CREATE TABLE ORD_HASH (
ORD_NO NUMbER(10) NOT NULL
, ORD_DT VARCHAR2(8) NOT NULL
, ORD_HMS VARCHAR2(6)
, BRANCH_CD VARCHAR2(10)
)
TABLESPACE TS_DBCAFE
-- [생략]
--PCTFREE 5
--PCTUSED 40
--INITRANS 11
--MAXTRANS 255
--STORAGE
--(
-- INITIAL 2048K
-- NEXT 1024K
-- PCTINCREASE 0
-- MINEXTENTS 1
-- MAXEXTENTS 121
--)
partition by hash (ORD_NO)
-- 추가로 서브파티션을 추가할 경우
--subpartition by range (sales_no) subpartition template
--(
-- subpartition S1 values less than (3),
-- subpartition S2 values less than (5),
-- subpartition S3 values less than (maxvalue)
--)
(
partition SALES_P1,
partition SALES_P2,
partition SALES_P3,
partition SALES_P4
);7.3 결합 (Composite) Partition[편집]
- 파티션의 칼럼을 Main-Sub 관계로 나누어 분할하는 방식.
- Composite Partition이 아닌 다른 파티션에서 물리적인 데이터가 저장되는 곳은 Table이 아닌 Partition Table에 저장이 되는 것처럼, Composite Partition에서는 Main Partition이 아닌 Sub Partition에 저장된다.
- Composite Partition의 조합 구성은 Oracle의 버전이 올라갈수록 조합하는 방식을 다양하게 지원한다.
- RANGE + HASH 파티션
CREATE TABLE ORD_RANGE_HASH (
ORD_NO NUMBER(10) NOT NULL
, ORD_DT VARCHAR2(8) NOT NULL
, ORD_HMS VARCHAR2(6)
, BRANCH_CD VARCHAR2(10)
)
TABLESPACE TS_DBCAFE
-- [생략]
--PCTFREE 5
--PCTUSED 40
--INITRANS 11
--MAXTRANS 255
--STORAGE
--(
-- INITIAL 2048K
-- NEXT 1024K
-- PCTINCREASE 0
-- MINEXTENTS 1
-- MAXEXTENTS 121
--)
PARTITION BY RANGE(ORD_DT)
SUBPARTITION BY HASH (ORD_NO)
SUBPARTITION TEMPLATE
(
SUBPARTITION S1
, SUBPARTITION S2
, SUBPARTITION S3
)
(
PARTITION P201901 VALUES LESS THAN('201902')
, PARTITION P201902 VALUES LESS THAN('201903')
, PARTITION P201903 VALUES LESS THAN('201904')
, PARTITION P201904 VALUES LESS THAN('201905')
, PARTITION P_DEFAULT VALUES LESS THAN(MAXVALUE)
);7.4 LIST Partition[편집]
- Partitioning Column의 특정 값으로 분할하는 방식
- 데이터 분포도가 낮지 않고, 균등하게 분포되어 있을때 유용.
- Composite Partition에서 'Range-List'일 경우 그 효율이 더욱 높아진다.
- 다른 파티션 방식처럼 다중 컬럼을 지원하지 않고 단일 컬럼만 가능.
- LIST Partition Table
CREATE TABLE ORD_LIST (
ORD_NO NUMBER(10) NOT NULL
, ORD_DT VARCHAR2(8) NOT NULL
, ORD_HMS VARCHAR2(6)
, BRANCH_CD VARCHAR2(10)
)
TABLESPACE TS_DBCAFE
-- [생략]
--PCTFREE 5
--PCTUSED 40
--INITRANS 11
--MAXTRANS 255
--STORAGE
--(
-- INITIAL 2048K
-- NEXT 1024K
-- PCTINCREASE 0
-- MINEXTENTS 1
-- MAXEXTENTS 121
--)
PARTITION BY LIST(BRANCH_CD)
-- SUBPARTITION BY HASH (ORD_NO)
-- SUBPARTITION TEMPLATE
-- (
-- SUBPARTITION S1
-- , SUBPARTITION S2
-- , SUBPARTITION S3
-- )
(
PARTITION RS VALUES('AAA') -- TABLESPACE TS_CYKIM_01,
, PARTITION RM VALUES('BBB') -- TABLESPACE TS_CYKIM_02,
, PARTITION RN VALUES('CCC') -- TABLESPACE TS_CYKIM_03,
, PARTITION DF VALUES ( DEFAULT )
);7.5 REFERENCE Partition[편집]
- Reference Key로 지정된 경우 부모 테이블의 컬럼이 존재하지 않아도 부모의 Partition Key로 분할하는 방식
- 구문 Sample
CREATE TABLE CUSTOMERS
(
CUST_ID NUMBER PRIMARY KEY,
CUST_NAME VARCHAR2(200),
RATING VARCHAR2(1) NOT NULL
)
PARTITION BY LIST(RATING)
(
PARTITION PA VALUES('A'),
PARTITION PB VALUES('B')
);
-- Detail Table
CREATE TABLE SALES
(
SALES_ID NUMBER PRIMARY KEY,
CUST_ID NUMBER NOT NULL,
SALES_AMT NUMBER,
CONSTRAINT FK_SALES_01 FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS
)
PARTITION BY REFERENCE (FK_SALES_01);- 제약조건
1) Foreign Key 제약조건이 설정되어 있어야 한다.
2) 상속받는 테이블의 Key값이 NOT NULL 이어야 한다.
- 테스트
-- Normal
SELECT *
FROM SALE_TMP A,
CUSTOMERS B
WHERE A.CUST_ID = B.CUST_ID
AND B.RATING = 'A';
Rows Row Source Operation
------- ------------------------------------
0 STATEMENT
28 HASH JOIN
28 PARTITION LIST SINGLE PARTITION: 1
28 TABLE ACCESS FULL CUSTOMERS PARTITION: 1
56 TABLE ACCESS FULL SALE_TMP
-- Reference Partition
SELECT *
FROM SALES A,
CUSTOMERS B
WHERE A.CUST_ID = B.CUST_ID
AND B.RATING = 'A';
Rows Row Source Operation
------- -------------------------------------
0 STATEMENT
28 PARTITION LIST SINGLE PARTITION: 1
28 HASH JOIN
28 TABLE ACCESS FULL CUSTOMERS PARTITION: 1
28 TABLE ACCESS FULL SALES PARTITION: 17.6 INTERVAL Partition[편집]
- 'Interval Partition'은 각 파티션 방식을 미리 ㅈ정하고 오라클이 자동으로 파티션 생성 함.
- - 'Range Partition'은 범위를 지정하고 관리할때 미리 Range를 만들어주어야 하고 생성 이후 분할 또는 병합을 할 때 추가적인 작업을 해야함.
- 파티션키 컬럼이 Number 와 Date형만 가능
- 파티션키 컬럼이 Number Type이고 Interval이 10 경우 파티션의 최대값 20 이상의 데이터가 Insert 될 경우 해당 데이터가 포함되는 10만큼의 범위를 가지는 새로운 파티션이 자동 생성됨.
- Date Type의 경우 numtoyminterval / numtodsinterval 함수를 통하여, date type의 데이터의 partition 범위를 지정함.
numtoyminterval(n, 'MONTH|YEAR') , numtodsinterval(n, 'day')
7.6.1 인터벌 파티션 예제[편집]
- 샘플 테이블 생성
CREATE TABLE sales (
sale_id NUMBER(10),
sale_date DATE,
amount NUMBER,
customer_id NUMBER
)
PARTITION BY RANGE (sale_id)
INTERVAL (100)
(
PARTITION p0 VALUES LESS THAN (2024072301)
);-데이타 삽입
INSERT INTO sales (sale_id, sale_date, amount, customer_id) VALUES (2024072301, TO_DATE('2024-07-23', 'YYYY-MM-DD'), 100, 101);
INSERT INTO sales (sale_id, sale_date, amount, customer_id) VALUES (2024072310, TO_DATE('2024-07-23', 'YYYY-MM-DD'), 200, 102);
INSERT INTO sales (sale_id, sale_date, amount, customer_id) VALUES (2024072320, TO_DATE('2024-07-23', 'YYYY-MM-DD'), 300, 103);
INSERT INTO sales (sale_id, sale_date, amount, customer_id) VALUES (2024072401, TO_DATE('2024-07-24', 'YYYY-MM-DD'), 400, 104);
INSERT INTO sales (sale_id, sale_date, amount, customer_id) VALUES (2024072402, TO_DATE('2024-07-24', 'YYYY-MM-DD'), 500, 105);
INSERT INTO sales (sale_id, sale_date, amount, customer_id) VALUES (2024072501, TO_DATE('2024-07-25', 'YYYY-MM-DD'), 600, 106);- 파티션 정보
SELECT
partition_name,
high_value,
num_rows
FROM
user_tab_partitions
WHERE
table_name = 'SALES';SELECT
partition_name,
subpartition_name,
high_value,
num_rows
FROM
user_tab_partitions
WHERE
table_name = 'SALES';
-- 파티션별 데이터 확인
SELECT
*
FROM
sales
PARTITION (p0);
SELECT
*
FROM
sales
PARTITION (SYS_P1);
SELECT
*
FROM
sales
PARTITION (SYS_P2);- 조회 결과
PARTITION_NAME | HIGH_VALUE | NUM_ROWS
----------------|----------------|---------
P0 | 2024072301 | 0
SYS_P1 | 2024072401 | 3
SYS_P2 | 2024072501 | 2
SYS_P3 | MAXVALUE | 1- - HIGH_VALUE는 해당 파티션의 상한값을 의미
- - NUM_ROWS는 해당 파티션에 저장된 행의 수
7.6.2 Range 파티션과 Interval 파티션 예제[편집]
- RANGE 파티션인 경우 에러 발생
- 예제 Sample
-- 1. Range Partition 생성
CREATE TABLE SALES6
(
SALES_ID NUMBER,
SALES_DT DATE
)
PARTITION BY RANGE(SALES_DT)
(
PARTITION P0701 VALUES LESS THAN (TO_DATE('20070201', 'YYYYMMDD')),
PARTITION P0702 VALUES LESS THAN (TO_DATE('20070301', 'YYYYMMDD'))
);
-- 2. Partition Key 값의 범위에 없는 값으로 Insert
INSERT INTO SALES6 VALUES(1, TO_DATE('20070401', 'YYYYMMDD'));- 파티션키 범위값을 넘으면 ERROR 발생함
-- Error
ORA-14400: inserted partition key does not map to any PARTITON
- 인터벌 파티션으로 하면 ERROR가 발생되지 않음.
-- 3. Intrval Partition 생성
CREATE TABLE SALES6
(
SALES_ID NUMBER,
SALES_DT DATE
)
PARTITION BY RANGE(SALES_DT) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION P0701 VALUES LESS THAN(TO_DATE('20080201', 'YYYYMMDD'))
);
-- 4. Partition Key 값의 범위에 없는 값으로 Insert
INSERT INTO SALES6 VALUES(1, TO_DATE('20070601', 'YYYYMMDD'));
-- No Error
1 row created.
- 파티션을 특정 테이블 스페이스에 저장하고 싶다면 STORE IN 구문으로 가능하다.
-> INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) STORE IN (TS1, TS2, TS3)
- 애플리케이션 개발자가 특정 파티션에 접하고 싶다면 다음의 구문으로 가능하다.
-> SELECT * FROM SALES6 PARTITION FOR(TO_DATE('20080501', 'YYYYMMDD'));7.7 STORE IN 구문[편집]
- 지정된 테이블스페이스에 돌아가면서 저장됨
- TS1, TS2, TS3 테이블스페이스에 round-robin방식으로 저장
interval (numtoyminterval(1,’MONTH’))
store in (TS1,TS2,TS3)
7.8 NUMTOYMINTERVAL(n,interval_unit) 함수[편집]
- NUMTOYMINTERVAL 은 number n을 INTERVAL YEAR TO MONTH 문자로 변환.
- 첫번째 파라미터 n값은 NUMBER or NUMBER 값 표현식 가능
- 2번째 파라미터 interval_unit값은 CHAR, VARCHAR2, NCHAR, or NVARCHAR2 데이터 형 가능
- interval_unit값은 'YEAR','MONTH' 에서 선택
- interval_unit은 대소문자 구분함.
- 이외 값은 무시됨
- 기본적은 9를 리턴함.
7.9 예시[편집]
- SUM 분석 함수에서 NUMTOYMINTERVAL을 사용하여 각 직원에 대해 입사일로부터 지난 1년 동안 고용된 직원의 총 급여를 계산
SELECT last_name, hire_date, salary
, SUM(salary) OVER (ORDER BY hire_date
RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal
FROM employees
ORDER BY last_name, hire_date;
LAST_NAME HIRE_DATE SALARY T_SAL
------------------------- --------- ---------- ----------
Abel 11-MAY-04 11000 90300
Ande 24-MAR-08 6400 112500
Atkinson 30-OCT-05 2800 177000
Austin 25-JUN-05 4800 134700
. . .
Walsh 24-APR-06 3100 186200
Weiss 18-JUL-04 8000 70900
Whalen 17-SEP-03 4400 54000
Zlotkey 29-JAN-08 10500 1190007.10 시스템(System) Partition[편집]
- 테이블 생성시 파티션 구간을 미리 설정하는 것이 아니라 임의로 나눈 파티션에 대해 사용자가 원하는 파티션에 데이터를 저장하는 방식.
- 이 방식은 사용자가 'System Partition'으로 되어 있는 테이블의 데이터를 DML하고자 할 때 직접 파티션을 지정하여 해야 함.
- 로컬 인덱스 생성 시, 인덱스도 동일한 방법으로 파티셔닝 된다.
- 예제 Sample
CREATE TABLE SALES3
(
SALES_ID NUMBER,
PRODUCT_CODE NUMBER,
STATE_CODE NUMBER
)
PARTITION BY SYSTEM
(
PARTITION P1 TABLESPACE USERS,
PARTITION P2 TABLESPACE USERS
);
- Insert 할 때는 반드시 파티션을 지정해 주어야 한다.
-- Insert할 때 테이블의 파티션을 지정하지 않을 경우
INSERT INTO SALES3 VALUES(1, 101, 1);
-- Error
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be sued for DMLs on tables partitioned by the System method
-- Insert을 할 때 테이블의 파티션을 지정한 경우
INSERT INTO SALES3 PARTITION(P1) VALUES (1, 101, 1);
-- No Error
1 row created.
- Delete, Update 할 때는 필수는 아니지만 파티션을 지정하지 않을 경우 모든 파티션을 찾아다녀야 하므로 이 경우에도 가급적 파티션을 지정해 주어야 한다.
DELETE SALES3 PARTITION(P1) WHERE STATUS_CODE = 1;7.11 Virtual Column Partition[편집]
- 파티션으로 나누고자 하는 칼럼이 테이블에서 가공되어 얻을 수 있는 칼럼일 경우 11g 이전에서는 새로운 칼럼을 추가하고 트리거를 이용하여 칼럼 값을 생성하는 방법을 사용하여 많은 오버헤드가 발생됨.
- 11g 부터 'Virtual Column Partition' 으로 실제로 저장되지 않는 가상 칼럼을 런타임에 계산하여 생성할 수 있다.
- 예제 Sample
-- Virtual Partition 생성
CREATE TABLE SALES
(
SALES_ID NUMBER,
CUST_ID NUMBER,
SALE_CATEGORY VARCHAR2(6)
GENERATED ALWAYS AS
(
CASE WHEN SALES_AMT <= 10000 THEN 'LOW'
WHEN SALES_AMT BETWEEN 10000 AND 100000 THEN CASE WHEN CUST_ID < 101 THEN 'LOW'
WHEN BETWEEN 101 AND 200 THEN 'MEDIUM'
ELSE 'LOW' END
WHEN SALES_AMT BETWEEN 100000 AND 1000000 THEN CASE WHEN CUST_ID < 1010 THEN 'MEDIUM'
WHEN BETWEEN 101 AND 200 THEN 'MEDIUM'
ELSE 'ULTRA' END
ELSE 'ULTRA' END
) VIRTUAL
PARTITION BY LIST(SALES_CATEGORY)
(
PARTITION P_LOW VALUES ('LOW'),
PARTITION P_MEDIUM VALUES ('MEDIUM'),
PARTITION P_HIGH VALUES ('HIGH'),
PARTITION P_ULTRA VALUES ('ULTRA')
);
-- Insert 테스트
INSERT INTO SALES(SALES_ID, CUST_ID, SALES_AMT) VALUES (1, 1, 100);
-- No Error
1 row created.
8 파티션을 사용할 때 알아야 할 사항들[편집]
8.1 일반 테이블을 파티션 테이블로 변경 하기[편집]
- 12cR2이상에서 일반테이블을 파티션 테이블로 변경
alter table NUMBER
modify PARTITION BY RANGE (CREATED)
( partition created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2015', 'DD/MM/YYYY')),
partition created_2105_p9 VALUES LESS THAN (TO_DATE('01/10/2015', 'DD/MM/YYYY')),
partition created_2105_p10 VALUES LESS THAN (TO_DATE('01/11/2015', 'DD/MM/YYYY')),
partition created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2015', 'DD/MM/YYYY')),
partition created_2105_p12 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
partition created_MX VALUES LESS THAN (MAXVALUE)
) ONLINE ;
- Export/Import 이용하여 변경 방법
-- 테이블 Export
exp user/password tables=number file=exp.dmp
-- 백업받은 테이블 제거
drop table numbers;
-- 파티션 테이블 생성
CREATE TABLE NUMBER(QTY NUMBER(3), NAME VARCHAR2(15))
PARTITION BY RANGE(QTY)
( PARTITION P1 VALUES LESS THAN (501),
PARTITION P2 VALUES LESS THAN (MAXVALUE));
--ignore=y를 사용하여 데이터를 Import한다.
imp user/password tables=number file=ex.dmp ignore=y
- Subquery를 이용한 변경 방법
-- 파티션 테이블을 생성한다.
CREATE TABLE PARTBL(QTY NUMBER(3), NAME VARCHAR2(15))
PARTITION BY RANGE(QTY)
( PARTITION P1 VALUES LESS THAN (501),
PARTITION P2 VALUES LESS THAN (MAXVALUE));
-- Subquery를 이용하여 파티션 테이블에 데이터를 입력한다.
INSERT INTO PARTBL(QTY, NAME) SELECT * FROM ORIGTBL;
- Partition Exchange 명령어를 하는 방법
- ALTER TABLE EXCHANGE PARTITION은 파티션 테이블을 일반 테이블로 변경함.
- 파티션 되어있지 않은 일반 테이블을 파티션 테이블로 변경시킬 때 사용.
-- 파티션 테이블 생성
CREATE TABLE P_EMP (SAL NUMBER(7,2))
PARTITION BY RANGE(SAL)
(PARTITION EMP_P1 VALUES LESS THAN (2000),
PARTITION EMP_P2 VALUES LESS THAN (4000));
-- 첫번째 파티션에 들어갈 데이터
CREATE TABLE DUMMY_Y
SELECT SAL
FROM EMP
WHERE SAL < 2000;
-- 두번째 파티션에 들어갈 데이터
CREATE TABLE DUMMY_Z
SELECT SAL
FROM EMP
WHERE SAL BETWEEN 2000 AND 3999;
ALTER TABLE P_EMP EXCHANGE PARTITION EMP_P1
WITH TABLE DUMMY_Y;
ALTER TABLE P_EMP EXCHANGE PARTITION EMP_P2
WITH TABLE DUMMY_Z;8.2 여러 파티션으로 분리된 테이블 중 일부의 파티션만 가진 테이블 생성하기[편집]
-- 데이타를 Export한 후 필요한 파티션으로 이루어진 테이블을 생성한다.
-- 데이터 생성
CREATE TABLE YEAR(COL1 DATE)
PARTITION BY RANGE (COL1)(
PARTITION OCTOBER VALUES LESS THAN ('01-NOV-1999') TABLESPACE OCTOBER,
PARTITION NOVEMBER VALUES LESS THAN ('01-DEC-1999') TABLESPACE NOVEMBER,
PARTITION DECEMBER VALUES LESS THAN (MAXVALUE) TABLESPACE DECEMBER
);
-- 데이터를 Import
IMP USERNAME/PASSWORD FILE=EXPDAT.DMP FROMUSER= TOUSER=
TABLES=(YEAR:OCTOBER, YEAR:NOVEMBER, YEAR:DECEMBER)8.3 파티션을 추가 하는 방법[편집]
-- 파티션 테이블 생성
CREATE TABLE PART_TBL (
IN_DATE CHAR(8) PRIMARY KEY,
EMPNO NUMBER,
ENAME VARCHAR2(20),
JOB VARCHAR2(20)
)
-- 파티셔닝
PARTITION BY RANGE (IN_DATE)
(
PARTITION PART_TBL_03 VALUE LESS THAN ('20000331') TABLESPACE PTS_03,
PARTITION PART_TBL_04 VALUE LESS THAN ('20000430') TABLESPACE PTS_04,
PARTITION PART_TBL_05 VALUE LESS THAN ('20000531') TABLESPACE PTS_05,
PARTITION PART_TBL_06 VALUE LESS THAN ('20000630') TABLESPACE PTS_06,
PARTITION PART_TBL_07 VALUE LESS THAN ('20000731') TABLESPACE PTS_07,
PARTITION PART_TBL_08 VALUE LESS THAN ('20000831') TABLESPACE PTS_08,
PARTITION PART_TBL_09 VALUE LESS THAN ('20000930') TABLESPACE PTS_09,
PARTITION PART_TBL_10 VALUE LESS THAN ('20001031') TABLESPACE PTS_10
);
-- 파티션 추가
ALTER TABLE PART_TBL
ADD PARTITION PART_TBL_11 VALUES LESS THAN ('20001130')
TABLESPACE PTS_11;
ALTER TABLE PART_TBL
ADD PARTITION PART_TBL_12 VALUES LESS THAN ('20001231')
TABLESPACE PTS_12;8.4 파티션 추가시 글로벌 인덱스 깨지지 않게 처리 (12c 이상)[편집]
- Syntax
ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME>
ADD PARTITION < PARTITION_NAME> VALUES LESS THAN < HIGH_VALUE>
TABLESPACE <TABLESPACE_NAME > < UPDATE GLOBAL INDEXES(optional)>;
-- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is presentALTER TABLE scott.DBCAFE
ADD PARTITION JAN VALUES LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY'))
TABLESPACE USERS
UPDATE GLOBAL INDEXES; -- 글로벌 인덱스를 index unusabel 되지 않게 처리-- In oracle 12c(new feature), we can add multiple partition in one command:
ALTER TABLE scott.DBCAFE
ADD PARTITION JAN VALUES LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY')) TABLESPACE USERS,
PARTITION FEB VALUES LESS THAN (TO_DATE('01-MAR-2016','DD-MON-YYYY')) TABLESPACE USERS,
PARTITION MAR VALUES LESS THAN (TO_DATE('01-APR-2016','DD-MON-YYYY')) TABLESPACE USERS,
UPDATE GLOBAL INDEXES;8.5 다중 파티션 추가/삭제 가능(12c 신기능)[편집]
-- 파티션 추가
ALTER TABLE PART_TBL ADD
PARTITION PART_TBL_11 VALUES LESS THAN ('20001130'),
PARTITION PART_TBL_12 VALUES LESS THAN ('20001231');
-- 파티션 삭제
ALTER TABLE PART_TBL DROP PARTITIONS
PART_TBL_11 , PART_TBL_12 , PART_TBL_13
;8.6 특정 파티션을 삭제 방법[편집]
- 특정 파티션 삭제 이후 삭제한 파티션의 값이 들어올 경우 그 다음 VALUES LESS THAN으로 편입됩니다.
ALTER TABLE PART_TBL DROP PARTITION PART_TBL_08;
- UPDATE GLOBAL INDEXES 명령으로 글로벌 인덱스 Index Unsable 되지 않게 하면서 삭제 (12c 이상)
ALTER TABLE scott.DBCAFE
DROP PARTITION JAN
UPDATE GLOBAL INDEXES;
- 여러개 파티션 동시삭제 (12c 이상)
ALTER TABLE CMADMIN.DBACLASS
DROP PARTITIONS JAN, FEB, MAR
UPDATE GLOBAL INDEXES;8.7 파티션을 나누는 방법[편집]
- 만약 3월만 들어가있는 파티션이 있을 경우, 여기서 1, 2월을 추가하려면 파티션에 ADD가 아닌 SPLIT을 해주어야 함.
-- 3월 파티션에서 2월과 3월을 SPLIT함.
ALTER TABLE PART_TBL
SPLIT PARTITION PART_TBL_03 AT ('20000229')
INTO (
PARTITION PART_TBL_02 TABLESPACE PTS_02,
PARTITION PART_TBL_03_1 TABLESPACE PTS_03
)
ONLINE ;
-- 2월 파티션에서 1월과 2월을 SPLIT함.
ALTER TABLE PART_TBL
SPLIT PARTITION PART_TBL_02 AT ('20000131')
INTO (
PARTITION PART_TBL_01 TABLESPACE PTS_01,
PARTITION PART_TBL_02_1 TABLESPACE PTS_02
)
ONLINE ;- 파티션 테이블 최고값 조회
select partition_name,read_only,high_value
from dba_tab_partitions
where table_name='PART_TBL';8.8 파티션 이름 변경[편집]
ALTER TABLE PART_TBL
RENAME PARTITION PART_TBL_02_1 TO PART_TBL_02;8.9 파티션의 테이블스페이스 이동[편집]
ALTER TABLE PART_TBL
MOVE PARTITION PART_TBL_10 TABLESPACE PTS_10_1
PARALLEL (DEGREE 4)
NOLOGGING ;
- 여러개 파티션 이동시
select 'ALTER TABLE '||TABLE_OWNER ||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE TS_USERS PARALLEL(DEGREE 4) NOLOGGING;'
from dba_tab_partitions
where table_name='&TABLE_NAME'
and table_owner='&SCHEMA_NAME';8.10 특정 파티션의 데이터만 TRUNCATE[편집]
- Partition의 Data를 Truncate하는 방법.
- Truncate는 Rollback이 불가능 하며 특정 Partition 전체를 삭제하므로 주의하여 사용.
ALTER TABLE PART_TBL
TRUNCATE PARTITION PART_TBL_02;- UPDATE GLOBAL INDEXES 옵션 추가
ALTER TABLE PART_TBL
TRUNCATE PARTITION JAN
UPDATE GLOBAL INDEXES;
--- multiple partitions
ALTER TABLE CMADMIN.DBACLASS
TRUNCATE PARTITIONS JAN, FEB, MAR
UPDATE GLOBAL INDEXES;8.11 파티션 테이블의 물리적인 속성 변경[편집]
- Partition Table은 특정 Partition의 속성만 변경할 수 있음.
- Table의 속성을 변경하여 전체 Partition에 대해 동일한 변경 해야함.
-- part_tbl의 모든 Partition의 Next 값이 변경
ALTER TABLE PART_TBL
STORAGE (NEXT 10M);
-- part_tbl_05 Partition의 Maxextents 값만 변경
ALTER TABLE APRT_TBL
MODIFY PARTITION PART_TBL_05
STORAGE (MAXEXTENTS 1000);- 12c R2 이상 에서 read only 기능
alter table APRT_TBL
modify partition P2105_10 read only;9 파티션 테이블의 인덱스 관리[편집]
- 파티션 테이블 관련 변경작업을 한 후에는 테이블에 걸려있는 Local, Global Index에 대해 반드시 Rebuild를 해주어야 합니다.
9.1 특정 파티션의 인덱스 Rebuild[편집]
ALTER INDEX IND_PART_TBL
REBUILD PARTITION I_PART_TBL_02;9.2 글로벌 인덱스 Rebuild[편집]
ALTER INDEX PART_TBL_PK
REBUILD;10 파티션 테이블 Backup & Recovery[편집]
10.1 Export[편집]
- Table-Level Export
- :기존의 Table Export처럼 Table 전체를 Export하는 경우.
- Emp Table(Partitioned 또는 Non-Partitioned) 전체를 Export
$ exp scott/tiger tables=emp file=emp.dmp- Partition-Level Export
- :이는 Partition Table의 일부 Partition만을 Export
- Full Mode의 Export시에는 사용 못하고, Table단위의 Export시에만 가능.
- ':'을 이용하여 Partition 이름을 지정
- Emp Table의 px Partition만을 Export
$ exp scott/tiger tables=emp:px file=emp_par.dmp
- 위 두가지 경우를 Level을 혼용하여 사용 가능.
- Sales Table은 전부를, Emp Table에서는 px Partition만을 Export.
$ exp scott/tiger tables=(emp:px, sales) file=both.dmp10.2 Import[편집]
- Table-Leve Import
- : Partitioned 또는 Non-Partitioned Table 전체 Import
- 모든 Import Mode (full, user, table)에서 사용.
- emp table(Partitioned 또는 non-Partitioned) 전체 Import.
$ imp scott/tiger file=wookpark.dmp tables=emp
10.3 Partition-Level Import[편집]
- - Export Dump File에서 (full, user, table 중 어떠한 Mode를 이용하여 Export했건간에) Partitioned Tabled의 일부 Partition만 Import.
- - Table Import Mode에서만 사용 가능.
- emp table의 px Partition만을 Import
- ':'을 이용하여 Partition을 지정
$ imp scott/tiger file=wookpark.dmp tables=emp:px- 테이블 단위의 Import시 우선 Table Creation 문장을 실행하고 Row Insert문을 수행하는 것 처럼 , Partition-level Import도 우선 Partitioned Table의 생성 문장을 수행하고 Row Insert문을 수행함.
- 따라서 ignore=y option등을 적절히 사용하면, Non-Partitioned Table과 Partitioned Table간의 Partitioned Table의 구조 변경등을 수행할 수 있음.
11 오라클 버전에 따른 파티션[편집]
- Oracle Ver 7.3
- Partition View를 처음으로 도입하였다.
- 당시 Partition View는 큰 테이블을 동일한 템플릿을 가진 여러 개의 다른 테이블로 분할하고 UNION ALL을 사용하여 View로 묶은 형태이다.
- 그러나 이 방식은 관리의 어려움, 활용성의 부족, 성능 등에 대한 이슈로 인하여 Oracle Ver 9i에서는 더이상 지원하지 않는다.
- Oracle Ver 8.0
- 컬럼 값의 Range 기반으로 된 Range Partition이 최초로 도입되었고, 비로소 Partition의 모습을 갖추었다.
- 각 파티션은 각기 다른 테이블 스페이스, Segment에 저장이 가능한다.
- Oracle Ver 8i
- 컬럼 값의 Hash 기반으로 된 hash partition과 Sub Partition을 할 수 있는 Composite Partition이 추가되었다.
- 이 당시 Composite Partition은 Range-Hash로만 구성 가능함.
- Oracle Ver 9i
- 리스트 값으로 파티션을 할 수 있는 List Partition이 추가되었다.
- Composite Partition에서는 Range-Hash 이외에 Range-List가 추가 지원되었다.
- Oracle Ver 10g
- 10T 파티션이 추가되었다.
- Oracle Ver 11g
- Composite Partition에서 확장된 Extended Composite Partition이 지원된다.
- -> Range-Range, List-Range, List-Hash, List-List
- Reference Partition 추가
- Interval Partition 추가
- System Partition 추가
- Virtual Column Partition 추가
- Composite Partition에서 확장된 Extended Composite Partition이 지원된다.
- Oracle Ver 12c
- Interval-Range , Interval-List , Interval-Hash 방식 추가
- Interval : 자동으로 다음 파티션이 늘어나는 range 파티션 (1월 파티션이 마지막 파티션일때 2월 데이터가 입력되면 2월 파티션이 자동 생성)