[OTN펌]PARTITION TABLE 이란?

제품 : ORACLE SERVER

작성날짜 : 2004-08-13

PARTITION TABLE 이란?
=====================

PURPOSE

---

partition table에 대한 기본 개념입니다.

SCOPE

---

8~10g Standard Edition 에서는 Partitioning Option 은 지원하지 않습니다.

Explanation

---

ORACLE 8에서 제공하는 partition table 에 대해 알아보자.

1. Partitioned Table이란?

partitioning 이란 큰 object 를 작고 manage 가 가능하게 분리하는 것을 의미하며,
table 이나 index 에서만 가능하고 cluster, snapshot 은 불가능하다.
각 partition 은 별개의 segment 에 저장되어진다.

Oracle8에서 table 은 기본이 되는 key value 에 의해 partition 으로 분리되어진다.
각 partition은 독립적으로 운영된다.
예를 들어 table partition 은 DML (insert, update, delete) 문에 의한
transaction 을 다른 partition 에 영향을 주지 않고 복구가 가능하다.
DBA_TAB_PARTITIONS 에 각 partition 의 storage 정보 등을 갖는다.

2. 어떻게 partitioned Table을 생성하는가 ?

partition key(s)와 개개의 partition 에 범위를 주어 생성한다.
이 partition 이름은 주어질 수 있으며 만일 생략되면 ORACLE 이 SYS_Pn 으로
generate 한다.

예제 :

emp partition 을 EMPNO column을 partition key 로 하여 생성해 보자.

CREATE TABLE emp
(EMPNO NUMBER(5),
...)
PARTITION BY RANGE(EMPNO)(
partition emp_p1 VALUES LESS THAN (2000),
partition emp_p2 VALUES LESS THAN (4000),
partition emp_p3 VALUES LESS THAN (MAXVALUE));

select * from emp partition (emp_p3);

ACCT_NO PERSON SALES_AMOUNT WEEK_NO

---

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

---

----------
1000 abc 10 30

insert into emp partition (emp_p3) values (7000, 'bcd', 10, 30);

3. partition table 관련한 dictionary 정보

. storage parameters
--> DBA_TAB_PARTITIONS

. partiton table 의 upper partition bound
--> select high_value, partition_position from sys.dba_tab_partitions
where table_name = 'SALES';

4. Partitioned tables의 제약점은?

a) Datatype 제약
Partitioned table은 LONG 이나 LONG RAW datatype을 가질 수 없다.
또한 LOB datatypes (BLOB, CLOB, NCLOB, or BFILE), object types을 가질 수
없다. 이 LOB type 은 V8.1부터는 가능할 것으로 기대된다.

b) Clusters 는 partition 될 수 없다.

c) Bitmap 제한

bitmap 은 local partitioned table 에서만 가능하고 global indexes 로는
불가능하다.

d) Physical 제한
Partitioned table은 여러 개의 database에 걸쳐 있을 수 없다.
오직 1 instance 에서만 가능하다.

5. Local Prefixed와 Local Non-Prefixed index란?

Local index란 partitioned table 의 index로 이는 오로지 한 partition 의
row들을 나타내는 ROWID 를 갖는 index 이다.
이는 주로 partition table 의 partition key 로 사용되어진다.
이를 equi-partitioning 이라 한다.

Prefixed index는 partition key 에 대응하는 leading index key(s) 이다.

Non-Prefixed index 는 leading column 이 되는 partition key 를 포함하지 않는
index key 이다.

6. Global index란?

global index 는 prefix 만 제공하며 non-prefix 는 제공하지 않는다.

global Index 는 전체 table 의 ROWID 처럼 사용되어진다.

7. partitions을 사용하는 방법?

Partition-Extended Table Name을 사용한다.

즉 "schema.table PARTITION part_name" 를 사용하는데 schema 는 schema owner
이고 table은 base table 이름이며, PARTITION 은 써도 되고 안 써도 되는 용어이고,
partition_name은 partition 의 name 이다.

이 partition-extented table 이름은 다음 문장에서 사용되어진다.
INSERT
UPDATE
DELETE
LOCK TABLE
SELECT

Q) partition 에 insert 시:
SQL> insert into sales partition (p8) values (7000, 'bcd', 10, 30);

Q) partition을 delete시:
SQL> delete from sales partition (p8);

Q) partition을 update 시:
SQL> update sales partition (p8) set sales_amount = 20;

Q) partition을 select 시:
SQL> select * from sales PARTITION (Q4);

8. partition-extended table 이름의 제약?

