oracle 에서 파티션(partition) 사용하기

오라클에 이런 게 있다는 걸 몰랐었다. 사실 모르는 게 많지만...
몇주째 고생하는 개발자에게 이걸 알려줬다면, 마이그레이션 작업이 훨씬 금방 끝났었을 텐데...
못도와준게 아쉽다. 그리고 역시 비싼 디비는 뭐가 다르긴 다르다.

파티션은 아직은 잘 모르지만, 키를 가지고 대용량의 테이블을 내부적으로 나눠서 빠른 처리를 하도록 돕는 기능이다. 개수가 많은 것보다는 개수가 적은 것이 쿼리가 훨씬 빠르기 때문이다.

참고 사이트
간략 설명
http://www.devarticles.com/c/a/Oracle/Partitioning-in-Oracle/1/

more..

Partitioning in Oracle. What? Why? When? Who? Where? How? - Partitioning in Oracle
(Page 2 of 3 )

Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a "partition". From an "Application Development" perspective, there is no difference between a partitioned and a non-partitioned table. The application need not be modified to access a partitioned table if that application was initially written on a non partitioned tables.

So now you know partitioning in oracle now the only thing that yo u need to know is little bit of syntax and that’s it, and you are a partitioning guru.

Oracle introduced partitioning with 8.0. With this version only, " Range Partitioning" was supported. I will come to details later about what that means. Then with Oracle 8i " Hash and Composite Partitioning" was also introduced and with 9i " List Partitioning", it was introduced with lots of other features with each upgrade. Each method of partitioning has its own advantages and disadvantages and the decision which one to use will depend on the data and type of application. Also one can MODIFY , RENAME, MOVE, ADD, DROP, TRUNCATE, SPLIT partitions. We will go thru the details now.

Advantages of using Partition’s in Table

1. Smaller and more manageable pieces of data ( Partitions )
2. Reduced recovery time
3. Failure impact is less
4. import / export can be done at the " Partition Level".
5. Faster access of data
6. Partitions work independent of the other partitions.
7. Very easy to use

Types of Partitioning Methods

1. RANGE Partitioning

This type of partitioning creates partitions based on the " Range of Column" values. Each partition is defined by a " Partition Bound" (non inclusive ) that basically limits the scope of partition. Most commonly used values for " Range Partition" is the Date field in a table. Lets say we have a table SAMPLE_ORDERS and it has a field ORDER_DATE. Also, lets say we have 5 years of history in this table. Then, we can create partitions by date for, lets say, every quarter.

So Every Quarter Data becomes a partition in the SAMPLE_ORDER table. The first partition will be the one with the lowest bound and the last one will be the Partition with the highest bound. So if we have a query that want to look at the Data of first quarter of 1999 then instead of going through the complete data it will directly go to the Partition of first quarter 1999.

This is example of the syntax needed for creating a RANGE PARTITION.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER)
PARTITION BY RANGE(ORDER_DATE)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
;

the above example basically created 8 partitions on the SAMPLE_ORDERS Table all these partitions correspond to one quarter. Partition SO99Q1 will contain the orders for only first quarter of 1999.


2. HASH Partitioning

Under this type of partitioning the records in a table, are partitions based of a Hash value found in the value of the column, that is used for partitioning. " Hash Partitioning" does not have any logical meaning to the partitions as do the range partitioning. Lets take one example.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
ORDER_ZIP_CODE)
PARTITION BY HASH (ORDER_ZIP_CODE)
(PARTITION P1_ZIP TABLESPACE TS01,
PARTITION P2_ZIP TABLESPACE TS02,
PARTITION P3_ZIP TABLESPACE TS03,
PARTITION P4_ZIP TABLESPACE TS04)
ENABLE ROW MOVEMENT;

The above example creates four hash partitions based on the zip codes from where the orders were placed.

3. List Partitioning ( Only with 9i)

Under this type of partitioning the records in a table are partitioned based on the List of values for a table with say communities column as a defining key the partitions can be made based on that say in a table we have communities like ‘Government’ , ‘Asian’ , ‘Employees’ , ‘American’, ‘European’ then a List Partition can be created for individual or a group of communities lets say ‘American-partition’ will have all the records having the community as ‘American’

