Oracle Partition Table 정리

 

Version 7

Partitioning은 partition view를 통해 V7.3부터 가능하였다. 이 view는 동일한 구조를 가지는 table의 UNION ALL에 기본을 두었고 column과 type이 동일하고 같은 순서를 가져야 한다. 더욱이 각 table은 partition key에 해당하는 index를 가져야 한다. 조건을 만족하지 않는 경우에는 optimizer가 execution plan을 세울 때 실패하게 된다.

Partition View

table의 UNION ALL view로 생성된다

Create view partitioned_emp as

select * from emp1

union all

select * from emp2

union all

select * from emp3;

 

Parameter "PARTITION_VIEW_ENABLED" 를 "TRUE"로 설정하면 optimizer가 partition view 를 인식하게 된다.

단점

1. DDL, DML의 순서가 고려되지 않는다.
2. Export, import, loader와 같은 툴에서 고려되지 않는다.
3. Global index의 사용이 불가능하다.

 

Version 8

Partitioning은 Oracle V8 Enterprise Server Edition의 option으로 제공된다. VLDB환경에서 성능, DB관리등에서 유용하다. Historical data와 관계된 Database나 성능과 유용성에 고민하는 OLTP database는 이 option을 필요로 한다.

장점

1. Table이나 index 를 partition level에서 관리할 수 있고 partition들 간의 독립성은 동시에 많은 partition들을 유지보수 할 수 있게 한다.
2. Database의 사용중지 시간이 감소한다. recovery는 partition level에서 수행된다.
3. 여러 partition을 서로 다른 disk상에 펼쳐놓아 보다 나은 데이타의 분산을 이룰 수 있다.
4. 특히 data warehousing 나 decision making 시스템에서 query의 성능 향상이 있다. optimizer는 query에 기술된 하나 또는 그 이상의 partition과 관련된 제한된 양만을 선택할 수 있는 기능이 있다.

제약사항

1. Table과 index에서 사용 가능하지만 cluster에서는 불가
2. RULE-based optimizer는 table과 index의 partition을 고려하지 않는다.

 

Version 8i

Oracle V8i 에서는 V8에서 지원하던 Range partition의 기능향상과 Hash, Composite Partition기능이 추가 되었다. 또 LOB을 포함하는 table, IOT, Object table에 대한 partition도 가능하게 되었다.

 

Version 9i

Oracle V9i에서는 LIST partition이 추가되었다. List partition은 사용자가 partition에 들어갈 row를 명시적으로 제어할 수 있게 한다. 사용자는 각 partition의 Key를 위한 비 연속적인 값을 정의한다. 이 방법은 partition과 관련된 값의 범위를 사용하는 Range partition이나, Hash function을 사용하는 Hash Partition과는 다르다.

장점 : 자연스런 방법으로 Data를 비순서적이고 관계없는 집합으로 분류할 수 있다.

단점 : Partition Key는 오직 하나의 Column만 구성할 수 있다.

 

Partition의 종류

Range-type, Hash-type, Composite-type Partition 을 살펴본다.

 

Range-type partition

Table과 Index의 partition을 column의 범위에 기반을 두고 있다.

CREATE TABLE emp_range (

empno NUMBER(4) NOT NULL,

ename VARCHAR2(10),

sal NUMBER(7,2)

)

PARTITION BY RANGE(EMPNO)

(

partition emp_p1 VALUES LESS THAN (50),

partition emp_p2 VALUES LESS THAN (100),

partition emp_p3 VALUES LESS THAN (150),

partition emp_p4 VALUES LESS THAN (MAXVALUE)

);

각 partition에는 상위 값이 정의된다. 각 값의 저장위치는 partition key와의 비교에 의해 결정되며 상위 값은 포함되지 않는다.

'VALUE LESS THAN (value1, value2 ..., valueN)' 에서 사용 가능한 function은 TO_CHAR과 RPAD이다.

Null의 처리

Partition Key의 NULL값은 partition key로 사용될 수 있고 MAXVALUE를 제외한 어떠한 값보다 상위로 간주한다. NULL값을 사용하기 위해서는 상위 partition의 최상위 값을 MAXVALUE로 지정해야 하고 그렇지 않을 경우에는 ORA-14400 error가 발생한다. 즉, NULL은 MAXVALUE가 지정된 경우 상위 partition에 저장되고 그렇지 않은 경우 ORA-14400이 발생한다.

Partition정보를 확인 할 수 있는 View

DBA_PART_KEY_COLUMNS DBA_PART_TABLES DBA_TAB_PARTITIONS DBA_PART_COL_STATISTICS DBA_PART_HISTOGRAMS DBA_PART_INDEXES DBA_IND_PARTITIONS DBA_PART_LOBS DBA_LOB_PARTITIONS

partition된 Object의 Partition Key 정보 partition table의 partition 정보 partition table의 partition, storage, 통계 정보 table partition에 관한 column의 통계, 분포 정보 partition table의 실제 분포 정보 partition index의 partition 정보 partition index의 partition, storage, 통계 정보 LOB part의 정보와 LOB data part의 속성 LOB partition 의 partition, storage 정보

 

 

 

 

Hash-type partition

Oracle에 의해 제공되는 hashing function을 사용하여 보다 나은 data의 분산을 가능하게 한다.

CREATE TABLE emp_hpart (

empno NUMBER(4) NOT NULL,

ename VARCHAR2(10),

sal NUMBER(7,2)

)

STORAGE (INITIAL 50k NEXT 50k)

PARTITION BY HASH(sal)

(

PARTITION H1 TABLESPACE data01,

PARTITION H2 TABLESPACE data02,

PARTITION H3 TABLESPACE data03,

PARTITION H4 TABLESPACE data04

);

Hash type의 partition이 필요한 경우의 예

1. data의 분산을 위한 기준을 찾는 것이 불가능하다.
2. partition을 위한 data 양의 예상이 어렵다.
3. 각 partition간의 균형을 맞추기가 어렵다.

제약 사항

