Partitioned Table

Chapter 1. 파티션 정의

1. 정의 
용량이 크거나 지속적인 데이터 증가가 예상되는 table에서 데이터에 따라 여러개의 작은 단위로 나눔으로써 성능 저하를 방지하고 관리를 수월하게 하는 방법.
  각 파티션은 column과 제약조건의 정의를 공유하며 별도의 segment에 저장되어 물리적인 속성을 다르게 지정가능.
  각각의 파티션은 독립적으로 존재하므로 각각의 파티션에 대해 독립적인 백업과 복구가 가능하다.

2. 파티션 테이블의 장점
   → 개선된 가용성
      - 각각의 파티션은 독립적으로 관리된다.
      - backup and restore를 파티션별로 작업할 수 있다.
      - 같은 테이블에서 unavailable한 파티션은 다른 파티션에 영향을 주지 않는다.

   → 관리의 용이성
      - 사용자가 지정한 값으로 파티션이 가능.
      - 테이블 스페이스간에 파티션 이동이 가능.
      - 파티션 레벨에서 select, delete, update가 가능.

   → 개선된 성능
      - 데이터를 엑세스 할 때 엑세스 하는 범위를 줄여 퍼포먼스 향상을 가져올수 있음.
      - RAC 환경에서 인스턴스간 block contention을 감소 시킴.

3. 파티션 테이블 사용시 주의 점
   → 관리적인 관점
      - 하나의 테이블을 세분화해 관리하기 때문에 보다 세심한 관리가 요구.
      - 파티션을 잘못 구성 또는 관리하여 index unusable에 빠지는것을 주의해야 함.
   → 사용하는 관점
      - 파티션 키로 나누어져 있는 table에 파티션 키를 조건으로 주지 않아 
        전체 파티션을 엑세스하지 않도록 주의 해야함

4. 파티션 테이블의 특징
   → 파티션 테이블은 파티션 키 값에 의해 구성되며, 한테이블 당 가능한 파티션은 이론적으로 65535개를
      지원하나 실질적으로는 10000개 까지만 생성 가능하다.

   → 모든 파티션 테이블(또는 인덱스)는 같은 logical attribute를 가져야한다.
      ex) columns, data types, constraints ...

   → 모든 파티션 테이블(또는 인덱스)는 다른 physical attribute를 가져야한다.
      ex) pctfree, pctused, inittrans, maxtrans, tablespace, sotrage ...

   → 파티션 테이블은 'KEY', 'VALUES LESS THAN Literal', 'physical attribute'로 구성된다.

   → 'VALUES LESS THAN Literal' 절에서 'Literal' 값에는 SQL Function을 지원한다.

   → Composite Column 구성은 16개까지 가능하다.
 

5. 파티션 테이블의 종류
Oracle 8.0 
   - range partition

Oracle 8i 
   - hash partition
   - composite partition

Oracle 9i 
   - list partition

Oracle 10g 
   - IOT partition

Oracle 11g 
   - Composite Partition에서 확장된 Extended Composite Partition이 지원. 
     -> Range-Range, List-Range, List-Hash, List-List 
   - Reference Partition 추가 
   - Interval Partition 추가 
   - System Partition 추가 
   - Virtual Column Partition 추가

 

6. 파티션과 관련된 dictionary table
select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE, PARTITION_POSITION
from user_tab_partitions
where table_name='TEST';

 

select table_name, partitioning_type, subpartitioning_type, partition_count
from user_part_tables
where table_name='TEST';

 

select table_name, partition_name, subpartition_name, tablespace_name, num_rows
from user_tab_subpartitions
where table_name='TEST';

Chapter 2. 파티션 종류

1. range partition
   - column value의 범위를 기준으로 행을 분할하는 형태.
   - rage partition에서 table은 단지 논리적인 구조이며 실제 데이터가 물리적으로 저장되는곳은
     partition으로 나누어진 tablespace에 저장.
   - partition by range ( column_list ) : 기본 table에서 어느 column을 기준으로 분할 할지 정함.
     values less than ( value_list )    : 각 partition이 어떤 값의 범위를 포함 할지 upper bound를 정함.

 

2. hash partition
   - partitioning column의 partitioning key 값에 hash 함수를 적용하여 data를 분할하는 방식.
   - 데이터 이력 관리의 목적 보다 성능 향상의 목적으로 나온 개념.
   - hash partition은 range partition에서 범위를 기반으로 나누었을 경우 특정 범위에 분포도가 몰려서
     각기 size가 다르게 되는 것을 보완하며, 일정 분포를 가진 파티션으로 나누고 균등한 데이터 분포도를 이용한
     병렬처리로 퍼포먼스를 보다 향상 시킬 수 있다.
   - hash partition에서 table은 단지 논리적인 구조이며 실제 데이터가 물리적으로 저장되는 곳은 
     partition으로 나누어진 tablespace에 저장된다.
   - 파티션의 수를 2의 제곱근(2,4,8,16...)으로 설정해야하며 data가 어떤 파티션에 저장 되는지 알수없다.(조회시가능)
   - NULL 값은 첫 번째 파티션에 위치하게 됨.
      
3. composite(SUB) partition
   - 파티션의 컬럼을 main-sub 관계로 나누어 분할하는 방식.
   - 다른 파티션에서 물리적인 데이터가 저장되는 곳은 table이 아닌 partition table에 저장되는 것처럼, 
     composite partition에서는 main partition이 아닌 sub partition에 저장된다,
   - composite partition의 조합 구성은 oracle의 버전이 올라갈수록 더욱 다양하게 지원한다.
     8i  : range + hash
     9i  : range + hash , range + list
     10g : range + hash , range + list
     11g : range + hash , range + list , range + range , list + range , list + hash, list + list

 

4. list partition
   - partitioning column의 특정 값으로 분할하는 방식
   - 데이터 분포도가 낮지 않고, 균등하게 분포되어 있을 때 유용.
   - composite partition에서 'range-list'일 경우 그 효울이 더욱 높아짐.
   - 다른 파티션 방식처럼 다중 컬럼을 지원하지 않고 단일 컬럼만 가능함.
   - 대소문자를 구분 함, 범위외 문자 입력 에러 발생.
   - Partition key 값은 NULL 값 또한 명시 가능하며, NULL 값을 포함한 어떠한 값이라도 한번만 명시할 수 있음.
  

5. reference partition
   - reference key로 지정된 경우 부모 테이블의 컬럼이 존재하지 않아도 부모 partition key로 분할하는 방식.
   - 제약조건
     - foreign key 제약 조건이 설정되어 있어야 한다.
     - 상속받는 테이블의 key값이 not null 이어야 한다.

6. interval partition
   - range partition에서 특정 범위를 지정하고 관리할때는 미리 range를 만들어주어야 하고 
     생성 이후 분할 또는 병합을 할 때는 추가적인 작업을 해주어야 한다.
   - interval partition에서는 각 파티션을 미리 정의함으로써 파티션 생성을 오라클이 직접 해주는 방식임.
   - 파티션을 특정 tablespace에 저장하고 싶다면 store in 구문으로 가능함.
     ex) interval (numtoyminterval(1, 'MONTH')) store in (TS1, TS2, TS3)

 

7. system partition
   - 테이블 생성시 파티션 구간을 미리 설정하는 것이 아니라 임의로 나눈 파티션에 대해 
     사용자가 원하는 파티션에 데이터를 저장하는 방식.
   - 사용자가 'system partition'으로 되어 있는 테이블에 DML 하고자 할 때 직접 파티션을 지정해 주어야 함.
     - insert는 반드시 파티션을 지정해야 한다. 
     - delete, update 할 경우 필수는 아니나 지정하지 않을 경우 모든 파티션을 검색 한다.
   - 로컬 인덱스 생성 시, 인덱스도 동일한 방법으로 파티셔닝 됨.

 