. remote schema object를 포함할 수 없다.

partition-extended table name 은 dblink 를 포함할 수 없으며, dblink 를 통해
table 로 변환 가능한 synonym 을 포함할 수 없다.
만일 remote partition의 사용을 원할 때에는 remote site 에서
partitioned-extended table 이름을 사용하여 view 를 생성할 수 있다.

. partition-extended table 이름은 PL/SQL에서 사용되지 않는다.

partition-extended table 이름을 사용한 SQL 문은 DBMS_SQL package 를 통해
만일 사용하고자 한다면 view 를 사용하여야 한다.

. 오로지 base table 만 허용된다.

partition extension 은 base table 에만 허용되고 synonyms, views, 그외 schema
에서는 허용되지 않는다.

9. Export/Import 시 Table-Level 과 Partition-Level 의 차이점?

테이블 단위의 export에서는 partitioned or non-partitioned table 전체가 index
와 그 table 에 dependent 한 다른 모든 object 가 함께 export 된다.
즉 partitioned table 의 모든 partition 이 export 된다. (이는 direct path
export and conventional path export에 모두 적용.)
또한 모든 export 모드 (full, user, table) 가 테이블 단위의 export 를 support
한다.

partition 단위의 export에서는 사용자가 테이블의 하나 또는 그 이상의 partition
을 export 할 수 있다.

Full database 단위나 user mode 는 partition-level의 export 를 support 하지
않는다. 오직 table levle 만 가능하다.
또한 incremental export (incremental,cumulative, and complete) 가 full
database mode 에서만 가능하기 때문에 partition-level export는 incremental
exports를 지원하지 못한다.

Partition-level import는 export 되어진 non-partitioned table을 import 하지
못한다. 그러나, table-level 의 import로 non-partitioned table 로부터
partitioned table 이 import되어진다.
즉 partition-level import 는 export 되어진 table 이 partitioned 되어 있고
export file 에 있을 때에만 가능하다.

export file 의 partition name 이 valid 하지 않는 경우 import 시 경고
message 를 발생한다.

모든 경우 partitioned data 는 import 시 선택적으로 가능하게 export 되어 진다.
export 나 import 시 table name 을 지정 시는
TABLES=schema_name : tables_name : partition_name 으로 사용한다.

Partition 단위의 export 는 table 내의 특정 partition 을 한개 또는 그 이상을
export 가능하게 한다.

이 때 partition name 이 주어지지 않으면 table 전체가 사용된다.

다음은 partiotion level 의 export 예제이다.

exp system/manager FILE = export.dmp TABLES = (scott.b:px, scott.b:py,
mary.c, d:qb)

이 예제에서 scott.b 는 반드시 partitioned table이고 px ,py 는 2개의
partition 이다.
mary.c 는 partitioned 또는 non-partitioned table 이다. 그러나 d table 은
반드시 partitioned table 이며 qb 는 그 partioion 중의 하나이다.

만일 table-name이나 같은 table 의 partition-name이 중복 사용되어지면
export 는 error 를 발생한다. 예를 들어 다음 partition-level의 export 명령어는
table sc 와 partition px 가 중복 사용되어 error 를 발생한다.

exp system/manager FILE = export.dmp TABLES = (sc, sc:px, sc)

10. partiton table 또는 view를 어떻게 non-partitioned table로 변환시키는가?

table 을 변환하기 위해 dummy table 을 생성하고,
alter table EXCHANGE PARTITION 명령어를 통해 수행한다.
이 명령어는 매우 빨리 data dictionary 를 update 시킨다.

SPLIT PARTITION 은 매우 큰 partition table 이나 view 를 handling 하는 데
유용하다.

SQL:
1. partition을 갖는 dummy_t table 을 생성
2. alter table EXCHANGE partition T with dummy_T
3. drop table T

exp/imp:
1. export the table
2. drop the table .
3. partiton 을 갖는 table 을 다시 생성
4. table data 를 import 한다.

11. table partition을 결합하는 법?

export/import:

partition-level 의 export, import 를 통해 가능하다.

1. partition data 를 갖는 temporary table을 생성한다.
2. drop the partition to be merged
3. insert into table (select * from temporary table)
4. drop temp.

그러나, table partition 을 분할하는 방법은 export, import 를 통해 불가능하다.

Example

---

Reference Document

출처 : https://forums.oracle.com/forums/thread.jspa?threadID=452831

============================================================================