1. 이 partition 방법은 다른 partition상에 data의 균일한 분배를 위해 2의 제곱의 수가 할당될 수 있는 partition이 필요하다.
2. 각 partition을 위한 상세한 storage절은 기술할 수 없다. 이 절은 partition이 존재하는 tablespace로부터 상속된다.
3. Hash partition의 관리는 SPLIT, MERGE를 제외하면 Range와 유사하며 ADD, COALESCE 명령어는 partition의 add, drop시에 사용된다.

 

Composite-type partition

이 방법은 Range-type, Hash-type 두 가지를 혼합한 형태이다. 첫번째 level은 Range, 두 번째는 Hash를 사용한다. 얻을 수 있는 장점은 첫번째 level에서는 논리적인 data의 정렬, 두 번째는 partition간의 균형이다.

CREATE TABLE emp_composite (

empno NUMBER(4) NOT NULL,

ename VARCHAR2(10),

sal NUMBER(6))

STORAGE (INITIAL 12k NEXT 12k)

PARTITION BY RANGE(empno)

SUBPARTITION BY HASH(sal) SUBPARTITIONS 4

STORE IN (DATA01, DATA02, DATA03, DATA04)

( PARTITION p1 VALUES LESS THAN (50),

PARTITION p2 VALUES LESS THAN (100),

PARTITION p3 VALUES LESS THAN (150),

PARTITION p4 VALUES LESS THAN (MAXVALUE));

CREATE TABLE sales_composite (

item INTEGER,

qty INTEGER,

store VARCHAR(30),

dept NUMBER,

sale_date DATE

)

PARTITION BY RANGE (sale_date)

SUBPARTITION BY HASH(item)

SUBPARTITIONS 8

STORE IN (tbs1, tbs2, tbs3, tbs4, tbs5, tbs6, tbs7, tbs8)

(

PARTITION q1_1997

VALUES LESS THAN (TO_DATE('01-apr-1997', 'dd-mon-yyyy')),

PARTITION q2_1997

VALUES LESS THAN (TO_DATE('01-jul-1997', 'dd-mon-yyyy')),

PARTITION q3_1997

VALUES LESS THAN (TO_DATE('01-oct-1997', 'dd-mon-yyyy'))

(

SUBPARTITION q3_1997_s1 TABLESPACE ts1,

SUBPARTITION q3_1997_s2 TABLESPACE ts3,

SUBPARTITION q3_1997_s3 TABLESPACE ts5,

SUBPARTITION q3_1997_s4 TABLESPACE ts7

),

PARTITION q4_1997

VALUES LESS THAN (TO_DATE('01-jan-1998', 'dd-mon-yyyy'))

SUBPARTITIONS 16

STORE IN (tbs1, tbs3, tbs5, tbs7, tbs8, tbs9, tbs10, tbs11),

PARTITION q1_1998

VALUES LESS THAN (TO_DATE('01-apr-1998', 'dd-mon-yyyy'))

);

Range type의 첫번째 level의 partition은 emp_composite table은 NUMBER type column (EMPNO)이고 sales_composite table은 DATE type column (SALE_DATE)이다. Hash type의 두번째 level은 emp_composite table은 NUMBER type column (SAL)이고 sales_composite table은 NUMBER type column (ITEM)이다. Partition 각각은 4개, 5개의 sub-partition은 16개, 44개로 나뉜다. Range level partition은 논리적인 정렬이고 Data는 물리적으로 Hash sub-partition에 저장된다.

Composite-type partition의 장점

1. Range type이 가지는 관리상의 이점
2. Data 저장의 효과적인 분산
3. Range level의 Global index와 Sub-partition (Hash)level의 Local index의 조합이 가능
4. Hash 방법은 parallel mode상에서 PDML을 가능하게 한다.

Partition정보를 확인 할 수 있는 View

DBA_TAB_SUBPARTITIONS DBA_SUBPART_KEY_COLUMNS DBA_SUBPART_COL_STATISTICS DBA_SUBPART_HISTOGRAMS DBA_IND_SUBPARTITIONS DBA_LOB_SUBPARTITIONS

Subpartition의 정보와 storage 속성 정보 Subpartition의 key column정보 Subpartition의 통계, 분포 정보 Subpartition의 실제 분포 정보 Subpartition index의 partition, storage, 통계 정보 LOB Subpartition 의 partition, storage 정보

 

select partition_name,

subpartition_name,

subpartition_position,

tablespace_name

from user_tab_subpartitions

where table_name = 'EMP_COMPOSITE';

 

PARTITION_NAME SUBPARTITION_NAME SUBPARTITION_POSITION TABLESPACE_NAME

-------------- ----------------- --------------------- ---------------

P1 SYS_SUBP27 1 DATA01

P1 SYS_SUBP28 2 DATA02

P1 SYS_SUBP29 3 DATA03

P1 SYS_SUBP30 4 DATA04

P2 SYS_SUBP31 1 DATA01

P2 SYS_SUBP32 2 DATA02

P2 SYS_SUBP33 3 DATA03

P2 SYS_SUBP34 4 DATA04

P3 SYS_SUBP35 1 DATA01

P3 SYS_SUBP36 2 DATA02

P3 SYS_SUBP37 3 DATA03

P3 SYS_SUBP38 4 DATA04

P4 SYS_SUBP39 1 DATA01

P4 SYS_SUBP40 2 DATA02

P4 SYS_SUBP41 3 DATA03

P4 SYS_SUBP42 4 DATA04

 

 

Partition의 구현

 

 

구현 방법

이론적으로 모든 조합이 가능하다.

- Partitioned table and non-partitioned index.

- Partitioned table and partitioned index.

- Non-partitioned table and non-partitioned index.

- Non-partitioned table and partitioned index.

 

제약 사항

Table

1. Clustered table은 partition할 수 없다.
2. LONG 또는 LONG RAW column이 있는 table은 partition할 수 없다. BLOB-type columns은 가능하다.
3. Index Only Table (IOT)는 Range partition만 가능하다.

Index

1. Cluster를 위한 index는 partition이 불가능 하다.
2. Clustered table를 위한 index는 partition이 불가능 하다.
3. Bitmap index는 local partitioned index만 가능하다.
4. Non-partitioned table로 된 Bitmap index는 partition이 불가능 하다.
5. Non-prefixed global index는 지원하지 않는다.
6. Local non-prefixed unique index는 index key가 포함된 partition key를 가져야 한다.

