loose index scan을 활용한 효과적인 쿼리 튜닝 방안

loose index scan은 인덱스 스캔시 tree 부분의 필요한 부분만 스캔하는 방법입니다.

전체 주소에서 시,도만 추출하는 경우나 전체 매출데이터에서 판매된 상품 같이 전체데이터에 비해서 중복이 많은 데이터 추출에 효과적입니다.

 

loose index scan이 가능한 조건에서 ‘INDEX_LS' 힌트를 추가해야지만 작동하게 됩니다.

loose index scan이 가능한 조건은 아래와 같습니다.

 

1.인덱스가 SELECT 리스트의 모든 부분을 커버하는 경우. 즉, 커버링 인덱스가 적용되는 경우

2.SELECT DISTINCT, SELECT ... GROUP BY 또는 단일 투플 SELECT 문인 경우

3.MIN/MAX 함수를 제외한 모든 집계 함수가 DISTINCT를 포함하는 경우

4.COUNT(*)가 사용되어선 안 됨

5.부분 키(subkey)의 카디널리티(고유 값의 개수)가 전체 인덱스의 카디널리티보다 100배 작은 경우

 

부분 키는 복합 인덱스(composite index)에서 앞 쪽 부분에 해당하는 것으로,

예를 들어 INDEX(a, b, c, d)로 구성되어 있는 경우 (a), (a, b) 또는 (a, b, c)가 부분 키에 해당합니다.

 

예제를 통해 자세히 알아봅시다.

-- 100만건 데이터 생성

CREATE TABLE lock_tbl AS

         SELECT

                 ROWNUM AS pk,

                 MOD(ROWNUM, 13) AS col1,

                 MOD(ROWNUM, 10) AS col2

         FROM TABLE({1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10}) a,

              TABLE({1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10}) b,

              TABLE({1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10}) c,

              TABLE({1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10}) d

         LIMIT 1000000;

 

-- 인덱스 생성

CREATE INDEX ix_01 ON lock_tbl(col2,pk);

 

-- TRACE ON

SET TRACE ON;

 

-- 힌트 없이 조회

SELECT  DISTINCT col2

FROM lock_tbl

WHERE col2 > 0;

 

SHOW trace;

 

-- TRACE 정보

Trace Statistics:

  SELECT (time: 852, fetch: 6952, ioread: 0)

    SCAN (index: lock_tbl.ix_01), (btree time: 566, fetch: 3071, ioread: 0, readkeys: 900000, filteredkeys: 900000, rows: 900000, covered: true)

    ORDERBY (time: 57, sort: true, page: 2, ioread: 0)

 

-- LOOSE 인덱스 스캔

SELECT /*+ index_ls */ DISTINCT col2

FROM lock_tbl

WHERE col2 > 0;

 

SHOW trace;

 

Trace Statistics:

  SELECT (time: 0, fetch: 32, ioread: 0)

    SCAN (index: lock_tbl.ix_01), (btree time: 0, fetch: 30, ioread: 0, readkeys: 9, filteredkeys: 9, rows: 9, covered: true, loose: true)

    ORDERBY (time: 0, sort: true, page: 0, ioread: 0)

 

수행되는 시간 및 fetch양을 봤을 때 커버링 인덱스 스캔과 많은 성능차이가 남을 알 수 있습니다.

Loose 인덱스 스캔은 count와 같은 집계함수가 불가한 제약 사항이 있지만

단순히 중복이 제거된 결과를 원하거나, MIN/MAX를 가져오는 쿼리에서 매우 효과적입니다.

 

LOOSE INDEX 스캔에 해당되는 조건이 조금 까다롭게 느껴질 수 있는데 아래 2가지로 축약할 수 있을 것 같습니다.

1.     전체 데이터에 비해서 중복이 많은 데이터 값 추출 시 사용.

2.     Covering index 스캔을 할 수 있으면서 부분 키 조건을 만족하는 인덱스가 존재.

 

2번 조건을 아래 예제를 통해 알아봅시다.

-- 인덱스 생성

DROP INDEX ix_01 ON lock_tbl;

CREATE INDEX ix_01 ON lock_tbl(col2);

 

-- COVERING 인덱스 스캔

SELECT /*+ index_ls */ DISTINCT col2

FROM lock_tbl

WHERE col2 > 0;

 

-- TRACE 정보