8. virtual column partition
   - 파티션으로 나누고자 하는 컬럼이 테이블에서 가공되어 얻을 수 있는 컬럼일 경우:
     - 11g 이전에서는 새로운 컬럼을 추가하고 트리거를 이용하여 컬럼 값을 생성하는 방법을 사용하여 많은 오버헤드를 감수하였다.
     - 11g에서는 virtual column partition을 지원하여 실제로 저장되지 않은 컬럼을 런타임에 계산하여 생성. 또한 가상 컬럼에 파티션을 적용하는것도 가능하다.

Chapter 3. Partition index

1. local index

   - 인덱스를 생성한 인덱스와 파티션된 인덱스가 동일하게 파티션된 형태를 말한다.
   - 인덱스와 테이블은 같은 컬럼에 의해 파티션 되며, 하나의 인덱스 파티션이 테이블 파티션 하나와 대응된다.
     대응되는 인덱스 파티션과 테이블 파티션은 각각 같은 범위를 갖게 된다.
   - 결국 특정한 하나의 인덱스에 포함된 모든 key들은 하나의 테이블 파티션 내의 데이터만을 가리키게 된다.

   1) local prefixed index
        - 인덱스의 맨 앞에 있는 컬럼에 의해 파티션 되는 방식.
        - Local Prefixed index에서 컬럼은 Unique/Non=Unique를 모두 허용.
        - base table의 파티션이 변경되면 local index의 관련 파티션만 변경.

   2) local non-prefixed index
        - index의 첫번째 column이 partition key가 아닌 형태로 
          base table과 동일한 partition구조를 가진 index. (equi-partitioned)
        - 빠른 access가 요구 될 때 유용. (base table의 partition key는 제외.)
        - partition 단위로 관리 할 수 있으므로 global index에 비해 운영상 편리.
        - OLAP 측면에서 global index보다 조회 속도가 저하.

2. global index
   - 테이블과 다르게 파티션 되는 경우.
   - 파티션 테이블의 파티션 수와 인덱스 파티션의 파티션 수가 일치하지 않음.
   - 파티션 테이블의 파티션 키와 파티션 인덱스의 인덱스 키도 일치하지 않음.
   - 단점 : 테이블의 일부 파티션에 작업을 수행하는 경우 모든 인덱스 파티션에 영향을 주게됨.

 

   1) global prefixed index
        - base table과 비교하여 not equi-partitioned 상태임.
        - oracle은 only index structure만 관리.(partition은 관리안함)
        - 최종 partition에는 maxvalue값이 반드시 기술되어야 함.
        - local index보다 관리하기가 힘듦
        - 기준 table의 partition이 변경되면 global index의 모든 partition에 영향을 미침.
          (global index의 재생성을 해야함)

 

   2) non-partitioned index
        - 파티션과는 아무런 상관없는 normal index를 말함.

 

chapter 4. 파티션을 사용할 때 알아야 할 사항들

1. 파티션 테이블 및 인덱스 관리

   1) 일반 테이블 파티션
      - Export/Import 하는 방법
      - Subquery를 이용한 방법
      - Partition Exchange 명령어를 사용하는 방법
      - 여러 개의 파티션으로 분리된 테이블 중 일부의 파티션만 가진 테이블 생성하기
      - 파티션을 추가하는 방법 
      - 특정 파티션을 삭제하는 방법
      - 파티션을 나누는 방법 
      - 파티션 이름을 변경하는 방법
      - 파티션의 테이블스페이스를 옮기는 방법
      - 특정 파티션의 데이터를 Truncate 하는 방법
      - 파티션 테이블의 물리적인 속성 변경하는 방법
      - 인덱스 관리

 

2. Backup & Recovery

   1) Export 
      - Table-Level Export
      - Partition-Level Export
      - 두 가지 경우를 Level을 혼용하여 사용하는 것도 가능.

 

   2) Import 
      - Table-Level Import
      - Partition-Level Import
        파티션되어 있지 않은 테이블을 Exp, Imp를 이용한 파티션.
        Partitioned Table의 Partition들을 exp/imp를 이용하여 Merge하는 파티션.

 

3. IU(Index Unusable) 발생 주의

※ IU(Index Unusable)란 파티션이 변경됨으로 인해 파티션 테이블에 있는 인덱스에 영향을 주어 SELECT나 DML을 시도할 때 오류가 발생되는 것을 말한다.

   1) IU(Index Unusable)를 발생시키는 Case
       - Direct Path Load의 경우
       - ROWID가 변경되는 경우 
       - ROWID를 지우는 작업
       - 테이블 Partition 정의를 변경하는 경우
       - 인덱스 Partition 정의를 변경하는 경우

   2) IU 상태가 되면 다음과 같이 조치
       - Partition Index : Rebuild 
       - Non-Partition Index : Drop and Recreate

 

chapter 5. 파티션 테이블 테스트

실습환경

HR의 employees table을 일부 변형 시켜서 test2 테이블에 생성시킴.
실제 test는 test 테이블에서 함.

##### partition 생성. range 기본 틀
##### 세부적인 설정시 tablespace 설정은 사용되며 실습에서는 사용하지 않았음

 

## 기본적인 생성 구문

CREATE TABLE [ table name ] 
(
        COLUMN_1 NUMBER NOT NULL, 
        .
        . 
)
TABLESPACE [ tablespace name ] 
PCTFREE 5                          // block 설정
PCTUSED 40
INITRANS 11
MAXTRANS 255
STORAGE       // extent 설정. ASSM이 나오면서 그닥 신경을 안쓰는 부분임.
(
        INITIAL 2048K 

        NEXT 1024K 

        PCTINCREASE 0

        MINEXTENTS 1 

        MAXEXTENTS 121 

)

PARTITION BY RANGE ( COLUMN_3, COLUMN_4, COLUMN_5 )          // range는 1개 이상
( 
        PARTITION P_200801 VALUES LESS THAN ('2008', '07', '01' ),
        .
        .
)

TABLESPACE TABLE_SPACE_DATA_2   
PCTFREE 5
PCTUSED 40
INITRANS 11   
MAXTRANS 255 
STORAGE 
(  
        INITIAL 1M 
        NEXT 1M 
        PCTINCREASE 0
        MINEXTENTS 1 
        MAXEXTENTS 121 
);

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
range partition
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
create table test
(
        employee_id number,
        department_id number,
        name varchar2(20),
        hire_date date,
        salary number
)
partition by range (department_id)
(
        partition p10 values less than(11),
        partition p20 values less than(21),
        partition p30 values less than(31),
        partition p40 values less than(41),
        partition p50 values less than(51),
        partition p60 values less than(61),
        partition p70 values less than(71),
        partition p80 values less than(81),
        partition p90 values less than(91),
        partition p100 values less than(101),
        partition p110 values less than(111)
);

━━━━━━━━━━━ dictionary 확인
SQL> col table_name for a10
SQL> col partition_name for a10
SQL> col high_value for a10

SQL> select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE, PARTITION_POSITION, NUM_ROWS
from user_tab_partitions
where table_name='TEST';

TABLE_NAME PARTITION_ TABLESPACE_NAME                HIGH_VALUE PARTITION_POSITION
---------- ---------- ------------------------------ ---------- ------------------
TEST       P10        TEST                           11                          1
TEST       P20        TEST                           21                          2
TEST       P30        TEST                           31                          3
TEST       P40        TEST                           41                          4
TEST       P50        TEST                           51                          5
TEST       P60        TEST                           61                          6
TEST       P70        TEST                           71                          7
TEST       P80        TEST                           81                          8
TEST       P90        TEST                           91                          9
TEST       P100       TEST                           101                        10
TEST       P110       TEST                           111                        11