공통 사항

• Multi-column partition key는 16개 까지 제한이 있다.
• 다음의 column type은 partition key의 부분이 될 수 없다 .

- LEVEL 또는 ROWID pseudo-types.

- 다음의 type의 column : Nested table, Varray, Object type, Ref, Rowid

- 서로다른 type의 LOB (BLOB,CLOB,NCLOB,BFILE)

 

Index Partition

Oracle Version 8이상에서 Local 과 Global 두 가지 index를 제공한다. 이 두 가지는 다시 Local prefixed, Local non-prefixed, Global prefixed로 세분할 수 있다.

Index partition은 연관된 table의 partition/sub-partition에 대한 추가, 삭제, 분할, 압축에 따라 자동적으로 관리된다. Index partition에 사용되는 방법은 그에 대응하는 table에 사용되는 방법과 동일하다.

Global index는 base table에서 사용되는 것과 다를 수 있는 column으로 partition된다. Oracle은 base table partition이 변경된 경우 index partition을 최신으로 유지하지 않으므로 index partition은 "UNUSABLE" 상태가 되고 rebuild해야 한다.

Local Index의 특징

1. Local index는 기반을 두고 있는 table과 동일한 partition으로 구성된다.
2. Local index의 속성

- 동일한 수의 partition/sub-partition

- partition table 과 동일한 한계

- 동일한 partition key

Prefixed 와 Non-prefixed의 차이

1. 만약 partition key의 구성이 index key, column의 왼쪽부분이 동일한 순서로 구성된다면 prefixed이다.
2. Non prefixed global index는 지원하지 않는다.
3. Non-partitioned index는 Global prefixed index로 본다.

예제로 사용될 Table

CREATE TABLE emp (

empno NUMBER NOT NULL,

ename VARCHAR2(10) NOT NULL,

job VARCHAR2(14),

deptno NUMBER

)

PARTITION BY RANGE (empno)

(

PARTITION part1 VALUES LESS THAN (30),

PARTITION part2 VALUES LESS THAN (60),

PARTITION part3 VALUES LESS THAN (MAXVALUE)

    );

 

Prefixed Local Index

CREATE INDEX emp_idx1 ON emp(empno) LOCAL;

이 index는 index key 'empno'가 partition key와 동일하기 때문에 prefixed local index이다. emp table과 동일한 key를 사용하므로 Oracle에 의해 자동으로 partition되며 index에 partition 범위를 다시 선언할 필요가 없다. Oracle은 emp table 과 동일한 형태의 3개의 index partition을 자동으로 생성한다. Index key 각각은 base table과 동일한 제한을 가진 partition에 저장된다.

Prefixed 방법의 주요한 점은 optimizer가 index key의 범위와 맞지않는 index partition은 무시 한다는 것이다.

Query의 예 : select * from emp where empno = 62;

 

è Optimizer는 part1, part2 두개의 partition은 무시하고 곧바로 3번째 index partition part3을 scan한다.

 

emp_idx1 Part1 part2 part3

 

Partition key range -> 0-29 30-59 60-MAXVALUE

(empno)

Index key range -> 0-29 30-59 60-MAXVALUE

(empno)

table emp -> empno

 

Non-prefixed Local Index

CREATE INDEX emp_idx1 ON emp(deptno) LOCAL;

이 index는 index key 'deptno'가 partition key와 다르므로 Non-prefixed local index이다. Oracle은 base table과 동일한 3개의 index partition을 생성하지만 Index key의 물리적인 저장은 partition rule을 따르지 않는다.

다른 partition상에 index key의 분산을 의미하므로 index key를 포함하는 query가 주어지면 선택된 값을 조회하기 위하여 모든 index partition이 scan된다.

Query의 예 : select * from emp where deptno = X;

 

è Optimizer는 특정 index key를 알아내기 위해 emp_idx2의 3개 partition을 탐색한다. Query에 기술된 값은 3개의 index partition중에 있게 된다.

 

emp_idx2 Part1 part2 part3

 

Partition key range -> 0-29 30-59 60-MAXVALUE

(empno)

Index key range -> 55-95 56-18 57-82

(deptnono)

table emp -> empno

Non-prefixed Unique key Local Index

Non-prefixed unique key local index를 사용하기 위해서는 index key는 하나의 partition에만 속해야 하므로 partition key의 부분 집합이어야 한다. 즉, Non-Prefixed Unique Index는 생성할 수 없다.

 

Global prefixed Index

CREATE INDEX emp_idx3 ON emp(ename)

GLOBAL PARTITION BY RANGE (ename)

(

PARTITION p1 VALUES LESS THAN ('N'),

PARTITION p2 VALUES LESS THAN (MAXVALUE)

);

이 방법은 table과 다른 partition key로 만들어진 index partition을 가능하게 한다. 이 형태는 prefixed가 되어야 한다. Index key는 optimizer가 query에 의해 관련된 partition을 선택하는 것을 가능하게 하는 partition key에 기반을 둔다.

Query의 예 : select * from emp where ename like 'B%';

 

è Optimizer는 자동으로 첫번째 index partition P1을 선택한다. 이 partition에 있는 row는 다른 table partition의 row를 참조하는 ROWID를 가지고 있다.

emp_idx3 Part1 part2

 

Partition key range -> A-M N-MAXVALUE

(ename)

Index key range -> A-M N-MAXVALUE

(ename)

table emp -> empno

주의사항

별개의 table partition상에 index access를 분산한다면 disk I/O 증가와 같은 성능저하를 유발한다. Global index는 Oracle이 table의 재조직과 같은 경우가 발생할 때 자동적으로 index를 관리하지 않기 때문에 local index보다 관리가 어렵다. Table의 storage변경은 모든index partition이 'UNUSABLE' 상태로 변경될 수 있다. 이때 Index rebuild가 필요하고 소요시간은 partition의 크기보다는 table의 크기에 비례한다.

 

Index의 사용 정책

특정 index 형태의 선택은 application의 제약조건에 달려있고 기능의 성능, 유용성, 관리의 용이성들 사이에 타협이 필요하다.