Lets take one example. In fact, we will modify the same example.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
PARTITION BY LIST (SHIP_TO_STATE)
(PARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01,
PARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02,
PARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03,
PARTITION SHIP_TO_MASACHUSETTES VALUES (‘MA’) TABLESPACE TS04,
PARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
ENABLE ROW MOVEMENT;

The above example creates List partition based on the SHIP_TO_STATE each partition allocated to different table spaces.

4. Composite Range-Hash Partitioning

This is basically a combination of range and hash partitions. So basically, the first step is that the data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data.

Lets modify the above example again:

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
CUST_NAME VARCAHR2,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE)
SUBPARTITION BY HASH(CUST_NAME)
SUBPARTITION TEMPLATE(
(SUBPARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01,
SUBPARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02,
SUBPARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03,
SUBPARTITION SHIP_TO_NORTHEAST VALUES (‘MA’, ‘NY’, ‘NJ’) TABLESPACE TS04,
SUBPARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
ENABLE ROW MOVEMENT;

The above example shows that each range partition has been further sub-partitioned into smaller partitions based on the list value specified. SHIP_TO_ARIZONA is a sub-partition by a List value AZ. This partition will be present in the main partitions by range SO99Q1 etc.

5. Composite Range-List Partitioning ( Only with 9i)

This is also a combination of Range and List Partitions, basically first the data is divided using the Range partition and then each Range partitioned data is further subdivided into List partitions using List key values. Each sub partitions individually represents logical subset of the data not like composite Range-Hash Partition.

Index organized tables can be partitioned using Range or Hash Partitions

Lets modify the above partition once more.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
CUST_NAME VARCAHR2,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE)
SUBPARTITION BY LIST(SHIP_TO_STATE)
SUBPARTITION TEMPLATE(
SUBPARTITION SP1 TABLESPACE TS01,
SUBPARTITION SP2 TABLESPACE TS02,
SUBPARTITION SP3 TABLESPACE TS03,
SUBPARTITION SP4 TABLESPACE TS04,
SUBPARTITION SP5 TABLESPACE TS05)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
ENABLE ROW MOVEMENT;

With Oracle 9i, there is also a feature to create indexes on the partitions. The indexes can be:

a. Local indexes

This is created the same manner as the index on existing partitioned table. Each partition of a local index corresponds to one partition only.

b. Global Partitioned Indexes

This can be created on a partitioned or a non-partitioned tables. But for now, they can be partitioned using the " Range Partitioning" only. For example, in above example, where I divided the table into partitions representing a quarter, a " Global Index" can be created by using a different " Partitioning Key" and can have different number of partitions.

c. Global Non- Partitioned Indexes

This is no different than the ordinary index created on a non-partitioned table. The index structure is not partitioned

조금 보기 좋은 설명
http://www.orafaq.com/node/55

잘 정리해 놓은 것
http://niflheim.tistory.com/tag/oracle%20partition

more..

Partition 테이블의 기본.

Oracle-Partitioning

2007/01/26 14:35

Prologue
       
       DW하면 파티셔닝과 씨름한다. 너무나도 간단한 지식. 하지만 소홀할 수 없는 지식.
         항상 자만하는 자는 실패한다. 나태한자. 실패한다. 하나씩 쌓아나가면서
         진정한 DW Architect로 거듭나고자 한다.

SCOPE

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

Explanation

예제] 아래와 같이 partition table을 생성한다.


SQL> create table part_tbl


( in_date number primary key ,

empno number,
ename varchar2(20),

job varchar2(20) )

partition by range (in_date)

(partition part_tbl_03 values less than (20000331)

tablespace pts_03,
partition part_tbl_04 values less than (20000430)
tablespace pts_04,
partition part_tbl_05 values less than (20000531)
tablespace pts_05,
partition part_tbl_06 values less than (20000630)
tablespace pts_06,
partition part_tbl_07 values less than (20000731)
tablespace pts_07,
partition part_tbl_08 values less than (20000831)
tablespace pts_08,
partition part_tbl_09 values less than (20000930)
tablespace pts_09,
partition part_tbl_10 values less than (20001031)
tablespace pts_10 );

partition 을 add하는 방법

11월과 12월에 대해 partition을 add하고 싶은 경우 다음과 같이 할 수 있다.


SQL> alter table part_tbl add partition part_tbl_11


values less than (20001130) tablespace pts_11;

SQL> alter table part_tbl add partition part_tbl_12

values less than (20001231) tablespace pts_12;

특정 partition 을 삭제하는 방법

8월에 해당하는 partition을 없애고 싶은 경우는 다음과 같이 실행한다.


SQL> alter table part_tbl drop partition part_tbl_08;


drop된 후에 새로 8월에 해당하는 데이타가 입력되면
9월의 partition이 less then (20000930) 으로 되어 있으므로
9월에 해당하는 partition에 저장된다.

partition을 나누는 방법

1월, 2월에 해당하는 partition을 생성하려면 partition을
add하는 것으로는 불가능하고 기존의 partition에서 split 해야 한다.


SQL> 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);

위와 같이 하면 기존의 partition에서 2월29일을 기준으로 2월과 3월로
partition이 나눈다. 그리고 나서 다시 split 해야한다.


SQL> 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);

partition name을 변경하는 방법

partition name 을 바꾸고 싶다면 다음과 같이 실행한다.


SQL> alter table part_tbl rename partition part_tbl_02_1 to part_tbl_02;
SQL> alter table part_tbl rename partition part_tbl_03_1 to part_tbl_03;

partition의 tablespace를 옮기는 방법

partition part_tbl_10을 저장하는 tablespace를 pts_10 에서 pts_10_1로
바꾸고 싶은 경우 아래와 같은 command를 사용한다.


SQL> alter table part_tbl move partition part_tbl_10


tablespace pts_10_1 nologging;

특정 partition의 data를 truncate하는 방법

partition의 data를 모두 삭제하려면 truncate하는 방법을 사용할 수가
있는 데, truncate는 rollback 이 불가능하며 특정 partition 전체를
삭제하므로 주의하여 사용하여야 한다.


SQL> alter table part_tbl truncate partition part_tbl_02;

Partition table의 물리적인 속성 변경

partition table은 특정 partition의 속성만 변경할 수 있고,
table의 속성을 변경하여 전체 partition에 대해 동일한 변경을 할 수 있다.


SQL> alter table part_tbl storage( next 10M);


-> part_tbl 의 모든 partition의 next 값이 변경된다.

SQL> alter table part_tbl modify partition part_tbl_05

storage ( maxextents 1000 );

-> part_tbl_05 partition의 maxextents 값만 변경한다.

Index의 관리

위와 같이 partition table 관련 작업을 한 후에는 table에 걸려 있는
local(partitioned) index 나 global index를 반드시 rebuild해 주어야 한다.


특정 partition의 index를 rebuild 하려면


SQL> alter index ind_part_tbl rebuild partition i_part_tbl_02;


그리고 global index를 rebuild하려면


SQL> alter index part_tbl_pk rebuild;

샘플?
http://asktom.oracle.com/pls/asktom/f?p=100:11:1619565279119772::::P11_QUESTION_ID:4636779130376

오라클 도움말
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm

문법 설명(발췌)
http://www.ss64.com/ora/table_a_part.html