Background Image
조회 수 2382 추천 수 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
번호 분류 제목 글쓴이 날짜 조회 수
259 운영관리 큐브리드 마스터 소켓 디렉토리 변경방법 정만영 2016.07.05 4212
258 응용개발 Windows 환경에서 JAVA SP 사용 utf-8 한글 깨짐 해결 file 손승일 2016.07.01 4582
257 질의작성 INSTR함수 사용하기 엄기호 2016.06.29 9224
256 질의작성 group_concat(문자열 그룹처리) 함수 사용하기 권호일 2016.06.29 28119
255 질의작성 두 datetime 연산결과를 "?일 ?시:?분:?초"로 표시하기 권호일 2016.05.18 6345
254 운영관리 Invalid XASL tree node content 에러 손승일 2016.04.26 4469
253 질의작성 ORACLE 테이블 및 컬럼 COMMENT 일광등록 스크립트 1 김창휘 2016.04.04 9314
252 응용개발 Windows에서 32bit 버전의 PHP 설치 후 CUBRID와 연동 실패 시 해결 방법 file 진우진 2016.03.28 4514
251 CUBRID 매니저 큐브리드 매니저 호스트 접속 시 JDBC 드라이버 찾을 수 없는 오류 해결 1 file 진우진 2016.03.28 6750
250 질의작성 스키마 및 인덱스 선언에 따른 최대 용량 산정을 위한 ROW SIZE 확인 성진 2016.03.21 4167
249 질의작성 Oracle UTL_ENCODE.TEXT_ENCODE를 CUBRID로 변환하기 김창휘 2016.03.21 4625
248 질의작성 테이블 리스트 취합 SQL 김창휘 2016.03.19 5510
247 운영관리 산술 연산 결과를 피젯수 또는 젯수의 자리 수에 맞춰 보자. 성진 2016.03.01 4805
246 질의작성 테이블 컬럼 변경 및 추가 정만영 2016.02.29 21696
245 마이그레이션 CMT를 이용하여 원본 특정 테이블의 일부 데이터만 가져와 대상 테이블에 넣기 엄기호 2016.01.28 4550
244 질의작성 FOR UPDATE 정만영 2016.01.27 5983
243 운영관리 'Has been interrupted.' CUBRIDException 발생 주현 2016.01.07 6564
242 질의작성 각 테이블 PK 유무 확인 쿼리문 엄기호 2016.01.01 8974
241 질의작성 ORACLE TRIGGER를 CUBRID TRIGGER로 변환하기 김창휘 2015.12.31 6455
240 응용개발 CUBRID DB에서 critical section 정보 출력하기 file 주현 2015.12.31 4552
Board Pagination Prev 1 2 3 4 5 6 7 8 9 10 ... 14 Next
/ 14

Contact Cubrid

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