주요 규칙

1. Local index는 Global index보다 관리가 쉽고 높은 유용성이 있다.
2. Prefixed index는 optimizer가 query와 관련된 partition을 직접 탐색할 수 있게 한다.
3. Local prefixed index와 Global index는 OLTP 형태에 적합하고 Local non prefixed index 는 Data warehousing 과 decision making 환경에 적합하다.

Index 형태 선택 시 고려사항

1. Index가 index column의 왼쪽부터 순서로 partition 되어진다. è Local prefixed
2. 별개의 partition에 parallel access의 사용이 필요 하고 주요업무가 DSS 환경이다. è Local non prefixed
3. Partition Key를 포함하지 않는 column을 unique index로 만들려고 한다. è Global prefixed
4. OLTP 환경이다. è Global prefixed.
5. Local non prefixed index는 DATE type같은 범주로 분할된 historical 형태에 특히 유용하다. date와 다른 형태의 범주로 database를 탐색할 때 유용하다. Partition의 추가, 삭제와 같은 유지보수도 쉽다.

 

LOB & IOT(Index Organized Table) Partition

 

LOB Partition

8i이상에서 partition table은 LOB type의 column을 가질수 있으나 이type이 partition key로 사용될 수 없다.

특징

1. Partition table에서 사용할 수 있는 LOB type : BLOB, CLOB, NCLOB, BFILE
2. 모든 LOB type column은 LOB을 access를 가능하게 하는 연관된 index와 함께 data segment를 생성한다. LOB, LOB index와 관련된 storage절은 연관된 table과 다를 수 있다.
3. LOB type column을 가진 partition table을 위해 table의 partition이나 sub-partition만큼data와 index segment를 생성한다. 이러한 별개의 segment는 table partition과 동일하게 분할된다.
4. Storage절은 table에 정의하거나 각 partition에 직접 정의한다. 각 LOB type partition은 tablespace 뿐만 아니라 자신의 storage절을 가질 수 있다.
5. LOB의 Index partition은 항상 LOB data의 partition과 동일한 tablespace에 저장된다. LOB의 Index partition을 위한 storage절의 사용은 불가능 하다.
6. BFILE type column의 경우 external file의 LOCATOR만은 가지므로 VARCHAR2 type column과 동일한 방법으로 취급한다.

예제 Table

CREATE TABLE emp_lob (

empno NUMBER(4) NOT NULL,

ename VARCHAR2(10),

sal NUMBER(7,2),

image BLOB)

LOB (image) STORE AS IMAGE_LOB

(

CHUNK 1

PCTVERSION 20

NOCACHE

LOGGING

TABLESPACE T_LOB

STORAGE (INITIAL 12k NEXT 12K PCTINCREASE 0)

)

TABLESPACE USERS

PARTITION BY RANGE(empno)

(

PARTITION emp_p1 VALUES LESS THAN (50),

PARTITION emp_p2 VALUES LESS THAN (100),

PARTITION emp_p3 VALUES LESS THAN (150),

PARTITION emp_p4 VALUES LESS THAN (MAXVALUE)

);

Table은 4개의 partition으로 분할되었다. 각 partition은 LOB type partition과 연관되어 있고 LOB partition들은 각각 data partition과 index partition으로 분리되어 있다.

Partition정보를 확인 할 수 있는 View

select TABLE_NAME, POSITION, TABLESPACE, PARTITION_NAME

from DBA_TAB_PARTITIONS

where TABLE_NAME = 'EMP_LOB';

TABLE_NAME POSITION TABLESPACE PARTITION_NAME

--------------- ---------- ---------- ---------------

EMP_LOB 1 USERS EMP_P1

EMP_LOB 2 USERS EMP_P2

EMP_LOB 3 USERS EMP_P3

EMP_LOB 4 USERS EMP_P4

select TABLE_NAME, COLUMN_NAME, SEGMENT_NAME, INDEX_NAME

from DBA_LOBS

where TABLE_NAME = 'EMP_LOB';

TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME

--------------- --------------- --------------- -------------------------

EMP_LOB IMAGE IMAGE_LOB SYS_IL0000012665C00004$$

 

Index segment 이름은 Oracle에 의해 생성된다. 두개의 Object "IMAGE_LOB 와 SYS_IL0000012665C00004$$"는 가상으로 Oracle의 dictionary에 존재하지만 물리적으로는 partition형태로 존재한다.

select TABLE_NAME, COLUMN, LOB_NAME, PARTITION,

LOB_PARTITION, LOB_INDPART, TABLESPACE

from DBA_LOB_PARTITIONS

where TABLE_NAME = 'EMP_LOB';

 

TABLE_NAME COLUMN LOB_NAME PARTITION LOB_PARTITION LOB_INDPART TABLESPACE

---------- ------ --------- --------- ------------- ----------- ----------

EMP_LOB IMAGE IMAGE_LOB EMP_P1 SYS_LOB_P63 SYS_IL_P67 T_LOB

EMP_LOB IMAGE IMAGE_LOB EMP_P2 SYS_LOB_P64 SYS_IL_P68 T_LOB

EMP_LOB IMAGE IMAGE_LOB EMP_P3 SYS_LOB_P65 SYS_IL_P69 T_LOB

EMP_LOB IMAGE IMAGE_LOB EMP_P4 SYS_LOB_P66 SYS_IL_P70 T_LOB

select TABLE_NAME, COLUMN, LOB_NAME,

LOB_INDEX_NAME, CHUNK, PCTVERSION

from DBA_PART_LOBS

where TABLE_NAME = 'EMP_LOB';

 

TABLE_NAME COLUMN LOB_NAME LOB_INDEX_NAME CHUNK PCTVERSION

---------- ------ ---------- ------------------------ ------ -----------

EMP_LOB IMAGE IMAGE_LOB SYS_IL0000012665C00004$$ 1 20

select INDEX_NAME, TABLE_NAME, TYPE,

COUNT, LOCALITY, ALIGNMENT

from DBA_PART_INDEXES

where TABLE_NAME = 'EMP_LOB';

 