━━━━━━━━━━━ data insert
========= 실행되는 경우
SQL> insert into test partition(p10) select * from test2 where department_id=10;

SQL> insert into test select * from test2 where department_id in(20,30);

SQL> insert into test values(199,10,'GEE','03-03-03',3000);

SQL> insert into test select * from test2 where department_id like '%';

#### 위에 첫번째, 두번재는 partition key값에 대응하는 department_id의 값을 정해주어서 자동적으로 입력되었다.
#### 세번째 것은 단순히 insert하는 경우이지만 p10 partition으로 자동 입력 되었다.

========= 실행되지 않는 경우
SQL> insert into test select * from test2;

#### test talbe은 partition table이기 때문에 일반 table인 test2의 data가 삽입이 안된다.

SQL> insert into test partition(p10) select * from test2;

#### 이 방법도 역시 되지 않는다.

━━━━━━━━━━━ data 조회
========= 실행되는 경우
SQL> select * from test partition(p10);

EMPLOYEE_ID DEPARTMENT_ID NAME                 HIRE_DAT     SALARY
----------- ------------- -------------------- -------- ----------
        200            10 Jennifer Whalen      87/09/17       4400
        199            10 GEE                  03/03/03       3000

========= 실행되지 않는 경우
SQL>  select * from test partition(p10,p20,p30);

ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다

#### insert와 다르게 다중으로 사용해도 안되는것을 확인.

━━━━━━━━━━━ data update
SQL> update test set name='GEE W.G' where name='GEE';

SQL> select * from test partition(p10);

EMPLOYEE_ID DEPARTMENT_ID NAME                 HIRE_DAT     SALARY
----------- ------------- -------------------- -------- ----------
        200            10 Jennifer Whalen      87/09/17       4400
        199            10 GEE W.G              03/03/03       3000

SQL> update test partition(p10) set name='GEE' where name='GEE W.G';
SQL>  select * from test partition(p10);

EMPLOYEE_ID DEPARTMENT_ID NAME                 HIRE_DAT     SALARY
----------- ------------- -------------------- -------- ----------
        200            10 Jennifer Whalen      87/09/17       4400
        199            10 GEE                  03/03/03       3000

━━━━━━━━━━━ data delete
SQL> delete from test where name='GEE';
SQL> delete from test partition(p10) where name='GEE';

##### 해당 파티션의 모든 data 삭제.
SQL> delete from test partition(p10);
SQL> delete test partition(p10);

##### 해당 파티션의 모든 data truncate.
SQL> alter table test truncate partition "P120";

━━━━━━━━━━━ partition 추가
SQL> alter table test add partition "p120" values less than(121);
SQL> alter table test add partition "p130" values less than(maxvalue) tablespace test2;

SQL> select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE, PARTITION_POSITION, NUM_ROWS
from user_tab_partitions
where table_name='TEST';

TABLE_NAME PARTITION_ TABLESPACE_NAME                HIGH_VALUE PARTITION_POSITION   NUM_ROWS
---------- ---------- ------------------------------ ---------- ------------------ ----------
TEST       P10        TEST                           11                          1          2
TEST       P20        TEST                           21                          2          0
TEST       P30        TEST                           31                          3          0
TEST       P40        TEST                           41                          4          0
TEST       P50        TEST                           51                          5          0
TEST       P60        TEST                           61                          6          0
TEST       P70        TEST                           71                          7          0
TEST       P80        TEST                           81                          8          0
TEST       P90        TEST                           91                          9          0
TEST       P100       TEST                           101                        10          0
TEST       P110       TEST                           111                        11          0
TEST       P120       TEST                           121                        12
TEST       P130       TEST2                          MAXVALUE                   13

SQL> alter table test add partition "P140" values less than(141) tablespace test3;
ORA-14074: 분할영역 유지 작업에 분할영역 범위가 너무 적습니다

#### High_value 값을 maxvalue로 주게 되면 이후에 새로운 partition을 추가할수 없다.

━━━━━━━━━━━ partition 제거
SQL> alter table test drop partition P130;
SQL> alter table test drop partition "P130";

#### 일반 테이블과 다르게 drop을 해도 recyclebin에 남지 않는다.
#### 파티션 삭제는 range, list partition만 가능.
#### 반드시 하나의 파티션은 남아있어야 한다.
#### 한번에 하나의 파티션만 삭제 가능.

━━━━━━━━━━━ partition 이름 변경
SQL> alter table test rename partition P120 to P121;
SQL> alter table test rename partition "P120" to "P121";

━━━━━━━━━━━ partition의 tablespace 변경
SQL> alter table test move partition P120 tablespace test2;
SQL> alter table test move partition P120 tablespace test2 [logging, nologging];

━━━━━━━━━━━ partition Merge
SQL> alter table test merge partitions P110, P120 into partition "P110~120";
SQL> alter table test merge partitions "P110", "P120" into partition "P110~120";

SQL> alter table test merge partitions "P110", "P120" into partition "P110~120" local indexs;
#### local indexs를 갱신
#### merge partitions 뒤에 p110은 p120보다 hige_value 값이 높으면 안된다. 낮은순 , 높은순

━━━━━━━━━━━ partition Split
#### hash partition, sub partition은 split 할수 없다.

SQL> alter table test split partition "P110~120" at (111)           // (111)은 분할할 기준이 되는 값
        into (partition P110 ,partition P120);

SQL> alter table test split partition "P110~120" at (111)
        into (partition P110 tablespace TEST, partition P120 tablespace test2)

━━━━━━━━━━━ partition Switch
#### 일반 table과 partition table의 컬럼 및 형식이 동일해야함.
#### 일반 table에서 partition table로, partition table에서 일반 table로 data를 이동시킴.

#### 하나의 명령어로 서로 이동함.
SQL> alter table test exchange partition P10 with table test3;
// 이 명령어 전에 test3 table을 test table과 동일한 형식으로 생성한다.

SQL> select * from test3;
EMPLOYEE_ID DEPARTMENT_ID NAME                 HIRE_DAT     SALARY
----------- ------------- -------------------- -------- ----------
        200            10 Jennifer Whalen      87/09/17       4400
        199            10 GEE                  03/03/03       3000

SQL> select * from test partition(p10);
선택된 레코드가 없습니다.

SQL> alter table test exchange partition P10 with table test3;

SQL> select * from test3;

선택된 레코드가 없습니다.

SQL> select * from test partition(p10);

EMPLOYEE_ID DEPARTMENT_ID NAME                 HIRE_DAT     SALARY
----------- ------------- -------------------- -------- ----------
        200            10 Jennifer Whalen      87/09/17       4400
        199            10 GEE                  03/03/03       3000

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
hash partition
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

━━━━━━━━━━━ hash partition 생성
########## case 1)
create table test(
        employee_id number,department_id number,
        name varchar2(20),
        hire_date date,
        salary number
)
partition by hash(department_id)
partitions 4
store in(test, test2, test3, test4);                   // tablespace name

########## case 2)
create table test2
(
        employee_id number,
        department_id number,
        name varchar2(20),
        hire_date date,
        salary number
)
partition by hash(department_id)
(
        partition ex1 tablespace test,
        partition ex2 tablespace test2,
        partition ex3 tablespace test3,
        partition ex4 tablespace test4
);

## 이와같은 직접지정 방식이 있으나 hash 파티션은 기본적으로 해당 row가 어느 tablespace에 저장되는지 
## 알수없기 때문에 무의미하다고 할수도 있다.

