ADD PARTITION시 NEW LOCAL INDEX PARTITION에 관하여

출처 : http://shadou.tistory.com/127

 

No. 12208

ADD PARTITION시 NEW LOCAL INDEX PARTITION에 관하여
==================================================

개   요
=======
ALTER TABLE ADD PARTITION을 할 경우 table partition의 경우 table partition,
tablespace, physical storage attribute를 지정할 수 있으나, 
local index partition의 경우 관련 syntax를 지원하지 않는다 (8.1.6 현재)

오라클은 자동으로 index partition, tablespace, physical storage attribute를 지정한다. 
(ALTER TABLE SPLIT PARTITION도 마찬가지)

new index partition의 이름은 new table partition과 같은 이름이 할당되고,
tablespace, physical storage attribute는 local index에 지정된 default값이 사용되고,
local index에 지정된 default값이 없으면 user의 default tablespace가 사용된다.

그러므로 PARTITION 추가하여 사용하는 경우 index partition 관리에 주의가 요구된다. 
다음은 ALTER TABLE ADD PARTITION한 이후에 index partition을 관리하는 예제이다.

예   제
=======
-- Create partition table --
CREATE TABLE tbl_emp (
  empno NUMBER,
  ename VARCHAR2(20),
  job   VARCHAR2(20)
)
PARTITION BY RANGE ( empno ) (
  PARTITION pt_tbl_emp01 VALUES LESS THAN( 1000 ) TABLESPACE data_emp01,
  PARTITION pt_tbl_emp02 VALUES LESS THAN( 2000 ) TABLESPACE data_emp02,
  PARTITION pt_tbl_emp03 VALUES LESS THAN( 3000 ) TABLESPACE data_emp03
);

-- Create local index --
CREATE INDEX idx_emp ON tbl_emp( empno )
  TABLESPACE users --> 여기서 local index default tablespace 지정
  LOCAL (
    PARTITION pt_idx_emp01 TABLESPACE indx_emp01,
    PARTITION pt_idx_emp02 TABLESPACE indx_emp02,
    PARTITION pt_idx_emp03 TABLESPACE indx_emp03
  );

-- PK 설정 --
ALTER TABLE tbl_emp ADD CONSTRAINT pk_emp PRIMARY KEY ( empno );

-- Local Index를 PK index로 사용함을 확인 --
SELECT index_name FROM  user_indexes WHERE table_name = 'TBL_EMP';

INDEX_NAME
------------------------------
IDX_EMP

DROP INDEX idx_emp
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

-- Add partition --
ALTER TABLE tbl_emp  
  ADD PARTITION pt_tbl_emp04 VALUES LESS THAN( 4000 ) 
  TABLESPACE data_emp04;

-- 추가된 Local index partition의 이름, Tablespace를 확인한다 --
SELECT partition_name, tablespace_name, status
FROM  user_ind_partitions
ORDER BY 1;

PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
PT_IDX_EMP01                   INDX_EMP01                     USABLE
PT_IDX_EMP02                   INDX_EMP02                     USABLE
PT_IDX_EMP03                   INDX_EMP03                     USABLE
PT_TBL_EMP04                   USERS                          USABLE

-- Tablespace는 local index의 default tablespace에 생긴다 --
SELECT index_name, def_tablespace_name
FROM user_part_indexes;

INDEX_NAME                     DEF_TABLESPACE_NAME
------------------------------ ------------------------------
IDX_EMP                        USERS

--< Local index partition name 변경 >--
ALTER INDEX idx_emp
RENAME PARTITION pt_tbl_emp04 TO pt_idx_emp04;

--< Local index tablespace 변경 >--
ALTER INDEX idx_emp
REBUILD PARTITION pt_idx_emp04 TABLESPACE indx_emp04;

SELECT partition_name, tablespace_name, status
FROM  user_ind_partitions
ORDER BY 1;

-- 변경된 Local index partition의 이름, Tablespace를 확인한다 --
PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
PT_IDX_EMP01                   INDX_EMP01                     USABLE
PT_IDX_EMP02                   INDX_EMP02                     USABLE
PT_IDX_EMP03                   INDX_EMP03                     USABLE
PT_IDX_EMP04                   INDX_EMP04                     USABLE

* 다음과 같이 local index의 default tablespace를 변경할 수도 있다.
alter index <indexname> 
modify default attributes tablespace <new tablespace name>;