PARTITIONED TABLE의 PK CONSTRAINT ENABLE 시의 생성 INDEX PARTITION
==================================================================

Partition table을 생성하고, primary key constraint를 지정하여 사용하고자
하는 경우, 작업을 진행 시 table data는 partition 되어 있으나 primary key
constraint를 추가 시 생성되는 index는 partition되지 않고, 하나의 segment
로 생성되는 것을 보게 된다.
이렇게 생성되는 index는 global index로 취급되게 되며, 이 경우 partition
table의 관리에 다소 어려움이 발생한다.
본 자료는 partition table에 대하여 primary key constraint를 생성 시
local index를 사용 하도록 하는 방법에 대하여 기술한다.


기본 개념.
Partitioned index는 local / global, prefixed / non-prefixed로 구분이
가능하다.

Local index ; table partition과 동일한 index partition을 사용.
Global index ; 하나의 index partition에서 둘 이상의 table
partition을 지정.
Prefixed ; index column의 left column을 사용하여 index
partition을 구성.
Non-Prefixed ; index column의 left column 이외의 column을 사용하여
index partition을 구성.

위의 option을 사용하여 partitioned index를 구현하게 되며, oracle8 에서는
local prefixed, local non-prefixed, global prefixed index의 사용을
허용한다.
(global non-prefixed index는 이점을 제공하지 못하므로 허용되지 않는다.)

partitioned table에 primary key constraint를 추가 시 local index를
사용하게 하려면, 먼저 local partitioned index를 생성 후, 해당 table에
대하여 alter table command를 사용하여 constraint를 생성하는 방법으로
작업을 진행하여야 한다.
여기서 주의할 점은 먼저 생성되는 index가 local이든 global이든 관계없이
prefixed index로 생성되어야 한다는 것이다.
( Non-prefixed index는 하나의 index key 값에 해당하는 data가 여러
table partition에 존재할 수 있다는 것을 의미. )
아래는 test 절차와 결과를 display한 것이다.

1. partitioned table을 생성.

create table test_a
(col1 number,
col2 number,
col3 varchar2(20))
partition by range (col1, col2)
(partition part_test_a_1 values less than (10, 100) tablespace ts0,
partition part_test_a_2 values less than (20, 200) tablespace ts1,
partition part_test_a_3 values less than (30, 300) tablespace ts2,
partition part_test_a_4 values less than (40, 400) tablespace ts3);


생성된 table의 구조.

SQL> @u_t_par

TN PN HV PP TBSN IE NE
------------------------------------------------------------------
TEST_A PART_TEST_A_1 10, 100 1 TS0 20480 20480
TEST_A PART_TEST_A_2 20, 200 2 TS1 20480 20480
TEST_A PART_TEST_A_3 30, 300 3 TS2 20480 20480
TEST_A PART_TEST_A_4 40, 400 4 TS3 20480 20480

TN : Table Name, PN : Partition Name, HV : High Value,
PP : Partition Position, TBSN : Tablespace Name, IE : Initial Extent,
NE : Next Extent


2. 먼저 local prefixed index를 생성.

create index ix_test_a on test_a(col1, col2)
local
(partition in_test_a_1 tablespace ts0,
partition in_test_a_2 tablespace ts1,
partition in_test_a_3 tablespace ts2,
partition in_test_a_4 tablespace ts3);

생성된 index 구조
SQL> @u_i_par

IDXN PN HV PP ST TBSN IE NE
------------------------------------------------------------------
IX_TEST_A IN_TEST_A_1 10, 100 1 USABLE TS0 20480 20480
IX_TEST_A IN_TEST_A_2 20, 200 2 USABLE TS1 20480 20480
IX_TEST_A IN_TEST_A_3 30, 300 3 USABLE TS2 20480 20480
IX_TEST_A IN_TEST_A_4 40, 400 4 USABLE TS3 20480 20480

IDXN : Index Name, PN : Partition Name, HV : High Value,
PP : Partition Position, ST : Status, TBSN : Tablespace Name,
IE : Initial Extent, NE : Next Extent

SQL> @u_p_is

INDEX_NAME PT PC PKC LOCAL ALIGN
----------------------------------------------------
IX_TEST_A RANGE 4 2 LOCAL PREFIXED

PT : Partition Type, PC : Partition Count, PKC : Partition Key Count
Local : Locality, ALIGN : Alignment


3. Primary Key Constraint 생성.

SQL> alter table test_a add constraint pk_test_a
2 primary key ( col1, col2 );

Table altered.


4. 생성되어 있던 IX_TEST_A Index를 drop하여 본다.

SQL> drop index ix_test_a;
drop index ix_test_a
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary
key

Ora-2429 error에서 볼 수 있듯이 기존에 생성되어 있던 IX_TEST_A Index가
primary key constraint와 연계되어 사용되는 것을 확인할 수 있다.

Primary Key Constraint 생성시, primary key로 사용되는 column들로
이루어진 index가 이미 존재하는 경우, 기존에 존재하는 index를 사용하게
된다.
oracle 8에서는 존재하는 index가 unique/non-unique, local/global
partitioned index 여부에 관계없이 사용하게 된다.
( 7.3에서는 동일한 column으로 이루어진 unique index만을 사용)

위의 예에서 볼 수 있듯이 IX_TEST_A Index는 non-unique index로 생성
되었으며, primary key constraint를 생성 시 무리없이 사용되고 있지만,
사실상 data가 unique한 경우에만 적용되며, 그렇지 않은 경우에는
ora-2437 : Primary key violated error가 발생하게 된다.

Primary key constraint를 drop 시, 생성되어 있던 index가 unique
index였다면 함께 drop되게 되며, 이는 해당 index가 local이든
global이든 동일하게 적용된다.

Monitoring에 사용된 SQL 문장.

u_t_par.sql

column tn format a12
column pn format a13
column hv format a15
column pp format 99
column tbsn format a5

select table_name tn, partition_name pn, high_value hv,
partition_position pp, tablespace_name tbsn, initial_extent ie,
next_extent ne
from user_tab_partitions
order by pn


u_i_par.sql

column idxn format a12
column pn format a13
column hv format a10
column pp format 99
column st format a8
column ie format 999999
column ne format 999999
column tbsn format a5

select index_name idxn, partition_name pn, high_value hv,
partition_position pp, status st, tablespace_name tbsn,
initial_extent ie, next_extent ne
from user_ind_partitions
order by idxn, pn


u_p_is

column index_name format a12
column partitioning_type format a10
column pc format 999
column pkc format 999

select index_name, partitioning_type pt, partition_count pc,
partitioning_key_count pkc, locality, alignment
from user_part_indexes