Primary, Unique constraint 에 의해 자동 생성되는 Index 관리
=========================================================

Table 에 primary 나 unique constraint를 걸면, 이 constraint 이름과 같은
이름의 index 가 자동으로 생성됩니다. Constraint 중에서 primary
constraint 는 not null과 unique 한 속성을 모두 만족해야 데이타가 table에
추가될 수 있고, unique constraint 는 중복되는 값을 금지하면서, 동시에
자동적으로 index가 생성된다는 특징을 갖고 있습니다. 따라서, constraint
에 의해서 생기는 index 는 일반 index 와는 달리 alter table명령을
통해서만 변경이 가능합니다.

일반적으로 index 를 생성할 때는 tablespace를 테이블과는 다른 곳으로
지정해 주지만, constraint에 의해 생기는 index는 tablespace 와 storage
option 을 지정하지 못한 경우가 있을 것입니다. 이 option을 생략할
경우에는 user 의 default tablespace 에 저장되는데, 때때로 이 영역에서
index space 부족에 대한 에러를 발견하게 됩니다.

이와 같은 문제를 해결하기 위하여, 1. table 생성 시에 constraint 에 의한
index 의 option을 함께 주는 방법과 2. 이미 사용 중인 index 를 다른
tablespace 에 저장하는 방법을 정리하였습니다.


1)table생성시에 constraint 에 의한 index 의 storage option 을 주는 방법

SQL> create table dept
2 (deptno number(2) primary key
3 using index pctfree 5
4 tablespace index_ts
5 storage (initial 10 k next 10 k pctincrease 0),
6 dname varchar2(9),
7* loc varchar2(10));

Table created.

SQL> create table dept
2 (deptno number(2),
3 dname varchar2(9) constraint unq_dname unique
4 using index
5 tablespace index_ts
6 storage (initial 10 k next 10 k pctincrease 0),
7* loc varchar2(10));

Table created.

2) 이미 생성된 constraint에 의한 index를 다른 tablespace 로 옮기는 방법

2.1) 먼저 constraint에 의한 index name을 확인하십시오.
이때, constraint name과 index name은 같습니다.
(예를 들어 table name = emp 일 경우)

SQL> SELECT SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,
DECODE(B.CONSTRAINT_TYPE,'P','PRIMARY KEY', 'U','UNIQUE KEY')
CONSTAINT_TYPE,
A.CONSTRAINT_NAME CONSTRAINT_NAME
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = 'EMP'
AND A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY 1,2;
/

COLUMN_NAME CONSTAINT_TYPE CONSTRAINT_NAME
--------------- ----------------- --------------------------------
EMPNO PRIMARY KEY SYS_C005966


2.2) 이 index 를 drop 하고 다른 tablespace 에 새로 생성하는 방법은
다음과 같습니다.

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

* 직접 index를 drop하는 것이 아니라, constraint를 drop 하셔야 합니다.

SQL> alter table emp
2* drop constraint sys_c005966;

Table altered.


2.3) 마지막으로 다음과 같은 방법으로 constraint 를 새로 생성하여 줍니다.

SQL>
alter table emp
add primary key(empno)
using index tablespace index_ts
storage (initial 10 k next 10 k pctincrease 0);

Table altered.

* 변경된 index 를 확인하려면, 다음과 같습니다.

SQL> select index_name
from user_ind_columns
where table_name ='EMP' and
column_name = 'EMPNO';

INDEX_NAME
------------------------------
SYS_C006041

SQL> select tablespace_name
from user_indexes
where index_name ='SYS_C006041';

TABLESPACE_NAME
------------------------------
INDEX_TS

출처 OTN

데이타베이스 유지보수 유건데이타