[Oracle] NUM_INDEX_KEYS 힌트 사용시 INDEX 힌트 함께 사용하기

인덱스 구성된 컬럼을 사용할때 IN 조건절로 사용하게 되는 경우가 있음

 

인덱스 구성 컬럼 중 IN 조건절로 쓰이는 컬럼이 액세스 조건으로 사용되는지

필터조건으로 사용되는지에 따라 성능이 다르며 사용되는 경우에 따라 유리한 방법이 있다.

( ex) in 조건 개수가 많지않고 데이터가 여러 블록에 존재하는 경우, 액세스 조건으로 사용시 유리할 수 있음 )

 

이럴때 num_index_keys 힌트를 사용하여 in 조건 컬럼을 액세스 조건으로 사용할지 필터 조건으로 사용할지 제어가능하다.

-- 인덱스의 n번째 컬럼까지 액세스 조건으로 사용
/*+ num_index_keys(t ix_01 n) */

 

 

개인 운영 노트) num_index_keys 힌트만으론 사용한 인덱스를 강제로 고정하지 않음

 

얼마전, 속도 저하 쿼리에 대해서 
IX_01 라는 인덱스의 사용하여 2번째 컬럼까지 액세스 조건으로 사용하고자 
NUM_INDEX_KEYS 힌트를 사용하여 플랜을 제어하였다.

원하던 결과대로 IX_01 인덱스의 2번째컬럼까지 액세스 조건으로 사용되어 in-list 로 잘풀렸는데,
추후 동일 쿼리에서 또 다시 속도 저하 발생 

확인해보니 IX_02 인덱스를 사용하여 in조건절을 필터 조건으로 사용함 

바인드 변수를 사용하는 쿼리였는데, 특정 바인드 변수의 경우 IX_02 의 인덱스를 타는게 낫다고 옵티가 판단한 것 같다.
실제 해당 테이블은 15억건이 넘는 대용량으로 통계정보 수집하지 않고, 파티션별 통계를 기존 값으로 복사하여 사용하고 있다.
그러다보니 컬럼의 히스토그램이 잘 반영되지 않아 동일한 플랜으로 풀리지 않았던 것 같음.

개발환경에서 동일 데이터를 가지고 통계 재수집 후 사용하니, 동일 플랜으로 잘 풀림

요 경험을 통해 num_index_keys에 기술한 인덱스로 힌트가 고정이 되지 않음을 확인

num_index_keys사용시 사용할 인덱스까지 같이 기술하여 사용하면 좋을 것 같음 

물론 통계가 잘 수집되는게 베스트...

 

ㅇ특정 바인드 변수에서 num_index_keys만 사용시, 옵티판단하에 다른 유리한 인덱스를 탐

select  /*+  NUM_INDEX_KEYS(p IDX_ XXXXXXXXX_SCODE 2) */ 

 

ㅇindex 힌트 사용하여 인덱스 고정함 

select  /*+ index(p IDX_XXXXXXXXX_SCODE)  NUM_INDEX_KEYS(p IDX_ XXXXXXXXX_SCODE 2) */ 

 

인덱스 힌트까지 같이 기술하여 특정 바인드 변수에서도 동일하게 플랜이 풀리는 것을 확인함.