INDEX_NAME TABLE_NAME TYPE COUNT LOCALITY ALIGNMENT

------------------------ ---------- ------- ------ ------ ------------

SYS_IL0000012665C00004$$ EMP_LOB RANGE 4 LOCAL NON_PREFIXED

 

select INDEX_NAME, HIGH_VALUE, PARTITION,

POSITION, STATUS, TABLESPACE

from DBA_IND_PARTITIONS

where TABLE_NAME = 'EMP_LOB';

 

INDEX_NAME HIGH_VALUE PARTITION POSITION STATUS TABLESPACE

------------------------ ---------- --------- -------- ------ ----------

SYS_IL0000012665C00004$$ 50 SYS_IL_P67 1 USABLE T_LOB

SYS_IL0000012665C00004$$ 100 SYS_IL_P68 2 USABLE T_LOB

SYS_IL0000012665C00004$$ 150 SYS_IL_P69 3 USABLE T_LOB

SYS_IL0000012665C00004$$ MAXVALUE SYS_IL_P70 4 USABLE T_LOB

 

IOT Partition

IOT는 Version 8.1.5이상에서 partition될 수 있다.

주의: IOT는 BTREE index 구조를 가진다. Index의 leaf들에 table의 row들을 저장한다. Table의 row들은 각 IOT의 생성에 필수적인 primary key상에 저장된다. IOT를 생성할 때 overflow영역이 생성된다. 이것은 primary key에 맞지않는 row의 부분들을 저장한다. 이 부가적인 segment의 목적은 각 IOT block에 저장된 row의 크기를 제한하는 것이다.

제약 사항

1. Partition은 Range type이어야 한다.
2. Partition key는 primary key의 부분 집합이어야 한다.
3. Overflow zone은 IOT partition과 동일하게 partition되어야 한다.
4. Partition key는 primary key의 부분 집합이거나 unique constraint 확인이 가능한 Column이 포함되도록 한다. Partition key가 prefixed라면 partition에 기반을 둔 query는 primary key상에 저장된 row를 직접 조회한다.

예제 Table

CREATE TABLE emp_iot(

empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(10),

sal NUMBER(7,2)

)

ORGANIZATION INDEX INCLUDING ename OVERFLOW

PARTITION BY RANGE(empno)

(

PARTITION emp_p1 VALUES LESS THAN (50) TABLESPACE data01,

PARTITION emp_p2 VALUES LESS THAN (100) TABLESPACE data02,

PARTITION emp_p3 VALUES LESS THAN (150) TABLESPACE data03,

PARTITION emp_p4 VALUES LESS THAN (MAXVALUE) TABLESPACE data04

);

각 IOT partition은 두개의 partition을 생성한다: data partition과 overflow partition이며 Overflow partition은 ename column을 초과하는 row를 저장한다.

 

Partition정보를 확인 할 수 있는 View

select TABLE_NAME, IOT_NAME, IOT_TYPE, PARTITIONED

from DBA_TABLES;

 

TABLE_NAME IOT_NAME IOT_TYPE PARTITIONED

-------------------- ---------- ------------ -----------

EMP_IOT IOT YES

SYS_IOT_OVER_12770 EMP_IOT IOT_OVERFLOW YES

select TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT, DEF_TABLESPACE_NAME

from DBA_PART_TABLES;

 

TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT DEF_TABLESPACE_NAME

------------------ ----------------- --------------- -------------------

SYS_IOT_OVER_12770 RANGE 4 USERS

select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS, PARTITIONED

from DBA_INDEXES;

 

INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS PARTITIONED

------------------ ----------- ----------- ---------- -----------

SYS_IOT_TOP_12770 IOT - TOP EMP_IOT UNIQUE YES

select INDEX_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME

from DBA_IND_PARTITIONS;

 

INDEX_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

----------------- -------------- ---------- ---------------

SYS_IOT_TOP_12770 EMP_P1 50 DATA01

SYS_IOT_TOP_12770 EMP_P2 100 DATA02

SYS_IOT_TOP_12770 EMP_P3 150 DATA03

SYS_IOT_TOP_12770 EMP_P4 MAXVALUE DATA04

select TABLE_NAME, PARTITION_POSITION, PARTITION_NAME, TABLESPACE_NAME

from DBA_TAB_PARTITIONS;

 

TABLE_NAME PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME

-------------------- ------------------ --------------- ---------------

SYS_IOT_OVER_12770 1 EMP_P1 USERS

SYS_IOT_OVER_12770 2 EMP_P2 USERS

SYS_IOT_OVER_12770 3 EMP_P3 USERS

SYS_IOT_OVER_12770 4 EMP_P4 USERS

 

DBA_TAB_PARTITIONS view에서, overflow와 연관된 data segment는 EMP_IOT table을 생성한 user의 default tablespace에 저장된다. 이는 overflow segment와 연관된 tablespace가 없고 table에 tablespace가 정의되지 않았기 때문이다. table을 위한 default tablespace가 있다면 overflow segment는 그 tablespace에 생성된다.

IOT의 다른 column에 정의할 수 있는 index

Local prefixed, Local non prefixed, 또는 global prefixed index가 가능하다.

 

Partition에 대한 작업

Test를 위한 예제 Table

CREATE TABLE test (

empno NUMBER(4) NOT NULL,

ename VARCHAR2(10),

sal NUMBER(7,2)

)

PARTITION BY RANGE(empno)

(

PARTITION emp_p1 VALUES LESS THAN (50),

PARTITION emp_p2 VALUES LESS THAN (100),

PARTITION emp_p3 VALUES LESS THAN (150),

PARTITION emp_p4 VALUES LESS THAN (200)

);

 

Partition의 이동

Table partition을 다른 tablespace로 이동 하는 것이 가능하다. 이 partition과 관련된 Local index partition의 상태는 'UNUSABLE'이 된다. Global Index인 경우 모든 partition이 'UNUSABLE'이 되므로 전체 index를 rebuild해야 한다.

ALTER TABLE test MOVE PARTITION emp_p1 tablespace tbs1;

è Table test의 첫번째 partition emp_p1을 tbs1 tablespace로 이동한다.

Index의 상태

