2010.06.25 오타를 수정 했습니다. 관련 댓글을 참조바랍니다.
결합 인덱스인 경우 뒤쪽 컬럼의 조건을 살리기 위해서 선두 컬럼에 BETWEEN 이나 LIKE 조건 대신에 IN을 사용해야 한다는 주장이 있다. 다시 말하면 column1 + column2 로 결합 인덱스가 생성되어 있고 column1 between 1 and 3 and column2 between 1 and 3 처럼 사용한다면 column2의 조건은 filter로 처리되어 인덱스의 효과를 보지 못하므로 column1 in (1,2,3) and column2 between 1 and 3 처럼 사용해야 한다는 주장이다. 이것은 항상 옳은 주장 일까?
예외 없는 규칙은 없다
위의 주장대로 하면 뒤쪽 컬럼까지 인덱스를 사용할 수 있다. 하지만 정작 문제가 되는것은 그렇게 하면 항상 성능이 빨라진다고 믿고 있는 사람들이다. 위의 주장은 일반적으로 통용되는 말이지만 오히려 성능이 불리해 질 수 있다. 먼저 IN 조건과 Range 조건(Between 이나 Like 조건)의 특징을 비교하기 위하여 가장 간단한 것(Single Column Index)부터 이야기 해보자.
column1에 인덱스가 있다고 가정하고 column1의 데이터가 1부터 100까지 정수만 존재한다고 했을때 column1 in (1,2) 처럼 사용해야 하는가 아니면 column1 between 1 and 2 처럼 사용해야 하는가? 결론부터 말하자면 column1 in (1,2) 조건은 정답이 아니다. 왜 그런지 아래의 SQL을 보자.
테스트 환경 Oracle 11.1.0.6
우선 sales 테이블에 인덱스를 만들자.
create index idx01 on sales (CUST_ID);
create index idx02 on sales (CUST_ID, TIME_ID);
이제 SQL을 실행 해보자.
SELECT /*+ gather_plan_statistics INDEX( a idx01) */
count(*)
FROM sales a
WHERE cust_id BETWEEN 33 AND 44 ;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 7 |
|* 2 | INDEX RANGE SCAN| IDX01 | 1 | 423 | 1432 |00:00:00.01 | 7 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_ID">=33 AND "CUST_ID"<=44)
위의 결과는 BETWEEN을 사용하였으므로 INDEX RANGE SCAN이 나왔으며 7개의 블럭을 scan 하였다. 별로 특별한 것이 없는 Plan이다. 이제 BETWEEN 대신에 IN 조건을 사용해보자.
SELECT /*+ gather_plan_statistics INDEX(A idx01) */
COUNT (*)
FROM sales a
WHERE cust_id IN (33, 34, 36, 37, 38, 40, 41, 42, 44) ;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 24 |
| 2 | INLIST ITERATOR | | 1 | | 1432 |00:00:00.01 | 24 |
|* 3 | INDEX RANGE SCAN| IDX01 | 9 | 1171 | 1432 |00:00:00.01 | 24 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("CUST_ID"=33 OR "CUST_ID"=34 OR "CUST_ID"=36 OR "CUST_ID"=37
OR "CUST_ID"=38 OR "CUST_ID"=40 OR "CUST_ID"=41 OR "CUST_ID"=42 OR
"CUST_ID"=44))
참고로 위의 IN 조건에서 35, 39, 43 은 데이터가 존재하지 않으므로 제외하였다.
IN 을 사용하였더니 BETWEEN 조건에 비해 3배 이상의 블럭을 Scan 하였다.
3배 차이 어디서 나타나는가?
Starts 항목(시도횟수)에 주목하기 바란다. BETWEEN을 사용한 Plan은 INDEX RANGE SCAN이 단 한번만 시도되었지만 IN을 사용한 Plan은 INLIST ITERATOR(반복처리) 때문에 INDEX RANGE SCAN이 9번 시도되었다. 즉 IN-LIST의 개수인 9번 만큼 RANGE SCAN을 반복한 것이다. 쓸모 없이 인덱스의 ROOT 노드와 중간 노드를 9번이나 Scan 하였으므로 비효율이 있는 것은 당연한 것이다.
결합인덱스를 사용할 때
이제 결합 인덱스인 경우 뒤쪽 컬럼의 조건을 살리기 위해서 선두 컬럼에 IN을 사용하면 오히려 성능이 불리해 지는 경우를 살펴보자.
SELECT /*+ gather_plan_statistics INDEX(A idx02) */
COUNT (*)
FROM tlo.sales a
WHERE cust_id IN (33, 34, 36, 37, 38, 40, 41, 42, 44)
AND time_id between to_date('20000101', 'YYYYMMDD') and to_date('20000131', 'YYYYMMDD');
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 21 |
| 2 | INLIST ITERATOR | | 1 | | 12 |00:00:00.01 | 21 |
|* 3 | INDEX RANGE SCAN| IDX02 | 9 | 95 | 12 |00:00:00.01 | 21 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access((("CUST_ID"=33 OR "CUST_ID"=34 OR "CUST_ID"=36 OR "CUST_ID"=37
OR "CUST_ID"=38 OR "CUST_ID"=40 OR "CUST_ID"=41 OR "CUST_ID"=42 OR
"CUST_ID"=44)) AND "TIME_ID">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "TIME_ID"<=TO_DATE(' 2000-01-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
인덱스 뒤쪽 컬럼인 time_id를 access 조건으로 만들기 위해 cust_id 에 IN 조건을 사용하였으며 21 블럭을 SCAN 하였다. 이제 cust_id에 between 조건을 사용해보자.
SELECT /*+ gather_plan_statistics INDEX( a idx02) */
count(*)
FROM tlo.sales a
WHERE cust_id BETWEEN 33 AND 44
AND time_id between to_date('20000101', 'YYYYMMDD') and to_date('20000131', 'YYYYMMDD');
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 2 | INDEX RANGE SCAN| IDX02 | 1 | 7 | 12 |00:00:00.01 | 8 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_ID">=33 AND "TIME_ID">=TO_DATE(' 2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "CUST_ID"<=44 AND "TIME_ID"<=TO_DATE('
2000-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter(("TIME_ID"<=TO_DATE(' 2000-01-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "TIME_ID">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
오히려 BETWEEN 조건이 성능상 우월하다
위의 Plan을 보면 cust_id에 between 조건을 사용하였으므로 time_id 조건을 사용하지 못하고 Filter로 빠졌다. 하지만 읽은 블럭수는 8개에 불과하다. 즉 IN 조건을 사용하여 인덱스의 뒤쪽 컬럼까지 엑세스 조건으로 만들었지만 성능은 오히려 저하되었다. INLIST ITERATOR(반복처리)의 부하 때문이다. 이 글에서 말하고자 하는것이 단순히 "INLIST 의 개수가 많아지면 부하가 증가된다" 라는 규칙을 이야기 하는것이 아니다. IN 조건과 BETWEEN 조건의 갈림길에서 BETWEEN 을 사용해야 유리한 경우가 있는데 그것이 어떤 경우인가? 혹은 기준은 무엇인가? 를 알아보는 것이다. 기준이 무엇일까? 결론을 보기전에 생각해보라.
결론
IN 조건과 Range 조건(Between 이나 Like 조건)을 구분하는 기준은 연속선 조건이다. 예를 들면 1부터 4까지 연속적으로 붙어있는 데이터를 엑세스 할 때는 Between 조건을 사용해야 한다. 왜냐하면 IN 조건은 LIST의 개수만큼 반복처리(LOOP) 되므로 쓸모 없는 부하가 증가하기 때문이다. 또한 특정 ITERATOR에서 조건에 만족하는 데이터가 없더라도 인덱스의 ROOT 노드와 중간 노드를 SCAN하는 비효율을 막을 수 없다. 하지만 Range 조건은 그러한 반복처리와 비효율이 없다.
결합인덱스의 뒤쪽 컬럼을의 조건을 처리주관조건으로 만들기 위해 선두 컬럼을 IN 조건으로 사용하고 싶을 때에도 연속선 기준을 검증용으로 적용시켜야 한다. 다시 말하면 선두 컬럼이 연속선 조건이라면 결합인덱스를 사용할 때에도 IN 조건과 Range 조건 중에 유리한 것을 선택해야 한다는 뜻이다. 초당 수백 번 이상 실행되는 중요한 SQL이고 0.001 초를 다투는 상황이라면 부하의 차이는 클 것이다.
연속적이지 않는 데이터를 엑세스 할 때는 BETWEEN 조건을 사용할 수 없으므로 당연히 IN 조건을 사용해야 한다. 이 글은 엑세스 하고자 하는 데이터가 연속선으로 되었을 때 IN 조건 보다는 BETWEEN 이나 LIKE 조건이 유리함을 나타낸 것이다.
PS
100% 연속된 조건일 때만 BETWEEN 조건을 써야 하는지 질문이 들어왔다. 그렇지 않다. 중간에 몇 개의 이빨이 빠진다고 한들 대세에 지장이 없으면 상관이 없다. 아래처럼 처리하기 바란다.
select *
from tab
where col1 between 1 and 5
오늘은 기분좋은 날 입니다.^^
'Oracle > Data Access Pattern' 카테고리의 다른 글
| Index_desc 힌트와 rownum = 1 조합은 안전한가? (12) | 2010.11.09 |
|---|---|
| Bloom Filter와 Group By의 관계 (10) | 2010.09.09 |
| 오렌지나 TOAD에서 Predicate Information을 참조하는 방법 (7) | 2010.01.12 |
| Partition Access Pattern (13) | 2009.12.24 |
| 모든 상식을 의심하라 (16) | 2009.11.05 |