Global and Local

  ① Global : 테이블 파티션 키와 인덱스 파티션 키가 서로 다름
      - Global Index는 테이블과 다르게 파티션 되는 경우 입니다.
      - 즉 테이블과 같은 칼럼으로 파티션 되지만, 그 범위가 틀리거나 혹은 다른 칼럼으로 파티션이 이루어지는 경우 입니다.

  ② Local : 테이블 파티션 키와 인덱스 파티션 키가 같음
      - 로컬 인덱스란 인덱스를 생성한 테이블과 파티션 된 인덱스가 동일하게 파티션 된 (Equi-Partition) 경우를 나타냅니다.
      - 즉, 인덱스와 테이블은 같은 칼럼에 의해 파티션 되며, 하나의 인덱스 파티션이 테이블 파티션 하나와 대응되며, 대응되는 인덱스 파티션과 테이블 파티션은 각각 같은 범위를 갖게 됩니다.

  ※ Partitioned Table에서는 대부분의 경우 Local Index가 성능이나, 가용성 및 관리의 용이성이 Global Index 보다 우수 합니다. 그러나 업무의 특성 및 기타 Access 의 형태에 따라 Partition Index를 만들고자 하나 Local Index를 생성할 수 없는 경우에 Global Index를 고려 합니다.

Prefixed / Non-prefixed

  ① Prefixed : 인덱스 첫 번째 컬럼이 인덱스 파티션 키와 같음

  ② Non-prefixed : 인덱스 첫 번째 컬럼이 인덱스 파티션 키와 다름

  ※ Partitioned Index 생성시 Prefixed/Non-prefixed에 대한 지정은 하지 않으며 위와 같은 Rule로 만들어 졌는지 여부를 DBMS가 확인하여 Dictionary(USER_PART_INDEXES.ALIGNMENT)에 기록하고 관리하게 됩니다.

예제 1]  Non-prefixed

select * from dba_part_key_columns where NAME in('IPMS_UDR_X2','IPMS_UDR','IPMS_UDR_X1');

OWNER	NAME	OBJECT_TYPE	COLUMN_NAME	COLUMN_POSITION
IDBA	IPMS_UDR	TABLE	LD_OP_DTM	1
IDBA	IPMS_UDR_X1	INDEX	LD_OP_DTM	1
IDBA	IPMS_UDR_X2	INDEX	LD_OP_DTM	1

select * from dba_ind_columns where index_NAME in('IPMS_UDR_X2','IPMS_UDR_X1');

INDEX_OWNER	INDEX_NAME	TABLE_OWNER	TABLE_NAME	COLUMN_NAME	COLUMN_POSITION
IDBA	IPMS_UDR_X1	IDBA	IPMS_UDR	EQUIP_MSID	1
IDBA	IPMS_UDR_X1	IDBA	IPMS_UDR	USE_STRT_DTM	2
IDBA	IPMS_UDR_X1	IDBA	IPMS_UDR	FRM_IP_ADDR	3
IDBA	IPMS_UDR_X2	IDBA	IPMS_UDR	UDR_CORRL_ID	1

예제 2]  Prefixed
select * from dba_part_key_columns where NAME in('FACT_IPMS_IN_TRAFFIC','FACT_IPMS_IN_TRAFFIC_PK');--YR_MTH_DAY_HR_CD

OWNER	NAME	OBJECT_TYPE	COLUMN_NAME	COLUMN_POSITION
IDBADM	FACT_IPMS_IN_TRAFFIC	TABLE	YR_MTH_DAY_HR_CD	1
IDBADM	FACT_IPMS_IN_TRAFFIC_PK	INDEX	YR_MTH_DAY_HR_CD	1

select * from dba_ind_columns where index_NAME in('FACT_IPMS_IN_TRAFFIC_PK');

INDEX_NAME	COLUMN_NAME	COLUMN_POSITION
FACT_IPMS_IN_TRAFFIC_PK	YR_MTH_DAY_HR_CD	1
FACT_IPMS_IN_TRAFFIC_PK	SVC_NW_LNKG_EQUIP_CD	2
FACT_IPMS_IN_TRAFFIC_PK	SVC_SVR_CD	3
FACT_IPMS_IN_TRAFFIC_PK	BILL_SYS_CD	4
FACT_IPMS_IN_TRAFFIC_PK	BILL_SYS_NOD_CD	5
FACT_IPMS_IN_TRAFFIC_PK	IUX_BILL_SYS_NOD_CD	6
FACT_IPMS_IN_TRAFFIC_PK	FACT_SEQ	7
FACT_IPMS_IN_TRAFFIC_PK	IUX_BILL_SYS_CD	8

출처 : http://www.oracleclub.com/lecture/1914