문제로 풀어보는 Partitioned Index

년월일(YYYYMMDD) 컬럼을 이용하여 년 파티션 테이블로 구성 하였다.
여기서 년 별로 [NO] 컬럼이 UNIQUE한 데이터가 구성되도록 하려고 합니다.
INDEX 생성 스크립는 어떻게 해야 할까?

 

== 문제 예제 테이블 스크립트 ==;

CREATE TABLE TEST
(
  NO VARCHAR2(10),
  YYYYMMDD VARCHAR2(8),
  C1 VARCHAR2(10),
  C2 VARCHAR2(10),
  C3 VARCHAR2(10)
)
PARTITION BY RANGE ( YYYYMMDD )
(
PARTITION P_2023 VALUES LESS THAN ( '2024' ),
PARTITION P_2024 VALUES LESS THAN ( '2025' ),
PARTITION P_2025 VALUES LESS THAN ( '2026' ),
PARTITION P_2026 VALUES LESS THAN ( '2027' ),
PARTITION P_MAX VALUES LESS THAN ( MAXVALUE )
)
;

== 다양한 관점으로 분석 ==

1) Non Partitioned Index or Global Non-Partitioned Index ( 비파티션 인덱스 with FBI ) : O

CREATE UNIQUE INDEX TEST_IDX02 ON TEST( NO, SUBSTR(YYYYMMDD,1,4)) GLOBAL;
INSERT INTO TEST ( NO, YYYYMMDD ) VALUES ( 1, '20240101' );
INSERT INTO TEST ( NO, YYYYMMDD ) VALUES ( 1, '20240102' );
: 파티션 테이블 단위로 DDL 수행시, 비파티션 인덱스는 UNUSABLE 상태가 되므로 관리적인 관점에서 좋지 않음.

2) Local Partitioned Index : X

(1) CREATE UNIQUE INDEX TEST_IDX01 ON TEST ( NO, YYYYMMDD ) LOCAL;
-- 년월일 별로만 UNIQUE 하고, 년 OR 년월 별로 UNIQUE 하지 않음.
INSERT INTO TEST ( NO, YYYYMMDD ) VALUES ( 1, '20240101' );
INSERT INTO TEST ( NO, YYYYMMDD ) VALUES ( 1, '20240102' );
INSERT INTO TEST ( NO, YYYYMMDD ) VALUES ( 1, '20240103' );
-- 위 3개는 모두 저장 되나 년별로 NO가 UNIQUE 하지 못함

(2) CREATE UNIQUE INDEX TEST_IDX01 ON TEST ( NO, SUBSTR(YYYYMMDD,1,4) ) LOCAL;
-- 파티션키를 변형하는 순간 에러 발생 ( 파티션키 없다는 에러 )
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

3) Global Partitioned Index : X

CREATE UNIQUE INDEX TEST_IDX03 ON TEST ( SUBSTR(YYYYMMDD,1,4) , NO )
GLOBAL PARTITION BY RANGE ( YYYYMMDD )
(
PARTITION P_2023 VALUES LESS THAN ( '2024' ),
PARTITION P_2024 VALUES LESS THAN ( '2025' ),
PARTITION P_2025 VALUES LESS THAN ( '2026' ),
PARTITION P_2026 VALUES LESS THAN ( '2027' ),
PARTITION P_MAX VALUES LESS THAN ( MAXVALUE )
);
: ORA-14038 : GLOBAL로 분할영역된 인덱스는 접두사여야 합니다. ( 2번과 유사한 에러 파티션키 변형하면 안됨 )

4) 테이블에 가상 컬럼 (Virtual Column) 추가 : O

