[SQLP] 2-3. 인덱스 확장기능 사용법

인덱스 기본 사용법	Index Range Scan 중심
인덱스 확장기능 사용법	Index Full Scan, Index Unique Scan, Index Skip Scan, Index Fast Full Scan

1. Index Range Scan [B*Tree 인덱스의 가장 일반적인 형태의 액세스]

 

인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후 필요한 범위만 스캔

 

SET AUTOTRACE TRACEONLY EXP
SELECT * FROM emp WHERE deptno=20;

 

Execution Plan
------------------------------------------------------------------
SELECT STAEMENT Optimizer=ALL_ROWS
 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
  INDEX (RANGE SCAN) OF 'EMP_DEPTNO+IDX' (INDEX)

-> 인덱스를 Range Scan 하려면 선두 칼럼을 가공하지 않은 상태로 조건절에 사용

-> 선두 칼럼을 가공하지 않은 상태로 조건절에 사용하면 Index Range Scan 항상 가능

[인덱스를 탄다고 해서 성능에 대해 단언하면 안되는 이유 -> 성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 줄이는데]

 

---

2. Index Full Scan

: 수직적 탐색없이 인덱스 리프블록을 처음부터 끝까지 수평적으로 탐색

[일반적으로 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택]

 

CREATE INDEX emp_ename_sal_idx on EMP (ename, sal);

SET AUTOTRACE TRACEONLY EXP

 

Execution Plan
---------------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS
 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
  INDEX (FULL SCAN) OF 'EMP_ENAME_SAL_IDX' (INDEX)

-> 인덱스 선두 칼럼인 ENAME이 조건절에 없어서, Index Range Scan이 불가능하고,

 

SAL 칼럼이 인덱스에 있으므로 Index Full Scan을 통해 조건에 맞는 레코드 출력

 

---

Index Full Scan의 효용성

: 인덱스 선두 칼럼이 조건절에 없을 때 먼저 Table Full Scan 고려 후 대용량 테이블인 경우 인덱스 활용 고려

 

대용량 테이블인 경우 인덱스 활용

데이터 저장공간 : 컬럼길이 X 레코드 수에 의해 결정되므로, 인덱스가 차지하는 면적은 테이블보다 적다.

인덱스를 Range Scan 할 수 없을 때, 테이블 전체 스캔이 아닌 인덱스 전체 스캔

 

인덱스 전체 스캔할 경우

-> 인덱스 스캔 과정에서 대부분 레코드를 필터링하고 일부만 테이블 액세스하는 상황이라면, 면적이 큰 테이블보다 인덱스 스캔하는 게 유리

-> 옵티마이저가 Index Full Scan 방식 선택

 

CREATE INDEX emp_ename_sal_idx on EMP (ename, sal);

SELECT *
 FROM EMP
 WHERE sal >9000
 ORDER BY name;

 

Execution Plan
----------------------------------------------------------
SELECT STAEMENT Optimizer=ALL_ROWS
 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
  INDEX (FULL SCAN) OF 'EMP_ENAME_SAL_IDX' (INDEX)

-> 조건절에 해당하는 레코드가 전체 중 소량이라면 Table Full Scan이 아닌 Index Full Scan을 통한 필터링이 효율적

[적절한 인덱스가 없어서 Index Range Scan의 차선택으로 선택하는 것으로

 수행빈도가 낮지 않다면, SAL 칼럼이 선두인 인덱스 생성하는 것이 성능에 유리]

 

---

인덱스를 이용한 정렬 연산 생략

: Index Full Scan도 Index Range Scan과 마찬가지로 인덱스 칼럼 순으로 정렬

-> Sort Order By 연산 생략 목적으로 사용 가능

 

SELECT /*+ first_rows */ *
 FROM EMP
 WHERE sal >1000
 ORDER BY ename;

 

Execution Plan
----------------------------------------------------------
SELECT STAEMENT Optimizer=FIRST_ROWS
 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
  INDEX (FULL SCAN) OF 'EMP_ENAME_SAL_IDX' (INDEX)

-> 조건절에 해당하는 레코드가 전체 중 대량일 때엔,  Index Full Scan을 선택하면, 거의 모든 레코드에 대해 테이블 액세스가 발생하므로, Table Full Scan보다 불리하다. [SAL이 인덱스 선투 칼럼이고, Index Range Scan할 경우에도 동일]

 

