[친절한 SQL] Between vs IN-List vs Index Skip Scan

앞서 Between을 IN-List로 바꾸게 되면 성능이 향상되는 경우가 있는 것을 확인해보았다. 이번에는 BETWEEN 조건을 IN-List 조건으로 변환하면 도움이 되는 상황에서 조건절을 바꾸지 않고도 같은 효과를 낼 수 있는 Index Skip Scan을 활용해 볼 것이다.

 

Between을 IN-List로 전환

2021.10.09 - [자격증 준비~] - [ 친절한 SQL] Between을 IN-List로 전환

 

이번엔 월별고객별 판매 집계 테이블을 생성하고 직접 테스트를 통해 확인해 보자.

 

■ TEST Data 생성

- 2018년도 1 ~ 12월까지 월별로 10만 개의 판매 데이터가 입력되도록 했다. 판매 구분 값 별로는 'A' 10만 개 / 'B' 110만 개다. 

SQL> create table consumer
as
select rownum cunsumer_num
, '2018' ||  lpad(ceil(rownum/100000), 2, '0 ' ) month
, decode(mod(rownum, 12), 1, 'A', 'B') category
, round(dbms_random . value(1000, 100000) , - 2) sales
from dual
connect by level <= 1200000 ;

 

■ TEST Query

SQL> select count(*) from consumer t where category='A' and month between '201801' and '201812';
  COUNT(*)
----------
    100000

 

■ INDEX 생성 ( consumer_idx1 ) & CONSUMER_IDX1 실행결과

- 위의 쿼리를 최적으로 수행하기위해 '=' 조건인 category를 선두 칼럼으로 인덱스 구성

- CONSUMER_IDX1을 이용하여 INDEX RANGE SCAN을 수행하였고 사용한 Buffer는 281이다.

SQL> create index consumer_idx1 on consumer(category,month);

SQL> select count(*) from consumer t where category='A' and month between '201801' and '201812';

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


---------------------------------------------------------------------------------------------
| Id  | Operation         | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |      1 |        |      1 |00:00:00.03 |     281 |
|   1 |  SORT AGGREGATE   |               |      1 |      1 |      1 |00:00:00.03 |     281 |
|*  2 |   INDEX RANGE SCAN| CONSUMER_IDX1 |      1 |    132K|    100K|00:00:00.02 |     281 |
---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CATEGORY"='A' AND "MONTH">='201801' AND "MONTH"<='201812')

 

■ INDEX 생성 ( consumer_idx2 )  & CONSUMER_IDX2 실행결과

- 이번에는 Between 조건의 Month 컬럼이 선두인 Index 생성

- CONSUMER_IDX1을 이용하여 INDEX RANGE SCAN을 수행하였고 사용한 Buffer는 3089이다.

SQL> create index consumer_idx2 on consumer(month,category);

SQL> select /*+ INDEX(t consumer_idx2) */ count(*) from consumer t where category='A' and month between '201801' and '201812';

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |      1 |        |      1 |00:00:01.67 |    3090 |   3089 |
|   1 |  SORT AGGREGATE   |               |      1 |      1 |      1 |00:00:01.67 |    3090 |   3089 |
|*  2 |   INDEX RANGE SCAN| CONSUMER_IDX2 |      1 |    132K|    100K|00:00:01.65 |    3090 |   3089 |
------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MONTH">='201801' AND "CATEGORY"='A' AND "MONTH"<='201812')
       filter("CATEGORY"='A')

 

* consumer_idx2를 사용할때와 consumer_idx1의 성능 차이가 큰 것을 알 수 있다.

이유는 IDX1은 선두컬럼이 Equal 조건이어서 판매 구분이 'A'인 레코드만은 탐색하였지만 IDX2 인덱스는 선두 칼럼이 BETWEEN 조건이어서 판매 구분이 'B'인 레코드까지 모두 스캔하기 때문이다.

 

 

■IN-List 로 Between 조건 튜닝 

- Between을 IN-List로 튜닝하여 기존 Between ( 3089 )에비해 Buffer가 304로 현저히 준것을 볼 수 있다.

SQL> select /*+ index(t consumer_IDX2) */ count(*) from consumer t where category = 'A'
and month in (
'201801' ,
'201802' ,
'201803' ,
'201804' ,
'201805' ,
'201806' ,
'201807' ,
'201808' ,
'201809' ,
'201810' ,
'201811' ,
'201812' );

SQL> select  * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |      1 |        |      1 |00:00:00.04 |     304 |
|   1 |  SORT AGGREGATE    |               |      1 |      1 |      1 |00:00:00.04 |     304 |
|   2 |   INLIST ITERATOR  |               |      1 |        |    100K|00:00:00.03 |     304 |
|*  3 |    INDEX RANGE SCAN| CONSUMER_IDX2 |     12 |    132K|    100K|00:00:00.02 |     304 |
----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access((("MONTH"='201801' OR "MONTH"='201802' OR "MONTH"='201803' OR
              "MONTH"='201804' OR "MONTH"='201805' OR "MONTH"='201806' OR "MONTH"='201807' OR
              "MONTH"='201808' OR "MONTH"='201809' OR "MONTH"='201810' OR "MONTH"='201811' OR
              "MONTH"='201812')) AND "CATEGORY"='A')

 

■INDEX_SKIP_SCAN 

- 선두컬럼이 BETWEEN 조건인 INDEX를 사용했음에도 큰 비효율 없이 300 블록만 읽었다. 

- INDEX_SKIP_SCAN 힌트를 사용하였다.

SQL> select /*+ INDEX_SS (t consumer_idx2) */ count(*) from consumer t where category='A' and month between '201801' and '201812';

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

--------------------------------------------------------------------------------------------
| Id  | Operation        | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |      1 |        |      1 |00:00:00.04 |     300 |
|   1 |  SORT AGGREGATE  |               |      1 |      1 |      1 |00:00:00.04 |     300 |
|*  2 |   INDEX SKIP SCAN| CONSUMER_IDX2 |      1 |    132K|    100K|00:00:00.04 |     300 |
--------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MONTH">='201801' AND "CATEGORY"='A' AND "MONTH"<='201812')
       filter("CATEGORY"='A')

 

 

■ TEST 결과 표

구분	cunsumer_idx1	cunsumer_idx2(Between)	IN-LIst	Skip Scan
블록 I/O	281	3089	304	300

 

 

 

참조: 친절한 SQL 튜닝(개발자를 위한 SQL 튜닝 입문서)