seoyyyy-dev
2회차 인덱스 기본 본문
1. 인덱스 구조 및 탐색
1.1 미리 보는 인덱스 튜닝
● 데이터를 찾는 두 가지 방법
- 어떤 초등학교를 방문해 '홍길동' 학생을 찾는다면 방법은 두 가지가 존재
- 첫째는 1학년 1반부터 6학년 맨 마지막 반까지 모든 교실을 돌며 홍길동 학생을 찾는 것 (학생이 많을 때 유리)
= 데이터베이스 테이블로 치면 테이블 전체를 스캔하는 것과 같음 (대량의 데이터를 조회할 때 유리)
- 둘째는 교무실에서 학생 명부를 조회해 홍길동 학생이 있는 교실만 찾아가는 것(학생이 적을 때 유리)
= 데이터베이스 테이블로 치면 인덱스를 이용하는 것과 같음(소량의 데이터를 조회할 때 유리)
- 이름으로 학생을 찾는 방문객이 많다면 이름순으로 정렬해 둔다면 편리함
- 이때 이름이 INDEX, 학년-반-번호가 ROWID에 해당
● 인덱스 튜닝의 두 가지 핵심요소
① 인덱스 스캔 효율화 튜닝
- 학생명부에서 시력이 1.0~1.5인 홍길동 학생을 찾는 경우
- 명부가 이름과 시력 순으로 정렬 되어있다면 소량만 스캔하면 됨(좌 그림)
- 시력과 이름순으로 정렬되어 있는 명부라면 똑같이 두 명을 찾는데도 아래와 같이 많은 양을 스캔해야 함(우 그림)
= 인덱스 스캔과정의 비효율을 줄여야 함
 |
 |