-> 힌트로 frist_rows를 설정해 옵티마이저 모드를 바꾸고, 소트 연산 생략함으로써 전체 집합 중 처음 일부를 빠르게 출력할 목적으로 Index Full Scan 선택

 

[부분범위 처리가 가능한 상황에서 극적인 성능 개선 효과]

-> 부분범위 처리 활용할 의도와 달리 fetch를 멈추지 않고 데이터를 끝까지 읽는 경우 Table Full Scan보다 많은 I/O를 일으키고 수행속도도 느려진다.

 

---

3. Index Unique Scan

: 수직적 탐색만으로 데이터를 찾는 스캔 방식 [Unique 인덱스를 '='조건으로 탐색하는 경우 작동]

CREATE UNIQUE INDEX pk_emp on emp(empno);
ALTER TABLE EMP ADD
 CONSTRAINT pk_emp PRIMARY KEY(empno) USING INDEX pk_emp;
 
 SET AUTOTRACE TRACEONLY EXPLAIN
  SELECT empno, ename FROM EMP WHERE empno=7788;

 

Execution Plan
----------------------------------------------------------
SELECT STAEMENT Optimizer=ALL_ROWS
 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
  INDEX (UNIQUE SCAN) OF 'EMP_ENAME_SAL_IDX' (UNIQUE)

-> Unique 인덱스가 존재하는 칼럼은 중복 값이 입력되지 않게 데이터 정합성 관리

-> 해당 인덱스 칼럼을 '='조건으로 검색시에는 데이터 한건 찾는 순간 정지

 

-> Unique 인덱스인 경우에도 범위검색 조건일 경우 Index Range Scan으로 처리

[empno >= 7788 조건으로 검색시 수직적 탐색만으로 조건에 해당하는 모든 레코드를 못 찾기 때문]

 

-> Unique 결합 인덱스에 대해 일부 칼럼만 검색할 경우에도 Index Range Scan

[ [주문일자 + 고객ID + 상품ID]로 구성한 주문상품 PK 인덱스를 주문일자와 고객ID로만 검색할 경우]

 

 

---

4. Index Skip Scan 

:인덱스 선두 칼럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔방식 [오라클]

[조건절에 빠진 인덱스 선두 칼럼의 Dinstinct Value 개수가 적고 후행 칼럼의 Distinct Value 개수가 많을 때 유용]

 

:루트 또는 브랜치 블록에서 읽은 칼럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성이 있는 '리프 블록만 골라서 액세스하는 스캔 방식 [미지의 값에 해당하는 레코드가 속한 블록을 찾아서 액세스]

 

 

-> 일반적으로 인덱스 선두 칼럼을 조건절에 사용하지 않으면, Table Full Scan 선택

[Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면, Index Full Scan 사용]

 

---

성별과 연봉 칼럼에 대한 조건식 모두 사용시 처리 과정

SELECT * FROM 사원 WHERE 성별 ='남' AND 연봉 BETWEEN 2000 AND 4000;

1. 성별 ='남'이면서 연봉 >=2000 인 첫 번째 레코드 찾기

2. 루트 블럭 3번째 레코드가 가리키는 3번 리프 블록으로 찾아간다.

3. 3번 리프 블록에서 성별 ='남'이면서 연봉>=2000인 첫 번째 레코드를 만나서, 리프 블록 차례로 스캔하다가

성별 ='남'이면서 연봉>4000인 첫 번째 레코드 만나면 스캔 멈춘다.

 

인덱스 선두 칼럼인 성별 조건 뺀 Index Skip Scan 작동원리

[이 스캔 방식 유도 : index_ss, 방지 : no_index_ss]

SELECT /*+index_ss(사원 사원_IDX) */ *
FROM 사원
WHERE 연봉 BETWEEN 2000 AND 4000;

 

Execution Plan
-------------------------------------------------------------
SELECT STATEMENT Optimizer = ALL_ROWS
 TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
  INDEX (SKIP SCAN) OF '사원_IDX' (INDEX)

1. 인덱스 루트 블록 첫 번째 레코드가 가리키는 리프 블록 [남 & 800이하] -> 액세스 넘기기

2. '남'보다 작은 성별값이 존재한다면, 그 사원에 대한 인덱스 레코드가 모두 1번 리프블록에 저장된다. -> 액세스