━━━━━━━━━━━ hash partition insert
SQL> insert into test select * from test3;             // range partition과는 다르게 입력이 된다.
SQL> insert into test2 select * from test3;
SQL> commit;

SQL> BEGIN
        DBMS_STATS.GATHER_TABLE_STATS('ORCL','TEST',CASCADE => TRUE);
        DBMS_STATS.GATHER_TABLE_STATS('ORCL','TEST2',CASCADE => TRUE);
        END;
        /
##### 통계 정보를 생성해야 user_tab_partitions 딕셔너리를 조회햇을때 num_rows의 수를 알 수 있다.

SQL> select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE, PARTITION_POSITION, NUM_ROWS
        from user_tab_partitions
        where table_name in('TEST','TEST2');

TABLE_NAME PARTITION_ TABLESPACE_NAME                HIGH_VALUE PARTITION_POSITION   NUM_ROWS
---------- ---------- ------------------------------ ---------- ------------------ ----------
TEST       SYS_P21    TEST                                                       1         87
TEST       SYS_P22    TEST2                                                      2          7
TEST       SYS_P23    TEST3                                                      3          8
TEST       SYS_P24    TEST4                                                      4          5
TEST2      EX1        TEST                                                       1         87
TEST2      EX2        TEST2                                                      2          7
TEST2      EX3        TEST3                                                      3          8
TEST2      EX4        TEST4                                                      4          5

#### 첫번째 파티션인 test tablespace로 많이 모이는것을 확인하였으며
#### department_id값을 기준으로 여러 tablespace에 흩어져 있었다. 
#### test table과 test2 table의 분포된 data는 같은 내용이었다.

#### 파티션에 분포된 department_id
SQL> select distinct(department_id) from test partition(sys_p21);
DEPARTMENT_ID
-------------
           30
   // department_id가 없는 사원이 1명 있음.
           50
           40
           80

SQL> select distinct(department_id) from test partition(sys_p22);
DEPARTMENT_ID
-------------
           70
           90
          110
           10

SQL> select distinct(department_id) from test partition(sys_p23);
DEPARTMENT_ID
-------------
          100
           20

SQL> select distinct(department_id) from test partition(sys_p24);
DEPARTMENT_ID
-------------
           60

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
list partition
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

━━━━━━━━━━━ list partition 생성

create table test
(
        employee_id number,
        department_id number,
        name varchar2(20),
        hire_date date,
        salary number
)
partition by list(department_id)
(
        partition ex1 values(10,20,30) tablespace test,
        partition ex2 values(40,50) tablespace test2,
        partition ex3 values(60) tablespace test3,
        partition ex4 values(70,80) tablespace test3,
        partition ex5 values(90,100,110) tablespace test4
);

#### partition values의 값을 여러개로 설정 가능하다는 것을 알 수 있다.

━━━━━━━━━━━ list partition insert
SQL> insert into test select * from test3 where department_id in(10);
SQL> insert into test select * from test3 where department_id in(10,20,30,40,50,60);
SQL> insert into test select * from test3 where department_id like '%';

SQL> BEGIN
        DBMS_STATS.GATHER_TABLE_STATS('ORCL','TEST',CASCADE => TRUE);
        END;
        /

SQL> select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE, PARTITION_POSITION, NUM_ROWS
        from user_tab_partitions
        where table_name='TEST';

TABLE_NAME PARTITION_ TABLESPACE HIGH_VALUE      PARTITION_POSITION   NUM_ROWS
---------- ---------- ---------- --------------- ------------------ ----------
TEST       EX1        TEST       10, 20, 30                       1          9
TEST       EX2        TEST2      40, 50                           2         46
TEST       EX3        TEST3      60                               3          5
TEST       EX4        TEST3      70, 80                           4         35
TEST       EX5        TEST4      90, 100, 110                     5         11

SQL> select distinct(department_id) from test partition(ex1);
DEPARTMENT_ID
-------------
           30
           20
           10

SQL> select distinct(department_id) from test partition(ex2);
DEPARTMENT_ID
-------------
           50
           40

SQL> select distinct(department_id) from test partition(ex3);
DEPARTMENT_ID
-------------
           60

━━━━━━━━━━━ list partition 추가
SQL> alter table test add partition ex6 values(120,130) tablespace test4 [ logging | nologging);

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
composite partition
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

━━━━━━━━━━━ Composite Range-Hash Partition
- Range 방식을 사용하여 데이터를 Partitioning하고 각각의 Partition 내에서 Hash 방식을 이용하여 Sub-Partitioning을 하는 방법으로 Range와 Hash 두 Partitioning 방식의 장점을 이용하여 만든 개념.

 

━━━━━━━━━━━ Composite Range-Hash Partition 생성 및 insert
create table test
(
        employee_id number,
        department_id number,
        name varchar2(20),
        hire_date date,
        salary number
)
partition by range(department_id)
subpartition by hash(employee_id)
subpartitions 4
store in(test, test2, test3, test4)
(
        partition ex1 values less than (31),
        partition ex2 values less than (61),
        partition ex3 values less than (91),
        partition ex4 values less than (121)
);

SQL> insert into test select * from test3 where department_id like '%';
SQL> commit;

SQL> BEGIN
        DBMS_STATS.GATHER_TABLE_STATS('ORCL','TEST',CASCADE => TRUE);
        END;
        /

SQL> select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE, PARTITION_POSITION, NUM_ROWS
        from user_tab_partitions
        where table_name='TEST';

TABLE_NAME PARTITION_ TABLESPACE HIGH_VALUE      PARTITION_POSITION   NUM_ROWS
---------- ---------- ---------- --------------- ------------------ ----------
TEST       EX1        TEST       31                               1          9
TEST       EX2        TEST       61                               2         51
TEST       EX3        TEST       91                               3         38
TEST       EX4        TEST       121                              4          8

SQL> select distinct(department_id) from test partition(ex1);
DEPARTMENT_ID
-------------
           30
           20
           10

SQL> select distinct(department_id) from test partition(ex2);
DEPARTMENT_ID
-------------
           50
           40
           60

SQL> select distinct(department_id) from test partition(ex3);
DEPARTMENT_ID
-------------
           70
           90
           80

SQL> select distinct(department_id) from test partition(ex4);
DEPARTMENT_ID
-------------
          100
          110

#### range partition 했을때와 별반 차이가 없다. 모든것이 동일하다.
#### 그렇다면 어떤면에서 range hash partition이 된것일까??

 

SQL> select rowid, employee_id, department_id from test2 partition(ex1);

ROWID              EMPLOYEE_ID DEPARTMENT_ID
------------------ ----------- -------------
AAAOCQAAGAAAAA3AAA         200            10
AAAOCQAAGAAAAA3AAB         201            20
AAAOCQAAGAAAAA3AAC         202            20
AAAOCQAAGAAAAA3AAD         114            30
AAAOCQAAGAAAAA3AAE         115            30
AAAOCQAAGAAAAA3AAF         116            30
AAAOCQAAGAAAAA3AAG         117            30
AAAOCQAAGAAAAA3AAH         118            30
AAAOCQAAGAAAAA3AAI         119            30

#### 이것은 동일한 작업을한 range partition의 ex1 partition의 rowid 이다.

SQL> select rowid, employee_id, department_id from test partition(ex1);

ROWID              EMPLOYEE_ID DEPARTMENT_ID
------------------ ----------- -------------
AAAOCZAAGAAAAAPAAA         200            10
AAAOCaAAHAAAAAPAAA         202            20
AAAOCaAAHAAAAAPAAB         115            30
AAAOCaAAHAAAAAPAAC         116            30
AAAOCaAAHAAAAAPAAD         118            30
AAAOCbAAIAAAAAPAAA         117            30
AAAOCbAAIAAAAAPAAB         119            30
AAAOCcAAJAAAAAPAAA         201            20
AAAOCcAAJAAAAAPAAB         114            30

 

