오라클 파티션 인덱스 (Partition Index) 관련 팁, FAQ 정리

Oracle Partition Index 관련 제가 알고있는 수준의 정보들을 쉐어합니다. ㅎㅎ 어디까지나 제가 알고 있는 수준~~

아주 미천하죠~ ㅋㅋ 가볍게 봐주세용.

 

 

♣ Local Index vs Global Index (로컬 인덱스 vs 글로벌 인덱스)

 

파티션 테이블(Partition Table)도 빠른 SQL처리를 위해서는 당연히 일반 테이블처럼 인덱스를 걸어야 합니다.

그런데, 파티션테이블의 경우 인덱스가 2종류가 있어서 헷갈리고 당황스럽죠~

바로 Local Partitioned Index 와 Global Partitioned Index 입니다.

 

원래는 "파티션드(Partitioned)" 라는 말을 붙여야 하는데, 귀찮아서 그냥 제목처럼 로컬인덱스, 글로벌인덱스 라고 부르기도 합니다.

하지만, 파티션테이블에 파티션되지 않은 일반 인덱스를 만들어버릴 수도 있기 때문에

엄밀히 말하자면 로컬파티션인덱스, 글로벌파티션인덱스 라고 말해야 정확합니다.

즉, 인덱스도 테이블처럼 파티션해서 만들려고 하는데, 이때 위 2가지 경우로 나뉘어지는 것이거든요.

 

파티션 테이블에는 파티션키(Partition Key)가 있죠. 이 파티션키로 테이블이 분할되어 있습니다.

인덱스를 만들때 인덱스 키컬럼으로 이 파티션키를 사용할 수 있으면, 로컬 인덱스를 만들 수 있습니다.

하지만, 파티션키를 사용할 수 없는 경우, 글로벌 인덱스를 만들 수 밖에 없습니다.

즉, 선택의 문제라기 보다는 어쩔 수 없는 상황이 벌어진다고 할 수 있겠습니다.

 

가급적 로컬 인덱스를 사용하라고 하지만, 그게 뜻대로 되지는 않습니다.

가급적 로컬 인덱스를 사용하라고 얘기하는 이유는 파티션테이블에서 일부 파티션을 Drop 하거나 Truncate 하게 되면

글로벌 인덱스의 경우는 인덱스가 Unusable 상태로 바뀌기 때문입니다. 이 인덱스는 못쓰게 된다는 뜻입니다.

다시 쓰려면 Index Rebuild 를 해줘야 합니다.

 

이 Index Rebuild 를 자동으로 해주는 옵션이 있습니다. drop 할때 update indexes 구문을 붙여주면 자동으로 리빌드 해줍니다.

 

            SQL> alter table sales drop partition SALES_P2 update indexes;

 

하지만, 이 작업을 하느라, 전체 테이블을 다 읽어야 하기 때문에 이렇게 한다고해서 작업이 빠르게 끝나지는 않습니다.

이 작업을 좀 빠르게 하려면 Parallel 옵션을 주는 정도 밖에는 없습니다.

 

그런데, Oracle 12cR1 버전에서 New Feature 가 나왔습니다. 이 update indexes 작업을 잠깐 미루는 ㅎㅎ ...

빠르게 하는게 아니고, 잠깐 미루는...  테이블 Create 할때의 deferred_segment_creation 기능과 같다고 보면 되겠습니다.

어쨌든, 커맨드를 치면 마치 인덱스 리빌드작업까지 모두 빠르게 끝난것처럼 프롬프트가 떨어지고, Status 로 Usable 상태로 나타납니다. 구뤠잇~

 

 

♣ 파티션 Index Status 가 N/A 로 나오는 경우

 

보통의 테이블에 대한 Index 는 USER_INDEXES 딕셔너리를 조회하면 Status 컬럼에 모두 Usable 로 나옵니다.

정상이라는 얘기죠~  Unusable 로 나오면 뭔가 문제가 있는거죠~~

파티션테이블의 경우는 USER_INDEXES 딕셔너리를 조회하면 Status 가 N/A 로 나옵니다. 당황하지 마시고 ^^

USER_IND_PARTITIONS 딕셔너리에서 Status 를 조회하시면 됩니다. 그러면 Usable 로 나옵니다.

만약 여기에도 Unusable 로 나온다면 그건 정말 문제가 있는 겁니다.

 

 

 

♣ 파티션 테이블에 일반 Index 를 만들수 있는가?

 

왠지 파티션테이블에는 파티션 인덱스만 만들어야 될 것 같은 느낌이 들지만...  일반 인덱스도 만들 수 있습니다. ㅎㅎ

구문은 일반테이블에 만드는 일반 인덱스 구문을 그냥 사용하면 됩니다.

일반 인덱스의 경우는 USER_INDEXES 딕셔너리에서 status 가 VALID 로 나옵니다. 하지만, 파티션 인덱스의 경우는 Local, Global 모두 N/A 로 나옵니다.

 

 

 

 

 

▶▶▶ 오라클 파티션 종합 페이지 바로가기