Oracle LOCAL INDEX를 drop하는 방법은 없습니다. ㅋ
아래 테스트를 보시면 local partition index로 만들어진 index는 drop이 되지 않는 군요.. 

LOCAL partition index는 partition table의 구조를 참조해서 만들어 지기 때문에 drop이 되지 않습니다. 
그러나 Global partition index는 자신만의 구조를 갖기 때문에 drop이 가능합니다.. 

+ LOCAL INDEX

SQL> create table p_emp (sal number(10))
 2  partition by range (sal)
 3   (partition emp_p1 values less than (2000),
 4    partition emp_p2 values less than (3000),
 5    partition emp_max values less than (maxvalue));

Table created.

SQL> create index p_emp_i on p_emp (sal) local;

Index created.

SQL> alter index p_emp_i drop partition emp_p1;
alter index p_emp_i drop partition emp_p1
           *
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index

+ GLOBAL INDEX

 1  create table p_emp (empno number(4), sal number(10))
 2  partition by range (sal)
 3   (partition emp_p1 values less than (2000),
 4    partition emp_p2 values less than (3000),
 5*   partition emp_pmax values less than (maxvalue))
SQL> /

Table created.

 1  create index p_emp_i on p_emp (sal)
 2  global  partition by range (sal)
 3   (partition emp_p1 values less than (2000),
 4    partition emp_p2 values less than (3000),
 5*   partition emp_max values less than (maxvalue))
SQL> /

Index created.

SQL>
SQL> alter index p_emp_i drop partition emp_p2;

Index altered.

+ 참고

'Oracle Database' 카테고리의 다른 글

오라클 테스트 환경 만들기  (0) 2010.11.29
Oracle Begin backup에 대한 몇몇 궁금한 사항.  (0) 2010.11.25
TRUSS 명령과 SQLNET tracing 설정 방법  (0) 2010.11.04
Oracle UTL_FILE을 이용한 데이터 loading의 간단한 예제  (0) 2010.11.03
DBMS_JOB으로 만들어진 JOB의 interval을 1초로 지정해도 5초 정도의 interval을 갖는다?  (0) 2010.10.12


SQL> alter table p_emp rename partition emp_p1 to emp_p01;
SQL> alter table p_emp rename partition emp_p2 to emp_p02;
SQL> alter table p_emp rename partition emp_p3 to emp_p03;

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P01                        2000
P_EMP                          EMP_P02                        3000
P_EMP                          EMP_P03                        4000

partition table의 partition 이름 변경시 index의 partition 이름은 같이 변경되지 않는다. 따라서 index partition이름도 같이 변경해 주어야 한다.

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       USABLE
P_EMP_I              EMP_P3                         4000       USABLE

SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p1 TO emp_p01;
SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p2 TO emp_p02;
SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p3 TO emp_p03;

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P01                        2000       USABLE
P_EMP_I              EMP_P02                        3000       USABLE
P_EMP_I              EMP_P03                        4000       USABLE

참고 : Oracle Technical iSeminar - Partition Table과 Index 관리







'Oracle Database' 카테고리의 다른 글

DB link를 통한 remote DB를 query하는데 적용되는 10가지 법칙  (2) 2009.10.23
oracle pre complier의 그 유명한 hold_cursor/release_cursor option  (0) 2009.10.22
oracle partition table 중간에 partition 추가하기 (split partition)  (0) 2009.10.22
oracle의 Instance Load Balancing (client-side vs server-side)  (8) 2009.10.13
Oracle SQLPLUS에서 CASE문 사용하기  (4) 2009.10.12


1. partition table & local index 생성.

CREATE TABLE p_emp (sal NUMBER(7,2))
 PARTITION BY RANGE(sal)
 (partition emp_p1 VALUES LESS THAN (2000),
  partition emp_p2 VALUES LESS THAN (4000),
  partition emp_max VALUES LESS THAN (MAXVALUE));

 create index p_emp_i on p_emp (sal) local;

insert into p_emp values (1000);
insert into p_emp values (2000);
insert into p_emp values (3000);
commit;

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P1                         2000
P_EMP                          EMP_P2                         4000

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions  where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         4000       USABLE

2. split partition table

전체 partition range의 중간에 partition을 삽입히기 위해서는 split partition 명령을 사용해야 한다. MAXVALUE가 없는 상태에서 제일 끝에 추가할 경우는 add partition 명령을 사용한다.

SQL> alter table p_emp split partition emp_p2 at (3000) into (partition emp_p2, partition emp_p3);

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P1                         2000
P_EMP                          EMP_P2                         3000
P_EMP                          EMP_P3                         4000


SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions  where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       UNUSABLE
P_EMP_I              EMP_P3                         4000       UNUSABLE

partition table의 local partition index도 같이 split 되나 데이터가 있는 상태라면 "UNUSABLE" 상태로 되므로 이에 대한 rebuild 작업이 반드시 필요하다.

If the partition you are splitting contains data, the ALTER TABLE...SPLIT PARTITION statement marks UNUSABLE the new partitions (there are two) in each local index, all global index partitions, and any
global nonpartitioned index. You must rebuild such affected indexes or index partitions.

(Metalink Note 165599.1 Top Partitioned Tables Issues 중.)


SQL> alter index p_emp_i rebuild partition emp_p2;
SQL> alter index p_emp_i rebuild partition emp_p3;

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       USABLE
P_EMP_I              EMP_P3                         4000       USABLE

참고 : Oracle Technical iSeminar - Partition Table과 Index 관리




'Oracle Database' 카테고리의 다른 글

oracle pre complier의 그 유명한 hold_cursor/release_cursor option  (0) 2009.10.22
oracle partition name 변경하기  (0) 2009.10.22
oracle의 Instance Load Balancing (client-side vs server-side)  (8) 2009.10.13
Oracle SQLPLUS에서 CASE문 사용하기  (4) 2009.10.12
oracle 접속 잘 안될때 system state dump 뜨기  (2) 2009.10.12

+ Recent posts

티스토리툴바