왜 partition drop을 할때 글로벌 인덱스는 깨질까?

대부분 사이트에서 파티션 테이블을 생성할때 “일자” 단위의 range partition 을 생성할 것이다. (년/월/일 단위)

로컬 파티션을 만들 때 주의해야하는 것은 인덱스를 생성할 때 “로컬 인덱스” 로 만들어주는 것이다.

로컬 인덱스란?

로컬 파티션 테이블은 각 파티션에 대해 로컬 인덱스를 생성하여 각 파티션마다 별도의 인덱스를 유지한다.
로컬 파티션 테이블에서는 rowid가 각 파티션의 행 위치를 나타내는데 사용되지만, 글로벌 인덱스는 전체 테이블을 기준으로 관리되어 파티션 삭제시 rowid가 무효화될 수 있다.

• ROWID : ORACLE에서 행이 지정된 위치를 나타내는 고유 ID

로컬 인덱스 생성 방식

-- 기본 인덱스 생성 문장 뒤에 local 붙여주면 된다.
-- local 안붙이면 기본은 global index로 생성된다. 
create index mem_app.emp_idx 
local ;

글로벌 인덱스란?

글로벌 인덱스는 파티션 경계를 무시하고 테이블 전체 데이터를 대상으로 작동하는 단일 인덱스 구조.
테이블의 모든 파티션에 걸쳐 있는 데이터를 정렬하고 관리한다.

로컬 파티션의 글로벌 인덱스 장단점

장점

1. 범위 쿼리 성능 향상
   다중 파티션을 걸친 쿼리에서 글로벌 인덱스는 각 파티션의 인덱스를 따로 스캔하는 로컬 인덱스보다 더 빠를 수 있습니다.
2. 특정 파티션을 고려하지 않는 쿼리에서 유리
   글로벌 인덱스는 파티션 키와 무관한 칼럼으로 필터링하는 쿼리에 효과적입니다.
3. 병렬 쿼리에서 효율적 사용
   데이터가 여러 파티션에 분산된 경우에도 병렬 처리가 간단해지고 일관된 접근 방식을 유지.

단점

1. 관리 복잡성
   파티션 조작(DROP, TRUNCATE, SPLIT 등) 시 글로벌 인덱스가 사용 불가능한 상태(UNUSABLE)로 변할 수 있음.
   예: 특정 파티션을 삭제하면 글로벌 인덱스도 수정되어야 함.
   이를 해결하려면 인덱스를 REBUILD해야 하므로 추가 작업이 필요.
2. 성능 문제
   파티션 삭제, 추가, 병합 시 글로벌 인덱스를 동기화하려면 더 많은 리소스가 필요.
   파티션 경계를 무시하기 때문에 특정 파티션에 집중된 쿼리에서는 로컬 인덱스보다 느릴 수 있음.
3. 대량 데이터 로드/삭제 작업에서의 비효율
   글로벌 인덱스는 테이블 전체를 대상으로 하므로 데이터가 한 파티션에만 영향을 미치는 작업에도 전체 인덱스가 잠금될 수 있음.
4. 스토리지 사용량 증가
   로컬 인덱스는 각 파티션에 대해 분리된 구조를 가지므로 일부 데이터만 캐싱 가능.
   글로벌 인덱스는 전체 데이터에 대해 인덱스를 유지해야 하므로 메모리와 스토리지가 더 많이 필요.

왜 partition table drop 하면 글로벌 인덱스가 깨질까?

1. 글로벌 인덱스는 전체 테이블의 모든 파티션을 기준으로 하기 때문에 일부 파티션 drop 되면 인덱스에 존재하지 않는 rowid가 남아있게 된다.
2. 특정 파티션 데이터가 삭제될 때 해당 파티션의 행을 가르키는 rowid는 더 이상 유효하지 않는다.
3. 글로벌 인덱스에는 유효하지 않은 rowid 레코드가 남아 인덱스가 손상될 수 있으며, 인덱스 rebuild가 필요하다.