파티션 테이블(partition table)

파티션 테이블(partition table)

 

파티션 테이블 설계

대용량 테이블의 성능저하를 방지하고 관리를 수월하게 하고자 파티션 테이블을 고려할 수 있는데, 서로 다른 파티션에 데이터를 저장함으로써 노드 간의 디스크 경합을 최소화하여 성능을 향상한다.

 

□ 파티션 테이블의 장단점

장점	- 디스크 장애시 해당 파티션만 영향을 받으므로 데이터의 훼손 가능성이 감소하고 가용성이 향상  - 개별 Partition 단위의 관리가 가능 (DML, Load, Import, Export, Exchange 등)  - 조인시 파티션 간의 병렬 처리 및 파티션 내에서의 병렬 처리를 수행    - 데이터 액세스 범위를 줄여 성능을 향상 시키고 테이블의 파티션 단위로 디스크의 I/O를 분산해 부하를 감소
단점	- 파티션 키 값 변경에 대한 별도 관리 필요 ( 관리가 불편 )  - 파티션에 기준이 되는 것이 컬럼의 일부일 때 일부를 기준으로 파티션을 구성할 수 없으므로 이에 해당하는 오버헤드 컬럼이 있어야 함.

 

□ 파티션의 종류와 특징

파티션 종류	내용
RANGE	- 일, 월, 분기 등 특정 컬럼의 정렬 값을 기준으로 분할하는 방식으로 논리적인 범위의 분산에 효율적  - 관리가 용이하며 이력 데이터에 적합  - 파티션을 결정하는 컬럼을 명시하여야 하며 MAXVALUE값은 NULL값을 포함  - 범위가 포함하는 데이터의 양이 일정하지 않은 경우 특정 파티션에 대해 데이터가 편중될 수 있음
HASH	- 데이터의 균등 분할을 통해 성능을 향상 시키고자 하는 경우에 효율적  - 파티션 키에 해시함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장해 두는 방식  - Row와 파티션 간의 매핑을 사용자가 제어할 수 없음  - 파티션 키의 해시 값에 의해 데이터가 다수의 파티션에 분배되며 균등한 분배를 위해서는 파티션 개수를   명시하여야 하며 파티션의 수는 2의 거듭제곱의 수로 지정  - NULL값은 첫 번째 파티션에 위치함  - 고객ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 키 컬럼으로 선정해야 효과적
LIST	-  파티션 컬럼을 명시, 키 컬럼 값을 기준으로 파티션하는 방식 컬럼의 구체적인 값들에 대해 파티션을 명확하게    컨트롤하고자 할 때 효율적  -  연관되지 않은 데이터, 순서에 맞지 않는 데이터의 그루핑을 쉽게 할 수 있음  -  명시되지 않은 값을 가진 Row는 Insert가 불가능  -  여러 컬럼으로 파티션 키를 생성할 수 없고 오직 하나의 컬럼만 가능  -  각 파티션에 대해 모든 파티션 키는 반드시 문자로 LIST 되어야 하며 파티션값의 List는 4K까지 가능  -  파티션 키의 값은 64K-1을 초과할 수 없고 NULL 값을 포함한 어떠한 값이라도 한 번만 명시 가능
COMPOSITE	- Range + List, Range + Hash 파티션 등의 조합으로 구성  - 이력 데이터와 온라인 데이터의 복합적인 성격을 지닌 데이터의 분할에 용이하며 병렬 DML 작업에 뛰어난   수행성능을 보장  - 파티션 및 서브 파티션 단위의 관리 작업 수행이 가능  - Hash 파티셔닝의 경우 스토리지 스트라이핑으로 인해 디스크 점핑이 발생할 수 있으므로 충분한 검토 후 적용
INTERVAL	- RANGE 파티셔닝과 유사하며 파티션이 추가되는 규칙을 지정하는 방식  - 기존 파티션에 데이터가 있고 새로운 데이터가 입력될 때에만 새로운 파티션을 생성함  - RANGE 파티션에서 MAXVALUE 파티션 없이 생성 후 데이터가 추가되면 지정된 INTERVAL 만큼 늘어난    범위를 가지는 파티션이 생성됨
REFERENCE	- 자식 테이블 파티션이 부모 테이블 파티션과 일대일 관계인 환경에서 자식테이블을 파티션할 때 적용  - 기본 키 – 외래 키 관계를 통해 자식 테이블의 파티셔닝을 부모 테이블로부터 상속  - 파티셔닝 키는 자식 테이블의 실제 컬럼에 저장되지 않음