1. Local Index는 해당 partition의 index를 rebuild해야 한다.
2. Global index인 경우 전체 partition에 대한 rebuild가 필요하다.
   èALTER INDEX test_idx1 REBUILD PARTITION partition_name;

 

Partition의 추가

상한값이 MAXVALUE가 아니라면 마지막 partition 다음에 새로운 partition의 추가가 가능하다. 상한값이 MAXVALUE라면 추가는 불가능 하므로 SPLIT을 이용해서 중간에 partition 추가 해야한다. SPLIT는 존재하는 partition을 두개의 별개의 partition을 만든다. 상위의 partition에 적용한다면 SPLIT는 상한선 다음에 별도의 partition을 추가한다.

ALTER TABLE test ADD PARTITION emp_p5 values less than (250);

è Table test의 마지막 partition emp_p4 다음에 emp_p5를 추가한다.

Index의 상태

1. Global index의 상한값은 항상 MAXVALUE가 되므로 SPLIT를 사용해야 한다.

제약 사항

1. Partition index의 경우 Global index에서만 사용 가능하다.

 

 

Partition의 삭제

Table이나 Global index partition의 삭제가 가능하다. Partition table 의 DROP은 Global index의 모든 partition의 상태가 'UNUSABLE'로 바뀌므로 전체 index의 rebuild가 필요하다.

ALTER TABLE test DROP PARTITION emp_p1;

è Table test의 첫번째 partition emp_p1을 삭제한다.

Index의 상태

1. Global index인 경우 전체 partition에 대한 rebuild가 필요하다.

제약 사항

1. Hash partitioned table에는 불가능 하고 COALESCE command를 사용해야 한다.

 

Partition의 truncate

해당 partition의 모든 row를 삭제하고 할당된 storage는 보존된다. Local index는 자동으로 변경되지만 Global index의 경우 'UNUSABLE' 상태가 된다.

ALTER TABLE test TRUNCATE PARTITION emp_p1;

è Table test의 첫번째 partition emp_p1을 truncate한다.

Index의 상태

1. Global index인 경우 전체 partition에 대한 rebuild가 필요하다.

제약 사항

1. Index에는 사용할 수 없다.

 

Partition의 분할

하나의 Partition의 내용을 두개의 개별적인 partition으로 분리한다. 연관된 Global/Local index partition 는 'UNUSABLE' 상태가 된다.

ALTER TABLE test SPLIT PARTITION emp_p1 AT (25)

INTO (PARTITION emp_p11, PARTITION emp_p12);

è emp_p1 partition은 두개의 partition emp_p11 과 emp_p12 로 나뉜다. 두개의 partition은 1-24, 25-49로 재정의된다.

ALTER TABLE test SPLIT PARTITION emp_p1 AT (25)

INTO (PARTITION emp_p1, PARTITION emp_p2);

è emp_p1이 emp_p1, emp_p2로 나뉜다.

Index의 상태

1. Local Index는 해당 partition의 index를 rebuild해야 한다.
2. Global index인 경우 전체 partition에 대한 rebuild가 필요하다.

제약 사항

1. Global index에서도 적용된다.
2. Hash partition에는 사용할 수 없고 ADD가 대신 사용된다.

Hash-type Partition 분할의 예

Hash-type partition의 예로 사용되었던 emp_hpart는 4개의 partition P1, P2, P3, P4를 가지고 있다.

 

PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME

--------------- ------------------ ---------------

P1 1 DATA01

P2 2 DATA02

P3 3 DATA03

P4 4 DATA04

 

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK

--------------- --------------- --------------- ----------- ------------

EMP_HPART P1 DATA01 8 21

EMP_HPART P2 DATA02 9 21

EMP_HPART P3 DATA03 10 2

EMP_HPART P4 DATA04 11 2

 

- 첫번째 partition P1의 내용은 다음과 같다

 

EMPNO ENAME SAL

---------- ---------- ----------

303 TEST 10302

306 TEST 10305

310 TEST 10309

325 TEST 10324

326 TEST 10325

332 TEST 10331

338 TEST 10337

341 TEST 10340

343 TEST 10342

347 TEST 10346

348 TEST 10347

349 TEST 10348

350 TEST 10349

360 TEST 10359

361 TEST 10360

365 TEST 10364

368 TEST 10367

370 TEST 10369

373 TEST 10372

380 TEST 10379

385 TEST 10384

386 TEST 10385

396 TEST 10395

400 TEST 10399

401 TEST 10400

 

25 rows selected.

 

- 다음 command는 새로운 partition P5를 추가하고 P1의 row들을 P1에서 P5에 재분산 한다.

ALTER TABLE emp_hpart ADD PARTITION P5;

 

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK

--------------- --------------- --------------- ----------- ------------

EMP_HPART P1 DATA01 8 2

EMP_HPART P2 DATA02 9 21

EMP_HPART P3 DATA03 10 2

EMP_HPART P4 DATA04 11 2

EMP_HPART P5 USERS 5 1292

 

SQL> select * from emp_hpart partition(P1);

 

EMPNO ENAME SAL

---------- ---------- ----------

306 TEST 10305

310 TEST 10309

325 TEST 10324

326 TEST 10325

338 TEST 10337

341 TEST 10340

343 TEST 10342

347 TEST 10346

350 TEST 10349

370 TEST 10369

373 TEST 10372

385 TEST 10384

400 TEST 10399

401 TEST 10400

 

14 rows selected.

 

SQL> select * from emp_hpart partition(P5);

 

EMPNO ENAME SAL

---------- ---------- ----------

303 TEST 10302

332 TEST 10331

348 TEST 10347

349 TEST 10348

360 TEST 10359

361 TEST 10360

365 TEST 10364

368 TEST 1036

380 TEST 10379

386 TEST 10385

396 TEST 10395

 

11 rows selected.

 

- Partition P1은 지워지고 다시 생성됨에 유의하자.

 

Partition의 교환

Non-partition table을 partition으로 또는 그 반대로 옮겨준다. 이 기능은 특히 V7의 partition view를 partition table로 migration하는데 유용하다.

다음과 같이 4개의 table less50, less100, less150, less200를 이용하는 partition view를 Partition Table로변경해 보자.

 