#### 이것은 range partition의 ex1 partition의 rowid이다.
#### 정확히 무엇이 좋아 졌는지는 솔직히 잘 모르겠다. 하지만 sub partition으로 hash를 하며
#### rowid에서의 변화는 있다는것은 알수있다. 내부적으로 성능 향상을 꾀할수 있는 무언가가 있을것이라 생각한다.

#### 아시는분은 답글좀 ㅋ

━━━━━━━━━━━ Composite Range-Hash Partition 추가
SQL> alter table test add partition ex5 values less than (151)
        subpartitions 4
        store in (test, test2, test3, test4);

━━━━━━━━━━━ Composite Range-List Partition

- Composite Range-Hash와는 달리 각 row가 어느 서브파티션에 속하게 될지를 조절할 수 있음. 

- multiple subpartition keys는 지원하지 않음. List 파티션이 지원하지 않기 때문이며, 
  오직 하나의 column만 key partition으로 가능하다.

━━━━━━━━━━━ Composite Range-Hash Partition 생성 및 insert

create table test
(

        employee_id number,

        department_id number,
        name varchar2(20),
        hire_date date,
        salary number
)
partition by range(employee_id)
subpartition by list(department_id)
(
partition ex1 values less than(160)
(
        subpartition ex1_1 values (10) tablespace test,
        subpartition ex1_2 values (20) tablespace test2,
        subpartition ex1_3 values (30) tablespace test3,
        subpartition ex1_4 values (40) tablespace test4,
        subpartition ex1_5 values (50) tablespace test,
        subpartition ex1_6 values (60) tablespace test2,
        subpartition ex1_7 values (70) tablespace test,
        subpartition ex1_8 values (80) tablespace test2,
        subpartition ex1_9 values (90) tablespace test3,
        subpartition ex1_10 values (100) tablespace test4,
        subpartition ex1_11 values (110) tablespace test3
),
partition ex2 values less than(220)
(
        subpartition ex2_1 values (10,110) tablespace test,
        subpartition ex2_2 values (20,100) tablespace test2,
        subpartition ex2_3 values (30) tablespace test3,
        subpartition ex2_4 values (40,90,80) tablespace test4,
        subpartition ex2_5 values (50,60,70) tablespace test
));

SQL> insert into test select * from test3 where employee_id like '%';
1행에 오류:
ORA-14400: 삽입된 분할 영역 키와 매핑되는 분할 영역이 없음

SQL> insert into test select * from test3 where department_id like '%';
106 개의 행이 만들어졌습니다.

 

##### range list partition에서는 한번에 여러개의 data를 다른 table에서 insert 할때
##### 주 파티션이 되는 range partition key 값을 기준으로 넣지 않고 sub partition key 값 기준으로 isnert 한다.

 

SQL> BEGIN
        DBMS_STATS.GATHER_TABLE_STATS('ORCL','TEST',CASCADE => TRUE);
        END;
        /

SQL> select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE, PARTITION_POSITION, NUM_ROWS
        from user_tab_partitions
        where table_name='TEST';

TABLE_NAME PARTITION_NAME  TABLESPACE_NAME                HIGH_VALUE PARTITION_POSITION   NUM_ROWS
---------- --------------- ------------------------------ ---------- ------------------ ----------
TEST       EX1             TEST                           160                         1         61
TEST       EX2             TEST                           220                         2         47

##### 조회 방법은 다른 조회방법과 동일하다
SQL> select * from test partition (ex1);

 

━━━━━━━━━━━ Composite Range-Hash Partition dictionary 조회
SQL> select table_name, partitioning_type, subpartitioning_type, partition_count
        from user_part_tables;

TABLE_NAME                     PARTITI SUBPART PARTITION_COUNT
------------------------------ ------- ------- ---------------
TEST                           RANGE   LIST                  2
TEST2                          RANGE   NONE                  4

SQL> select table_name, partition_name, subpartition_name, tablespace_name, num_rows
        from user_tab_subpartitions;

TABLE_NAME PARTITION_NAME  SUBPARTITION_NAME              TABLESPACE_NAME   NUM_ROWS
---------- --------------- ------------------------------ --------------- ----------
TEST       EX1             EX1_1                          TEST                     0
TEST       EX1             EX1_2                          TEST2                    0
TEST       EX1             EX1_3                          TEST3                    6
TEST       EX1             EX1_4                          TEST4                    0
TEST       EX1             EX1_5                          TEST                    25
TEST       EX1             EX1_6                          TEST2                    5
TEST       EX1             EX1_7                          TEST                     0
TEST       EX1             EX1_8                          TEST2                   15
TEST       EX1             EX1_9                          TEST3                    3
TEST       EX1             EX1_10                         TEST4                    6
TEST       EX1             EX1_11                         TEST3                    0
TEST       EX2             EX2_1                          TEST                     3
TEST       EX2             EX2_2                          TEST2                    2
TEST       EX2             EX2_3                          TEST3                    0
TEST       EX2             EX2_4                          TEST4                   20
TEST       EX2             EX2_5                          TEST                    21

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
partition index
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
// index 정보
SQL>  select index_name, partition_name, high_value, num_rows
        from user_ind_partitions
        where index_name='TEST_IDX';

 

// index segment 정보
SQL> select segment_name, partition_name, tablespace_name
        from user_segments
        where segment_name='TEST_IDX';

 

// partition index 정보
SQL> select index_name, table_name, partitioning_type, locality, alignment from user_part_indexes
        where index_name='TEST_PREFIX_IDX';

 

━━━━━━━━━━━ 실습을 하기 위한 range partition 생성 및 insert
create table test
(
        employee_id number,
        department_id number,
        name varchar2(20),
        hire_date date,
        salary number
)
partition by range (department_id)
(
        partition p10 values less than(11),
        partition p20 values less than(21),
        partition p30 values less than(31),
        partition p40 values less than(41),
        partition p50 values less than(51),
        partition p60 values less than(61),
        partition p70 values less than(71),
        partition p80 values less than(81),
        partition p90 values less than(91),
        partition p100 values less than(101),
        partition p110 values less than(111)
)
;

SQL> insert into test select * from test2 where department_id like '%';

SQL> BEGIN
        DBMS_STATS.GATHER_TABLE_STATS('ORCL','TEST',CASCADE => TRUE);
        END;
        /

SQL> select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE, PARTITION_POSITION, NUM_ROWS
        from user_tab_partitions
        where table_name='TEST';

TABLE_NAME PARTITION_NAME  TABLESPACE_NAME HIGH_VALUE PARTITION_POSITION   NUM_ROWS
---------- --------------- --------------- ---------- ------------------ ----------
TEST       P10             TEST            11                          1          1
TEST       P20             TEST            21                          2          2
TEST       P30             TEST            31                          3          6
TEST       P40             TEST            41                          4          1
TEST       P50             TEST            51                          5         45
TEST       P60             TEST            61                          6          5
TEST       P70             TEST            71                          7          1
TEST       P80             TEST            81                          8         34
TEST       P90             TEST            91                          9          3
TEST       P100            TEST            101                        10          6
TEST       P110            TEST            111                        11          2

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
global index
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
#### 9i까지는 range partition에 대해서만 global index가 생성 되었으나
#### 10g 부터는 hash partition에 대해서도 global index가 생성된다.
#### 파티션 index별로 tablespace 지정 가능.

━━━━━━━━━━━ global index 생성