| 이름, 시력 순의 학생명부 | 시력, 이름 순의 학생명부 |
② 랜덤 액세스 최소화 튜닝
| 예시) - 이름순 학생명부, 시력순 학생명부 따로 존재 - 시력이 1.0 ~ 1.5인 학생은 50명, 이름이 '홍길동'인 학생은 다섯명 |
- 학생명부에서 시력순으로 학생들을 찾아 교실을 직접 방문해 '홍길동' 학생을 찾게 된다면 50명의 반을 찾아가야 함..
- 이름순으로 학생을 찾는다면 '홍길동'은 다섯 명이므로 다섯 번의 반만 방문해 시력이 1.0 ~ 1.5인 학생을 찾으면 됨
- 교실을 일일이 방문하는 횟수를 줄여야 함
= 인덱스 스캔 하여 테이블 레코드를 액세스 하는 횟수를 줄여야 함
- 인덱스 스캔 효율화 튜닝, 랜덤 액세스 최소화 튜닝 둘 다 중요하지만 굳이 비교하자면 랜덤 액세스 최소화 튜닝이 무엇보다 중요! (성능에 미치는 영향이 큼)
● 튜닝은 랜덤 I/O와의 전쟁
- 데이터베이스의 성능이 느린 이유는 디스크 I/O 때문임
- 성능을 위해 DBMS가 제공하는 많은 기능이 느린 랜덤 I/O를 극복하기 위해 개발됨 (EX. IOT, 클러스터, 파티션, 테이블 Prefectch, Batch I/O 등등)
- 조인메서드 중 NL조인이 대량 데이터를 조인할 때 느린 이유도 바로 이 랜덤 I/O 때문임 -> 소트머지 조인, 해시 조인이 이를 극복하기 위해 개발됨
1.2 인덱스 구조
- 인덱스: 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스 하기 위해 사용하는 오브젝트, 책의 색인과 같은 역할
- 데이터베이스에서 인덱스 없이 데이터 검색 시 테이블을 처음부터 끝까지 읽어야 함
- 인덱스를 이용하면 일부만 읽고 멈출 수 있음(정렬되어 있기 때문)
= 범위 스캔(Range Scan) 가능 *
- DBMS는 일반적으로 B*Tree 인덱스를 사용
- 루트와 브랜치 블록의 각 레코드에는 하위 블록에 대한 주소값이 존재
- 키값은 하위블록에 저장된 키값의 범위를 나타냄(ex. '서' 레코드가 가리키는 하위블록엔 고객명이 '서'보다 크거나 같은 레코드가 저장되어 있음(파란색 화살표))
- LMC: 루트와 브랜치 블록에 존재하며 키값을 갖지 않음, 가장 왼쪽 끝에 위치한 블록으로 LMC가 가리키는 주소로 찾아간 블록엔 키값을 가진 첫 번째 레코드보다 작거나 같은 레코드가 저장되어 있음
- 리프 블록에 저장된 각 레코드는 키값 순으로 정렬되어 있으며 ROWID(테이블 레코드를 가리키는 주소값)을 가짐
- 인덱스의 키값이 같으면 ROWID 순으로 정렬됨
- ROWID 구성
● ROWID = 데이터 블록 주소 + 로우번호
● 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
● 블록 번호 = 데이터파일 내에서 부여한 상대적 순번
● 로우 번호 = 블록 내 순번
- 인덱스 탐색 과정
● 수직적 탐색: 인덱스 스캔 시작 지점을 찾는 과정
● 수평적 탐색: 데이터를 찾는 과정
1.3 인덱스 수직적 탐색
- 인덱스 스캔 시작지점을 찾는 과정
- 인덱스 수직적 탐색은 루트(Root) 블록에서부터 시작
- 루트를 포함해 브랜치(Branch) 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 가진다.
- 수직적 탐색과정에서 찾고자 하는 값보다 크거나 같은 값을 찾으면 바로 직전 레코드가 가리키는 하위 블록으로 이동함
- 찾고자 하는 값과 정확히 일치하는 레코드가 존재할 때 그 레코드가 가리키는 하위블록으로 바로 이동하면 안 된다. 조건과 일치하는 값이 여러 개일 때 그전 블록에 존재하는 레코드를 빠드릴 수 있음 => 조건을 만족하는 첫 번째 레코드가 목표지점
1.4 인덱스 수평적 탐색
- 수직적 탐색을 통해 스캔 시작지점을 찾고서 찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프 블록을 수평적으로 스캔한다.(본격적으로 데이터를 찾는 과정)
- 인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 가짐(양방향 연결 리스트 - double linked list)
- 인덱스를 수평적으로 탐색하는 이유
① 조건절을 만족하는 데이터를 모두 찾기 위해서
② ROWID를 얻기 위해서 (인덱스를 스캔하고 테이블을 액세스 하기 위함)
1.5 결합 인덱스 구조와 탐색
- 두 개 이상의 칼럼을 결합해서 아래와 같이 결합 인덱스를 만들 수 있음
- 예시로 고객테이블의 인덱스를 성별, 고객명 순으로 생성하고 성별이 '남'이며 고객명이 '이재희'인 사람을 찾아보자
위의 그림 예시에서 수직적 탐색을 거쳐 찾은 인덱스 스캔 시작점은 성별 = '남' 이면서 고객명 = '이재희'인 첫 번째 레코드이다.
- 만약 인덱스 칼럼의 순서를 바꿔서 고객명, 성별 순으로 구성하게 되면 아래와 같이 스캔하게 되는데
- 위의 결과 인덱스를 [고객명 + 성별]로 구성하든 [성별 + 고객명]으로 구성하든 읽는 인덱스 블록 개수가 똑같다.
- 이유는 인덱스 선두 컬럼을 모두 "=" 조건으로 검색해 어느 컬럼을 인덱스의 앞쪽으로 두든 블록 I/O 개수가 같기 때문, 성능도 똑같음
- DBMS 가 사용하는 B*Tree 인덱스는 다단계 구조로 루트에서 브랜치를 거쳐 리프 블록까지 탐색하는 구조이기 때문(엑셀처럼 평면의 구조가 아님)
- B*Tree 인덱스의 'B'는 'Balanced'의 약자로 어떤 값으로 탐색하더라도 인덱스 루트에서 리프블록에 도달하기까지 읽는 블록 수가 같음을 의미한다. 즉 루트로부터 모든 리프 블록까지의 높이(height)는 항상 같음 => delete 작업에도 인덱스 불균형 상태에 놓이지 않음
2. 인덱스 기본 사용법
2.1 인덱스를 사용한다는 것
- '인덱스를 정상적으로 사용한다'는 표현은 리프블록에서 스캔 시작점을 찾아 거기서부터 스캔을 하다가 중간에 멈추는 것을 의미함(리프블록 일부만 탐색) == Index Range Scan
- 인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고 멈출수도 없어 리프 블록 전체를 스캔해야만 함 == Index Full Scan
2.2 인덱스를 Range Scan 할 수 없는 이유
- 인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다. (Range Scan 불가)
- 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문. Index 'Range' Scan을 위해서는 '시작지점'과 '끝지점'이 있어야 함
- 예시로 생년월일이 인덱스인 학생 테이블에서 아래 조건에 만족하는 학생을 찾을 때
select * from 학생 where 생년월일 between '20070101' and '20070131'
2007년 1월 9일생을 시작으로 2007년 1월 26일생인 학생을 끝지점으로 일부(Range)만 스캔했다.
하지만 아래와 같이 생년월일을 가공하여 연도와 상관없이 5월에 태어난 학생을 찾게 된다면
select * from 학생 where substr(생년월일, 5, 2) = '05'
조건절의 인덱스 컬럼이 가공되어 스캔지점을 찾을 수 없으므로 전교생을 다 스캔하여 찾아야한다.
+) 조건절의 인덱스 컬럼이 가공되었을 때 Range Scan이 불가능한 예시
① SUBSTR - select * from 학생 where substr(생년월일, 5, 2) = '05'
② NVL - select * from 주문 where nvl(주문수량, 0) < 100;
③ 중간값 Like - select * from 업체 where 업체명 like '% 대한%';
④ OR 조건 - select * from 고객 where (전화번호 = '01012341234' OR 고객명 = '김땡땡')
→ Index Range Scan 해결법
/**
* OR대신 Union all을 쓰면 Index Range Scan 이 가능함
**/
select *
from 고객
where 고객명 = '김땡땡' -- 고객명이 선두 컬럼인 인덱스 Range Scan
union all
select *
from 고객
where 전화번호 = '01012341234' -- 전화번호가 선두 컬럼인 인덱스 Range Scan
and (고객명 <> '김땡땡' or '고객명' is null)
/**
* use_concat 힌트를 이용해 SQL 옵티마이저가
* 위와 같이 union all 형태로 변환해줄 수 있음 => 'OR Expansion'
*/
select /*+ use_concat */ *
from 고객
where (전화번호 = '01012341234' OR 고객명 = '김땡땡')
● EMP 테이블로 테스트 해 본 OR Expansion
SELECT /*+ use_concat */*
FROM EMP
WHERE (ENAME='SMITH' OR SAL = 1600)
⑤ in 절 - select * from 고객 where 전화번호 in ('01012341234', '01056785678');
→ Index Range Scan 해결법
/**
* 이것도 union all 방식을 사용하면 index Range Scan 가능함
* IN 조건절의 경우 SQL 옵티마이저가 IN-List Interator 방식을 사용해
* union all 방식을 사용한 것같은 효과를 가짐
**/
select *
from 고객
where 전화번호 = '01012341234'
union all
select *
from 고객
where 전화번호 = '01056785678'
● EMP 테이블로 테스트했을 때 실행 계획 결과(옵티마이저가 알아서 INLIST ITERATOR 해줌)
SELECT *
FROM EMP
WHERE ENAME IN ('SMITH', 'BLAKE');
;
2.3 더 중요한 인덱스 사용 조건
- 인덱스를 정상적(Range Scan)으로 사용하기 위한 더 중요한 선행조건은 가공하지 않은 상태의 인덱스 선두 컬럼이 조건절에 있어야 한다.
- 예를 들어 [소속팀 + 사원명 + 연령] 순의 사원 테이블에서 아래 쿼리를 실행하면 Index Range Scan이 되지 않는다.
select 사원번호, 소속팀, 연령, 입사일자, 전화번호
from 사원
where 사원명 = '홍길동'- 왜냐하면 인덱스는 소속팀순으로 정렬되어 있으므로 이름이 같아도 소속팀이 다르면 서로 멀리 떨어질 수 있어 리프 블록 전체를 스캔해야 한다.
- 또한 선두 컬럼이 가공되지 않은 상태로 조건절에 있다면 다음 순서의 인덱스 컬럼을 가공하게 되어도 Range Scan이 가능해진다.
/**
* [DEPTNO, SAL] 인덱스 사용
*/
SELECT *
FROM EMP e
WHERE 1=1
AND DEPTNO = 20
AND TO_CHAR(SAL / 10) > '100'= DEPTNO, SAL 순의 인덱스를 사용하여 선두 컬럼인 DEPTNO는 가공하지 않은 채 조건절에 사용, SAL 은 일부러 가공하여 조건절에 사용해 봤을 때 INDEX RANGE SCAN을 타는 것을 확인했다.
- 하지만 위와 같은 경우 Index Range Scan을 탄다고 해서 항상 성능이 좋은 것만은 아니다. 대량의 데이터를 조회할 때 인덱스 스캔의 양이 많아지면 성능에 영향을 끼칠 수 있다.
2.4 인덱스를 이용한 소트 연산 생략
- 인덱스를 사용하면 인덱스 컬럼 순으로 데이터가 정렬되어 있어 Index Range Scan이 가능하고 소트 연산 생략 효과를 부수적으로 얻게 된다.
/* INDEX = [DEPTNO, SAL] */
SELECT *
FROM EMP e
WHERE 1=1
AND DEPTNO = 20
AND sal > 100
위의 쿼리를 실행해 주었을 때 따로 ORDER BY를 명시하지 않아도 SAL 이 오름차순으로 정렬되어 있는 것을 확인할 수 있다.
마지막에 ORDER BY SAL을 추가해 주어도 실행계획을 보았을 때 소트 연산이 자동으로 생략된다.
SELECT DEPTNO, SAL
FROM EMP e
WHERE 1=1
AND DEPTNO = 20
AND sal > 100
ORDER BY SAL;
만약 정렬 연산을 생략할 수 있게 인덱스가 구성되어 있지 않다면 실행계획에서 SORT (ORDER BY) 연산단계가 추가된다.
내림차순(ORDER BY ... DESC) 정렬 시에도 인덱스 리프 블록은 양방향 연결 리스트 구조이기 때문에 소트 연산이 생략되며 INDEX RANGE SCAN 단계에서 DESCENDING 이라고 표시된다.
SELECT DEPTNO, sal
FROM EMP e
WHERE 1=1
AND DEPTNO = 20
AND sal > 100
ORDER BY sal DESC;
2.5 ORDER BY 절에서 컬럼 가공
- 만약 아래와 같이 인덱스를 가공하여 ORDER BY 절에서 사용했다면 정렬 연산이 생략이 될까?
/**
* 장비번호, 변경일자, 변경순번 순의 인덱스
**/
SELECT *
FROM 상태변경이력
WHERE 장비번호 = 'C'
ORDER BY 변경일자 || 변경순번
정답은 생략이 되지 않는다. 인덱스에는 가공하지 않은 상태로 값을 저장했는데, 가공한 값을 기준으로 정렬해 달라고 요청했기 때문.
- 또 아래의 경우에는 정렬 연산이 생략될까?
/**
* 주문일자, 주문번호 순의 인덱스
**/
SELECT *
FROM (
SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호, A.주문금액
FROM 주문 A
WHERE A.주문일자 = :dt
and A.주문번호 > NVL(:next_ord_no, 0)
order by 주문번호
)
where rownum <= 30정답은 생략이 되지 않는다. ORDER BY 절에 기술한 '주문번호'는 TO_CHAR 함수로 가공한 주문번호(ALIAS)를 가리키기 때문, 인덱스로 인식되도록 하기 위해서는 ORDER BY A.주문번호 와 같이 가공되지 않은 컬럼을 직접 참조하도록 해주어야 한다.
2.6 SELECT-LIST 에서 컬럼 가공
인덱스를 이용해 정렬 연산 없이 최소, 최대값을 빠르게 찾을 때 인덱스 리프 블록의 왼쪽(MIN) 또는 오른쪽(MAX)에서 레코드 하나(FIRST ROW)만 읽고 멈춘다.
예시)
/** 인덱스 순서 = [장비번호 + 변경일자 + 변경순번] */
SELECT MIN(변경순번)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'위와 같은 순서의 인덱스로 구성하여 변경순번의 최소값을 구할 때 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가 첫 번째 읽는 레코드가 바로 최소값이므로 옵티마이저는 정렬 연산을 따로 수행하지 않는다.
/** 인덱스 순서 = [장비번호 + 변경일자 + 변경순번] */
SELECT MAX(변경순번)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'또한 최댓값의 경우에도 수직적 탐색을 통해 조건을 만족하는 가장 오른쪽 지점으로 내려가 첫 번째 읽는 레코드가 최댓값이므로 옵티마이저가 정렬을 수행하지 않게 된다.
하지만! 가공한 컬럼의 최대값(MIN), 최소값(MAX)을 구하게 된다면 또한 정렬 연산을 생략하지 않게된다.
SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'
이럴때는 가공하지 않은 컬럼에 MAX 값을 구한 뒤 가공을 하는 것이 바람직하다.
SELECT NVL(TO_NUMBER(MAX(변경순번)), 0)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'
또하나의 예시
SELECT 장비번호, 장비명, 상태코드
, (SELECT MAX(변경일자)
FROM 상태변경이력
WHERE 장비번호 = P.장비번호) 최종변경일자
FROM 장비 P
WHERE 장비구분코드 = 'A001'위의 경우 정렬 연산 없이 MIN/MAX, FIRST ROW 방식으로 실행된다.
SELECT 장비번호, 장비명, 상태코드
, (SELECT MAX(변경일자)
FROM 상태변경이력
WHERE 장비번호 = P.장비번호) 최종변경일자
, (SELECT MAX(변경순번)
FROM 상태변경이력
WHERE 장비번호 = P.장비번호
AND 변경일자 = (SELECT MAX(변경일자)
FROM 상태변경이력
WHERE 장비번호 = P.장비번호)) 최종변경순번
FROM 장비 P
WHERE 장비구분코드 = 'A001'
하지만 최종 변경 순번까지 출력하고자 위의 쿼리처럼 작성하게 되면 SQL문이 더 복잡해지며 상태변경이력 테이블을 여러번 읽어야 해 비효율적이다. (성능이 나빠짐)
SELECT 장비번호, 장비명, 상태코드
, (SELECT MAX(변경일자 || 변경순번)
FROM 상태변경이력
WHERE 장비번호 = P.장비번호) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
또한 위의 쿼리처럼 작성하면 SQL문은 전보다 간단해졌지만 인덱스 컬럼을 가공했으므로 INDEX RANGE SCAN을 타지 않게되어 이력 데이터가 많으면 성능이 더 안좋아지게 될 수 있다.
이런 문제에서는 Top N (ROWNUM < =1 조건을 이용)알고리즘을 사용하면 해결할 수 있다고 한다.
SELECT 장비번호, 장비명
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM (
SELECT 장비번호, 장비명
, (SELECT 변경일자 || LPAD(변경순번, 4) || 상태코드
FROM (SELECT 변경일자, 변경순번, 상태코드
FROM 상태변경이력
WHERE 장비번호 = P.장비번호
ORDER BY 변경일자 DESC, 변경순번 DESC
)
WHERE ROWNUM <= 1) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
2.7 자동 형변환
조건절 비교 시 서로 데이터 형이 다를 때 DBMS에 따라 에러를 내기도 하며, 자동으로 형변환을 처리해주기도 한다고 한다.
SELECT *
FROM 고객
WHERE 생년월일 = 19821225위의 쿼리를 조회한다고 할 때 생년월일 컬럼이 문자형이라면 옵티마이저는 WHERE 조건의 비교값이 숫자형이므로 생년월일 컬럼을 TO_NUMBER(생년월일) 로 자동 형변환을 시도한다.
결과적으로 인덱스 컬럼이 가공되었기 때문에 INDEX RANGE SCAN을 하지못한다.
왜 비교값을 문자형으로 변환하지 않고 생년월일이 숫자형으로 변경될까??
이유는 오라클에서 숫자형과 문자형이 만나면 숫자형 컬럼을 기준으로 문자형 컬럼을 변환하기 때문(숫자형이 이김)
SELECT *
FROM 고객
WHERE 가입일자 = '01-JAN-2018'
또한 날짜형(HIREDATE) 과 문자형(20180103) 이 만나면 날짜형이 이긴다고 한다.
그래서 위의 쿼리는 비교값이 변환되기 때문에 인덱스를 정상적으로 사용하게 된다. 하지만 이런식으로 코딩하게 되면 NLS_DATE_FORMAT 파라미터가 다르게 설정된 환경에서는 컴파일 오류 또는 결과집합이 틀려지므로 날짜 포맷을 정확하게 지정해주는 습관을 들여야함
SELECT *
FROM 고객
WHERE 가입일자 = TO_DATE('01-JAN-2018', 'DD-MON-YYYY')
만약 숫자 VS 문자 에서 연산자가 LIKE 라면?
SELECT *
FROM 고객
WHERE 고객번호 LIKE '9410%'
숫자 VS 문자 일때 숫자가 이긴다고 했지만 LIKE 자체가 문자열 비교 연산자이므로 이때는 숫자형 컬럼이 문자형 기준으로 변경된다.
LIKE 연산 시 주의할점
SELECT *
FROM 거래
WHERE 계좌번호 LIKE :anct_no || '%'
AND 거래일자 between :trd_dt1 and :trd_dt2- 위 쿼리는 계좌번호가 있을수도 있고 없을 수도 있어 like와 '%'를 사용해 옵션 처리를 하게고, like 와 between 이 같이 쓰였으므로 인덱스 스캔 효율이 안좋아진다.
- 또한 계좌번호 컬럼이 숫자형이면 숫자형 컬럼이 like 조건에 의해 문자형으로 형변환 되므로 [계좌번호 + 거래일자] 순으로 구성된 인덱스를 RANGE SCAN 하지 못하게 된다.
- [거래일자 + 계좌번호] 순의 인덱스를 RANGE SCAN 할 수는 있겠지만 인덱스 스캔 효율은 매우 안좋아진다.
자동 형변환시 주의 할 점
- 조건절에서 문자와 숫자의 비교 시 만약 문자형 컬럼에 숫자로 변환할 수 없는 문자열이 입력되면 쿼리 수행 도중 아래와 같은 에러가 발생할 수 있다.
ORA-01722: 수치가 부적합합니다.
또한 숫자가 문자로 바뀌어 결과 오류가 생기는 사례도 있다.
MAX_SAL2 를 보면 JOB이 'PRESIDENT'가 아닌 사람들의 연봉 최고값이 950으로 나온다. 하지만 실제 데이터 확인을 하면 다음과 같다.
이유는 DECODE(A,B,C,D)를 처리할 때 a = b이면 c를 반환하고 아니면 d를 반환하게 된다. 이때 반환 값의 데이터 타입은 세번째 인자 c에 의해 결정되며 세번째 인자의 null값은 varchar2로 취급하게 되어 950이 나오게 되었다.
그래서 다음과 같이 DECODE에서 세번째 인자에 TO_NUMBER(NULL)또는 0을 사용하여 잘못된 결과가 나오는 상황을 피할 수 있다.
결론은 자동 형변환 시 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변환 해주자
3. 인덱스 확장기능 사용법
3.1 Index Range Scan
- B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식
- 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후 '필요한 범위(Range)' 만 스캔한다.
실행계획은 아래와 같다.
3.2 Index Full Scan
- 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
- 대개 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.
- 테이블 저장공간은 '가로 X 세로' 즉 '컬럼 길이 X 레코드 수'에 의해 결정되므로 인덱스가 차지하는 면적은 테이블보다 훨씬 적다.
- 인덱스 스캔 단계에서 대부분의 레코드를 필터링하고 아주 일부만 테이블을 액세스 하는 상황이라면 TABLE FULL SCAN 보다 INDEX FULL SCAN이 유리하다.
- 하지만 결론은 Index Full Scan 은 Index Range Scan의 차선책이기 때문에 조건절의 컬럼이 선두인 인덱스를 생성해주는 것이 좋다.
● 인덱스를 이용한 소트 연산 생략
- 인덱스 Full Scan 시 Range Scan과 마찬가지로 결과집합이 인덱스 컬럼 순으로 정렬된다.
- 따라서 Sort Order By 연산을 생략할 목적으로 사용할수도 있다.(옵티마이저가 전략적으로 선택함)
- 소트연산을 생략함으로써 전체 집합 중 처음 일부를 빠르게 출력하며, 부분 범위 처리가 가능한 상황에서 극적인 성능 개선 효과를 가져다줄 수 있다.
- 주의할 점은 사용자의 의도인 부분 범위 처리 활용 목적과 달리 Fetch를 멈추지 않고 데이터를 끝까지 읽는다면 Table Full Scan보다 훨씬 더 많은 I/O를 일으키고, 수행속도가 느려진다.
3.3 Index Unique Scan
- 수직적 탐색만으로 데이터를 찾는 스캔 방식으로 Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동한다.
- Unique 인덱스라고 해도 범위검색 조건(between, 부등호, like) 으로 검색할 때는 Index Range Scan으로 처리된다.
- 또한 Unique 결합 인덱스에서 일부 컬럼만 검색할 때도 Index Range Scan이 나타난다.
3.4 Index Skip Scan
- 오라클 9i 버전부터 인덱스 선두 컬럼이 조건절에 없더라도 인덱스를 활용하는 Index Skip Scan 방식이 생김
- 9i 이전엔 조건절에 인덱스 선두컬럼이 없으면 Table Full Scan 또는 Index Full Scan을 했음
- 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용
- 하지만 선두컬럼에 대한 조건절이 있고 중간컬럼에 대한 조건절이 없는 경우에도 Index Skip Scan을 사용할 수 있다.
- 관련힌트: index_ss
-- Index Skip Scan 예시
select * from 사원 where 성별 = '남' and 연봉 between 2000 and 4000위 인덱스 구조일때 해당 쿼리를 실행한다면
1. 성별 = '남' & 연봉 >= 2000 인 레코드를 찾는다. -> 4번 리프블록의 직전 블록인 3번 리프 블록을 찾아감
2. 3번 리프블록부터 차례로 스캔하다 성별 = '남' & 연봉 > 4000 인 첫번째 레코드를 만나는 순간 스캔을 멈추게 된다.
Index Skip Scan은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프블록만 골라 액세스하는 스캔방식이다.
-- 선두컬럼 조건절은 있고, 중간컬럼 조건절이 없는 경우에도 Index Skip Scan은 가능하다.
--PK : 업종유형코드 + 업종코드 + 기준일자
SELECT /*+ INDEX_SS(A 일별업종별거래_PK) */
기준일자, 업종코드, 체결건수, 체결수량, 거래대금
FROM 일별업종별거래 A
WHERE 업종유형코드 = '01' -- 선두컬럼 조건절
AND 기준일자 BETWEEN '20080501' AND '20080531' -- 기준일자 조건절은 존재하지만 업종코드 조건절이 없음
위의 쿼리는 Index Skip Scan될 때 업종유형코드 = '01'인 구간에서 기준일자 '20080501' 부터 '20080531' 사이의 레코드를 포함할 가능성이 있는 리프블록만 골라 액세스할 수 있다.
또한 아래처럼 Distinct Value가 적은 두개의 선두컬럼이 모두 조건절에 없는 경우에도 Index Skip Scan이 유용할 수 있다.
SELECT /*+ INDEX_SS(A 일별업종별거래_PK) */
기준일자, 업종코드, 체결건수, 체결수량, 거래대금
FROM 일별업종별거래 A
WHERE 기준일자 BETWEEN '20080501' AND '20080531'
인덱스는 기본적으로 Index Range Scan을 목표로 설계해야하며, 수행 횟수가 적은 SQL을 위해 인덱스를 추가하는 것이 비효율적일 때 Index Skip Scan 또는 Index Full Scan 방식을 차선책으로 활용하자.
3.5 Index Fast Full Scan
- Index Fast Full Scan은 Index Full Scan보다 빠르다.
- 논리적인 인덱스 트리구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하는 방식 (물리적으로 디스크에 저장된 순서대로 인덱스 리프블록들을 읽어들임)
- 대량의 인덱스 블록을 읽어야 할 때 큰 효과를 발휘
- 속도는 빠르지만 인덱스 리프 노드가 갖는 연결 리스트를 무시한 채 데이터를 읽어들여 결과집합이 인덱스 키 순서대로 정렬되지 않는다.
- 관련 힌트: index_ffs, no_index_ffs
3.6 Index Range Scan Desending
- Index Range Scan과 동일한 스캔방식이며 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.
- 관련 힌트: index_desc
힌트를 써서 강제로 Index Range Scan Desending 도 가능하다.
'개발 독서 스터디 > 친절한 SQL 튜닝' 카테고리의 다른 글
| 4회차 조인 튜닝 (0) | 2025.02.25 |
|---|---|
| 3회차 인덱스 튜닝 - 3 (0) | 2025.02.08 |
| 3회차 인덱스 튜닝 - 2 (0) | 2025.02.08 |
| 3회차 인덱스 튜닝 - 1 (1) | 2025.02.08 |
| 1회차 SQL 처리 과정과 I/O 정리 (1) | 2025.01.04 |
