실습 56. 리스트 파티션 테이블 생성과 관리
※ 책에서 제시하는 실습용 스키마를 생성하지 않았기 때문에 다소 예제가 다르나, 내용은 대동소이합니다. SCOTT 유저의 기본 스키마 (EMP, departments) 등을 이용합니다.
(1)파티션 테이블 생성
create table scott.emp_list_part (
EMPNO NUMBER(22),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(22),
HIREDATE DATE,
SAL NUMBER(22),
COMM NUMBER(22),
DEPTNO NUMBER(22),
constraint pk_list_empno primary key(empno)
using index tablespace indx
)
partition by list (job)
(
partition job_entry values ('CLERK','ANALYST') tablespace t1,
partition job_middle values ('MANAGER','SALESMAN') tablespace t2,
partition job_high values ('PRESIDENT') tablespace t3);
(2)데이터 삽입
insert into emp_list_part
select * from emp;
commit;
(3)파티션 등 확인
select * from user_tab_partitions
where table_name = 'EMP_LIST_PART';
select table_name, partitioned from user_tables;
select name, column_name
from user_part_key_columns
where name = 'EMP_LIST_PART';
(4)파티션 드랍 (alter table을 사용)
alter table emp_list_part
drop partition job_entry;
(5)데이터 재 삽입 --> 오류가 발생한다. partition key가 list에 존재하지 않기 때문!
insert into emp_list_part
select * from emp
where job in ('CLERK','ANALYST');
ORA-14400: inserted partition key does not map to any partition
(6)파티션 추가 (역시 alter table을 사용)
alter table emp_list_part
add partition job_entry values ('CLERK','ANALYST') tablespace t1;
(7)데이터 재 삽입 --> 어랏? 에러 다시 발생
insert into emp_list_part
select * from emp
where job in ('CLERK','ANALYST');
SQL 오류: ORA-01502: 인덱스 'SCOTT.PK_LIST_EMPNO'또는 인덱스 분할영역은 사용할 수 없은 상태입니다
01502. 00000 - "index '%s.%s' or partition of such index is in unusable state"
*Cause: An attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL
operation
*Action: DROP the specified index, or REBUILD the specified index, or
REBUILD the unusable index partition
무엇이 문제인지 확인해본다.
dba_objects 조회 --> 정상
select owner, object_name, object_type, status from dba_objects
where owner = 'SCOTT'
and object_type= 'INDEX';
SCOTT PK_LIST_EMPNO INDEX VALID
SCOTT PK_EMP INDEX VALID
SCOTT PK_DEPT INDEX VALID
dba_indexes 조회 --> UNUSABLE로 문제가 생긴걸 알 수 있다.
select owner, index_name, status from dba_indexes
where owner = 'SCOTT';
SCOTT PK_DEPT VALID
SCOTT PK_EMP VALID
SCOTT PK_LIST_EMPNO UNUSABLE
-- 그럼 일단 REBUILD를 해준다.
alter index PK_LIST_EMPNO rebuild;
(8)데이터 재 삽입
insert into emp_list_part
select * from emp
where job in ('CLERK','ANALYST');
commit;
--> 인덱스 REBUILD 후에는 정상적으로 데이터가 삽입됨을 알 수 있다.
-- 이상의 실습에서 알 수 있는점
1. 새로운 레코드가 파티션 테이블에 insert 될 때, 오라클이 자동적으로 key column에 따라 각 파티션으로 배분해준다. 이때 key column 값이 list 에 없는 값이라면 ORA-14400 에러가 발생하며 값이 insert 되지 않는다.
2. (1)번 과정에서 인덱스를 비파티션 인덱스로 만들었다. 그 후 각 테이블 파티션(이 예제에서는 job_entry의 drop)에 변경이 발생하자 index가 unusable 상태로 변경된걸 확인할 수 있었다. (PK 제약조건이 딸린 INDEX가 unusable 상태로 변경되었기 때문에 새 레코드의 insert가 불가능) --> 이후 Rebuild를 통해 인덱스를 valid 상태로 만든 후에야 레코드의 insert가 가능했다.
즉, 파티션 테이블에 비파티션 인덱스가 구성되어 있다면, 특정 테이블 파티션에 변경(split, exchange, drop 등)을 가하고 난 후에는 반드시 INDEX를 REBUILD 해 주어야 한다.
--> 이런 불편함을 감소시키기 위해서 로컬 파티션 인덱스를 사용할 수 있다.
실습 재개
(9) 인덱스 삭제 (PK_LIST_EMPNO)
alter table emp_list_part
drop constraint pk_list_empno;
(* PK Constraint에 해당하는 index는 drop 할 수 없으므로, 대신 PK Constraint를 삭제 해준다. 그럼 자동적으로 해당 INDEX도 DROP 된다.
참고
drop index PK_LIST_EMPNO
오류 보고 -
SQL 오류: ORA-02429: 고유/기본 키 적용을 위한 인덱스를 삭제할 수 없습니다.
02429. 00000 - "cannot drop index used for enforcement of unique/primary key"
*Cause: user attempted to drop an index that is being used as the
enforcement mechanism for unique or primary key.
*Action: drop the constraint instead of the index.)
(10) 로컬 파티션 인덱스 생성 --> 에러발생!
alter table emp_list_part add constraint pk_list_empno primary key(empno)
using index local tablespace indx;
SQL 오류: ORA-14039: 열을 분할영역한 것은 UNIQUE 인덱스로 키 열의 부분 집합을 폼 합니다
14039. 00000 - "partitioning columns must form a subset of key columns of a UNIQUE index"
*Cause: User attempted to create a UNIQUE partitioned index whose
partitioning columns do not form a subset of its key columns
which is illegal
*Action: If the user, indeed, desired to create an index whose
partitioning columns do not form a subset of its key columns,
it must be created as non-UNIQUE; otherwise, correct the
list of key and/or partitioning columns to ensure that the index'
partitioning columns form a subset of its key columns
구문은 위와 같으나 에러가 발생했다. 그 이유는 local partition index의 대상 컬럼은 파티션 키 컬럼(여기서는 job)과 같아야 하기 때문이다.
empno를 조회 조건으로 많이 사용하기 때문에(가정) empno를 버릴 수는 없다. 따라서 다음과 같이 수정하여 인덱스를 생성하였다.
(11) 로컬 파티션 인덱스 생성 (local non-prefixed index) --> 성공
* 첫번째 컬럼이 empno, 즉 list partitioning key column이 아니기에 non-prefixed가 된다.
alter table emp_list_part add constraint pk_list_empno primary key(empno, job)
using index local tablespace indx;
생성 확인) DBA_OBJESTS에서는 생성이 된걸 확인할 수 있으나, DBA_SEGMENTS에서는 확인되지 않는다.
이 때 SEGMENT_TYPE을 INDEX PARTITION으로 해야 조회가 가능하다.
*SEGMENT_TYPE이 INDEX가 아닌 INDEX PARTITION인 까닭 : LOCAL PARTITION INDEX라는 개념 자체가 인덱스 파티션과 테이블 파티션이 1:1 매칭이 되는 걸 전제로 하고 있기 때문이다.
추가 확인) INDEX 파티션 생성여부, 구성 컬럼, Constraint 조건 확인
select * from dba_ind_partitions --> 인덱스 파티션은 여기서 확인할 수 있다.
where index_owner = 'SCOTT';
select * from dba_ind_columns
where index_owner = 'SCOTT';
select * from dba_constraints
where constraint_name = 'PK_LIST_EMPNO';
(12)테스트: 테이블 파티션 DROP
실제로 local index를 사용한 경우, 테이블 파티션 변경시에도 INDEX가 VALID로 유효한지 확인해보자.
alter table emp_list_part
drop partition job_entry; --> 테이블 파티션 DROP
인덱스 상태확인)
select owner, object_name, object_type, status from dba_objects
where owner = 'SCOTT'
and object_type= 'INDEX';
SCOTT PK_LIST_EMPNO INDEX VALID
SCOTT PK_EMP INDEX VALID
SCOTT PK_DEPT INDEX VALID
--> dba_objects 확인. 일단 정상이다
select owner, index_name, status from dba_indexes
where owner = 'SCOTT';
SCOTT PK_DEPT VALID
SCOTT PK_EMP VALID
SCOTT PK_LIST_EMPNO N/A
--> dba_indexes 확인. 상태가 N/A이다. (아까전 비 파티션 인덱스 때 UNUSABLE이었던것과 비교해보자)
select index_name, partition_name, status from dba_ind_partitions
where index_owner = 'SCOTT';
PK_LIST_EMPNO JOB_MIDDLE USABLE
PK_LIST_EMPNO JOB_HIGH USABLE
--> 나머지 인덱스 파티션의 경우 상태가 USABLE임을 확인 할 수 있다.
우선 PK Constraints가 정상인지 먼저 확인 해보자.
insert into emp_list_part
select * from emp
where job in ('MANAGER'); --> 일부러 중복되는 값을 insert
SQL 오류: ORA-00001: 무결성 제약 조건(SCOTT.PK_LIST_EMPNO)에 위배됩니다
--> PK Constraints가 정상적으로 작동함을 확인 할 수 있다.
delete emp_list_part
where empno='7499'
and job='SALESMAN';
commit; --> INSERT가 정상적으로 되는지 확인하기 위해 일부러 값을 삭제함.
insert into emp_list_part
select * from emp
where empno='7499'
and job='SALESMAN';
commit; --> INSERT가 정상적으로 되며, 따라서 PK 제약조건이 딸린 INDEX도 문제 없이 사용가능함을 확인하였다.
최종적으로 DROP 했던 테이블 파티션을 다시 추가해주고, 해당 파티션에 속했던 record들이 정상적으로 INSERT되는지 확인해보자.
(13) 파티션 추가 및 레코드 INSERT
alter table emp_list_part
add partition job_entry values ('CLERK','ANALYST') tablespace t1; --> 파티션 추가 완료
*추가 후 dba_objects, dba_ind_partitions에서 STATUS가 각각 valid, usable로 조회가 되나, 여전히 DBA_INDEXES에서는 N/A로 조회됨을 알수 있다.
insert into emp_list_part
select * from emp
where job in ('CLERK','ANALYST');
commit; --> 레코드 추가 완료
이로써 로컬 파티션 인덱스 생성시에는 특정 테이블 파티션에 대한 변경(drop, split, exchange 등)이 발생하더라도 해당 INDEX가 unusable 되지 않음을 확인하였다.
(참고)
참고1.DBA_INDEXES의 STATUS에서 N/A는 무엇을 말하는 것일까? oracle reference를 읽어보자
--> Indicates whether a nonpartitioned index is VALID or UNUSABLE
nonpartition index일 경우에 대해서만 valid/unusable을 말해주고, partition index일 경우 N/A (해당 없음)이라는 것. partition index를 생성하면 초기값 부터가 N/A이다.
한편 ALL_IND_PARTITIONS의 STATUS 설명을 보면,
--> Indicates whether the index partition is usable (USABLE) or not (UNUSABLE)
즉 파티션 인덱스(=인덱스 파티션)은 dba_ind_partitions에서 조회하는 것이 맞다는 것이다.
참고2. 하지만 여전히 DBA_INDEXES 조회시 N/A로 나타나는게 찜찜하다... Rebuild를 하면 어떨까?
alter index PK_LIST_EMPNO rebuild;
오류 보고 -
SQL 오류: ORA-14086: 분할영역된 인덱스는 전체를 다시 만들 수 없습니다
14086. 00000 - "a partitioned index may not be rebuilt as a whole"
*Cause: User attempted to rebuild a partitioned index using
ALTER INDEX REBUILD statement, which is illegal
*Action: Rebuild the index a partition at a time (using
ALTER INDEX REBUILD PARTITION) or drop and recreate the
entire index
설명 처럼 파티션 인덱스는 개별적으로 (한번에 하나) rebuild 하여야 하며, 구문도 다르다. 예제 -->
alter index PK_LIST_EMPNO rebuild partition job_entry;
참고3. 파티션 인덱스 생성 구문
create index emp_list_part_idx1 on emp_list_part (job) local;
생각외로 간단하며 뒤에 local만 붙여주면 된다. 단 파티션 인덱스는 tablespace 미 지정시 해당 USER의 Default tablespace가 아니라 '짝지' 테이블 파티션과 동일한 tablespace에 위치하게 되므로, 이것이 싫을 경우 tablespace를 지정해주자. 구문은 다음과 같다.
create index emp_list_part_idx1 on emp_list_part (job) local
(partition list_p_idx1_entry tablespace indx,
partition list_p_idx1_middle tablespace indx,
partition list_p_idx1_high tablespace indx);
테이블 파티션과 인덱스 파티션의 매칭 결과는 어디서 볼 수 있는가? 역시 dba_ind_partitions에서 확인 가능하다.
SCOTT EMP_LIST_PART_IDX1 NO LIST_P_IDX1_HIGH 0 'CLERK', 'ANALYST'
SCOTT EMP_LIST_PART_IDX1 NO LIST_P_IDX1_ENTRY 0 'MANAGER', 'SALESMAN'
SCOTT EMP_LIST_PART_IDX1 NO LIST_P_IDX1_MIDDLE 0 'PRESIDENT'
SCOTT PK_LIST_EMPNO NO JOB_HIGH 0 'PRESIDENT'
SCOTT PK_LIST_EMPNO NO JOB_MIDDLE 0 'MANAGER', 'SALESMAN'
SCOTT PK_LIST_EMPNO NO JOB_ENTRY 0 'CLERK', 'ANALYST'
위에서부터 3번째까지 레코드를 보면 알겠지만, 초기 설계의도와는 다르게 매칭이 뒤죽박죽이 되었다... 인덱스 생성시 테이블 파티션을 지정해 줄수 없는가? --> 방법을 못 찾음... 찾으면 알려주시길 ㅠㅜ
내가 찾아낸 방법은 rename이 최선이었다. 구문은 다음과 같다 -->
alter index EMP_LIST_PART_IDX1 rename partition LIST_P_IDX1_HIGH to LIST_P_IDX1_ENTRY;