create index test_global_idx
on test (employee_id) 
global partition by range(employee_id)
(
        partition ex1_idx values less than (130),
        partition ex2_idx values less than (150),
        partition ex3_idx values less than (170),
        partition ex4_idx values less than (190),
        partition ex5_idx values less than (maxvalue)
);

SQL> col index_name for a15
SQL> col partition_name for a15
SQL> col high_value for a15

SQL> select index_name, partition_name, high_value, num_rows
        from user_ind_partitions
        where index_name='TEST_GLOBAL_IDX';

INDEX_NAME                     PARTITION_NAME  HIGH_VALUE   NUM_ROWS
------------------------------ --------------- ---------- ----------
TEST_GLOBAL_IDX                EX1_IDX         130                30
TEST_GLOBAL_IDX                EX2_IDX         150                20
TEST_GLOBAL_IDX                EX3_IDX         170                20
TEST_GLOBAL_IDX                EX4_IDX         190                19
TEST_GLOBAL_IDX                EX5_IDX         MAXVALUE           17

SQL> select index_name, index_type, table_name, table_type
        from user_indexes
        where index_name='TEST_GLOBAL_IDX';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME TABLE_TYPE
------------------------------ --------------------------- ---------- -----------
TEST_GLOBAL_IDX                NORMAL                      TEST       TABLE

━━━━━━━━━━━ global index rename

SQL> alter index test_global_idx rename partition ex1_idx to ex11_index;

인덱스가 변경되었습니다.

 

SQL> select index_name, partition_name, high_value, num_rows
        from user_ind_partitions
        where index_name='TEST_GLOBAL_IDX';

INDEX_NAME                     PARTITION_NAME  HIGH_VALUE   NUM_ROWS
------------------------------ --------------- ---------- ----------
TEST_GLOBAL_IDX                EX11_INDEX      130                30
TEST_GLOBAL_IDX                EX2_IDX         150                20
TEST_GLOBAL_IDX                EX3_IDX         170                20
TEST_GLOBAL_IDX                EX4_IDX         190                19
TEST_GLOBAL_IDX                EX5_IDX         MAXVALUE           17

━━━━━━━━━━━ global index tablespace rebuild

SQL> select segment_name, partition_name, tablespace_name
        from user_segments
        where segment_name='TEST_GLOBAL_IDX';

SEGMENT_NAME         PARTITION_NAME  TABLESPACE_NAME
-------------------- --------------- ---------------
TEST_GLOBAL_IDX      EX11_INDEX      TEST
TEST_GLOBAL_IDX      EX2_IDX         TEST
TEST_GLOBAL_IDX      EX3_IDX         TEST
TEST_GLOBAL_IDX      EX4_IDX         TEST
TEST_GLOBAL_IDX      EX5_IDX         TEST

SQL> alter index test_global_idx rebuild partition ex11_index tablespace test2;

SQL> select segment_name, partition_name, tablespace_name
        from user_segments
        where segment_name='TEST_GLOBAL_IDX';

SEGMENT_NAME         PARTITION_NAME  TABLESPACE_NAME
-------------------- --------------- ---------------
TEST_GLOBAL_IDX      EX11_INDEX      TEST2
TEST_GLOBAL_IDX      EX2_IDX         TEST
TEST_GLOBAL_IDX      EX3_IDX         TEST
TEST_GLOBAL_IDX      EX4_IDX         TEST
TEST_GLOBAL_IDX      EX5_IDX         TEST

━━━━━━━━━━━ global index drop후 rebulid

SQL> select index_name, partition_name, high_value, num_rows
        from user_ind_partitions
        where index_name='TEST_GLOBAL_IDX';

INDEX_NAME                     PARTITION_NAME  HIGH_VALUE   NUM_ROWS
------------------------------ --------------- ---------- ----------
TEST_GLOBAL_IDX                EX11_INDEX      130                30
TEST_GLOBAL_IDX                EX2_IDX         150                20
TEST_GLOBAL_IDX                EX3_IDX         170                20
TEST_GLOBAL_IDX                EX4_IDX         190                19
TEST_GLOBAL_IDX                EX5_IDX         MAXVALUE           17

SQL> alter index test_global_idx drop partition ex11_index;

 

SQL> select index_name, partition_name, high_value, num_rows
        from user_ind_partitions
        where index_name='TEST_GLOBAL_IDX';

INDEX_NAME                     PARTITION_NAME  HIGH_VALUE   NUM_ROWS
------------------------------ --------------- ---------- ----------
TEST_GLOBAL_IDX                EX2_IDX         150                20
TEST_GLOBAL_IDX                EX3_IDX         170                20
TEST_GLOBAL_IDX                EX4_IDX         190                19
TEST_GLOBAL_IDX                EX5_IDX         MAXVALUE           17

SQL> alter index test_global_idx rebuild partition ex2_idx;

 

SQL> select index_name, partition_name, high_value, num_rows
        from user_ind_partitions
        where index_name='TEST_GLOBAL_IDX';

INDEX_NAME                     PARTITION_NAME  HIGH_VALUE   NUM_ROWS
------------------------------ --------------- ---------- ----------
TEST_GLOBAL_IDX                EX2_IDX         150                50
TEST_GLOBAL_IDX                EX3_IDX         170                20
TEST_GLOBAL_IDX                EX4_IDX         190                19
TEST_GLOBAL_IDX                EX5_IDX         MAXVALUE           17

━━━━━━━━━━━ global index drop partition table 후 index rebuild 방법

SQL> select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE, PARTITION_POSITION, NUM_ROWS
        from user_tab_partitions
        where table_name='TEST';

TABLE_NAME PARTITION_NAME  TABLESPACE_NAME HIGH_VALUE PARTITION_POSITION   NUM_ROWS
---------- --------------- --------------- ---------- ------------------ ----------
TEST       P10             TEST            11                          1          1
TEST       P20             TEST            21                          2          2
TEST       P30             TEST            31                          3          6
TEST       P40             TEST            41                          4          1
TEST       P50             TEST            51                          5         45
TEST       P60             TEST            61                          6          5
TEST       P70             TEST            71                          7          1
TEST       P80             TEST            81                          8         34
TEST       P90             TEST            91                          9          3
TEST       P100            TEST            101                        10          6
TEST       P110            TEST            111                        11          2

SQL> alter table test drop partition p10;
SQL> alter table test drop partition p60;
SQL> alter table test drop partition p100;

 

SQL> select index_name, partition_name, high_value, num_rows, status
        from user_ind_partitions
        where index_name='TEST_GLOBAL_IDX';

INDEX_NAME                     PARTITION_NAME  HIGH_VALUE   NUM_ROWS STATUS
------------------------------ --------------- ---------- ---------- --------
TEST_GLOBAL_IDX                EX2_IDX         150                50 UNUSABLE
TEST_GLOBAL_IDX                EX3_IDX         170                20 UNUSABLE
TEST_GLOBAL_IDX                EX4_IDX         190                19 UNUSABLE
TEST_GLOBAL_IDX                EX5_IDX         MAXVALUE           17 UNUSABLE

#### index가 unusable일때는 table에 대해 dbms~~~~ 통계 정보생성을 할수 없다. 에러발생.

SQL> alter index test_global_idx rebuild partition ex2_idx;
SQL> alter index test_global_idx rebuild partition ex3_idx;
SQL> alter index test_global_idx rebuild partition ex4_idx;
SQL> alter index test_global_idx rebuild partition ex5_idx;

 

SQL> select index_name, partition_name, high_value, num_rows, statusfrom user_ind_partitions
        where index_name='TEST_GLOBAL_IDX';

