Background Image
조회 수 2428 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

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외에 집계함수가 사용되지 않은 쿼리에 대해서 해당 스캔을 적용할 수 있는지 검토하는 것이 좋겠습니다.


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
74 질의작성 synonym에 대하여 김민종 2023.09.07 274
73 질의작성 VALUES 절 활용하기 황영진 2020.05.13 826
72 질의작성 여러개의 컬럼을 하나로 묶어주는 함수 CONCAT_WS 사용방법 엄기호 2019.01.29 7296
71 질의작성 입력된 날자를 이용하여 해당 주차의 모든 날자 구하기 성진 2018.04.10 1121
70 질의작성 JAVA_SP를 이용해서 정규표현식을 이용해서 치환을 하자. file 성진 2017.10.10 1338
69 질의작성 테이블 명세서 쿼리 1 성진 2017.08.04 3196
» 질의작성 loose index scan을 활용한 효과적인 쿼리 튜닝 방안 박세훈 2017.06.01 2428
67 질의작성 CONNECT BY 절 포함 질의 튜닝 예제 박세훈 2017.02.28 9795
66 질의작성 UPDATE에서 조인을 통해, 결과값 수정하는 방법 박동윤 2016.12.27 7196
65 질의작성 subquery를 이용한 튜닝예제 file 김창휘 2016.12.27 3335
64 질의작성 입력된 년월 또는 두개의 날짜을 이용하여 달력 및 주차 구하기 1 성진 2016.08.10 5596
63 질의작성 INSTR함수 사용하기 엄기호 2016.06.29 9279
62 질의작성 group_concat(문자열 그룹처리) 함수 사용하기 권호일 2016.06.29 28285
61 질의작성 두 datetime 연산결과를 "?일 ?시:?분:?초"로 표시하기 권호일 2016.05.18 6373
60 질의작성 ORACLE 테이블 및 컬럼 COMMENT 일광등록 스크립트 1 김창휘 2016.04.04 9345
59 질의작성 스키마 및 인덱스 선언에 따른 최대 용량 산정을 위한 ROW SIZE 확인 성진 2016.03.21 4182
58 질의작성 Oracle UTL_ENCODE.TEXT_ENCODE를 CUBRID로 변환하기 김창휘 2016.03.21 4648
57 질의작성 테이블 리스트 취합 SQL 김창휘 2016.03.19 5523
56 질의작성 테이블 컬럼 변경 및 추가 정만영 2016.02.29 21915
55 질의작성 FOR UPDATE 정만영 2016.01.27 5992
Board Pagination Prev 1 2 3 4 Next
/ 4

Contact Cubrid

대표전화 070-4077-2110 / 기술문의 070-4077-2113 / 영업문의 070-4077-2112 / Email. contact_at_cubrid.com
Contact Sales