* RANGE > LIST > HASH 순으로 많이씀

 

 

□ 테이블 선정 조건

 - 일부 데이터가 손상되더라도 나머지 데이터 사용이 가능해야 하는 테이블

 - 복구를 최대한 빨리 적용해야 하는 테이블

 - 테이블을 크기가 큰 경우

 

□ 파티션 키 선정 조건

   데이터가 어떤 파티션에 저장되는지 알 수 있는 Range Partitioning을 사용하는 것이 Hash Partitioning을 사용하는 경우에 비해

   관리측면에서 유리한 점이 많다. Range Partitioning 에서의 Load Balancing은 파티션 키에 의존하므로 파티션 키 선정시 이를 고려해야 함.

   여러 파티션에 대한 조회는 한 테이블로 구성하였을 경우보다 떨어진다. 따라서 파티션으로 구성하였을 경우 파티션의 기준이되는 키를 잘 구성해야한다

 

   - Primary Key 혹은 Primary Key의 첫번째 컬럼  -- > * 파티셔닝 의미없음

   - 매일 생성되는 날짜 컬럼

   - 백업 기준이 되는 날짜 컬럼

   - 자주 조인이 일어나는 테이블의 Foreign Key

   - Partition간 이동이 없는 컬럼

   - OLTP에서 자주 SQL구문에서 사용되는 컬럼

   - I/O 병목을 줄일 수 있는 데이터 분포도가 양호한 컬럼

 

□ 파티션 개수 결정

  - 검색조건에서 Composite Primary Key의 일부분만을 사용할 경우 Partition의 개수가 적은 것이 속도가 더빠름

    그러나 Composite Primary Key의 전체를 사용할 경우는 Partition 의 개수가 많은 것이 속도가 더 빠름

  - Batch, OLAP 작업일 경우는 partition 개수가 적은 거이, OLTP작업일 경우는 Partition 개수가 많은 것이  Performance가 더 우수

  - 실제 테이블을 구성함에 있어 Partition의 개수가 너무 적을 경우 Partition효과를 볼수 없게 되고 너무 많을 경우 각 Partition의 value range를

   체크 하므로 parsing time이 길어지고 관리대상이 많아지는 단점이 있음.

 

 

RANGE PARTITION

--업무 쿼리(예를들어 업무때 사용하는 쿼리가 이렇다 치면)
select order_id, product_id
 from ORDER_ITEMS
where to_char(order_date,'yyyymm') between 200801 and 2000804;

* 결과가 너무 많이 나와 관리하기 불편하다.

이때 파티션을 나눠 관리하며 보통 월단위로 많이 나눈다.

 

select * from order_items;

select /*+ parallel(a 16) */ to_char(order_date, 'yyyy'), count(*)
  from order_items a
 group by to_char(order_date, 'yyyy')
 order by to_char(order_date, 'yyyy');

 

--가상컬럼 추가하여 테이블생성
 create table order_item_pt_rg (
    ORDER_ID    VARCHAR2(17),
    PRODUCT_ID  VARCHAR2(7),
    ORDER_DATE  DATE,
    UNIT_PRICE  NUMBER,
    QUANTITY    NUMBER,
    NEW_ORDER_DATE VARCHAR2(10) GENERATED ALWAYS AS (to_char(order_date, 'yyyymm'))
)
tablespace users
partition by range(NEW_ORDER_DATE) (
partition p2007 values less than('20080101'),
partition p2008 values less than('20090101'),
partition p2009 values less than('20100101'),
partition p2010 values less than('20110101'),
partition p2011 values less than('20120101'),
partition p2012 values less than('20130101'),
partition pmax  values less than(maxvalue)
);

 

-- DATA INSERT
ALTER TABLE order_item_pt_rg NOLOGGING; -- 빠른 INSERT를 위해 설정

INSERT INTO order_item_pt_rg
SELECT * FROM ORDER_ITEMS;
-- ORA - 00947 : 값의 수가 충분하지 않습니다. 발생
-- ( 컬럼 수가 맞지 않아서 발생 )