INDEX_NAME                     PARTITION_NAME  HIGH_VALUE   NUM_ROWS STATUS
------------------------------ --------------- ---------- ---------- --------
TEST_GLOBAL_IDX                EX2_IDX         150                39 USABLE
TEST_GLOBAL_IDX                EX3_IDX         170                20 USABLE
TEST_GLOBAL_IDX                EX4_IDX         190                19 USABLE
TEST_GLOBAL_IDX                EX5_IDX         MAXVALUE           16 USABLE

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
local index
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
#### partition table과 partition index의 구조가 같은 index.
#### 생성할때 tablespace 지정하지 않으면 table partition과 같은 위치에 생성된다.
#### partition 별로 동시 작업 가능.
#### partition table이 삭제되면 index도 같이 삭제됨.

━━━━━━━━━━━ local index 생성 및 조회

SQL> create index test_idx
on test(department_id)
local
(
        partition p10_idx tablespace test,
        partition p20_idx tablespace test2,
        partition p30_idx tablespace test3,
        partition p40_idx tablespace test4,
        partition p50_idx tablespace test,
        partition p60_idx tablespace test2,
        partition p70_idx tablespace test3,
        partition p80_idx tablespace test4,
        partition p90_idx tablespace test,
        partition p100_idx tablespace test2,
        partition p110_idx tablespace test3
);

// index 정보
SQL>  select index_name, partition_name, high_value, num_rows
        from user_ind_partitions
        where index_name='TEST_IDX';

// index segment 정보
SQL> select segment_name, partition_name, tablespace_name
        from user_segments
        where segment_name='TEST_IDX';

// partition index 정보
SQL> select index_name, table_name, partitioning_type, locality, alignment from user_part_indexes
        where index_name='TEST_PREFIX_IDX';

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Prefixed / Non-prefixed
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
- Prefixed:  인덱스 첫 번째 컬럼이 인덱스 파티션 키와 같음. 쿼리 성능 향상을 가져옴
- Non-prefixed: 인덱스 첫 번째 컬럼이 인덱스 파티션 키와 다름.
 
Partitioned Index 생성시 Prefixed/Non-prefixed에 대한 지정은 하지 않으며 위와 같은 Rule로 만들어 졌는지 여부를 DBMS가 확인하여 Dictionary(USER_PART_INDEXES.ALIGNMENT)에 기록하고 관리

━━━━━━━━━━━ Prefixed index 생성 및 조회

SQL> create index test_prefix_idx
on test(employee_id)
global partition by range(employee_id)
(
        partition ex1_idx values less than(160) tablespace test,
        partition ex2_idx values less than(maxvalue) tablespace test2
);

SQL> select index_name, table_name, partitioning_type, locality, alignment from user_part_indexes
        where index_name='TEST_PREFIX_IDX';

INDEX_NAME                     TABLE_NAME PARTITI LOCALI ALIGNMENT
------------------------------ ---------- ------- ------ ------------
TEST_PREFIX_IDX                TEST       RANGE   GLOBAL PREFIXED

━━━━━━━━━━━ Non-Prefixed index 생성 및 조회
- 성능의 저하를 가져올수 있다. 하지만 모든 Access path를 수용하기 위해 존재 할 수 밖에 없는 index이다.
- unique index를 생성하려면 해당 table의 partition key column을 추가되어야 한다.

SQL> create index test_nprefix_idx
on test(salary)
local;

SQL> select index_name, table_name, partitioning_type, locality, alignment from user_part_indexes;
        where index_name='TEST_NPREFIX_IDX';

INDEX_NAME                     TABLE_NAME PARTITI LOCALI ALIGNMENT
------------------------------ ---------- ------- ------ ------------
TEST_NPREFIX_IDX               TEST       RANGE   LOCAL  NON_PREFIXED

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Global prefixed Index
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
- Global Index 테이블과 같은 컬럼으로 파티션 되지만, 그 범위가 틀리거나 혹은 다른 컬럼으로 파티션이 이루어지는 경우
- Global Index 생성시 인덱스 칼럼의 맨 처음 칼럼을 사용하여 생성된 인덱스는 Global Prefixed Index
- 다른 칼럼을 사용하여 파티션 되는 경우에는 Global Non-Prefixed Index이지만, 오라클에서는 Global Prefixed Index만을 지원
- Global Index는 항상 인덱스의 맨 앞 칼럼 값만을 이용하여 파티션 됨.

━━━━━━━━━━━ Global Prefixed index 생성 및 조회

SQL> create index test_gl_pr_idx
on test(employee_id)
global partition by range(employee_id)
(
        partition ex1_idx values less than(150) tablespace test,
        partition ex2_idx values less than(maxvalue) tablespace test2
);

SQL> select index_name, table_name, partitioning_type, locality, alignment from user_part_indexes
        where index_name='TEST_GL_PR_IDX';

INDEX_NAME                     TABLE_NAME PARTITI LOCALI ALIGNMENT
------------------------------ ---------- ------- ------ ------------
TEST_GL_PR_IDX                 TEST       RANGE   GLOBAL PREFIXED

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
local Prefixed / Non-prefixed Index
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
- Local의 의미는 테이블 파티션 키와 인덱스 파티션 키가 같음을 의미.
- Prefixed 는 인덱스 첫 번째 컬럼이 인덱스 파티션 키와 같은 index.

━━━━━━━━━━━ Local Prefixed index 생성 및 조회
---- EX1)

SQL> create index test_lc_pr_idx
on test(department_id)
local
(
        partition ex1_idx tablespace test,
        partition ex2_idx tablespace test2,
        partition ex3_idx tablespace test3,
        partition ex4_idx tablespace test4,
        partition ex5_idx tablespace test,
        partition ex6_idx tablespace test2,
        partition ex7_idx tablespace test3,
        partition ex8_idx tablespace test4,
        partition ex9_idx tablespace test,
        partition ex10_idx tablespace test2,
        partition ex11_idx tablespace test3
);

SQL> select index_name, table_name, partitioning_type, locality, alignment from user_part_indexes
        where index_name='TEST_LC_PR_IDX';

INDEX_NAME                     TABLE_NAME PARTITI LOCALI ALIGNMENT
------------------------------ ---------- ------- ------ ------------
TEST_LC_PR_IDX                 TEST       RANGE   LOCAL  PREFIXED

---- EX2)
SQL> create index test_lc_pr_idx
on test(department_id)
local;

SQL> select index_name, table_name, partitioning_type, locality, alignment from user_part_indexes
        where index_name='TEST_LC_PR_IDX';

INDEX_NAME                     TABLE_NAME PARTITI LOCALI ALIGNMENT
------------------------------ ---------- ------- ------ ------------
TEST_LC_PR_IDX                 TEST       RANGE   LOCAL  PREFIXED

━━━━ Hash Partition 테이블의 Local Prefixed Index.

SQL> create index test_hash_idx 
on test(hire_date)
local store in (test, test2, test3, test4);

 

━━━━━━━━━━━ Local Non-Prefixed index 생성 및 조회
- Index의 첫 번째 컬럼이 파티션 키 컬럼으로 시작하지 않는 Local Index.
- 파티션 키 컬럼이 Index에 중간에 올 수도 있지만 항상 선두에 오지 않는 한 Non-prefixed index.

---- EX 1)
SQL> create index test_lc_npr_idx
on test(salary)
local
(
        partition ex1_idx tablespace test,
        partition ex2_idx tablespace test2,
        partition ex3_idx tablespace test3,
        partition ex4_idx tablespace test4,
        partition ex5_idx tablespace test,
        partition ex6_idx tablespace test2,
        partition ex7_idx tablespace test3,
        partition ex8_idx tablespace test4,
        partition ex9_idx tablespace test,
        partition ex10_idx tablespace test2,
        partition ex11_idx tablespace test3
);

