SQL튜닝이론 - IN 조건과 등치조건(=) 의 비교

SQL튜닝이론 - IN 조건과 등치조건(=) 의 비교

 

 

IN 조건은 항상 등치조건(=)의 반복으로 처리될까?

 

IN 조건을 IN-LIST-ITERATOR 방식으로 수행할때 리스트의 각 항목에 대한 등치조건이 되지만 IN-LIST-ITERATOR 방식으로 수행하지 않는 경우도 많습니다.  따라서 인덱스를 어떻게 구성하느냐에따라 성능이 달라집니다.

 

-- index : 상품id + 고객번호
select *
from 고객별가입상품
where 고객번호 = :cust_no
and 상품ID in ('nh0037', 'nh0041', 'nh0050')

 

고객번호의 평균 카디널리티가 3이라고 가정합니다. 즉 고객별로 평균 3건의 상품을 가입합니다. 인덱스를 상품id + 고객번호 순으로 생성하면 상품id는 고객번호 순으로 정렬된 상태로 리프블록에 저장됩니다. 고객번호는 상품id 순서에 따라 흩어진 상태가 됩니다.

 

인덱스가 위와같이 구성돼있다면 상품id 조건절이 IN-LIST ITERATOR 방식으로 수행되는 것이 효과적입니다. 고객번호 조건을 만족하는 레코드가 분산돼있기 때문입니다.

 

-- in-list Iterator방식으로 수행
-- index : 상품id + 고객번호

SELECT *
FROM 고객별가입상품
WHERE 상품ID = 'nh00037' 
AND 고객번호 = :Cust_no
union all
SELECT *
FROM 고객별가입상품
WHERE 상품ID = 'nh00041' 
AND 고객번호 = :Cust_no 
union all
SELECT *
FROM 고객별가입상품
WHERE 상품ID = 'nh00050' 
AND 고객번호 = :Cust_no

 

 

인덱스 구성컬럼모두를 인덱스 엑세스 조건으로 사용해 세번의 수직탐색을 합니다. 이 과정에서 총 아홉개의 블록을 읽습니다. 만약 IN-LIST ITERATOR 방식을 사용하지 않으면 인덱스 전체를 스캔하여 비효율이 발생합니다. 인덱스 [고객번호 + 상품ID] 로 구성하면 같은 고객은 상품 ID순으로 정렬된 상태로 리프블록에 저장됩니다.

 

IN-LIST-ITERATOR 방식을 사용하면, 인덱스를 수직적으로 세번 탐색하는 과정에서 아홉개의 블록을 읽습니다. IN-LIST-ITERATOR 방식을 사용하지 않으면 특정 고객번호의 레코드를 모두 스캔합니다. 같은 고객에 대한 인덱스는 한 블록에 모여있으므로 블록 I/O는 3 개만 발생합니다.

 

요약하면, IN 조건은 등치조건(=)이 아닙니다. 등치조건이 되려면 IN-LIST-ITERATOR 방식으로 실행되야 합니다. 그렇지 않으면 IN 조건은 필터 조건입니다. IN-LIST를 엑세스 조건으로 만드는 것이 항상 효율적인것은 아닙니다. 고객별 상품ID가 아주 많은 것이 아니라면 오히려 필터방식으로 처리되는 것이 낫습니다.

 

IN-LIST를 엑세스 조건 또는 필터 조건으로 유도하는 방법

 

NUM-INDEX-KEYS(table index columnNum) 힌트는 index 의 columnNum 번 까지의 컬럼을 인덱스 엑세스 조건으로 이용하고 나머지는 필터조건으로 이용합니다.

 

SELECT /*+NUM-INDEX-KEYS(table index columnNum)*/ *
FROM 고객가입상품 a
WHERE 고객번호 = :cust_no
AND 상품 ID IN ('Nh0037', 'nh0041', 'nh0050')

-- execution plan
select statement
  table access (by index rowid) of '상품'(table)
    index (range scan) of '상품_x01'

-- predicate information
access('고객번호' = TO_NUMBER(:CUST_NO))
filter("상품ID" = 'nh00037' or "상품ID" = 'nh00041' or "상품ID" = 'nh00050')

 

힌트를 사용하지 않고 인덱스 컬럼을 가공하는 방법도 있습니다. 상품ID 조건절의 좌변을 가공해서 인덱스 엑세스 조건으로 사용하지 못하도록 강제합니다.

 

SELECT
FROM 고객별가입상품
WHERE 고객번호 = :cust_no
and RTRIM(상품ID) in ('Nh0037', 'nh0041', 'nh0050')

SELECT
FROM 고객별가입상품
WHERE 고객번호 = :cust_no
and 상품ID || '' in ('Nh0037', 'nh0041', 'nh0050')

 

상품 ID까지 인덱스 엑세스 조건으로 사용하려면, 아래와 같이 힌트를 사용하면 됩니다. 상품 ID가 IN-LIST ITERATOR 방식으로 실행되면서 인덱스 엑세스 조건으로 사용됩니다.

 

SELECT /*+num_index_keys(a 고객별가입상품_x1 2)*/ *
FROM 고객별가입상품
WHERE 고객번호 = :cust_no
and 상품 ID in ('Nh0037', 'nh0041', 'nh0050')

-- execution plan
select statement
  INLIST ITERATOR
    table access (by index rowid BATCHED) of '고객별가입상품'(table)
      index (range scan) of '고객별가입상품_x01'

-- predicate information
access('고객번호' = : TO_NUMBER(:CUST_NO))
AND ("상품ID" = 'nh00037' or "상품ID" = 'nh00041' or "상품ID" = 'nh00050')