[오라클힌트skip scanning]index skip scanning(ISS,ORACLE힌트), [자바개발자교육/자바교육/자바강좌/자바교육잘하는곳/자바교육추천/자바실무교육/JAVA/JAVA교육/JAVA학원/JAVA실무교육]
“skip scanning” for index
구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr
전통적인 “복합 인덱스(composite index)”의 경우 가장 많이 사용되고 분포도가 좋은 컬럼을 leading column으로 하는 것을 원칙으로 하고 있다. 그 이유는 여러 컬럼으로 인덱스가 구성되었을 경우 맨 처음 나타나는 컬럼이 중요한데, where절에 leading column은 기술하지 않고 그 외의 컬럼을 기술하는 경우에는 index를 사용 할 수 없지만 index 컬럼만을 where절에 기술하는 경우엔 인덱스의 사용이 가능 하기 때문이다. (물론 select 항목이 index 영역에서 몽땅 가지고 올 수 있는 경우엔 CBO인 경우 leading columns이 where 절에 없더라도 인덱스를 full scan하여 데이터를 가지고 온다.)
이를 보완하기 위해 Oracle9i 이후 leading column을 모르더라도 index를 사용 할 수 있는 방법이 있는데 이러한 형태로 데이터를 찾아 가는 것을 “skip scanning”이라고 한다.
skip scanning의 특징은 다음과 같다.
- 때때로 발생하는 leading 컬럼이 아닌 컬럼의 값만을 where절에 사용하는 Query를 지원
- 잠재적으로 많은 수의 인덱스 생성 필요성의 감소를 의미하며 따라서 DML의 성능 향상에 도움
- 복합 인덱스 생성시 어떤 것을 리딩 컬럼으로 할지에 대해 고민하지 않아도 되며 분포도(선택성)가 좋은 컬럼과 사용 빈도가 높은 컬럼이 있을 때 composite index를 만들 것인지 아니면 별도의 인덱스 2개를 만들 것인지에 대해 고민하지 않아도 된다.
- reverse key, bitmap, domain, function-based index는 지원하지 않는다.
- INDEX SKIP SCAN은 CBO (비용기반최적화) 를 필요로 한다.
- SKIP INDEX는 복합키를 논리적으로 작은 인덱스로 분해 하는 개념이며 리딩 컬럼은 SKIP 된다.
- SKIP SCANNING 은 LEADING COLUMN의 DISTINCT VALUE가 적을수록 하고 NONLEADING COLUMN의 DISTINCT VALUE가 클수록 좋다.
처리 방식
- index는 branch block을 통해 leaf block을 찾아간다.
- 따라서 branch block별 첫번째 엔트리부터 마지막 엔트리까지 대소 비교를 통해 사용자가 찾으려는 두번째 컬럼의 존재 유무를 확인한다.
- 원하는 값을 찾을 가능성이 있으면 해당 leaf block 전체를 read 한다.
- 원하는 값이 없다는 판단을 하면 (branch block의 대소 비교를 통해) skip 한다.
- 원하는 값이 있을 가능성이 있어 read하는 경우 원하는 값의 범위를 넘는 값이 나타나면 나머지를 skip 한다.
- 이상과 같은 skip scanning을 통해 block을 읽는 범위를 줄여 성능 향상을 가지고 올 수 있다.
- 계층질의나 집계함수 질의는 대상 아님.
---------------------------------------
[예]
실습을 위한 데이터는 아래 URL에서 생성하자.
http://oraclejavanew.kr/bbs/board.php?bo_table=LecOrccleTun&wr_id=53&sfl=&stx=&spt=0&page=0
2000만건인 myemp1에서 부서코드(deptno) 컬럼이 가지는 값은 0,1,2,3,4 이니 이 컬럼을 선두 컬럼으로하고 ename 컬럼을 다음에 그리고 sal 컬럼을 위치 시키자.
결과를 보면 알겠지만 리딩컬럼을 제외하고 쿼리 했을 때 대체적으로 ISS(Index Skip Scanning)을 이용했으면 큰 무리는 없었다.
SQL> create index idx_myemp1_deptno_ename_sal on myemp1(deptno, ename, sal) nologging;
인덱스가 생성되었습니다.
별다르게 index_ss 힌트를 주지 않아도 index skip scanning을 한다.
SQL> select count(ename) from myemp1
2 where ename = '홍길동456771';
COUNT(ENAME)
------------
1
경 과: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2541676498
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX SKIP SCAN| IDX_MYEMP1_DEPTNO_ENAME_SAL | 1 | 15 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='홍길동456771')
filter("ENAME"='홍길동456771')
SQL> select count(ename) from myemp1
2 where ename > '홍길동456771';
COUNT(ENAME)
------------
1207173
경 과: 00:00:01.68
Execution Plan
----------------------------------------------------------
Plan hash value: 2541676498
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX SKIP SCAN| IDX_MYEMP1_DEPTNO_ENAME_SAL | 1 | 15 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
SQL> select count(ename) from myemp1
2 where ename > '홍길동456771'
3 and sal > 1999999;
COUNT(ENAME)
------------
400000
경 과: 00:00:01.54
Execution Plan
----------------------------------------------------------
Plan hash value: 2541676498
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | INDEX SKIP SCAN| IDX_MYEMP1_DEPTNO_ENAME_SAL | 1 | 21 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
두번 째 실행할 때는 훨씬 빠르다.
SQL> select count(ename)
3 from myemp1
4 where ename > '홍길동456771'
5 and sal > 1999999;
COUNT(ENAME)
------------
400000
경 과: 00:00:00.39
위 쿼리를 인덱스 Fast Full Scan으로 해보자. 많이 느리게 나온다.
SQL> select /*+ index_ffs(e IDX_MYEMP1_DEPTNO_ENAME_SAL) */
2 count(ename)
3 from myemp1 e
4 where ename > '홍길동456771';
COUNT(ENAME)
------------
1207173
경 과: 00:00:15.75
Execution Plan
----------------------------------------------------------
Plan hash value: 3338731707
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 25899 (1)| 00:05:11 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_MYEMP1_DEPTNO_ENAME_SAL | 1 | 15 | 25899 (1)| 00:05:11 |
-----------------------------------------------------------------------------------------------------
이번에는 ename을 등호로 검색해 보자.
SQL> select /*+ index_ffs(e IDX_MYEMP1_DEPTNO_ENAME_SAL) */
2 count(ename)
3 from myemp1 e
4 where ename = '홍길동456771';
COUNT(ENAME)
------------
1
경 과: 00:00:20.61
Execution Plan
----------------------------------------------------------
Plan hash value: 3338731707
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 25899 (1)| 00:05:11 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_MYEMP1_DEPTNO_ENAME_SAL | 1 | 15 | 25899 (1)| 00:05:11 |
-----------------------------------------------------------------------------------------------------
오라클자바커뮤니티에서 운영, 개발자 전문교육, 개인80%환급 오엔제이프로그래밍실무교육센터(www.onjprogramming.co.kr)“skip scanning” for index
구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr
전통적인 “복합 인덱스(composite index)”의 경우 가장 많이 사용되고 분포도가 좋은 컬럼을 leading column으로 하는 것을 원칙으로 하고 있다. 그 이유는 여러 컬럼으로 인덱스가 구성되었을 경우 맨 처음 나타나는 컬럼이 중요한데, where절에 leading column은 기술하지 않고 그 외의 컬럼을 기술하는 경우에는 index를 사용 할 수 없지만 index 컬럼만을 where절에 기술하는 경우엔 인덱스의 사용이 가능 하기 때문이다. (물론 select 항목이 index 영역에서 몽땅 가지고 올 수 있는 경우엔 CBO인 경우 leading columns이 where 절에 없더라도 인덱스를 full scan하여 데이터를 가지고 온다.)
이를 보완하기 위해 Oracle9i 이후 leading column을 모르더라도 index를 사용 할 수 있는 방법이 있는데 이러한 형태로 데이터를 찾아 가는 것을 “skip scanning”이라고 한다.
skip scanning의 특징은 다음과 같다.
- 때때로 발생하는 leading 컬럼이 아닌 컬럼의 값만을 where절에 사용하는 Query를 지원
- 잠재적으로 많은 수의 인덱스 생성 필요성의 감소를 의미하며 따라서 DML의 성능 향상에 도움
- 복합 인덱스 생성시 어떤 것을 리딩 컬럼으로 할지에 대해 고민하지 않아도 되며 분포도(선택성)가 좋은 컬럼과 사용 빈도가 높은 컬럼이 있을 때 composite index를 만들 것인지 아니면 별도의 인덱스 2개를 만들 것인지에 대해 고민하지 않아도 된다.
- reverse key, bitmap, domain, function-based index는 지원하지 않는다.
- INDEX SKIP SCAN은 CBO (비용기반최적화) 를 필요로 한다.
- SKIP INDEX는 복합키를 논리적으로 작은 인덱스로 분해 하는 개념이며 리딩 컬럼은 SKIP 된다.
- SKIP SCANNING 은 LEADING COLUMN의 DISTINCT VALUE가 적을수록 하고 NONLEADING COLUMN의 DISTINCT VALUE가 클수록 좋다.
처리 방식
- index는 branch block을 통해 leaf block을 찾아간다.
- 따라서 branch block별 첫번째 엔트리부터 마지막 엔트리까지 대소 비교를 통해 사용자가 찾으려는 두번째 컬럼의 존재 유무를 확인한다.
- 원하는 값을 찾을 가능성이 있으면 해당 leaf block 전체를 read 한다.
- 원하는 값이 없다는 판단을 하면 (branch block의 대소 비교를 통해) skip 한다.
- 원하는 값이 있을 가능성이 있어 read하는 경우 원하는 값의 범위를 넘는 값이 나타나면 나머지를 skip 한다.
- 이상과 같은 skip scanning을 통해 block을 읽는 범위를 줄여 성능 향상을 가지고 올 수 있다.
- 계층질의나 집계함수 질의는 대상 아님.
---------------------------------------
[예]
실습을 위한 데이터는 아래 URL에서 생성하자.
http://oraclejavanew.kr/bbs/board.php?bo_table=LecOrccleTun&wr_id=53&sfl=&stx=&spt=0&page=0
2000만건인 myemp1에서 부서코드(deptno) 컬럼이 가지는 값은 0,1,2,3,4 이니 이 컬럼을 선두 컬럼으로하고 ename 컬럼을 다음에 그리고 sal 컬럼을 위치 시키자.
결과를 보면 알겠지만 리딩컬럼을 제외하고 쿼리 했을 때 대체적으로 ISS(Index Skip Scanning)을 이용했으면 큰 무리는 없었다.
SQL> create index idx_myemp1_deptno_ename_sal on myemp1(deptno, ename, sal) nologging;
인덱스가 생성되었습니다.
별다르게 index_ss 힌트를 주지 않아도 index skip scanning을 한다.
SQL> select count(ename) from myemp1
2 where ename = '홍길동456771';
COUNT(ENAME)
------------
1
경 과: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2541676498
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX SKIP SCAN| IDX_MYEMP1_DEPTNO_ENAME_SAL | 1 | 15 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='홍길동456771')
filter("ENAME"='홍길동456771')
SQL> select count(ename) from myemp1
2 where ename > '홍길동456771';
COUNT(ENAME)
------------
1207173
경 과: 00:00:01.68
Execution Plan
----------------------------------------------------------
Plan hash value: 2541676498
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX SKIP SCAN| IDX_MYEMP1_DEPTNO_ENAME_SAL | 1 | 15 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
SQL> select count(ename) from myemp1
2 where ename > '홍길동456771'
3 and sal > 1999999;
COUNT(ENAME)
------------
400000
경 과: 00:00:01.54
Execution Plan
----------------------------------------------------------
Plan hash value: 2541676498
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | INDEX SKIP SCAN| IDX_MYEMP1_DEPTNO_ENAME_SAL | 1 | 21 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
두번 째 실행할 때는 훨씬 빠르다.
SQL> select count(ename)
3 from myemp1
4 where ename > '홍길동456771'
5 and sal > 1999999;
COUNT(ENAME)
------------
400000
경 과: 00:00:00.39
위 쿼리를 인덱스 Fast Full Scan으로 해보자. 많이 느리게 나온다.
SQL> select /*+ index_ffs(e IDX_MYEMP1_DEPTNO_ENAME_SAL) */
2 count(ename)
3 from myemp1 e
4 where ename > '홍길동456771';
COUNT(ENAME)
------------
1207173
경 과: 00:00:15.75
Execution Plan
----------------------------------------------------------
Plan hash value: 3338731707
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 25899 (1)| 00:05:11 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_MYEMP1_DEPTNO_ENAME_SAL | 1 | 15 | 25899 (1)| 00:05:11 |
-----------------------------------------------------------------------------------------------------
이번에는 ename을 등호로 검색해 보자.
SQL> select /*+ index_ffs(e IDX_MYEMP1_DEPTNO_ENAME_SAL) */
2 count(ename)
3 from myemp1 e
4 where ename = '홍길동456771';
COUNT(ENAME)
------------
1
경 과: 00:00:20.61
Execution Plan
----------------------------------------------------------
Plan hash value: 3338731707
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 25899 (1)| 00:05:11 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_MYEMP1_DEPTNO_ENAME_SAL | 1 | 15 | 25899 (1)| 00:05:11 |
-----------------------------------------------------------------------------------------------------
평일주간(9:30~18:30) 개강
(4/07)[기업100%환급]SQL기초에서 Schema Object까지
(4/07)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
(4/14)C#4.0,ADO.NET,Network 프로그래밍
(4/14)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(4/14)[기업100%환급]PL/SQL,ORACLE HINT,TUNING
평일야간(19:00~22:00) 개강
(4/07)SQL초보에서실전전문가까지
(4/08)Spring3.X, MyBatis, Hibernate실무과정
(4/10)C#,ASP.NET마스터
(4/10)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(4/14)웹퍼블리싱 마스터
+ (4/22)안드로이드개발자과정
주말(10:00~18:00) 개강
(4/12)웹퍼블리싱 마스터
(4/12)SQL초보에서실전전문가까지
(4/12)안드로이드개발자과정
(4/12)JAVA기초에서실무까지
(4/19)C#,ASP.NET마스터
(4/19)Spring3.X, MyBatis, Hibernate실무과정
댓글 없음:
댓글 쓰기