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
번호 분류 제목 글쓴이 날짜 조회 수
279 운영관리 csql 데이터 입력방법 정만영 2009.06.09 212696
278 마이그레이션 CUBRID vs MySQL vs ORACLE SQL 타입별 비교 정만영 2013.07.12 69109
277 응용개발 weblogic 8.1 에 CUBRID Connection Pool, DataSource 설정 방법 file 웁쓰 2009.07.01 66305
276 응용개발 WHERE 조건에서 다중 컬럼 IN절 처리 최적화 방법 (cubrid + ibatis) 1 이상신 2015.08.21 55065
275 응용개발 Weblogic 10.0 사용시 JDK 1.5를 사용한 JDBC 드라이버 사용시 주의사항. cubebridge 2012.02.22 51957
274 마이그레이션 타 DBMS를 CUBRID로 마이그레이션 시 varchar,char 컬럼 사이즈 관련 손승일 2010.07.01 38012
273 질의작성 데이터 존재하면 update, 존재하지 않으면 insert 방법(ON DUPLICATE KEY UPDATE) 손승일 2010.08.11 37990
272 질의작성 예약어를 테이블명이나 컬럼명으로 사용시 admin 2008.11.21 37304
271 응용개발 JDBC 사용시 SQL 로깅 - p6spy 사용 1 웁쓰 2009.07.01 35985
270 응용개발 CUBRID AUTO_INCREMENT 컬럼 MySQL LAST_INSERT_ID() 대체 방법 손승일 2009.12.22 33233
269 운영관리 CUBRID에서의 BLOB/CLOB 사용시 백업 및 복구에 대한 주의 점 cubebridge 2012.09.18 33156
268 마이그레이션 MySQL의 limit 명령어 처리 1 admin 2008.11.21 32682
267 기타 좀비 프로세스 일괄 삭제하기 janus 2009.12.12 32051
266 CUBRID 매니저 Java SP사용시 JNI_CreateJavaVM 에러메시지 해결 방법 file seongjoon 2009.07.15 32041
265 응용개발 php에서 serialize/unserialize 사용 시 주의할 점 시난 2009.12.29 31624
264 운영관리 CUBRID 사용시 방화벽 설정 3 admin 2008.11.21 30980
263 마이그레이션 CUBRID 하위버젼에서 CUBRID2008로의 업그레이드 방법 1 남재우 2008.12.06 30153
262 운영관리 큐브리드 사용포트 정리 3 정만영 2009.06.19 29744
261 질의작성 CUBRID DB 내의 auto_increment 값을 초기화 하자. 손승일 2009.10.28 29718
260 응용개발 CUBRID와 Oracle에서의 ''(공백)의 차이 비교 cubebridge 2010.10.01 28582
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