(1) Index Rang Scan
(2) Index Full Scan
Index Full Scan의 효율성
인덱스를 이용한 소트 연산 대체
select /*+ first_rows */ * from emp
where sal > 1000
order by ename
Execution Plna
------------------------------------------------------------------------
..
INDEX (FULL SCAN) OF 'EMP_IDX' (INDEX)
first_rows_n : 완전 비용기준 최적화 방법 (all_rows,first_rows_n), 처음 n건을 가장 빠르게 출력하기 위한 목표로 비용 계산.
(1, 10, 100, 1000)
first_rows : 비용기준과 경험적 방법(Heuristic method)을 혼합한 접근방법, first_rows 는 마치 과거에 'choose' 모드가
그러했던 것처럼 통계정보를 기반으로 한 완전한 비용기준 접근법과 어떤 규칙을 가지고 최적화를 선택해 가는 방법 중에서
주어진 상황에 따라 선택하여 최적화를 수행하는 방식.
(3) Index Unique Scan
(4) Index Skip Scan
<p..45 그림 1-9 참고>
| Range Scan |
| select * from 사원 where 성별 = '남' and 연봉 between 2000 and 4000 |
| 1. 성별이 '남'이면서 연봉이 2,000과 4,000 사이에 있는, 그 중 가장 작은 첫번째 레코드 찾는다. 2. 루트 블록의 세번째 레코드가 조건을 만족하는 레코드를 담당. 3번 리프블록을 찾아 간다. 3. 리브 블록을 차례로 스캔하다가 조건이 만족하지 않는 레코드를 만나면 스캔 중지. |
| Skip Scan |
| select /*_+ index_ss(사원 사원_IDX) */ * from 사원 where 연봉 between 2000 and 4000 |
| 1. 첫 번째 레코드가 가리키는 리프 블록은 '남 & 800' 미만인 레코드를 담고 있지만 혹시 '남' 보다 작은 값이 존재 할 지 모르므로 방문. 2. 두 번째 레코드는 '남 & 800' 이상이면서 '남 & 1500' 미만인 레코드이므로 Skip. 3. 세 번째 레코드는 '남 & 1500' 이상이면서 '남 & 5000' 미만인 레코드이므로 방문. 4. 네 번째 레코드는 '남 & 5000' 이상이면서 '남 & 8000' 미만인 레코드이므로 Skip. 다섯 번째도 마찬가지. 5. 여섯 번째 레코드는 '남 & 10000' 이상이지만 '여' 중에 '연봉 < 3000' 이거나 '남', '여' 사이에 다른 값이 존재 할지 모르므로 방문. 6. 일곱 번째는 조건에 포함되므로 방문. 7. 여덟, 아홉 번째 레코드가 가리키는 리프블록은 Skip. 8. 마지막 열 번째레코드는 '여' 보다 큰 미지의 값이 존재할지 모르므로 방문. |
버퍼 Pinning을 이용한 Skip 원리
| ☞ 버퍼 Pinning - 버퍼를 읽고 나서 버퍼 Pin을 즉각 해제하지 않고 데이터베이스 Call이 진행되는 동안 유지하는 기능. - 같은 블록을 반복적으로 읽을 때 버퍼 Pinning을 통해 래치 획득 과정을 생략한다면 논리적인 블록 읽기(Logical Read) 횟수를 획기적으로 줄일 수 있다. - 모든 버퍼 블록을 이 방식으로 읽는 것은 아니며, 같은 블록을 재 방문할 가능성이 큰 몇몇 오퍼레이션을 수행할 때만 사용한다. - v$sysstat, v$sesstat, v$mystat등을 조회해 보면, 래치 획득 과정을 통해 블록을 액세스 할 때는 session logical leads 항목이 증가하고, 래치 획득 과정 없이 버퍼 Pinning을 통해 블록을 곧바로 액세스할 때는 buffer is pinned count 항목의 수치가 증가한다. - 버퍼 Pinning은 하나의 데이터베이스 Call(Parse Call, Execute Call, Fetch Call)내에서만 유효하다. (Call이 끝나고 사용자에게 결과를 반환하고 나면 Pin은 해제되어야 한다. 따라서 첫 번째 Fetch Call에서 Pin된 블록은 두 번째 Fetch Call에서 다시 래치 획득 과정을 거쳐 Pin 되어야 한다.) - 버퍼 Pinning이 적용되던 지점은 인덱스를 스캔하면서 테이블을 액세스할 때의 인덱스 리프 블록이다. Index Range Scan하면서 인덱스와 테이블 블록을 교차 방문할 때 블록 I/O를 체크해 보면, 테이블 블록에 대한 I/O만 계속 증가하는 이유가 여기에 있다. |
Index Skip Scan이 작동하기 위한 조건
- 일별업종별거래_PK : 업종유형코드 + 업종코드 + 기준일자
1. 중간 컬럼에 대한 조건절이 누락된 경우
where 업종유형코드 = '01'
and 기준일자 between '20080501' and '20080531'
2. Distinct Value가 적은 두 개의 선두컬럼이 모두 누락된 경우
where 기준일자 between '20080501' and '20080531'
- 일별업종별거래_X01 : 기준일자 + 업종유형코드
3. 선두 컬럼이 부등호, between, like 같은 범위검색 조건일 경우.
where 기준일자 between '20080501' and '20080531'
and 업종유형코드 = '01'
- 선두 컬럼이 이처럼 범위검색 조건일 때 인덱스 스캔 단계에서 비효율이 발생하는데, 그럴 때 Index Skip Scan이 유용.
In-List Iterator와의 비교
select * from 사원
where 연봉 between 2000 and 4000
and 성별 in ('남', '여');
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS
1 0 INLIST ITERATOR
2 1 TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
3 2 INDEX (RANGE SCAN) OF '사원_IDX' (INDEX (INDEX))
1. 인덱스 루트 블록을 읽어 세 번째 레코드가 가리키는 3번 리프 블록을 읽고 거기서 멈추거나 4번 블록 첫 번째 레코드까지 스캔.
(남자사원 검색)
2. 다시 인덱스 루트 블록을 읽어 여섯 번째 레코드가 가리키는 6번 리프 블록을 시작으로 7번 또는 8번 블록까지 스캔.
(여자사원 검색)
(6) Index Fast Full Scan
create table big_table
nologging
as
select rownum id, a.*
from all_objects a
where 1 = 0
/
set verify off
declare
l_cnt number;
l_rows number := 1000000;
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a;
l_cnt := sql%rowcount;
commit;
while(l_cnt < l_rows)
loop
insert /*+ append */ into big_table
select rownum + l_cnt
, owner, object_name, subobject_name
, object_id, data_object_id
, object_type, created, last_ddl_time
, timestamp, status, temporary
, generated, secondary
from big_table
where rownum <= l_rows - l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/
alter table big_table add constraint big_table_pk primary key(id);
create index big_table_idx on big_table(object_name);
alter system flush buffer_cache;
show parameter multiblock
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
- FULL SCAN
select /*+ index(b big_table_idx) */ count(object_name)
from big_table b
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.009 0 78 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.453 0.914 4915 4900 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.453 0.924 4915 4978 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: KJWON (ID=62)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=4900 pr=4915 pw=0 time=914098 us)
1000000 INDEX FULL SCAN BIG_TABLE_IDX (cr=4900 pr=4915 pw=0 time=4020779 us)(Object ID 53818)
Wait Event Name Count Wait(sec) Max Wait
-------------------------------------------------- ------- ---------- --------
db file sequential read 625 0.182 0.007
SQL*Net message to client 2 0.000 0.000
SQL*Net message from client 2 0.002 0.001
db file scattered read 627 0.311 0.013
--------------------------------------------------- ------- --------- --------
Total 1256 0.49
- FAST FULL SCAN
select /*+ index_ffs(b big_table_idx) */ count(object_name)
from big_table b
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.016 0.009 0 78 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.391 0.757 4921 4935 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.406 0.766 4921 5013 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: KJWON (ID=62)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=4935 pr=4921 pw=0 time=756600 us)
1000000 INDEX FAST FULL SCAN BIG_TABLE_IDX (cr=4935 pr=4921 pw=0 time=4048031 us)(Object ID 53818)
Wait Event Name Count Wait(sec) Max Wait
-------------------------------------------------- ------- ---------- --------
SQL*Net message to client 2 0.000 0.000
SQL*Net message from client 2 0.003 0.002
db file scattered read 321 0.389 0.045
--------------------------------------------------- ------- --------- --------
Total 325 0.39
Index Fast Full Scan의 특징
| Index Full Scan | Index Fast Full Scan |
| 1. 인덱스 구조를 따라 스캔 2. 결과집합 순서 보장 3. Single Block I/O 4. 병렬스캔 불가(파티션 돼 있지 않으면) 5. 인덱스에 포함되지 않은 컬럼 조회 시에도 사용가능 |
1. 세그먼트 전체를 스캔
2. 결과집합 순서 보장 않됨
3. Multiblock I/O
4. 병렬스캔 가능
5. 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능 |
Index Fast Full Scan을 활용한 튜닝 사례
select * from 공급업체
where 업체명 like '%네트웍스%'
- TABLE ACCESS Full
- 실행시간 5초 이상 소요
- 결과 건수 21
=>
select /*+ ordered use_nl(b) no_merge(b) rowid(b) */ b.*
from (select /*+ index_ffs(공급업체 공급업체_X01) */ rowid rid
from 공급업체
whjere instr(업체명, '네트웍스') > 0) a, 공급업체 b
where b.rowid = a.rid
- 업체명 컬럼의 인덱스를 Fast Full Scan 해서 얻은 rowid를 이용해 테이블을 다시 억세스.
- like 연산자보다 빠른 instr 함수 사용.
- 최종 결과 건수가 많아지더라도 부분범위 처리가 가능한 애플리케이션 환경이면 유리.
- 11g라면 결과 건수가 많더라도 인라인 뷰에 'order by rowid'를 추가함으로써 큰 효과를 얻을 수 있다(5절 (5)항에서 설명)
- 데이터 건수가 많다면 parallel_index 힌트를 이용해 병렬쿼리도 가능.
(6) Index Range Scan Descending
- max 값을 구할 때 해당 컬럼에 인덱스가 있으면 인덱스를 뒤쪽에서부터 한 건만 읽고 멈추는 실행 계획이 자동으로 수립
- FIRST ROW
INDEX (RANGE SCAN (MIN/MAX)) OF 'EMP_X02' (INDEX)
- first row(min/max) 알고리즘이 개발되기 전(오라클 7버전)에는 index_desc 힌트와 rownum <= 1 조건을 사용
(7) And-Equal, Index Combine, Index Join
create index emp_deptno_idx on emp(deptno);
create index emp_job_idx on emp(job);
And-Equal
select /*+ and_equal(e emp_deptno_idx emp_job_idx) */ *
from emp e
where deptno = 30
and job = 'SALESMAN';
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 348 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 4 | 348 | 3 (0)| 00:00:01 |
| 2 | AND-EQUAL | | | | | |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IDX | 4 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 6 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Index Combine
select /*+ index_combine(e emp_deptno_idx emp_job_idx) */ *
from emp e
where deptno = 30
and job = 'SALESMAN';
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 37 | 4 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IDX | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
1. 일반 B*Tree 인덱스를 스캔하면서 각 조건을 만족하는 레코드의 rowid 목록을 얻는다.(5, 7)
2. 1단계에서 얻은 rowid 목록을 가지고 비트맵 인덱스 구조를 하나씩 만든다
(4, 6 - 조건을 만족하는 레코드의 비트를 '1'로 설정, 비트맵 인데스가 있으면 1~2 생략)
3. 비트맵 인덱스에 대한 Bit-Wise 오퍼레이션 수행.(3)
4. Bit-Wise 오퍼페이션을 수행한 결과가 '참(true)'인 비트 값들을 rowid 값으로 환산해 최종적으로 방문할 테이블의
rowid 목록을 얻는다.(2)
5. rowid를 이용해 테이블을 액세스한다.(1)
select /*+ index_combine(e emp_deptno_idx emp_job_idx) */ *
from emp e
where deptno = 30
or job = 'SALESMAN';
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 296 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 8 | 296 | 14 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | EMP_JOB_IDX | | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Index Join
select /*+ index_join(e emp_deptno_idx emp_job_idx) */ deptno, job
from emp e
where deptno = 30
and job = 'SALESMAN';
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (34)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 11 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN| EMP_JOB_IDX | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| EMP_DEPTNO_IDX | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
1. 크기가 비교적 작은 쪽 인덱스에서 키 값과 rowid를 읽어 PGA 메모리에 해시 맵을 생성. 해쉬 키로는 rowid가 사용.
2. 다른 쪽 인덱를 스캔하면서 앞서 생성한 해시 맵에 같은 rowid 값을 갖는 레코드가 있는지 탐색.
3. rowid끼리 조인에 성공한 레코드만 결과집합에 포함.