CREATE VIEW test_view AS

SELECT * FROM less50

UNION ALL

SELECT * FROM less100

UNION ALL

SELECT * FROM less150

UNION ALL

SELECT * FROM less200;

1. Partition view의 Table과 동일한 구조의 빈 partition Table을 생성한다.

CREATE TABLE new_test (

empno NUMBER(4) NOT NULL,

ename VARCHAR2(10),

sal NUMBER(7,2)

)

PARTITION BY RANGE(EMPNO)

(

PARTITION emp_p1 VALUES LESS THAN (50),

PARTITION emp_p2 VALUES LESS THAN (100),

PARTITION emp_p3 VALUES LESS THAN (150),

PARTITION emp_p4 VALUES LESS THAN (200)

);

1. View를 구성하는 각 table을 partition table로의 옮겨준다.

ALTER TABLE new_test EXCHANGE PARTITION

emp_p1 WITH TABLE less50 WITH VALIDATION;

장점

1. 이 작업은 Oracle의 dictionary를 변경만 발생하여 아주 짧은 시간이 소요된다.
2. Segment의 물리적인 이동은 발생하지 않는다.

제약 사항

1. Non-partition table은 물론 partition table로의 바꾸기 위한 table의 구조는 type, column, size, column의 수가 동일해야 한다.
2. Hash partition에서는 사용할 수 없고 대신 COALESCE를 사용한다.

 

Partition의 coalesce

Hash 형태의 partition을 위한 8.1.5의 신기능으로 남겨진 partition에 row를 재분배 하고 Oracle은 선택된 partition을 삭제한다.

ALTER TABLE test TRUNCATE PARTITION emp_p1;

è Table test의 첫번째 partition emp_p1을 truncate한다.

 

Hash-type Partition Coalesce의 예

Hash-type partition의 예로 사용되었던 emp_hpart는 4개의 partition P1, P2, P3, P4를 가지고 있다.

 

PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME

--------------- ------------------ ---------------

P1 1 DATA01

P2 2 DATA02

P3 3 DATA03

P4 4 DATA04

 

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK

--------------- --------------- --------------- ----------- ------------

EMP_HPART P1 DATA01 8 21

EMP_HPART P2 DATA02 9 21

EMP_HPART P3 DATA03 10 2

EMP_HPART P4 DATA04 11 2

ALTER TABLE emp_hpart COALESCE PARTITION;

 

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK

--------------- --------------- --------------- ----------- ------------

EMP_HPART P1 DATA01 8 21

EMP_HPART P2 DATA02 9 2

EMP_HPART P3 DATA03 10 2

 

Oracle은 새로운 P2 partition을 재 생성은 물론 P2와 P4의 row가 재 분배됨에 유의하라.

Index의 상태

1. 선택된 partition의 Local Index partition는 삭제된다.
2. 삭제된 Partition의 data가 이동하게 되는 Local Index의 partition은 'UNUSABLE' 상태가 되므로 rebuild가 필요하다.
3. Global Index partition전체가 'UNUSABLE' 상태가 되므로 모든 partition에 대한 rebuild가 필요하다.

제약 사항

1. Index에 대해서는 사용할 수 없다.
2. Global index인 경우 전체 partition에 대한 rebuild가 필요하다.

 

Partition의 추가로 인한 속성의 변경

Index를 생성할 때 partition의 이름과 tablespace를 지정할 수 있으나 자동적으로 생성되는 Local index partition의 경우 새로운 table의 partition과 동일한 이름과 tablespace에 생성된다. 이런 경우는 성능, 공간활용 등의 문제발생 여지가 있으므로 변경하여야 한다.

1. 새로 추가된 Local Index의 Tablespace를 변경한다.

ALTER INDEX emp_idx1 REBUILD PARTITION new_part_name

TABLESPACE index_tablespace_name;

1. 새로 추가된 Local Index의 이름을 변경한다.

ALTER INDEX emp_idx1 RENAME PARTITION new_part_name TO partition_name;

 

Non-partition Table과 Partition Table간의 변경

Non-partition Table과 Partition table을 상호 변경하는 3가지 방법을 설명한다.

 

Export / Import 사용

원본 Table을 export하여 목표 Table에 import 한다.

수행 예제

1. 원본 table export

exp user/passswd tables=table_name file=exp.dmp

1. 원본 table 삭제

drop table table_name;

1. 목표 Partition table로 생성

create table table_name (

qty number(3),

name varchar2(15))

partition by range (qty)

(

partition p1 values less than (501),

partition p2 values less than (maxvalue)

);

1. ignore=y option을 사용하여 import

imp user/passwd file=exp.dmp ignore=y

ignore=y option은 import시에 table creation시 발생하는 error를 무시한다.

Non-partition Table과 Partition table을 상호 변경하는 3가지 방법을 설명한다.

 

Sub query를 이용한 insert

원본 Table을 select 하여 목표 Table에 insert 한다.

수행 예제

1. 목표 Partition table로 생성

create table new_table (

qty number(3),

name varchar2(15))

partition by range (qty)

(

partition p1 values less than (501),

partition p2 values less than (maxvalue)

);

1. 원본 table을 select하여 목표 table에 insert

insert into new_table select * from org_table

1. 원본 table 삭제 후에 목표 table을 rename

drop table org_table;

alter table new_table rename to org_table;

 

Partition exchange이용

Non-partition Table이 Partition table의 하나의 partition으로 변환된다. 그러므로 하나의 non-partition table로 하나의 partition table로 변환을 위해서는 원본 dummy table을 필요로 한다.

<Partition의 작업, Partition의 교환 참고>

수행 예제

1. 목표 Partition table로 생성

create table new_table (

qty number(3),

name varchar2(15))

partition by range (qty)

(

partition p1 values less than (501),

partition p2 values less than (maxvalue)

);

1. 원본 table과 targer table을 exchange

CREATE TABLE dummy_y as SELECT sal FROM emp WHERE qty < 501;

CREATE TABLE dummy_z as SELECT sal FROM emp WHERE qty >= 501;

ALTER TABLE new_table EXCHANGE PARTITION p1 WITH TABLE dummy_y;