SQL> select index_name, table_name, partitioning_type, locality, alignment from user_part_indexes
        where index_name='TEST_LC_NPR_IDX';

INDEX_NAME                     TABLE_NAME PARTITI LOCALI ALIGNMENT
------------------------------ ---------- ------- ------ ------------
TEST_LC_NPR_IDX                TEST       RANGE   LOCAL  NON_PREFIXED

---- EX 2)
SQL> create index test_lc_npr_idx
on test(salary)
local;

SQL> select index_name, table_name, partitioning_type, locality, alignment from user_part_indexes
        where index_name='TEST_LC_NPR_IDX';

INDEX_NAME                     TABLE_NAME PARTITI LOCALI ALIGNMENT
------------------------------ ---------- ------- ------ ------------
TEST_LC_NPR_IDX                TEST       RANGE   LOCAL  NON_PREFIXED

출처: Lucky

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

1. Partitioned Table

• Partitioning 이란 큰 Object 를 작고 Manage가 가능하게 분리하는 것을 의미하며, Table 이나 Index 에서만 가능하고 Cluster, Snapshot 은 불가능 합니다(Oracle 8 기준)
• 각 Partition 은 별개의 Segment에 저장 되어 집니다.
• Oracle8에서 Table은 기본이 되는 Key Value에 의해 Partition으로 분리되어 집니다.
• 각 Partition은 독립적으로 운영 됩니다.
• 예를 들면 Table Partition은 DML(insert, update, delete) 문에 의한 Transaction이 다른 Partition에 영향을 주지 않고 사용이 가능 합니다.
• DBA_TAB_PARTITIONS에 각 Partition의 Storage 정보가 있습니다.

2. 장점

• 여러 분할 영역에서의 데이터 훼손 가능성이 감소 됩니다.
• 각 분할 영역을 독립적으로 백업하고 복구 할 수 있습니다.
• 더 용이하게 관리할 수 있으며 가용성 및 성능을 향상 시킵니다.

3. 파티션 테이블 생성 예제

• 파티션 테이블을 범위 분할 방식(PARTITION BY RANGE)으로 생성 했습니다.

 SQL>CREATE TABLE emp_pt

        (

                EMPNO NUMBER(4),

                ENAME VARCHAR2(30),

                JOB VARCHAR2(9),

                MGR  NUMBER(4),

                HIREDATE  DATE,

                SAL     NUMBER(7,2),

                COMM    NUMBER(7,2),

                DEPTNO  NUMBER(2)

        )

        PARTITION   BY  RANGE(EMPNO)

        (    -->범위 분할 방식으로 생성 합니다. 

                PARTITION emp_p1 VALUES LESS THAN (2000) TABLESPACE data1,

                PARTITION emp_p2 VALUES LESS THAN (4000) TABLESPACE data2,

                PARTITION emp_p3 VALUES LESS THAN (7000) TABLESPACE data3

        );

emp_p1 파티션은 2000보다 적은 값이 들어가며 data1 테이블스페이스에, emp_p2 파티션은 4000보다 적은 값이 들어가며 data2 테이블스페이스에, emp_p3 파티션은 7000보다 적은 값이 들어가며 data3 테이블스페이스에 각각 할당을 했습니다. 각각의 파티션을 일정한 범위로 나누어서 각각의 테이블 스페이스를 할당 했습니다. 오라클에서는 이 외에도 여러 가지 다른 방법으로 파티션 테이블을 생성 할 수 있습니다.

4. 데이터 조작 예제

• INSERT

-- emp_p1 파티션에 등록이 됩니다. 

SQL>INSERT INTO emp_pt(empno, ename, job, hiredate, sal) VALUES(1000, 'Ultra', 'SALESMAN', sysdate, 3000);

-- emp_p2 파티션에 등록이 됩니다. 

SQL>INSERT INTO emp_pt(empno, ename, job, hiredate, sal) VALUES(2000, 'lion', 'CLERK', sysdate, 2500);

☞ 위의 두 예제와 같이 일반적으로 Insert를 해도 empno에 따라서 자동적으로 파티션이 나누어져 Insert가 됩니다. 아래의 예제와 같이 따로 파티션을 지정해서 Insert를 할 수도 있습니다.

-- 파티션을 통해서 INSERT하는 방법

SQL>INSERT INTO emp_pt PARTITION (emp_p3) VALUES (6000, 'scott2', 'CLERK', 5000, sysdate, 1500,0 ,10);

• SELECT

 -- emp_p1파티션 조회

SQL>SELECT empno, ename, job FROM emp_pt PARTITION (emp_p1);

    EMPNO   ENAME                    JOB

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

      1000    Ultra                        SALESMAN

-- emp_p2파티션 조회

SQL>SELECT empno, ename, job FROM emp_pt PARTITION (emp_p2);

     EMPNO    ENAME                    JOB

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

     2000       lion                        CLERK

• UPDATE/DELETE

-- 파티션을 통해서 UPDATE 예제.

SQL>UPDATE emp_pt PARTITION (emp_p3) SET sal = 4000 WHERE empno = 5000;

-- 파티션을 통해서 DELETE 예제.

SQL>DELETE FROM emp_pt PARTITION (emp_p3) WHERE empno = 6000;

5. Partition Table 관련한 Dictionary 정보

• Storage Parameters  

DBA_TAB_PARTITIONS를 통해서 확인 할 수 있습니다.   

• Partiton Table 의 Upper Partition Bound

 SQL>SELECT high_value, partition_position FROM sys.dba_tab_partitions WHERE table_name = 'EMP_PT';

 HIGH_VALUE     PARTITION_POSITION

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

4000               2

MAXVALUE     3

2000               1

6. 참고 : 오라클 9i에서의 리스트 분할 기능

• Partition을 추가 방법

empno에 대해서 Partition을 추가 하고 싶은 경우 다음과 같이 할 수 있습니다.

 SQL>ALTER TABLE emp_pt ADD PARTITION emp_p4 VALUES less than (9000) TABLESPACE  test;

ALTER TABLE 테이블명 ADD PARTITIOM 파티션명 VALUES 범위 TABLESPACE 테이블스페이스명으로 empno값이 9000이전의 값을 가지는 파티션 emp_p4를 추가했습니다.

• 특정 Partition을 삭제하는 방법

Partition을 없애고 싶은 경우는 DROP PARTITION 명령어를 사용하면 됩니다.

  SQL>ALTER TABLE emp_pt DROP PARTITION emp_p4;

• Partition Name을 변경하는 방법

Partition Name 을 바꾸고 싶다면 RENAME PARTITION 명령어를 사용하면 됩니다.

 SQL>ALTER TABLE emp_pt RENAME PARTITION emp_p3 to emp_p; 

emp_p3 파티션의 이름을 emp_p로 변경 하였습니다.

• Partition의 Tablespace를 옮기는 방법

MOVE PARTITION 명령어를 이용해서 테이블스페이스를 변경 할 수 있습니다.

  SQL>ALTER TABLE emp_pt MOVE PARTITION emp_p3 TABLESPACE test nologging;

Partition emp_p3의 테이블스페이스를 data3에서 test로 변경했습니다.

• 특정 Partition의 Data를 Truncate하는 방법

TRUNCATE PARTITION 명령을 사용하여 특정 파티션의 테이터를 Truncate시킬수 있습니다. Truncate는 Rollback 이 불가능하며 특정 Partition 전체를 삭제하므로 주의해서 사용해야 합니다.

  SQL>ALTER TABLE emp_pt TRUNCATE PARTITION emp_p3;

출처: DAUM TiP