-> 옵티마이저가 성별에 '남', '여' 두 개 값만 존재한다는 사실을 모르기 때문

3. 인덱스 루트 블록 두 번째 레코드가 가리키는 리프 블록 [남 & 800이상] &  [남 & 1500이하] -> 액세스

4. 인덱스 루트 블록 세 번째 레코드가 가리키는 리프 블록 [남 & 1500이상] &  [남 & 5000이하] -> 액세스 넘기기

5. 인덱스 루트 블록 네 번째 레코드가 가리키는 리프 블록 [남 & 5000이상] &  [남 & 8000이하] -> 액세스 넘기기

6. 인덱스 루트 블록 다섯 번째 레코드가 가리키는 리프 블록 [남 & 8000이상] &  [남 & 10000이하] -> 액세스 넘기기

7. 인덱스 루트 블록 여섯 번째 레코드가 가리키는 리프 블록 [남 & 10000이상] -> 2000<=연봉<=4000 구간을 초과

하지만-> 액세스 필요

[여자 중에서 [연봉< 3000]이거나 '남'과 '여' 사이에 다른 성별이 혹시 존재한다면 이 리프 블록에 저장되고, 연봉 =3000인 여자 직원도 뒤쪽에 일부 저장돼 있을 수 있기 때문

 

8. 인덱스 루트 블록 일곱 번째 레코드가 가리키는 리프 블록 [여 & 3000이상] -> 액세스

9. 인덱스 루트 블록 여덟 번째 레코드가 가리키는 리프 블록 [여 & 5000이상] ->액세스 넘기기

9. 인덱스 루트 블록 아홉 번째 레코드가 가리키는 리프 블록 [여 & 7000이상] ->액세스 넘기기

10. 인덱스 루트 블록 열 번째 레코드가 가리키는 리프 블록 [여 & 10000이상] -> 2000<=연봉<=4000 구간 초과

하지만 -> 액세스 필요

['여'보다 값이 큰 미지의 성별 값이 존재한다면 여기에 모두 저장될 것이므로 액세스]

 

---

Index Skip Scan이 작동하기 위한 조건

: Distinct Value 개수가 적은 선두 칼럼이 조건절에 없고 후행 칼럼의 Distinct Value 개수가 많을 때 효과적

-> 인덱스 선두 칼럼이 없을때만 사용하는건 아니다. [선두칼럼 조건절은 있는데 중간 칼럼 조건절이 없는 경우]

 

인덱스 구성 : [일별업종별거래_PK : 업종유형코드 + 업종코드 + 기준일자]

 

선두 칼럼 (업종유형코드)에 대한 조건절은 있고 중간 칼럼(업종코드)에 대해 조건절이 없는 경우에도 사용 가능

SELECT /*+INDEX_SS(A 일별업종별거래_PK) */
 기준일자, 업종코드, 체결건수, 체결수량, 거래대금
FROM 일별업종별거래 A
WHERE 업종유형코드= '01'
AND 기준일자 BETWEEN '20080501' AND '20080531'

 

Execution Plan
------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS
 TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별업종별거래' (TABLE) (Cost=91 ...)
  INDEX (SKIP SCAN) OF '일별업종별거래_PK' (INDEX(UNIQUE)) (Cost=182...)

Index Range Scan을 사용	업종유형코드 ='01'인 인덱스 구간 모두 스캔
Index Skip Scan을 사용	업종유형코드 ='01'인 구간에서 기준 일자가 '20080401'보다 크거나 같고 '20080531'보다 작거나 같은 레코드 '포함할 가능성이 있는 블록만' 골라서 액세스

 

1. Distinct Value가 적은 두 개의 선두칼럼이 모두 조건절에 없는 경우

SELECT /*+INDEX_SS(A 일별업종별거래_PK) */
 기준일자, 업종코드, 체결건수, 체결수량, 거래대금
FROM 일별업종별거래 A
WHERE 기준일자 BETWEEN '20080501' AND '20080531'

 

Execution Plan
------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS
 TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별업종별거래' (TABLE) (Cost=91 ...)
  INDEX (SKIP SCAN) OF '일별업종별거래_PK' (INDEX(UNIQUE)) (Cost=90 Card=1)

 

 

2. 선두칼럼이 범위검색 조건일 때

 

일별업종별거래_X01 : 기준일자 + 업종유형코드

SELECT /*+INDEX_SS(A 일별업종별거래_PK) */
 기준일자, 업종코드, 체결건수, 체결수량, 거래대금
FROM 일별업종별거래 A
WHERE 기준일자 BETWEEN '20080501' AND '20080531'
AND 업종유형코드='01'

Index Range Scan을 사용	기준일자 BETWEEN 조건 만족하는 인덱스 구간 '모두' 스캔
Index Skip Scan을 사용	기준일자 BETWEEN 조건 만족하는 인덱스 구간에서 업종유형코드 ='01'인 레코드를  '포함할 가능성이 있는 리프 블록만' 골라서 액세스

 

기본적으로 Index Range Scan을 하려고 하지만,
선두칼럼이 가공되어있어서 불가능하거나 비효율적일 때 Index Skip Scan을 사용
하지만, 부분범위 처리가 가능하다면 Index Full Scan도 효율적일 수 있다.

 

---

5. Index Fast Full Scan [이 스캔 방식 유도 : index_ffs, 방지 : no_index_ffs]

: Index Full Scan을 대체하는 방식으로 쿼리에 사용한 칼럼이 모두 인덱스에 포함되어있을 때만 사용 가능하며

논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔

 

[인덱스 구조는 리프 블록간 양방향 연결 리스트구조]

Index Full Scan	인덱스의 논리적 구조를 따라 블록을 읽어들인다.
Index Fast Full Scan	인덱스의 물리적 구조로 디스크에 저장된 순서대로 인덱스 리프 블록들을 읽어들인다. Multiblock I/O 방식으로 익스텐트별로 읽는다. 루트와 두 개의 브랜치 블록도 읽지만 필요 없는 블록은 버린다.

 

Index Fast Full Scan은 Multiblock I/O 방식을 사용하므로, 디스크로부터 대량의 인덱스 블록 읽을 때 효과적

-> 인덱스 리프 노드가 갖는 연결리스트 구조를 무시한 채 데이터를 읽어서 인덱스 키 순서대로 정렬되지 않는다.

[쿼리에 사용한 칼럼이 모두 인덱스에 포함되어있을 때만 사용 가능]

[인덱스가 파티션 되어 있지 않더라도 병렬쿼리가 가능 -> Direct Path I/O 방식 사용해 더 빠른 I/O 속도]

 

Index Full Scan	Index Fast Full Scan
1. 인덱스 구조를 따라 스캔 2. 결과집합 순서 보장 3. single Block I/O 4. (파티션되어 있지 않으면) 병렬스캔 불가 5. 인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능	1. 세그먼트 전체를 스캔 2. 결과집합 순서 보장 안됨 3. Multiblock I/O 4. 병렬스캔 가능 5. 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능

 

---

6. Index Range Scan Descending [이 스캔 방식 유도 : index_desc]

: Index Range Scan과 동일한 스캔 방식으로, 인덱스를 뒤에서부터 앞쪽으로 스캔해서 내림차순으로 정렬된 결과집합

 

EMP 테이블을 EMPNO 기준으로 내림차순 정렬시,

EMPNO 칼럼에 인덱스가 존재할 경우 옵티마이저가 인덱스를 거꾸로 읽는 실행 계획 수립

SELECT *
FROM EMP
WHERE empno>0
ORDER BY empno DESC

 

Execution Plan
---------------------------------------------------------------------
SELECT STATEMENT Optimizer= ALL_ROWS
 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
  INDEX (RANGE SCAN DESCENDING) OF 'PK_EMP' (INDEX(UNIQUE))

옵티마이저가 인덱스를 거꾸로 읽지 않을 경우, index_desc 힌트를 이용해 유도

 

MAX값을 구하고자 할 때, 해당 칼럼에 인덱스가 있으면 인덱스를 뒤에서부터 한 건만 읽고 멈추는 실행계획

CREATE INDEX emp_x02 ON EMP(deptno, sal);

SELECT deptno, dname, loc
 ,(SELECT MAX(sal) FROM EMP WHERE deptno=d.deptno)
 FROM DEPT D

 

Execution Plan
----------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS
 SORT(AGGREGATE)
  FIRST ROW
   INDEX (RANGE SCAN(MIN/MAX)) OF 'EMP_X02' (INDEX)
  TABLE ACCESS (FULL) OF 'DEPT' (TABLE)