Trace Statistics:

  SELECT (time: 535, fetch: 6737, ioread: 0)

    SCAN (index: lock_tbl.ix_01), (btree time: 294, fetch: 3219, ioread: 0, readkeys: 9, filteredkeys: 9, rows: 900000, covered: true)

    ORDERBY (time: 49, sort: true, page: 12, ioread: 0)

 

-- 인덱스 생성

DROP INDEX ix_01 ON lock_tbl;

CREATE INDEX ix_01 ON lock_tbl(col2,pk);

 

-- LOOSE 인덱스 스캔

SELECT /*+ index_ls */ DISTINCT col2

FROM lock_tbl

WHERE col2 > 0;

 

Trace Statistics:

  SELECT (time: 0, fetch: 32, ioread: 0)

    SCAN (index: lock_tbl.ix_01), (btree time: 0, fetch: 30, ioread: 0, readkeys: 9, filteredkeys: 9, rows: 9, covered: true, loose: true)

    ORDERBY (time: 0, sort: true, page: 10048, ioread: 1674)

 

위 예제를 보면 부분 키 조건을 만족하지 않은 인덱스 생성시에는 힌트를 주어도 covered로 처리됨을 알 수 있습니다.

즉  커버가 가능한 인덱스에서 하나이상의 컬럼이 추가되어야 loose index 스캔 조건이 만족됨을 알 수 있습니다.

위 예제에서는 PK 컬럼을 추가하였고, 상황에 따라 적절한 컬럼을 추가해서 인덱스를 생성하면 되겠습니다.

 

조회조건이 동적으로 변경되어 모든 조건에 대한 인덱스 생성이 어렵거나 기존에 존재 하는 인덱스를 활용하려고 하는 경우

where 절에 exists 조건식을 사용하여 우회하여 쿼리 튜닝하는 방안도 있겠습니다.

 

-- 인덱스 생성

DROP INDEX ix_01 ON lock_tbl;

CREATE INDEX ix_01 ON lock_tbl(col2,pk);

CREATE INDEX ix_02 ON lock_tbl(col1,col2);

 

-- 인덱스에 만족하지 않는 조건 존재

SELECT /*+ index_ls */ DISTINCT col2

FROM lock_tbl

WHERE col2 > 0

AND col1 > 3;

 

-- TRACE 정보 (loose 인덱스 스캔이 되지 않음)

Trace Statistics:

  SELECT (time: 478, fetch: 5809, ioread: 0)

    SCAN (index: lock_tbl.ix_02), (btree time: 311, fetch: 3608, ioread: 0, readkeys: 90, filteredkeys: 81, rows: 623076, covered: true)

    ORDERBY (time: 39, sort: true, page: 24, ioread: 0)

 

-- EXISTS으로 우회

SELECT /*+ index_ls */ DISTINCT col2

FROM lock_tbl a

WHERE col2 > 0

   AND EXISTS (SELECT 1 FROM lock_tbl b WHERE a.col2 = b.col2 AND b.col1 > 3 LIMIT 1);

 

Trace Statistics:

  SELECT (time: 0, fetch: 83, ioread: 0)

    SCAN (index: lock_tbl.ix_01), (btree time: 0, fetch: 30, ioread: 0, readkeys: 9, filteredkeys: 9, rows: 9, covered: true, loose: true)

    ORDERBY (time: 0, sort: true, page: 0, ioread: 0)

    SUBQUERY (correlated)

      SELECT (time: 0, fetch: 51, ioread: 0)

        SCAN (index: lock_tbl.ix_02), (btree time: 0, fetch: 42, ioread: 0, readkeys: 54, filteredkeys: 9, rows: 9, covered: true)

 

EXISTS 조건식 사용시 exists에 포함된 sub 쿼리에도 적절한 인덱스가 있어야 좋은 성능이 나올 수 있습니다.

상황에 따라 loose index 스캔에 맞는 인덱스를 추가하거나 exists 조건식을 활용하여 우회할 수 있겠습니다.

 

Loose index 스캔은 조건이 까다로워 적용하기 어려운 경우가 있지만 적용 가능한 경우에는 쿼리 수행 시간을 상당히 줄일 수 있습니다. Distinct가 사용된 쿼리 혹은 group by에서 min/max외에 집계함수가 사용되지 않은 쿼리에 대해서 해당 스캔을 적용할 수 있는지 검토하는 것이 좋겠습니다.