🖥️ 들어가며
📌 인덱스 스캔 방식
: 여러가지가 있지만, Index Unique Scan을 제외하면 Index Range Scan의 성능이 가장 좋습니다.
✏️ 1. Index Range Scan
- 항상 빠른 속도를 보장하지는 않습니다.
(인덱스 스캔의 손익분기점: Index Rage Scan에 의한 테이블 액세스가 Full Table Scan보다 느려지는 지점) - SQL 튜닝: Key Point
- 인덱스 스캔하는 범위를 얼마나 줄일 수 있는가?
- 테이블로 액세스하는 횟수를 얼마나 줄일 수 있는가? (즉, R.A를 얼마나 줄일 수 있는가)
- 힌트: Range Scan을 유도하는 인트는 없으며, Index 힌트*를 활용합니다.
👉🏻 *힌트
- 힌트는 사용자의 명령어로 인식됩니다.
- 따라서 힌트가 적용되지 않는 경우
- 그 힌트가 논리적으로 맞지 않거나 (ex: unnest와 push subq*를 함께 사용하는 경우?)
- 없는 인덱스를 사용하고 있거나
- 버그거나, 오타거나...
*unnest: 서브쿼리를 메인 쿼리와 같은 레벨로 푸는 역할 / push subq: 서브쿼리의 처리 순서를 제어하는 데 사용됨
- 힌트 작성 방법
- /*+ */: 사용 권장 (다중 라인 지원, 가독성, 오류 방지를 위해)
- --+: 사용 가능 (단일 라인에서만 사용 가능)
- Index 힌트
- Index (테이블명 index명): 특정 테이블의 특정 인덱스를 사용하도록 지시합니다.
ex: /*+ INDEX(employees emp_name_idx) */ - alias를 사용한 경우 alias로: 테이블에 별칠(alias)를 사용한 경우, 힌트에서도 그 별칭을 사용해야 합니다.
ex: FROM employees e → /*+ INDEX(e emp_name_idx) */ - Index(e (성별)): e 별칭의 테이블에서 '성별' 컬럼에 대한 인덱스를 사용하라는 의미입니다.
- Index (테이블명 index명): 특정 테이블의 특정 인덱스를 사용하도록 지시합니다.
- 힌트 순서
- 힌트 사용 시 일반적으로 고려하는 순서입니다.
- 1. 쿼리 변환 힌트: USE_CONCAT
- 2. 조인 방식 힌트: USE_NL, USE_MERGE, USE_HASH
- 3. 인덱스 힌트: INDEX_FFS
- 주의할 점: 스캔 속도와 'WHERE절에 쓰는 순서'는 관계 없습니다. 인덱스에 있는 컬럼이 WHERE절에 같이 존재하기만 하면 됩니다.
-- 인덱스: 부서코드(선행컬럼) + 이름 -> 부서코드별, 이름별로 정렬됨
WHERE 이름 = '홍길동'; -- X: 선행컬럼이 없으므로 인덱스 스캔 불가
-- 인덱스: 이름 + 부서코드
WHERE 이름 = '홍길동'; -- O: 선두컬럼이 있으므로 인덱스 스캔 가능- 인덱스를 구성하는 선두 컬럼을 조건절에 사용해야 합니다. ⭐⭐⭐
- 알아두면 좋은 내용: SQL 실행계획*
👉🏻 *SQL 실행계획 보는 법
- Level이 같다면 위에서 아래로 ↓
- 안쪽에서 바깥으로 ←
(같은 레벨이라면 박스로 묶은 뒤, ↓←)
1. SELECT -
2. NESTED LOOP -
3. TABLE ACCESS -
4. INDEX -
5. TABLE ACCESS -
6. INDEX -- 3번과 5번의 레벨이 같기 때문에 박스로 묶고, ↓←
- 4 - 3
- 6 - 5
- 박스에서 나와, ↓←
- 2 - 1
- 즉, 4 - 3 - 6 - 5 - 2 - 1
✏️ 2. Index Full Scan
- Index Full Scan을 활용하는 경우
- 조회 조건의 인덱스가 있으나, 선두 컬럼이 아닌 경우
- 옵티마이저가 인덱스 활용 시 이익이 있다고 판단할 경우
- 적당한 인덱스가 없을 경우 ⭐⭐⭐ Full Table Scan 대신 Index Full Scan을 활용합니다.
- 최종 결과 값이 적을 때 Full Table Scan 보다 Index Full Scan이 효율적입니다. ⭐⭐⭐
최종 결과 값이 많을 때 Full Table Scan이 효율적입니다. ⭐⭐⭐ - 힌트: Index_fs(이런 힌트는 없음), index 힌트 활용
-- 인덱스: 성별 + 연봉
WHERE 연봉 > 1억;
-- 선두컬럼이 없으므로 인덱스 스캔 불가 -> Full Table Scan
SELECT /*+ index[e ix_사원_성별_연봉]*/ * -- 옵티마이저 힌트
FROM 사원 E
WHERE 보너스 >= 1백만;
-- 인덱스 힌트를 명시적으로 사용했기에 Index Full Scan
- 두번째 SQL문이 Index Full Scan을 사용하는 이유는
1. 인덱스 힌트를 명시적으로 사용했기 때문입니다. 옵티마이저 힌트를 통해 강제로 인덱스 사용을 지시합니다.
2. Index Full Scan은 선두 컬럼 조건이 없어도 실행 가능합니다.
3. WHERE 절의 조건과 인덱스 컬럼과 관계가 없지만, 힌트로 인해 인덱스를 통해 데이터를 읽고 그 후 보너스 조건을 필터링합니다.- 다만, 이 경우에는 성능이 좋지 않을 수 있습니다. 인덱스를 전체 스캔한 후 각 레코드에 대해 테이블 액세스가 필요하고, 보너스 조건에 대한 필터링이 추가로 필요하기 때문입니다. 따라서 이런 경우 실제로는 Table Full Scan이 더 효율적일 수 있습니다.
✏️ 3. Index Unique Scan
- 수직적 스캔만 발생합니다.
- Unique 인덱스일 경우 사용합니다.
- = 조건일 경우만 사용합니다.
-- 인덱스: 사번(Unique Index)
WHERE 사번 = '001'; Unique니까 리프노트에서 찾으면 끝- 즉, Unique 인덱스는 각 값이 단 한번만 나타내는 고유성을 보장합니다. 리프 노드에서 값을 찾으면 그것이 유일한 결과임을 보장하기 때문에 추가적인 검색이 불필요합니다.
✏️ 4. Index Skip Scan
- 조회 조건이 인덱스 선두 컬럼이 아니며, 인덱스 선두 컬럼의 Distinct가 매우 낮을 때 사용합니다.
- 인덱스 선두 컬럼이 Between, Like, 부등호일 때도 사용 가능합니다.
- 힌트: index_ss
-- 인덱스: 성별 + 연봉
SELECT /*+ index_ss[e ix_사원_x01]*/ 성별, 연봉
FROM 사원
WHERE 연봉 > 7000;
-- 선두컬럼이 WHERE절에 없기 때문에 index range scan 불가
-- 대신, 선두컬럼 성별의 Distinct가 낮기에 index_ss 사용 적합
-- 성별의 Distinct: 남/여로 2개 (Distinct의 개수만큼 Skip해야 합니다.)
✏️ 5. Index Fast Full Scan
- 전체 Index를 Full Scan 합니다.
- Multi-Block I/O
- 파라미터의 db_file_multiblock_read_count 개수*만큼
또는 하나의 익스텐트 개수 중 숫자가 작은 것을 한번에 Read 합니다.
*db_file_multiblock_read_count: 이 파라미터는 Oracle이 한 번의 I/O 작업으로 읽을 수 있는 최대 데이터베이스 블록 수를 지정합니다.
- 일반적인 값: 128
- 계산: 128 * 8KB(일반적인 블록 크기) = 1MB
이 설정의 의미는 대부분의 운영체제에서 효율적으로 수행할 수 있는 최대 I/O 크기가 1MB라는 것입니다.
*Multi Block I/O에는 중요한 제한사항이 있습니다.
- 익스텐트 경계: Multi Block I/O는 하나의 익스텐트를 벗어날 수 없습니다.
- I/O 횟수 계산: 128블록 크기의 익스텐트 → 1번의 I/O로, 1024블록 크기의 익스텐트 → 8번의 I/O가 필요합니다. (1024 / 128 = 8)
- Index의 논리적 순서와 무관하게 물리적 순서대로 Read 합니다.
- 속도가 빠릅니다.
- 결과는 인덱스 키 컬럼의 순서와 무관합니다.
- 힌트: index_ffs
- 위 상황에서 Index Full Scan을 한다면? 싱글블록 I/O → 디스크 I/O 8회
- Index Fast Full Scan을 한다면? 멀티블록 I/O → 디스크 I/O 3회
- 단, 인덱스 키 순서대로 정렬이 보장되진 않습니다.
-- 인덱스: C1 + C2 + C3 + C4
SELECT C1, C2, C3
FROM T
WHERE C2 = 'A'
AND C3 = 'B'
AND C4 = 'C';
-- Index Fast Full Scan 가능
SELECT C1, C2, C3, C5
-- 하지만 인덱스에 없는 C5가 SELECT절에 추가된다면?
-- Index Fast Full Scan 불가
- Index Full Scan vs. Index Fast Full Scan
- 즉, Index Fast Full Scan은 R.A가 일어나지 않습니다.
(R.A 필요 시 Index Full Scan을 쓰세용)
- 즉, Index Fast Full Scan은 R.A가 일어나지 않습니다.
| Index Full Scan | Index Fast Full Scan |
| 인덱스 구조를 따라 스캔 | 세그먼트* 전체를 스캔 *테이블, 인덱스, 파티션 |
| 결과집합 순서 보장 | 결과집합 순서 보장 안 됨 (물리적 순서대로 Read하기 때문) |
| Single Block I/O | Multi Block I/O |
| 병렬스캔 불가 (파티션 시 가능) | 병렬스캔 가능 |
| 인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능 | 인덱스에 포함된 컬럼으로만 조회 시 사용 가 |
✏️ 6. Index Range Scan Descending
- 힌트: index_desc
- 인덱스를 역순으로 스캔합니다.
- 끝점부터 찾기 때문에, 내림차순 정렬이 필요한 경우 유용합니다.
✏️ 7. Index Combine
- 두 개 이상의 인덱스를 결합하여 사용하는 방식입니다.
- ex: Index C2와 Index C3가 있을 때, 옵티마이저가 이들을 결합하여 사용하는 것이 더 효율적이라고 판단하는 경우 실행됩니다.
- 이런 케이스는 실제로 매우 드물게 발생합니다.
✏️ 8. In-List Iterator
- 추후 내용 추가 예정입니다.
📒 정리하면
- Index Range Scan: 가장 일반적이고 효율적인 방식으로, 인덱스 선두 컬럼이 Where절에 있어야 합니다.
- Index Full Scan: 선두 컬럼이 없어도 사용 가능하며, 결과 값이 적을 때 효율적입니다. (Table Full Scan 대비)
- Index Unique Scan: 유니크 인덱스에서 '=' 조건으로 사용되며, 가장 빠른 검색 방식입니다.
- Index Skip Scan: 선두 컬럼의 Distinct가 낮을 때 유용합니다. (힌트: index_ss)
- Index Fast Full Scan: 멀티블록 I/O를 사용해 빠르게 전체 인덱스를 스캔합니다. (힌트: index_ffs)
반응형
