'2010/06'에 해당되는 글 1건

  1. 2010.06.23 Inlist Iterator를 사용하지 말아야 할 때 (12)

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

   and col1 <> 3 ; -- 연속선이 아닐 때의 처리

오늘은 기분좋은 날 입니다.^^
신고
Posted by extremedb

댓글을 달아 주세요

  1. 홍인훈 2010.06.25 10:24 신고  댓글주소  수정/삭제  댓글쓰기

    첫단락에서 "column1 between (1,2,3) and column2 between 1 and 3 처럼 사용해야 한다는 주장이다." 구문에 오타입니다.
    "column1 IN (1,2,3) and column2 between 1 and 3" 이게 아닐런지요...
    between (1,2,3)를 IN (1,2,3) 으로 쓰려던게 같은데요..^^

  2. 김봉호 2010.06.27 21:50 신고  댓글주소  수정/삭제  댓글쓰기

    아!! 오늘 BETWEEN 보다 IN 이 더 빠르다는걸 깨달았는데 오늘 더 높은 벽을 알려주시는군요!!
    감사합니다 (__)

  3. 백면서생 2010.06.28 11:50 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 항상 감사히 잘 보고 있습니다.

    늦어지만 책출간 축하드리고, 출간하신 책도 올 여름 보약 처럼 잘보고 있습니다.

    좋은 활동 계속 보여주시길 기대할께요~^^

  4. feelie 2010.06.29 12:33 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 감사합니다.
    얼마동안 정신이 없어서 오랜만에 들렀는데요.
    이제 좀 적응이 되었으니 자주 오겠습니다.
    앞으로 계속 좋은 글 부탁합니다.

  5. 야함나르 2010.06.29 14:42 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 감사합니다.

    글과 좀 다른 내용이긴 하지만
    저도 얼마전에 index(col1 , col2, col3, col4) 로 구성된 인덱스를 사용하는 SQL이
    조건절을 and col1 in ( ) and col2 = '' and col3 ='' and col4 in ( )

    이렇게 쓸 경우에 마지막 컬럼인 col4 에 대해서 인덱스를 적절히 활용하지 못하는것을 보면서 in 절의 효율성에 대해서 다시 생각해보았습니다.

    머 결국 col1 에 대해서 union 문으로 분리해서 개선 효과를 보았습니다만,..

    inlist 에 대해서 좀더 구체적으로 작동 원리를 연구해봐야 할것 같습니다 ^^..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.06.29 22:49 신고  댓글주소  수정/삭제

      말씀하신 문제는 NUM_INDEX_KEYS 힌트를 사용해 보시기 바랍니다. 사용법은 아래의 링크를 참조하세요.
      http://scidb.tistory.com/entry/InList-vs-Concatnation-vs-Range-Scan-Control-하기

  6. 야함나르 2010.07.07 16:53 신고  댓글주소  수정/삭제  댓글쓰기

    아 친절하신 댓글 감사합니다 ^^...

    근데 저희 DBMS는 9.2.0.7 이라서 ....적용이 불가합니다..

    위 힌트가 10.x 버전에서 추가된 것이 맞지요??.. ㅎ

    다시 한번 감사합니다 ^^..

  7. KIDO 2011.08.18 14:58 신고  댓글주소  수정/삭제  댓글쓰기

    좋은글 감사합니다. ^^

    In 은 선분의 개념으로 사용할때 사용하는 구문으로 알고 잇습니다.

    말씀하신대로 대부분 연속선상의 데이터를 조회할때는 IN이 불리하다는 것 참으로 좋은 내용이네요.. ^^

    Unique 의 경우에라면, IN을 쓰던 Between을 쓰던 상관이 없을꺼 같네요..
    또한 데이터가 연속되지 않고 흩어진경우에는 당연히 IN이 더욱 유리할꺼구요~ ^^

    인덱스가 Unique가 아니고, IN과 + 연속적인 데이터를 조회하는 경우 Between을 사용하는 경우가 IN을 사용하는 경우보다 낳은 결과로 생각하면 되겠네요 ~ ^^