3.3.5 인덱스 선행 컬럼이 등치(=) 조건이 아닐 때

 

 

-- 1. 사전 정의된 10개의 distinct apt_price_code 값을 사용 CREATE TABLE apt_sales (   floor_level         NUMBER,   price_per_pyeong    NUMBER,   input_date          DATE,   building_number     VARCHAR2(10),   sale_type           VARCHAR2(1),   usage_days          NUMBER,   agent_code          VARCHAR2(10),   apt_price_code      VARCHAR2(20),   area                VARCHAR2(10),   area_type           VARCHAR2(2),   internet_listing    VARCHAR2(1) ); BEGIN   FOR i IN 1..100000 LOOP     INSERT INTO apt_sales (       floor_level, price_per_pyeong, input_date, building_number,       sale_type, usage_days, agent_code, apt_price_code,       area, area_type, internet_listing     ) VALUES (       TRUNC(DBMS_RANDOM.VALUE(1, 30)),                             -- floor_level       TRUNC(DBMS_RANDOM.VALUE(1000, 2000)),                        -- price_per_pyeong       TRUNC(SYSDATE - DBMS_RANDOM.VALUE(0, 365)),                  -- input_date       TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(101, 199))),                 -- building_number       CASE WHEN MOD(i, 2) = 0 THEN 'S' ELSE 'R' END,               -- sale_type       TRUNC(DBMS_RANDOM.VALUE(100, 365)),                          -- usage_days       'AG' || LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 999)), 3, '0'),      -- agent_code       'A010113509000' || TO_CHAR(MOD(i, 10)),                      -- apt_price_code (10종 고정)       CASE WHEN MOD(i, 5) = 0 THEN '84' ELSE '59' END,             -- area       CASE WHEN MOD(i, 3) = 0 THEN 'B' ELSE 'A' END,               -- area_type       TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 5)))                      -- internet_listing ('1' ~ '4')     );     IF MOD(i, 10000) = 0 THEN       COMMIT;     END IF;   END LOOP;   COMMIT; END; /

 

 

인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIKE 같은 범위검색 조건 이면, 인덱스를 스캔하는 단계에서 비효율이 생긴다.

 

 

예시 1

[APT_PRICE_CODE + AREA + AREA_TYPE + INTERNET_LISTING] 순으로 구성한 상황에서 아래 SQL을 수행한다.

 

select FLOOR_LEVEL, PRICE_PER_PYEONG, INPUT_DATE,

  BUILDING_NUMBER, SALE_TYPE, USAGE_DAYS, AGENT_CODE

from apt_sales

where apt_price_code= 'A0101135090008'

and area = '59'

and area_type = 'B'

and INTERNET_LISTING between '1' and '3'

order by INPUT_DATE desc;

 

아래 그림은 위와 같은 상황에서 인덱스 스캔 과정을 도식화 한 것이다.

 

 

인덱스 선행 컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은 조건을 만족하는 레코드가 모두 모여있기 때문.

 

select /*+ INDEX(APT_SALES IDX_APT_SALES_01) */ FLOOR_LEVEL, PRICE_PER_PYEONG, INPUT_DATE, BUILDING_NUMBER, SALE_TYPE, USAGE_DAYS, AGENT_CODE from apt_sales where apt_price_code= 'A0101135090008' and area = '59' and area_type = 'B' and INTERNET_LISTING between '1' and '3' order by INPUT_DATE desc; Execution Plan --------------------------------------------------------------------------------    1  ORDER BY (SORT) (Cost:10343, %%CPU:0, Rows:19820)    2    TABLE ACCESS (ROWID): APT_SALES (Cost:10342, %%CPU:0, Rows:19820)    3      INDEX (RANGE SCAN): IDX_APT_SALES_01 (Cost:95, %%CPU:0, Rows:19820) Predicate Information --------------------------------------------------------------------------------    3 - access: ("APT_SALES"."APT_PRICE_CODE" = 'A0101135090008') AND ("APT_SALES "."AREA" = '59') AND ("APT_SALES"."AREA_TYPE" = 'B') AND ("APT_SALES"."INTERNET_ LISTING" >= '1') AND ("APT_SALES"."INTERNET_LISTING" <= '3') (0.125 * 1.000 * 1. 000 * 1.000 * 1.000)

 

예시 2
[INTERNET_LISTING+ APT_PRICE_CODE + AREA + AREA_TYPE] 순으로 구성한 상황에서 아래 SQL을 수행한다.

 