INSERT /*+ PARALLEL (A 4) APPEND */    -- NOLOGGING 일때 INSERT할때 APPEND하는게 좋음
  INTO order_item_pt_rg A (ORDER_ID,
                           PRODUCT_ID,
                           ORDER_DATE,
                           UNIT_PRICE,
                           QUANTITY)
SELECT /*+ PARALLEL (B 4) */ *
  FROM ORDER_ITEMS B;

 

-- 데이터 조회
desc order_items;

 

select /*+ PARALLEL (A 4) */ sum(QUANTITY)
  FROM ORDER_ITEMS A
 WHERE TO_CHAR(ORDER_DATE,'YYYY') BETWEEN '2008' AND '2009';

 

 

SELECT /*+ PARALLEL (A 4) */ sum(QUANTITY)
  FROM ORDER_ITEMS A
 WHERE NEW_ORDER_DATE BETWEEN TO_DATE(2008,'YYYY') AND TO_DATE(2009,'YYYY');
 
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'))

 

PARTITION ADMIN

-- 파티션 별 DML
SELECT * FROM order_item_pt_rg PARTITION (P2007);

INSERT INTO order_item_pt_rg PARTITION (PMAX)
(ORDER_ID,PRODUCT_ID,ORDER_DATE, UNIT_PRICE, QUANTITY)
VALUES ('C00150235','C084', SYSDATE, 999, 9);

 

-- PARTITION에 맞지 않는 데이터 INSERT
INSERT INTO ORDER_ITEM_PT_RG PARTITION (PMAX)
(ORDER_ID,PRODUCT_ID,ORDER_DATE, UNIT_PRICE, QUANTITY)
VALUES ('C00150235','C084', TO_DATE(201101,'YYYYMM'), 999, 9);
-- ORA-14401:삽입된 분할영역 키는 지정된 분할영역의 밖에 있습니다.
-- 키 값이 맞지 않아 INSERT 불가능

 

-- KEY COLUMN UPDATE
UPDATE order_item_pt_rg
   SET ORDER_DATE = SYSDATE
 WHERE PRODUCT_ID='P183';   -- 2008년 데이터
 -- ORA-14402 : 분할영역 키 열을 수정하는것은 분할영역 변경이 생깁니다.
 
SELECT TABLE_NAME,ROW_MOVEMENT
  FROM DBA_TABLES
 WHERE TABLE_NAME='ORDER_ITEM_PT_RG';

 

TABLE_NAME           ROW_MOVE
-------------------- --------
ORDER_ITEM_PT_RG  DISABLED

 

-- ROW MOVEMENT ENABLE
ALTER TABLE ORDER_ITEM_PT_RG ENABLE ROW MOVEMENT;
ALTER TABLE ORDER_ITEM_PT_RG DISABLE ROW MOVEMENT;

 

 

-- PARTITION DROP
ALTER TABLE ORDER_ITEM_PT_RG DROP partition p2007;

 

SELECT * FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME='ORDER_ITEM_PT_RG'

 

 

-- PARTITION SPLIT
ALTER TABLE ORDER_ITEM_PT_RG SPLIT partition PMAX
AT ('20140101')
INTO ( PARTITION P2013, PARTITION PMAX);

 

INSERT INTO ORDER_ITEM_PT_RG
(ORDER_ID, PRODUCT_ID, ORDER_DATE, UNIT_PRICE, QUANTITY)
VALUES ('AAAA','H111',TO_DATE(201311,'YYYYMM'),1,1);

commit;

 

SELECT * FROM ORDER_ITEM_PT_RG PARTITION(P2013);

 

 

 

 

-- PARTITION ADD
ALTER TABLE ORDER_ITEM_PT_RG ADD partition
p2013 values less than('20140101');
* ADD하는 FORM은 이게 맞지만 현재 구성은 PMAX로 해놓아서 추가가 되지 않음 --> SPLIT 해야함

 

-- PARTITION ADD
ALTER TABLE ORDER_ITEM_PT_RG ADD partition
p2013 values less than('20140101');

 

HASH PARTITION

SELECT * FROM DBA_DATA_FILES;

CREATE TABLESPACE TBS1 DATAFILE
'/oracle11/app/oradata/testdb/tbs1.dbf' SIZE 100M AUTOEXTEND ON;

CREATE TABLESPACE TBS2 DATAFILE
'/oracle11/app/oradata/testdb/tbs2.dbf' SIZE 100M AUTOEXTEND ON;