ALTER TABLE new_table EXCHANGE PARTITION p2 WITH TABLE dummy_z;

 

Partition Table/Index와 연관된 DML

Partition Table/Index와 관련한 DML에 대하여 설명한다.

 

Partition Key 값의 update

Partition Key 값의 변경은 row가 존재하는 partition을 바뀌어야 하는 경우가 있다. Update후에도 동일한 partition에 존재할 수 있다면 상관없지만 다른 partition으로 옮겨질 필요가 있는 경우가 있다. 이런 경우에 해당 Table에 row movement가 enable되어 있지 않으면 ORA-14402 error (updating partition key column would cause a partition change)가 발생한다.

이 option은 Partition Table의 생성시 또는 나중에 변경할 수 있다.

CREATE TABLE part_tab_name …. ENABLE/DISABLE ROW MOVEMENT;

또는

ALTER TABLE part_tab_name ENABLE/DISABLE ROW MOVEMENT;

 

9i의 List Partition

9i의 신기능인 List Partition의 command에 대하여 설명한다.

 

CREATE a List Partitioned Table

List Partition Table을 생성한다.

CREATE TABLE employees_reg_p

(

employee_id NUMBER(6)

, first_name VARCHAR2(20)

, last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn_p NOT NULL

, email VARCHAR2(25) CONSTRAINT emp_email_nn_p NOT NULL

, phone_number VARCHAR2(20)

, hire_date DATE CONSTRAINT emp_hire_date_nn_p NOT NULL

, job_id VARCHAR2(10) CONSTRAINT emp_job_nn_p NOT NULL

, salary NUMBER(8,2) CONSTRAINT emp_salary_nn_p NOT NULL

, commission_pct NUMBER(2,2)

, manager_id NUMBER(8)

, department_id NUMBER(4)

, region VARCHAR2(15)

, CONSTRAINT emp_salary_min_p CHECK (salary > 0)

)

PCTFREE 60

partition BY LIST (REGION)

(

partition Zone_1 VALUES ('R1', 'R10', 'R11', 'R12') TABLESPACE ZONE1,

partition Zone_2 VALUES ('R13', 'R14', 'R15', 'R16')TABLESPACE ZONE2,

partition Zone_3 VALUES ('R17', 'R18', 'R19', 'R2') TABLESPACE ZONE3,

partition Zone_4 VALUES ('R20', 'R21', 'R22', 'R23')TABLESPACE ZONE4,

partition Zone_5 VALUES ('R24', 'R25', 'R26', 'R27')TABLESPACE ZONE5,

partition Zone_6 VALUES ('R28', 'R29', 'R3', 'R30') TABLESPACE ZONE6,

partition Zone_7 VALUES ('R31', 'R32', 'R4', 'R5') TABLESPACE ZONE7,

partition Zone_8 VALUES ('R6', 'R7', 'R8', 'R9') TABLESPACE ZONE8

);

- 다음의 View로 확인할 수 있다.

SELECT table_name, partition_name partition,

high_value, partition_position position

FROM user_tab_partitions

WHERE table_name = 'EMPLOYEES_REG_P';

 

TABLE_NAME PARTITION HIGH_VALUE POSITION

--------------- ---------- --------------------------------- --------

EMPLOYEES_REG_P ZONE_1 'R1', 'R10', 'R11', 'R12' 1

EMPLOYEES_REG_P ZONE_2 'R13', 'R14', 'R15', 'R16' 2

EMPLOYEES_REG_P ZONE_3 'R17', 'R18', 'R19', 'R2' 3

EMPLOYEES_REG_P ZONE_4 'R20', 'R21', 'R22', 'R23' 4

EMPLOYEES_REG_P ZONE_5 'R24', 'R25', 'R26', 'R27' 5

EMPLOYEES_REG_P ZONE_6 'R28', 'R29', 'R3', 'R30' 6

EMPLOYEES_REG_P ZONE_7 'R31', 'R32', 'R4', 'R5' 7

EMPLOYEES_REG_P ZONE_8 'R6', 'R7', 'R8', 'R9' 8

 

MODIFY a List Partition

List Partition Table의 partition key 값을 삭제한다. 삭제하고자 하는 Key value에 해당하는 Row를 삭제한 후 modify한다.

DELETE employees_reg_p WHERE REGION IN ('R32', 'R22');

ALTER TABLE employees_reg_p MODIFY PARTITION zone_4

DROP values ('R22');

ALTER TABLE employees_reg_p MODIFY PARTITION zone_7

DROP values ('R32');

 

ADD a List Partition

List Partition Table에 새로운 Partition을 추가한다.

ALTER TABLE employees_reg_p ADD partition Zone_add values ('R22', 'R32');

 

MERGE List Partitions

List Partition Table의 partition들을 합한다.

ALTER TABLE employees_reg_p MERGE PARTITIONS Zone_add, Zone_7 INTO

PARTITION Zone_7;

 

RENAME a List Partitions

List Partition Table의 partition의 이름을 변경한다.

ALTER TABLE employees_reg_p RENAME PARTITION Zone_7 TO Zone_merge;

 

SPLIT a List Partition

List Partition Table의 partition을 둘로 분할한다. Partition Zone_1중에서 R11, R12값은 Zone_11로 나머지는 Zone_12로 분할된다.

ALTER TABLE employees_reg_p SPLIT PARTITION Zone_1 VALUES ('R11', 'R12')

INTO ( PARTITION Zone_11, PARTITION Zone_12);

 

TRUNCATE a List Partition

List Partition Table의 partition내의 Data를 지우고 초기화한다.

ALTER TABLE employees_reg_p TRUNCATE PARTITION Zone_3 DROP STORAGE;

 

EXCHANGE a List Partition

Non Partition Table과 Partition table의 하나의 partition과 교환한다.

ALTER TABLE employees_reg_p EXCHANGE PARTITION Zone_3

WITH TABLE exchange_zone_3

WITHOUT VALIDATION;

결론

Partitioning 기능은 성능과 관리 측면에서 현저한 향상뿐만 아니라 data의 구성에 있어 보다 나은 유연성을 제공한다.