가상(Virtual) 컬럼
- 특정 컬럼(yyyyymmdd)에서 파생된 가상 컬럼(yyyy)임 ( 아래 예제에서 파타션키로 사용 )
- 물리적인 공간을 차지 하지 않음
- 사용자가 INSERT, UPDATE 하지 못하는 컬럼
- 해당 컬럼에 인덱스 생성 하면 FBI( FUNCTION BASED INDEX ) 형태로 생성됨
- TEMP, EXTERNAL, IOT TABLE에서는 사용 불가

 가상 컬럼 추가 후, 위 2,3번 실패된 인덱스 생성이 모두 잘 된다.
 ( "년" 컬럼을 추가하면 개발자가 컬럼을 개발에 이용해야 되나, 
   "가상컬럼"은 개발자가 신경 쓸 필요가 없는 장점은 있다 )

CREATE TABLE TEST
(
NO VARCHAR2(10),
YYYYMMDD VARCHAR2(8),
C1 VARCHAR2(10),
C2 VARCHAR2(10),
C3 VARCHAR2(10),
YYYY VARCHAR(16) INVISIBLE GENERATED ALWAYS AS (SUBSTR(YYYYMMDD,1,4)) VIRTUAL
)
PARTITION BY RANGE ( YYYYMMDD )
(
PARTITION P_2023 VALUES LESS THAN ( '2024' ),
PARTITION P_2024 VALUES LESS THAN ( '2025' ),
PARTITION P_2025 VALUES LESS THAN ( '2026' ),
PARTITION P_2026 VALUES LESS THAN ( '2027' ),
PARTITION P_MAX VALUES LESS THAN ( MAXVALUE )
) ;

1) Local Partitioned Index
CREATE UNIQUE INDEX TEST_IDX02 ON TEST ( NO, YYYY ) LOCAL;

2) Global Partitoin Index

CREATE UNIQUE INDEX TEST_IDX03 ON TEST ( YYYY, NO )
GLOBAL PARTITION BY RANGE ( YYYY )
(
PARTITION P_2023 VALUES LESS THAN ( '2024' ),
PARTITION P_2024 VALUES LESS THAN ( '2025' ),
PARTITION P_2025 VALUES LESS THAN ( '2026' ),
PARTITION P_2026 VALUES LESS THAN ( '2027' ),
PARTITION P_MAX VALUES LESS THAN ( MAXVALUE )
);
-- 2번째 부터 데이터가 들어가 않아 정상임을 알 수 있다.
INSERT INTO TEST ( NO, YYYYMMDD ) VALUES ( 1, '20240101' );
INSERT INTO TEST ( NO, YYYYMMDD ) VALUES ( 1, '20240102' );

위 2번은 1번과 동일한 역할을 하나 "파티션키가 선두로 와야하는 제약" 있어 유연하지 못하다.
1번과 동일한 역할인데 불필요하게 어렵게 작성하여 만들 필요가 없으므로 선택할 이유가 전혀 없다.
기술적으로 되는 확인용도로 만들어본 SCRIPT 일 뿐이다.

 

※ Global Partition의 특징 

Global Partition Index는 Local Partition Index의 테이블 파티션 키로 구분되어야만 하는 OBJECT가 아니다.독립적인 OBJECT 이다. 아래와 같이 테이블 파티션키가 아닌 사용자가 인덱스 파티션 키를 지정하고,인덱스 구성컬럼 선두에 두면 생성이 된다.

CREATE INDEX TB_PART_EXAM_01_IDX03 ON TB_PART_EXAM_01 ( C2 )
GLOBAL PARTITION BY RANGE ( C2 )
(
PARTITION P_C2_1 VALUES LESS THAN ('ABC'),
PARTITION P_C2_2 VALUES LESS THAN ('DEF'),
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
);

 

실제 현장에서는 VIRTUAL과 GPI(Global Partitioned Index)를 쓰는 경우를 본적이 없다 ( Global Non-Partitioned Index와 Global Partitioned Index는 서로 다름). 관리적인 면 어려움, 보편화 되지 않은 사례, 대체 가능한 다른 방법 등이 있기 때문이다. 그렇지만 예외적인 상황은 언젠가 나오기 마련이므로 정리해 보았다.