'INDEX RANGE SCAN'에 해당되는 글 2건

  1. 2010.06.23 Inlist Iterator를 사용하지 말아야 할 때 13
  2. 2009.10.29 Oracle Data Access Pattern을 정복하라 18

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
,

첨부파일의 일부 인덱스명에 오타가 있으니 댓글을 참조하기 바람(2009.11.05)

SQL
튜닝을 할 때 가장 기본적으로 익혀야 하는 것을 꼽으라면 무엇일까?

첫 번째, Data Access Pattern

두 번째, Join Method

위의 두 가지는 기본 중에 기본이다. 이 두 가지는 너무도 중요하기 때문에 튜닝을 시작하는 사람 혹은 튜너를 지망하는 사람들은 보고 또 보아야 한다. 실제로 이두가지를 모두 정복한다면 SQL 튜닝중의 많은 부분을 커버할수 있다. 이것은 비단 오라클 SQL 튜닝만이 아니라 모든 DBMS가 동일 할 것이다.

오늘은 두가지 중에 첫 번째의 Data Access Pattern의 개념에 대해서 이야기 해보자.

 

Data Access Pattern을 최대한 간단하게 표현하면 아래와 같다.

 

Index Scan       : 인덱스 사용

Full Table Scan  : 인덱스 미사용

Rowid            : Rowid로 테이블 엑세스

 

하지만 위의 3가지는 너무나 추상적이다.
위의 3가지를 좀더 상세히 나타내 보자.


Rowid

Index Unique Scan    

Index Range Scan

Index Inlist Iterator

Index Skip Scan 

Index Full Scan

Index Fast Full Scan 

Bit Map Index Combination

Full Table Scan        

 

3가지가 9가지가 되었다. 이제 제법 실전의 Plan에서 볼 수 있는 모습이 나왔다고 생각할 것이다.

하지만 아직 많이 부족하다.
실전의 Plan에는 위의 모습 보다 더욱 상세한 것이 필요하기 때문이다.

 

Rowid

Index Unique Scan    

Index Range Scan

Index Range Scan Descending

Index Range Scan (min/max)    

Index Inlist Iterator

Index Inlist Iterator Descending

Index Skip Scan 

Index Skip Scan Descending

Index Full Scan

Index Full Scan Descending 

Index Full Scan (min/max)   

Index Fast Full Scan 

BIT MAP OR           

BIT MAP AND          

BIT MAP MINUS        

BIT MAP MERGE

Full Table Scan        

 

드디어 3가지가 18가지가 되었고 실전적인 모습이 되었다.

위의 18가지 중에 한가지라도 찜찜한것이 있으면 첨부파일을 다운받아서 개념을 익히길 바란다.
파일을 배포 할 경우  출처를 밝혀주기 바란다.
파일 3개를 모두 다운 받은후 압축을 풀면 된다.

사용자 삽입 이미지

사용자 삽입 이미지



PS :
여기에 나오지 않은것들이 몇가지 있다. Partition Access Pattern은 이문서에서 언급되지 않았다.
다음 기회에..
Index Join 은 Data Access Pattern에 속하는 것이 아니고 Join method 에 포함되어야 한다.

Posted by extremedb
,