CREATE TABLESPACE TBS3 DATAFILE
'/oracle11/app/oradata/testdb/tbs3.dbf' SIZE 100M AUTOEXTEND ON;

CREATE TABLESPACE TBS4 DATAFILE
'/oracle11/app/oradata/testdb/tbs4.dbf' SIZE 100M AUTOEXTEND ON;

alter user system quota unlimited on tbs1;
alter user system quota unlimited on tbs2;
alter user system quota unlimited on tbs3;
alter user system quota unlimited on tbs4;

 

-- CRAETE TABLE PARTITION 이름이 랜덤 설정 ( 아래 그림 참조 )

CREATE TABLE CUSTOMERS_PT_HS
(
    CUSTOMER_ID     VARCHAR2(11),
    CUST_FIRST_NAME VARCHAR2(20) NOT NULL,
    CUST_LAST_NAME  VARCHAR2(20) NOT NULL,
    EMAIL           VARCHAR2(92),
    BIRTHDAY        DATE,
    PHONE_NUMBER    VARCHAR2(325),
    GENDER          VARCHAR2(1),
    WEDDING_TYPE    VARCHAR2(1),
    CUST_JOB_NAME   VARCHAR2(12)
)
PARTITION BY HASH(CUSTOMER_ID)
PARTITIONS 4
STORE IN (TBS1, TBS2, TBS3, TBS4);

 

-- 파티션 이름을 설정하면서 CRATE TABLE

CREATE TABLE CUSTOMERS_PT_HS2
(
    CUSTOMER_ID     VARCHAR2(11),
    CUST_FIRST_NAME VARCHAR2(20) NOT NULL,
    CUST_LAST_NAME  VARCHAR2(20) NOT NULL,
    EMAIL           VARCHAR2(92),
    BIRTHDAY        DATE,
    PHONE_NUMBER    VARCHAR2(325),
    GENDER          VARCHAR2(1),
    WEDDING_TYPE    VARCHAR2(1),
    CUST_JOB_NAME   VARCHAR2(12)
)
PARTITION BY HASH(CUSTOMER_ID)
(PARTITION P1 TABLESPACE TBS1,
 PARTITION P2 TABLESPACE TBS2,
 PARTITION P3 TABLESPACE TBS3,
 PARTITION P4 TABLESPACE TBS4
 );
 
 SELECT * FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME LIKE 'CUSTOMERS_PT%'
   AND OWNER ='SYSTEM';
 

INSERT /*+ PARALLEL(A 4) APPEND */ INTO CUSTOMERS_PT_HS2 A
SELECT /*+ PARALLEL(B 4) */ * FROM CUSTOMERS B;

COMMIT;

SELECT * FROM CUSTOMERS_PT_HS2 PARTITION(P1);
-- 어떤 기준으로 나눠진지는 모르겠음

 

LIST PARTITION

-- list partition table 생성

CREATE TABLE LOCATIONS_PT_LI
(
    LOCATION_ID     NUMBER(4),
    STREET_ADDRESS  VARCHAR2(40),
    POSTAL_CODE     VARCHAR2(12),
    CITY            VARCHAR2(30) CONSTRAINT LOC_CITY_NN CHECK ("CITY" IS NOT NULL),
    STATE_PROVINCE  VARCHAR2(25),
    COUNTRY_ID      CHAR(2)
)
    PARTITION BY LIST (STATE_PROVINCE)
    (PARTITION REGION_EAST VALUES ('MA','NY','CT','NH','MD','VA','PA','NJ')
    TABLESPACE TBS1,
    PARTITION REGION_WEST VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')
    TABLESPACE TBS2,
    PARTITION REGION_SOUTH VALUES ('TX','KY','TN','LA','MS','AR','AL','GA')
    TABLESPACE TBS3,
    PARTITION REGION_CENTRAL VALUES ('OH','ND','SD','MO','IL','MI','IA',NULL)
    TABLESPACE TBS4,
    PARTITION EXTRA VALUES (DEFAULT) TABLESPACE USERS
    );

insert into LOCATIONS_PT_LI
select * from hr.LOCATIONS

 

COMMIT;

 

SELECT * FROM LOCATIONS_PT_LI PARTITION(EXTRA);

* 조건에 맞게 PARTITION이 나눠짐