이렇게 되면 아래 그림처럼 인덱스 스캔 범위가 넓어지게 된다.

 

 

select /*+ INDEX(APT_SALES IDX_APT_SALES_02) */ FLOOR_LEVEL, PRICE_PER_PYEONG, INPUT_DATE, BUILDING_NUMBER, SALE_TYPE, USAGE_DAYS, AGENT_CODE from apt_sales where apt_price_code= 'A0101135090008' and area = '59' and area_type = 'B' and INTERNET_LISTING between '1' and '3' order by INPUT_DATE desc; Total elapsed time 00:00:00.140915 Execution Plan --------------------------------------------------------------------------------    1  ORDER BY (SORT) (Cost:10391, %%CPU:0, Rows:19820)    2    TABLE ACCESS (ROWID): APT_SALES (Cost:10390, %%CPU:0, Rows:19820)    3      INDEX (SKIP SCAN): IDX_APT_SALES_02 (Cost:143, %%CPU:0, Rows:19820) Predicate Information --------------------------------------------------------------------------------    3 - access: ("APT_SALES"."INTERNET_LISTING" >= '1') AND ("APT_SALES"."APT_PRI CE_CODE" = 'A0101135090008') AND ("APT_SALES"."AREA" = '59') AND ("APT_SALES"."A REA_TYPE" = 'B') AND ("APT_SALES"."INTERNET_LISTING" <= '3') (1.000 * 0.125 * 1. 000 * 1.000 * 1.000)

 

 

인덱스 선두컬럼인 INTERNET_LISTING 에 BETWEEN 연산자를 사용하게 되면
나머지 조건을 만족하는 레코드들이 인터넷 매물 값별로 뿔뿔이 흩어지게 된다

 

착각할 수 있는게 0부터 읽어야 한다고 생각할 수 있는데 인덱스 선두컬럼이 인터넷 매물이라 1부터 읽으면 된다.

 

 

예시 3 (BETWEEN to IN-LIST)

예시 2처럼 [INTERNET_LISTING+ APT_PRICE_CODE + AREA + AREA_TYPE] 로 되어 있을 때

 

범위검색 컬럼인 INTERNET_LISTING을 맨 뒤로 보내서

 

[APT_PRICE_CODE + AREA + AREA_TYPE + INTERNET_LISTING] 으로 구성하면 좋겠지만 그렇게 하기 어려운 경우가 많다.

 

그럴 때는 BETWEEN 조건을 IN-LIST로 바꿔주면 큰 효과를 얻을 수 있는 경우가 있다.

 

위에서는 INTERNET_LISTING 이 1~3인 부분은 모두 탐색하는데 IN-LIST는 3번의 수직 탐색을 하게 되어

불필요한 리프 블록의 탐색을 줄여준다.

 

 

select /*+ INDEX(APT_SALES IDX_APT_SALES_02) */ FLOOR_LEVEL, PRICE_PER_PYEONG, INPUT_DATE, BUILDING_NUMBER, SALE_TYPE, USAGE_DAYS, AGENT_CODE from apt_sales where apt_price_code= 'A0101135090008' and area = '59' and area_type = 'B' and INTERNET_LISTING in ('1','2','3') order by INPUT_DATE desc; Total elapsed time 00:00:00.203969 Execution Plan --------------------------------------------------------------------------------    1  ORDER BY (SORT) (Cost:7245, %%CPU:0, Rows:13626)    2    TABLE ACCESS (ROWID): APT_SALES (Cost:7244, %%CPU:0, Rows:13626)    3      INLIST ITERATOR (Cost:0, %%CPU:0, Rows:13626)    4        INDEX (RANGE SCAN): IDX_APT_SALES_02 (Cost:199, %%CPU:0, Rows:13626)   Predicate Information --------------------------------------------------------------------------------    4 - access: ("APT_SALES"."INTERNET_LISTING" = :0) AND ("APT_SALES"."APT_PRICE _CODE" = 'A0101135090008') AND ("APT_SALES"."AREA" = '59') AND ("APT_SALES"."ARE A_TYPE" = 'B') (1.000 * 0.125 * 1.000 * 1.000)

 

 

 

* 주의사항

 

IN-LIST 항목 개수가 늘어날 수록 In-LIST 방식으로 전환하는 것은 곤란하다.

그럴 때는 NL방식의 조인문이나 서브쿼리로 구현하면 좋다.