Background Image
조회 수 578 추천 수 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
번호 분류 제목 글쓴이 날짜 조회 수
272 운영관리 윈도우 OS에서 java sp 사용 시 Java VM is not running 해결 방법 정훈 2017.09.04 16
271 질의작성 테이블 명세서 쿼리 1 성진 2017.08.04 166
» 질의작성 loose index scan을 활용한 효과적인 쿼리 튜닝 방안 박세훈 2017.06.01 578
269 운영관리 CUBRID 8.2.2 ~ 8.4.x 버전에서 'ALTER SERIAL ... CACHE <cached_num> / NOCACHE' ERROR 해결 방법 주영진 2017.03.08 1170
268 질의작성 CONNECT BY 절 포함 질의 튜닝 예제 박세훈 2017.02.28 1521
267 질의작성 UPDATE에서 조인을 통해, 결과값 수정하는 방법 박동윤 2016.12.27 1749
266 질의작성 subquery를 이용한 튜닝예제 file 김창휘 2016.12.27 1856
265 기타 Oracle import 시 character set 변경관련 김창휘 2016.12.26 1935
264 기타 JBoss 사용 시 statement pooling 설정 손승일 2016.12.16 1332
263 기타 Tomcat's JDBC Pool 사용 시 JNDI DataSource 설정 손승일 2016.12.16 1611
262 응용개발 CUBRID 와 DBCP의 관계 엄기호 2016.12.14 1157
261 질의작성 입력된 년월 또는 두개의 날짜을 이용하여 달력 및 주차 구하기 1 성진 2016.08.10 3323
260 운영관리 큐브리드 포트 정리 정만영 2016.07.05 3431
259 운영관리 큐브리드 마스터 소켓 디렉토리 변경방법 정만영 2016.07.05 3427
258 응용개발 Windows 환경에서 JAVA SP 사용 utf-8 한글 깨짐 해결 file 손승일 2016.07.01 3332
257 질의작성 INSTR함수 사용하기 엄기호 2016.06.29 4219
256 질의작성 group_concat(문자열 그룹처리) 함수 사용하기 권호일 2016.06.29 3602
255 질의작성 두 datetime 연산결과를 "?일 ?시:?분:?초"로 표시하기 권호일 2016.05.18 3342
254 운영관리 Invalid XASL tree node content 에러 손승일 2016.04.26 3783
253 질의작성 ORACLE 테이블 및 컬럼 COMMENT 일광등록 스크립트 1 김창휘 2016.04.04 5176
Board Pagination Prev 1 2 3 4 5 6 7 8 9 10 ... 14 Next
/ 14

Contact Cubrid

Tel. 070-4077-2110 / Email. contact_at_cubrid.com
Contact Sales