ch2. 인덱스 기본

2.1 인덱스 구조 및 탐색

주안점: 수직적 탐색과 수평적 탐색

 

2.1.1 미리 보는 인덱스 튜닝

데이터를 찾는 두 가지 방법

 

• Table Full Scan : 찾는 데이터가 많다면 테이블 전체를 스캔한다.
• Index Range Scan : 큰 테이블에서 소량의 데이터를 검색할 때 사용

인덱스의 예

이름	학년-반-번호
강수지	4학년 3반 37번
김철수	3학년 2반 13번
...	...
홍길동	1학년 5반 15번
홍길동	2학년 6반 24번
홍길동	5학년 1반 16번

Table Full Scan과 관련해서는 튜닝 요소가 적다. 그러나 인덱스와 관련해서는 튜닝 요소가 많고 기법도 다양하다.

 

 

 

인덱스 튜닝의 두 가지 핵심 요소

 

인덱스 튜닝은 큰 테이블에서 소량의 데이터를 검색할 때 사용.

OLTP(online transaction processing)  시스템에서는 소량 데이터를 주로 검색하므로 인덱스 튜닝이 무엇보다 중요하다.

 

• 인덱스 스캔 효율화 튜닝 : 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것
• 랜덤 액세스 최소화 튜닝 : 테이블 액세스 횟수를 줄이는 것

--

인덱스 스캔 효율화

이름	시력	학년-반-번호
강수지	1.5	4학년 3반 37번
김철수	0.5	3학년 2반 13번
...	...	...
홍길동↓	1.0↓	1학년 5반 15번
홍길동↓	1.5↓	2학년 6반 24번
홍길동	2.0	5학년 1반 16번

 

학생 명부에서 시력이 1.0~1.5인 홍길동을 찾는 경우, 학생 명부를 이름과 시력 순으로 정렬했을 경우 위와 같이 소량만 스캔하면 된다.(이름 먼저 탐색, 그 이후에 시력으로 탐색).

 

만약 시력과 이름으로 정렬해 두었다면 시력으로 1.0부터 1.5까지를 먼저 찾고 그 사이에 존재하는 홍길동을 찾아내야 한다. 이 경우는 많은 양을 스캔해야 한다.

 

랜덤 액세스 최소화

 

시력으로만 정렬한 테이블과 이름으로만 정렬한 테이블이 존재하고 하나만 선택 가능하다고 가정.

시력이 1.0~1.5는 50명. 홍길동이란 이름은 5명. 시력이 1.0~1.5사이의 시력은 가진 홍길동은 2명.

이 경우 이름 순으로 정렬한 테이블을 사용하여 찾는 게 효율적이다. 이름 순으로 찾는 경우 5개의 로우만 읽으면 되지만 시력으로 먼저 찾는 경우 50개를 다 읽어야 하기 때문이다.

 

인덱스 스캔 효율화와 랜덤 액세스 최소화 중에서는 랜덤 액세스 최소화가 성능에 미치는 영향이 더 크다.

 

학생 명부를 뒤지는 과정(인덱스 스캔)에도 비효율이 있을 수 있지만 명부에 없는 나머지 정보를 찾아 직접 찾아가는 경우의 비효율이 훨씬 크다.

 

이처럼 SQL 튜닝은 랜덤 I/O와의 전쟁이다.

 

 

SQL 튜닝은 랜덤I/O와의 전쟁

 

DB 성능이 느린 이유는 디스크I/O 때문이다. 데이터 량은 많은데 그 과정에 디스크 I/O가 많이 발생하면 느리다. 인덱스를 이용하는 OLTP 시스템이라면 디스크 I/O 중에서도 랜덤 I/O가 특히 중요하다.

 

성능을 위해 DBMS가 제공하는 많은 기능이 느린 랜덤I/O를 극복하기 위해 개발됐다. 본질은 그러하다.

 

 

2.1.2 인덱스 구조

인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용하는 오브젝트다. 책의 색인과 같은 역할을 한다.

 

DB에서 인덱스 없이 데이터를 검색하려면 Table Full Scan를 사용해야 한다. 그러나 인덱스를 이용하면 일부만 읽고 탐색을 멈출 수 있다. 이를 범위 스캔(Range Scan)이라고 한다. 범위 스캔이 가능한 이유는 인덱스가 정렬돼 있기 때문이다.

 

B*Tree 인덱스 구조 (B는 Balanced라는 뜻)

: DBMS는 일반적으로 B*Tree 인덱스를 사용한다. (Root  => Branch => Leaf)

https://ssocoit.tistory.com/217 , https://ko.wikipedia.org/wiki/B_%ED%8A%B8%EB%A6%AC 참고.

 

 

루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 갖는다. 키값은 하위 블록에 저장된 키 값의 범위를 나타낸다. 

 

위의 도표를 참조하여 아래의 문장의 의미를 이해할 것.

• 루트 블록 '서' 레코드가 가리키는 하위 블록에는 '서'보다 크거나 같은 (고객명>='서') 레코드가 저장돼 있다.
• 오른쪽 브랜치 블록 '이재룡'이 가리키는 하위 블록에는 '이재룡'보다 크거나 같은 (고객명>='이재룡') 레코드가 저장돼 있다.
• LMC:루트와 브랜치 블록에는 키값을 갖지 않는 특별한 브랜치가 있다.  가장 왼쪽 첫 번째 레코드다. 이를 LMC(Leftmost child)라고 한다. LMC는 자식 노드 중 가장 왼쪽(leftmost) 끝에 위치한 블록을 가리킨다.
• LMC가 가리키는 주소로 찾아간 블록에는 키값을 가진 첫 번째 레코드보다 작거나 같은 레코드가 저장돼 있다. (루트에서 '서' 왼쪽 브랜치에서 '강덕승', 오른쪽 브랜치에서 '송재훈')

리프블록에 저장된 각 레코든느 키값 순으로 정렬돼 있을 뿐만 아니라 테이블 레코드를 가리키는 주소값, 즉 ROWID를 갖는다. 인덱스 키값이 같으면 ROWID 순으로 정렬된다. 인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 빨리 찾고 거기서 ROWID를 얻기 위해서다. ROWID는 아래와 같이 DBA(Data Block Address)와 로우 번호로 구성되므로 이 값을 알면 테이블 레코드를 찾아갈 수 있다.

 

• ROWID = 데이터 블록 주소 + 로우 번호
• 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
• 블록 번호 : 데이터파일 내에서 부여한 상대적 순번
• 로우 번호: 블록 내 순번

 

 

인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 나눌 수 있다.

• 수직적 탐색: 인덱스 스캔 시작지점을 찾는 과정
• 수평적 탐색: 데이터를 찾는 과정

 

2.1.3 인덱스 수직적 탐색

수직적 탐색은 정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정이다.

즉 인덱스 스캔 시작지점을 찾는 과정이다.

 

인덱스 수직적 탐색은 루트(root) 블록에서부터 시작한다.

루트를 포함해 브랜치 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 갖는다. 이것이 루트에서 시작해 리프 블록까지 수직적 탐색이 가능한 이유다.

 

수직적 탐색 과정에서 찾고자 하는 값보다 크거나 같은(>=) 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동한다. 위의 도표를 다시 참고해 보자.

 

1. '이재희' 찾기

• 루트 블록에는  '이재희'보다 크거나 같은 값이 없다. 그럴 때는 맨 마지막 '서' 레코드가 가리키는 하위 블록으로 이동한다.
• 브랜치 블록에서는 '이재희' 보다 큰 '정재우'를 찾았다. 따라서 바로 직전 레코드인 '이재룡'이 가리키는 하위 블록으로 이동한다. 
• 이제 리프 블록에 도달했고, 거기에서 '이재희'가 있는 첫 번째 레코드를 찾았다.

2. '강덕승' 찾기

• 루트 블록에 '강덕승'보다 큰 값인 '서'가 있다. 따라서 바로직전 레코드인 LMC가 가리키는 하위 블록으로 이동한다.
• 그러나 이 때 그 레코드가 가리키는 하위 블록으로 이동하면 안 된다.
• 바로 직전 레코드(LMC)가 가리키는 하위 블록으로 이동해야 첫 번째 리프까지 도달하며, 그곳의 맨 마지막에 저장된 '강덕승' 레코드를 포함할 수 있다.

 

수직적 탐색은 '조건을 만족하는 레코드'를 찾는 과정이 아니라 '조건을 만족하는 첫 번째 레코드'를 찾는 과정임을 기억하자.

 

인덱스를 수직으로 탐색할 떄, 루트를 포함한 브랜치 블록은 등산 푯말과 같다. 조건을 만족하는 첫 번째 레코드가 목표 지점이다. 그곳이 스캔 시작점이 된다.

 

 

2.1.4 인덱스 수평적 탐색

수직적 탐색을 통해 스캔 시작점을 찾았으면 찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프 블록을 수평적으로 스캔한다.

(루트) => (브랜치) => (리프) ...<=...=>...(리프)

 

이것이 본격적으로 인덱스에서 데이터를 찾는 과정이다. 인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖는다. 즉, 양방향 연결리스트 구조다. 이것이 수평적 탐색이 가능한 이유다.

 

인덱스를 수평으로 탐색하는 이유

1. 조건절을 만족하는 데이터를 모두 찾기 위해서.

2. ROWID를 얻기 위해서.(인덱스만 스캔하고 끝나는 경우보다는, 인덱스 스캔 후 테이블로 액세스하는 경우가 일반적이며, 이 때 ROWID가 필요하다)

 

 

2.1.5  결합 인덱스 구조와 탐색

두 개 이상의 컴럼을 결합하여 인덱스를 생성할 수도 있다.

 

 

1. 인덱스에서 남자 '이재희' 고객 찾기. 

• 루트 블록을 스캔하다 보면 찾고자 하는 값보다 큰 처 번째 레코드를 만나게 된다. (남&최). 그 레코드가 가리키는 하위 블록으로 내려가도 '이재희'는 존재하지 않는다.
• 따라서 바로 직전 LMC 레코드가 가리키는 하위 블록, 즉 왼쪽 브랜치 블록으로 이동한다.
• 왼쪽 브랜치 블록을 스캔하다 보면 찾고자 하는 값보다 큰 첫 번째 레코드를 만난다(남&정재우). 그러나 이 역시 레코드가 가리키는 하위블록으로 내려가도 '이재희' 를 만날 수 없었다.
• 따라서 바로 직전 레코드(남&이재룡)가 가리키는 하위 블록으로 이동한다.
• 이제 리프 블록에 도달했고 거기서부터 '남&이재희'를 찾는다. 리프 블록은 인덱스 키값 순으로 정렬돼 있으므로 스캔하다가 '남&이재희'보다 큰 값을 만나면 거기서 멈춘다.

---

수직적 탐색을 거쳐 찾은 인덱스 스캔 시작점이 성별='남'인 첫 번째 레코드가 아니라 성별='남'&&고객명='이재희'인 사실을 반드시 기억해야 한다.

 

실습

남자&이재희 찾기

• '이재희&남' 루트블록에서 찾고자 하는 값보다 작은 값(서&남)을 만남. 서&남을 따라가도 찾고자 하는 값을 만날 수 없었기 때문에 바로 직전 LMC 레코드가 가리키는 하위 블록, 즉 오른쪽 브랜치 블록으로 이동함.
• 거기서 찾고자 하는 값보다 큰 정재우&남을 만남. 거기를 통해 하위 블록으로 내려가도 이재희 없음.
• 따라서 그 직전 레코드(이재룡&남)이 가리키는 하위 블록으로 이동한다. 이제 리프블록으로 이동했고 거기서 이재희&남을 탐색한다. 찾는 값보다 더 큰 값이 나오면 탐색을 멈춘다.

 

인덱스를 [고객명+성별]로 구성하든, [성별+고객명]으로 구성하든 읽는 인덱스 블록 개수는 같다. 인덱스 선두 컬럼을 모두 "="조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 두든 블록I/O 개수가 같으므로 성능도 같다.

 

 

'Balanced'의 의미
delete 때문에 인덱스가 불균형(unbalanced) 상태가 될 수 있다는 주장이 있다. 특정 리프 노드들과 루트와의 거리가 더 짧거나 길 수 있다는 의미이다. 그러나 B*Tree 인덱스에서 이런 현상은 절대 발생하지않는다. Balalced는 어떤 값으로 탐색하더라도 인덱스 루트에서 리프 블록까지의 높이(height)가 같다는 뜻이다.

 

 

 

2.2 인덱스 기본 사용법

인덱스 기본 사용법은 Range Scan 하는 방법을 의미한다.

 

2.2.1 인덱스를 이용한다는 것

우리가 책의 색인을 보고 눈으로 훑어 쉽게 해당 값들이 있는 곳을 찾는 것은 수직적 탐색에 해당한다. 물론 처음부터 훑을 수도 있다. 어떤 방법을 사용했든 이것이 가능한 것은 색인이 가나다순으로 정렬돼 있기 때문이다.(찾고자 하는 단어들이 모여있다는 뜻)

 

그러나 이런 방법으로는 가공한 값이나 중간값(중간에 포함된 값)을 사용하여 스캔 시작점을 찾을 수 없다. 스캔을 도중에 멈출 수도 없다. 단어들이 흩어져 있기 때문이다. 어쩔 수 없이 색인 전체를 스캔해야 한다.

 

DB에서도 마찬가지다. 인덱스 컬럼(정확히는 선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.

• 정상적인 인덱스 사용 = 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미. 즉, Index Range Scan을 의미

인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있다. 그러나 스캔 시작점을 찾을 수 없고 멈출 수도 없기 때문에 Index Full Scan 방식을 사용해야 한다. 

 

 

2.2.2 인덱스를 Range Scan 할 수 없는 이유

 

"인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용(range scan)할 수 없다."

 

인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다.

Range를 정하려면 시작점과 끝지점을 있어야 하는데 시작점이 없으므로 범위를 정할 수 없기 때문이다.

 

- 2007년 1월 1일 이후에 태어난 첫 번째 학생을 찾는 것이 수직적 탐색에 해당한다.

-년도에 상관 없이 5월에 태어난 학생. 시작점도 없고  종료점도 없음. 풀스캔. 필요

 

스캔 불가한 경우

where substr(생년월일, 5, 2) = '05'
-- 스트링 일부를 잘라냄. e.g. 20060511의 다섯 번째부터 2개를 잘라냄
-- 인덱스에는 가공되지 않은 값들이 저장돼 있다. 가공된 값으로는 시작점을 찾을 수 없다.

where 업체명 like '%대한$'
-- '대한'으로 시작하면 스캔 가능하지만 '대한'을 포함하는 값은 전체구간에 산포돼 있으므로 Range Scan 불가

-- OR조건으로 검색하는 경우
where (전화번호 = :tel_no OR 고객명 = :cust_nm)

-- 전화번호가 '123123'이거나 고객명이'홍길동'인 한 시작지점을 바로 찾을 수 없음.
-- 따라서 어떤 방식으로 인덱스를 구성해도 Range Scan할 수 없음

 

OR Expansion

-- 아래와 같이 쿼리하면 고객명, 전화번호 인덱스 각각에 대해 Index Range Scan 가능

select *
from 고객
where 고객명= :cust_nm --고객명이 선두 컬럼인 인덱스 Range Scan
union all
select *
from 고객
where 전화번호 = :tel_no --전화번호가 선두 컬럼인 인덱스 Range Scan
and (고객명 <> :cust_nm or 고객명 is null)

-- OR조건식을 SQL 옵티마이저가 위와 같은 형태로 변환할 수 있다.
-- 이를 OR Expansion이라고 한다.

-- use_concat 힌트를 이용해 OR Expansion을 유도했을 때의 실행계획
select /*+ use_concat */ * from 고객
where (전화번호 = :tel_no OR 고객명 = :cust_nm

p.89 확인

 

-- IN 조건절
-- 전화번호가 123456789이거나 987654321인 지점 찾기

where 전화번호 in (:tel_no1, :tel_no2)

-- 불가능
-- IN 조건은 OR조건을 표현하는 다른 방식일 뿐.
-- 그러나 UNION ALL 방식으로 작성하면 각 브랜치 별로 인덱스 스캔 시작점을 찾을 수 있다.
-- Range Scan 가능하단 얘기

select *
from 고객
where 전화번호 = :tel_no1
union all
select *
from 고객
where 전화번호 = :tel_no2

-- 따라서 IN 조건절에 대해서는 SQL옵티마이저가 IN-List Iterator 방식을 사용한다.
-- IN-List 개수만큼 Index Range Scan을 반복하는 것이다.
-- 이를 통해 SQL을 UNION ALL방식으로 변환한 것과 같은 효과를 얻을 수 있다.

-- 실행계획은 p.90 참고

 

정리:

• 인덱스를 정상적으로 사용한다는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미한다.(Index Range Scan)
• 위에서 예로 든 조건들에서는 Index Range Scan이 불가하다.
• 단, OR 또는 IN 조건절은 옵티마이저의 쿼리 변환 기능을 통해 Index Range Scan으로 처리되기도 한다.

 

 

2.2.3 인덱스 사용조건

인덱스를 정상적으로 사용하기 위한 선행조건.

 

예시) 인덱스를 [소속팀 + 사원명 + 연령] 순으로 구성하였다. p.91

 

select 사원번호, 소속팀, 연령, 입사일자, 전화번호
from 사원
where 사원명 = '홍길동'

위 조건절은 Index Range Scan을 할 수 없다.

 

[소속팀 + 사원명 + 연령]인덱스의 의미

1. 데이터를 소속팀 정렬

2. 소속팀이 같은 경우 사원명순으로 정렬

3. 사원명이 같은 경우 연령순으로 정렬

 

==> 따라서 같은 사원이더라도 소속팀이 다르면 서로 멀리 떨어지게 된다. (리프 블록 전 구간에 흩어질 수 있음)

 

따라서 이 조건으로 검색하면 인덱스 스캔 시작점을 찾을 수 없다. 따라서 Table Full Scan을 해야한다.

Index Range Scan을 하기 위한 가장 첫 번째 조건은, 가공하지 않은 상태의 인덱스 선두 칼럼이 조건절에 있어야 한다는 것이다.

 

 

아래의 SQL은 선두칼럼인 '기준연도'를 가공하지 않은 상태로 조건절에 넣었기 때문에 Index Range Scan이 가능하다.

인덱스 선두 칼럼이 가공되지 않은 상태로 조건절에 있으면 Index Range Scan이 무조건 가능하다.

TXA1234_IX02 인덱스 : 기준연도 + 과세구분코드 + 보고회차 + 실명확인번호

select * from TXA1234
where 기준연도 = :stdr_year
and substr(과세구분코드, 1, 4) = :txtn_dcd
and 보고회차 = :rpt_tmrd
and 실명확인번호 = :rnm_cnfm_no

물론 Index Range Scan을 사용한다고 해서 항상 좋은 성능이 보장되는 것은 아니다.

 

인덱스를 정말 잘 타는지는 인덱스 리프 블록에서 스캔하는 양을 따져봐야 알 수 있다.

 

 

e.g. 주문상품_N1 인덱스: [주문일자 + 상품번호]

SELECT *
FROM 주문상품
WHERE 주문일자 = :ord_dt
AND 상품번호 LIKE '%PING%';

SELECT *
FROM 주문상품
WHERE 주문일자 = :ord_dt
AND SUBSTR(상품번호, 1, 4) ='PING';

 

위 SQL에서 상품번호는 스캔범위를 줄이는 데 일조하지 못했다. 첫 번째  SQL은 중간값 검색이고, 두 번째는 컬럼을 가공했기 때문이다. 만약 이 테이블에 쌓이는 데이터가 100만 건이라면, 이 SQL들은 100만건 모두를 탐색해야 한다.

 

 

2.2.4 인덱스를 이용한 소트 연산 생략

• 인덱스를 Range Scan 할 수 있는 이유는 데이터가 정렬돼 있기 때문.
• 인덱스 컬럼을 가공해도 인덱스를 사용할 순 있지만 데이터가 전체 구간에 흩어져 있기 때문에 Range Scan이 불가능하거나 비효율적.
• 결국 인덱스가 정렬돼 있기 때문에 Range Scan이 가능하며 소트 연산 생략 효과도 부수적으로 얻게 된다.

 

PK를 그림처럼 [장비번호+변경일자+변경순번]으로 구성상 테이블이 존재한다고 가정

 

 

PK인덱스에 장비번호, 변경일자가 같은 레코드는 변경 순번 순으로 정렬돼 있음.

아래와 같이 장비 번호와 변경일자를 모두 '=' 조건으로 검색할 때 PK인덱스를 사용하면 결과집합은 변경순번 순으로 출력됨.

 

SELECT *
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '201836'
--order by '변경순번': 정렬 연산이 있지만 수행하지 않음.

-- 장비번호와 변경일자가 같으므로 그 다음인 변경순번으로 출력함

옵티마이저는 이런 속성을 활요해 SQL에 ORDER BY 가 있어도 따로 정렬 연산을 수행하지 않는다.

물론 정렬 연산을 생략할 수 있게 인덱스가 구성돼 있지 않다면 SORT ORDER BY 연산 단계가 실행계획에 추가된다.

 

내림차순 (DESC) 정렬

인덱스 리프 블록은 양방향 연결 리스트 구조이다.

• 오름차순(ASC) 정렬일 때는 조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적 탐색한 후 우측으로 수평적 탐색을 한다. (↙→)
• 내림차순 정렬일 때는 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 수평적 탐색을 한다.(←↘) 

Index Range Scan이 Descending으로 이루어지는 경우 Order By 변경순번 DESC가 있다고 하더라도 SORT ORDER BY  연산이 실행계획에 포함되지 않는다. 이미 인덱스가 내림차순으로 정렬돼 있기 때문이다.

 

 

2.2.5 ORDER BY 절에서 컬럼 가공

인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다. 이 때 '인덱스 컬럼'은 대개 조건절에 사용한 컬럼을 의미한다. 그런데 조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 있다.

 

PK인덱스를 [장비번호 + 변경일자 + 변경순번] 순으로 구성했다면 아래 SQL도 정렬 연산이 생략된다. 

SELECT *
FROM 상태변경이력
WHERE 장비번호 = 'C'
ORDER BY 변경일자, 변경순번

 

만약 아래와 같이 작성했다면 정렬연산을 생략할 수 없다. 인덱스에는 가공하지 않은 상태로 값을 저장했는데, 가공한 값 기준으로 정렬을 요청했기 때문이다.

 

SELECT *
FROM 상태변경이력
WHERE 장비번호 = 'C'
ORDER BY 변경일자 || 변경순번

 

사례연구

cf. 주문_PK 인덱스는 [주문일자 + 주문번호] 순으로 구성돼 있다.

선두 컬럼인 주문일자가 '=' 조건이므로 데이터가 주문번호 순으로 출력된다. 그러나 실행계획에는 SORT ORDER BY 연산이 포함돼 있다. ORDER BY 절에 기술한 '주문번호'는 순수한 값이 아니라 TO_CHAR 함수로 가공한 주문번호를 가리키기 떄문이다.

 

SELECT *
FROM (
	SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호, A.주문금액
    FROM 주문 A
    WHERE A.주문일자 = :dt
    AND A.주문번호 > NVL(:next_ord_no, 0)
    ORDER BY 주문번호 --가공된 주문번호--
    --ORDER BY A.주문번호 라고 적어주면 가공되지 않은 값을 기준으로 정렬한다.
    --여기서는 실행계획에 SORT ORDER BY 연산이 생략된다.
)
WHERE ROWNUM <= 30


-- TO_CHAR 함수에 'FM000000'옵션을 적용하면 
--첫 번째 인자에 입력한 숫자 값을 '0'으로 시작하는 여섯자리 문자 값으로 변환해 준다.
--e.g.  '1234' => '001234'

--NVL("값","지정값"):
--값이 null 인 경우, 지정 값을 출력하고 그렇지 않으면 원래값을 그대로 출력한다.

--NVL("값","지정값1", "지정값"):
--null이면 지정값1, 아니면 지정값2를 출력한다. 조건이 null 체크인 IF문이라고 생각하면 되겠다.

 

2.2.6 SELECT-LIST에서 컬럼 가공

인덱스를 [장비번호 + 변경일자 + 변경순번]으로 구성하면 아래와 같이 변경순번 최소값을 구할 때도 옵티마이저는 정렬 연산을 따로 수행하지 않는다.

수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫 번쨰 읽는 레코드가 바로 최소값이기 때문이다.

SELECT MIN(변경순번)
FROM 상태변경이력
WHERE 장비번호 ='C'
AND 변경일자 = '20180316'

-- 상태변경이력 테이블에서, 장비번호가 c이고 변경일자가 20180316인 값들을 찾아낸다.
-- 그 값들 가운데 변경 순번이 가장 작은 값을 조회한다.
-- 애초에 그렇게 정렬돼 있으므로 정렬 연산을 생략해도 된다.

 

변경순번이 최대값인 값을 구할 때도 마찬가지다.

SELECT MAX(변경순번)
FROM 상태변경이력
WHERE 장비번호 ='c'
AND 변경일자 = '20180316'

--최대값을 찾기 위해 오른쪽으로 내려간다.
--수직적 탐색을 통해 조건을 만족하는 가장 오른쪽 지점으로 내려가서 처음 읽는 레코드가 최대값이다.
--따라서 최대값 역시 정렬 연산을 하지 않아도 된다.

 

이런 경우는 실행계획에 FIRST ROW가 나타난다. 인덱스 리프 블록의 왼쪽(MIN)또는 오른쪽(MAX)에서 레코드 하나만 읽고 멈춘다는 의미다.

 

SELECT-LIST에서 컬럼을 가공하는 경우

아래와 같이 작성하면 정렬연산을 생략할 수 없다. 

인덱스에는 문자열 기준으로 정렬돼 있는데 이를 숫자값으로 바꾼 값 기준으로 최종 변경순번을 요구했기 때문이다.

SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
-- SELECT NVL(TO_NUMBER(MAX(변경순번)), 0) 이렇게 변경하면 정렬연산 생략됨
-- 그러나 애초에 값이 고정너비(여기선 6자리)로 입력돼 있어야 한다.
-- 데이터 타입을 숫자형으로 설계했다면 이렇게 튜닝할 일도 없다.
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 ='20180316'

 

또다른 예

 

아래 SQL은 장비 구분코드 ='A001'에 해당하는 장비들의 최종 변경일자를 스칼라 서브쿼리를 이용해 상태변경이력 테이블에서 조회하고 있다. 정렬 연산 없이 MIN/MAX, FIRST ROW 방식으로 실행하고 있음을 실행계획에서 확인 가능하다. p.102

SELECT 장비번호, 장비명, 상태코드
	,(SLECT MAX(변경일자)
    FROM 상태변경이력
    WHERE 장비번호 = P.장비번호) 최종변경일자

FROM 장비 P
WHERE 장비구분코드 = 'A001'

 

최종변경일자에 최종 변경순번까지 출력하기 위한 SQL

 

방법1

SELECT 장비번호, 장비명, 상태코드
	,(SELECT MAX(변경일자)
    FROM 상태변경이력
    WHERE 장비번호 = P.장비번호) 최종변경일자
    
    ,(SELECT MAX(변경순번))
    FROM 상태변경이력
    WHERE 장비번호 = P.장비번호
    AND 변경일자 = (SELECT MAX(변경일자)
    				FROM 상태변경이력
                    WHERE 장비번호 = P.장비번호) 최종변경순번

FROM 장비 P
WHERE 장비구분코드 = 'A001'

--상태변경이력 테이블을 여러 번 읽어야 하므로 비효율적.
-- PK컬럼이 더 많아지면 SQL문도 훨씬 더 복잡해지므로 성능저하

 

방법2

SELECT 장비번호, 장비명, 상태코드
	, SUBSTR(최종이력, 1, 8) 최종변경일자
    , SUBSTR(최종이력, 9) 최종변경순번

FROM (
	SELECT 장비번호, 장비명, 상태코드
    	,(SELECT MAX(변경일자 || 변경순번)
    	FROM 상태변경이력
    	WHERE 장비번호 = P.장비번호) 최종이력
    FROM 장비 P
    WHERE 장비구분코드 = 'A001'

)
--PK컬럼이 많아져도 복잡도가 높지 않음
-- 그러나 장비당 이력이 많으면 성능 문제 발생 가능한 패턴
-- 인덱스 컬럼을 가공했기 때문.
-- 각 장비에 속한 과거 이력 데이터를 FULL SCAN 해야하므로 바로 위의 SQL문보다 성능이 낮을 수 있음.

 

 

2.2.7 자동 형변환

 

고객 테이블에 생년월일이 선두 컬럼인 인덱스가 있다고 가정.

아래 SQL은 생년월일 컬럼을 조건절에서 가공하지 않았는데도 옵티마이저는 TABLE FULL SCAN을 선택한다.

SELECT * FROM 고객
WHERE 생년월일 =19821225

-- filter(TO_NUMBER("생년월일")=19821225)

옵티마이저가 SQL을 아래와 같이 변환했고, 인덱스 칼럼이 가공됐기 때문에 Index Range Scan을 할 수 없었다. 따라서 풀스캔 한 것이다.

 

문자형과 숫자형의 비교

SELECT * 고객
WHERE TO_NUMBER(생년월일) = 19821225

-- 고객테이블 생년월일 컬럼이 문자열인데, 조건절 비교값을 숫자로 넣었다.
-- DBMS마다 다르지만 오라클은 타입체크 시 자동으로 형변환을 해준다. 에러를 내는 DBMS도 있다.
-- 오라클에서는 숫자형과 문자형 비교 시 문자형을 숫자형으로 변경한다.

 

날짜형과 문자형의 비교

 

아래는 날짜형과 문자형의 비교이다. 여기서는 날짜형이 이긴다. 문자형을 날짜형으로 변환한다.

이 경우는 좌변 칼럼을 기준으로 우변을 변환하므로 인덱스 사용에 문제가 없다. 그러나!

SELECT * FROM 고객
WHERE 가입일자 = '01-JAN-2018'

그러나 이런 버릇을 들이면 안 된다.

NLS_DATE_FORMAT 파라미터가 다르게 설정된 환경에서 수행하면 컴파일 오류가 발생하거나 결과집합이 달라질 수 있다. 반드시 날짜 포맷을 정확히 지정하는 코딩 습관을 들이자.

SELECT * FROM 고객
WHERE 가입일자 = TO_DATE('01-JAN-2018', 'DD-MON-YYYY')

 

연산자가 LIKE인 경우.(예외)

 

LIKE자체가 문자열 비교 연산자이므로, 이 때는 문자형 기준으로 숫자형 칼럼이 변환된다.

SELECT * FROM 고객
WHERE 고객번호 LIKE '9410%'

-- filter(TO_CHAR("고객번호") LIKE '9410%')

 

 

LIKE 조건을 옵션 조건 처리 목적으로 사용하는 경우

 

e.g. 거래 데이터 조회 시 사용자가 계좌번호를 입력할 수도 있고 안할 수도 있는 옵션 조건의 경우

두 개의 SQL이 필요하다.

-- SQL1: 사용자가 계좌를 입력하는 경우
SELECT * FROM 거래
WHERE 계좌번호 = :acnt_no
AND 거래일자 between :trd_dt1 and :trd_dt2

-- SQL2: 사용자가 계좌번호를 입력하지 않을 경우
SELECT * FROM 거래
WHERE 거래일자 between :trd_dt1 and :trd_dt2

 

이를 하나의 SQL로 처리하는 방법 중 하나.

계좌를 입력하지 않으면 :acnt_no 변수에 NULL값을 집어 넣어모든 계좌번호가 조회되도록 한다.

SELECT * FROM 거래
WHERE 계좌번호 LIKE :acnt_no || '%' --null 또는 전부?
AND 거래일자 between :trd_dt1 and :trd_dt2


-- 이 방법을 사용하면 LIKE, BETWEEN 조건을 함꼐 사용했으므로 인덱스 스캔 효율이 낮아진다.
-- 특히 계좌번호컬럼이 숫자형이면 LIKE 조건으로 인해 자동 형변환이 일어나기 떄문이다.
-- 그렇게 되면 계좌번호가 아예 인덱스 액세스 조건으로 사용되지 못한다.

-- 계좌번호가 형변환되면 [계좌번호+거래일자] 순으로 구성된 인덱스느 Range SCAN할 수 없다.
-- [거래일자+계좌번호]는 Range스캔할 수 있지만 스캔 효율이 안 좋다.
-- 거래일자를 전부 읽고 그 안에 흩어진 계좌번호를 탐색해야 하기 때문이다.

 

자동형변환 주의

 

자동형변환은 편리하지만 이 때문에 성능과 애플리케이션 품질에 문제가 생긴다.

아래와 같이 숫자형 컬럼(n_col)과 문자형 컬럼(v_col)을 비교하면 문자형 컬럼이 숫자형으로 변환되는데, 만약 문자형 컬럼에 숫자로 변환할 수 없는 문자열이 포함돼 있으면 쿼리 수행 도중 에러가 발생한다.

where n_col = v_col
--수치 부적합

 

실행 에러가 아니라 결과 오류가 발생하는 경우도 있다.

select round(avg(sal)) avg_sal
	, min(sal) min_sal
    , max(sal) max_sal
    , max(decode(job, 'PRESIDENT', NULL, sal)) max_sal2

from emp;


-- AVG_SAL	MIN_SAL	MAX_SAL	MAX_SAL2
-- -------	-------	-------	--------
-- 2073		800	5000	950



-- decode(a, b, c, d)
-- 'a=b'이면 c를 반환. 아니면 d를 반환한다.

• 가장 많이 받는 직원 급여: 5000
• 두 번째로 많이 받는 직원의 급여: 950 (? 평균급여인 2073에도 못 미침?)
• 가장 적게받는 직원 급여 : 800
• PRESIDENT는 가장 많이 받을 거라 예하고 이를 제외

 

그런데 실제로 테이블에는 사장을 제외하고 3000을 받는 analyst가 둘이나 있었다.

• 이런 값이 나온 이유는 형변환 때문이다. decode함수를 처리할 때 내부에서 형변환이 일어난다.
• 이 때 변환 데이터 타입은 세 번째 인자 c에 의해 결정된다.
• 만약 c가 문자형이고 d가 숫자형이라면, d는 문자형으로 변환된다.
• 또한 decode 함수는, 세 번째 인자의 값이 null이라면 varchar2(문자형)로 취급한다는 것이다.

 

즉, 앞선 쿼리는 사장을 조회할 때, 세 번째 인자가 null이므로 네 번째 인자 sal을 문자열로 변환하고 문자열 가운데 가장 큰 값을 출력한 것이다.(950)

따라서 아래와 같이 데이터 타입을 명시적으로 일치시키면 위의 오류를 피할 수 있다.

select round(avg(sal)) avg_sal
	, min(sal) min_sal
    , max(sal) max_sal
    , max(decode(job, 'PRESIDENT', to_number(NULL), sal)) max_sal2

from emp;


-- AVG_SAL	MIN_SAL	MAX_SAL	MAX_SAL2
-- -------	-------	-------	--------
-- 2073		800	5000	3000


-- to_number()대신 0(숫자형)을 써도 된다.

자동 형변환에 의존하지 마라. 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변환 해줘야 한다.

어떤 사람들은 TO_CHAR, TO_DATE, TO_NUMBER 함수와 같은 형변환 함수를 의도적으로 생략한다. 연산회수가 줄 거라고 생각하기 때문이다. 그러나 실제로 내부적으로는 옵티마이저가 형변환을 수행하고 있고, 형변환 인해 TABLE FULL SCAN이 일어난다. 이는 읽어야 할 블록I/O를 늘리는 행위이다.

SQL의 성능은 그런 사소한 연산이 아니라 블록 I/O를 줄이는 데서 결정된다.

 

 

 

 

 

2.3 인덱스 확장 기능 사용법

인덱스 스캔 방식은 여러 종류가 있다.

 

• Index Range Scan
• Index Full Scan
• Index Unique Scan
• Index Skip Scan
• Index Fast Full Scan
• etc.

 

2.3.1 Index Range Scan

Index Range Scan은 B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식이다.

인덱스 루트(Root)에서 리프(Leaf)까지 수직적으로 탐색한 후에 필요한 범위(Range)만 스캔한다.

 

실행계획

SQL> set autotrace traceonly exp

SQL> select * from emp where deptno = 20;

Execution plan
--------------------------------------------
0	SELECT STATEMENT Optimizer=ALL_ROWS
1 0	 Table ACCESS(BY INDEX ROWID) OF 'EMP' (TABLE)
2 1 	  INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX)
			^

 

주의점. 

• 인덱스를 Range Scan 하려면 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 한다.
• 선두 컬럼을 가공하지 않은 상태로 조건절에 사용하면 무조건 Index Range Scan이 가능하다.
• 무조건 인덱스 스캔 범위, 테이블 액세스 횟수를 줄이는 정도에 따라 결정된다.

 

 

2.3.2 Index Full Scan

Index Full Scan은 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.

 

SQL> create index emp_ename_sal_idx on emp (ename, sal) --인덱스 생성: 선두컬럼은 ename
SQL> set autotrace traceonly exp

SQL> select * from emp
2	where sal > 2000
3	order by ename;


Execution Plan
----------------------------------------------------
0	SELECT STATEMENT Optimizer=ALL_ROWS
1 0	 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
2 1	  INDEX (FULL SCAN) OF 'EMP_ENAME_SAL_IDX' (INDEX)
			^
          	-- 인덱스 전부 훑음

• Index Full Scan은 데이터 검색을 위한 최적의 인덱스가 없을 때 사용되는 것이 일반적이다.
• 위 SQL에서 선두컬럼인 ENAME이 조건절에 없으므로 Index Range Scan은 불가하다.
• SAL 컬럼이 인덱스에 있ㅇ므로 Index Full Scan을 통해 SAL이 2000보다 큰 레코드를 찾을 수 있다.

 

Index Full Scan의 효용성

 

• 인덱스 선두컬럼(위에서는 ENAME)이 조건절에 없으면 옵티마이저는 먼저 Table Full Scan을 고려한다.
• 테이블이 대용량이라 Table Full Scan이 부담스럽다면 옵티마이저는 Index Full Scan 을 고려한다.

 

데이터 저장공간은 '가로*세로' 즉, '컬럼 길이 * 레코드 수'에 의해 결정된다.

따라서 인덱스가 차지하는 면적은 테이블 전체의 면적보다 적을 수밖에 없다.

만약 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 일부 테이블만 액세스하는 경우라면, 면적이 큰 테이블을 Table Full Scan하는 것보다 인덱스를 Index Full Scan하는 편이 낫다.

 

아래는 Index Full Scan이 사용되는 SQL이다.

SQL> create index emp_ename_sal_idx on emp (ename, sal);

SQL> select *
2	from emp
3	where sal > 9000
4	order by ename;


Execution Plan
----------------------------------------------------
0	SELECT STATEMENT Optimizer=ALL_ROWS
1 0	 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
2 1	  INDEX (FULL SCAN) OF 'EMP_ENAME_SAL_IDX' (INDEX)
			^
            --Index Full Scan

 

왼쪽 직사각형: 인덱스, 오른쪽 직사각형: 테이블, 노란색: 레코드

위처럼 SAL > 9000인 사원이 전체 중 극히 일부라면 Table Full Scan보다 Index Full Scan이 유리.

하지만 이 방식은 적절한 인덱스가 없어 Index Range Scan의 차선책으로 선택한 것이다.

SQL의 수행빈도가 낮다면 상관 없지만, 그렇지 않다면 SAL 컬럼을 선두로하는 인덱스를 생성하는 편이 좋다.

 

 

인덱스를 이용한 소트 연산 생략

Index Full Scan을 수행하면 Range Scan과 마찬가지로 결과집합이 인덱스 컬럼 순으로 정렬된다.

Sort Order By연산을 생략할 목적으로 이 스캔을 선택할 수도 있다. (옵티마이저의 의도적인 전략적 선택)

 

 

중요 예시

SAL 조건 변경

SAL > 1000

SQL> select /*+ first_rows*/ *
2	from emp
3	where sal > 1000
4	order by ename;


Execution Plan
--------------------------------------------
0	SELECT STATEMENT Oprimizer=HINT: FIRST_ROWS
1 0	 TABLE ACCESS(BY INDEX ROWID) OF 'EMP' (TABLES)
2 1	  INDEX (FULL SCAN) OF 'EMP_NAME_SAL_IDX' (INDXE)

 

대부분의 레코드가 조건을 만족하는 경우

이 경우는 대부분의 경우가 조건을만족하고, 거의 모든 레코드에 대한 테이블 액세스가 발생한다.

따라써 Table Full Scan보다 오히려 불리하다. SAL을 선두 레코드로 바꿨더라도 마찬가지다.

 

그럼에도 옵티마이저는 Index Full Scan을 선택했다. 사용자가 first_rows 힌트로 옵티마이저 모드를 변경했기 때문이다. 소트 연산을 생략함으로써 전체 집합 중 일부를 빠르게 출력할 목적이었기 때문이다. 이 선택은 부분범위 처리가 가능한 상황에서 극적인 성능 개선을 보여준다.

 

*주의

만약 사용자가 처음 의도(부분범위 처리 활용)와 달리 fetch를 멈추지 않고 데이터를 끝까지 읽는다면 Table Full Scan보다 더 많은 I/O를 일으킨다. 이는 옵티마이저의 잘못이 아니다.

 

 

2.3.3 Index Unique Scan

Index Unique Scan은 수직적 탐색으로만 데이터를 찾는 독특한 스캔 방식이다.

Unique 인덱스를  '=' 조건으로 탐색하는 경우에 작동한다.

SQL> create unique index pk_emp on emp(empno); --인덱스 생성: Unique Index = pk_emp
SQL> alter table emp add
2	constraint pk_emp primary key(empno) using index pk_emp;


SQL> set autotrace traceonly explain
SQL> select empno, ename from emp where empno = 7788;

Excution Plan
------------------------------------------------------------

0	SELECT STATEMENT Optimizer=ALL_ROWS
1 0	 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1	  INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않도록 DBMS가 데이터 정합성을 관리한다. (같은 값이 두 개 존재하지 않도록 한다.) 따라서 해당 인덱스 키 칼럼을 모두 '=' 조건으로 검색할 때는 데이터를 한 건 찾는 순간 더이상 탐색할 필요가 없다.

 

Unique 인덱스라고 해도 범위검색 조건(between, 부등호, like)으로 검색할 때는 Index Range Scan으로 처리된다. 

e.g. empno >= 7788 은 수직 탐색 뿐만 아니라 수평탐색까지 필요하기 때문이다.

 

또한 Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도  Index Range Scan으로 처리된다.

e.g. 주문상품 PK인덱스를 [주문일자 + 고객ID + 상품ID]로 구성했는데 주문일자와 고객ID로만 검색하는 경우

 

2.3.4 Index Skip Scan

인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택한다.

Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면 Index Full Scan을 고려한다.

 

Index Skip Scan은 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔방식이다. (오라클 9i 버전부터 사용가능). 이 스캔 방식은 조건절에서 빠진 인덱스 컬럼의 Distinct Value가 적고 후행 칼럼의 Distinct Value가 많을 때 유용하다.

(고객 테이블에서 Distinct Value가 가장 적은 컬럼은 성별이다. 가장 많은 컬럼은 '고객번호'다.)

성별과 연봉으로 구성된 결합 인덱스

 

select * from 사원 where 성별='남' and 연봉 between 2000 and 4000

 

• 성별이 '남'이면서 연봉이 >= 2000인 첫 번째 레코드 탐색
• 루트 블록 네 번째 레코드가 가리키는 4번 리프 블록으로 이동하면 찾는 조건보다 큰 >=5000을 만나게 되므로, 직전에 위치한 3번 리프 블록으로 이동(여기까지가 수직적 탐색)
• 3번 리프 블록에서 성별=남, 연봉>=2000인 첫 번째 레코드를 만났다면, 거기서부터 리프블록을 차례로 스캔하다가 성별=남, 연봉 > 4000인 첫 번째 레코드를 만나면 탐색 중지

 

인덱스 선두 컬럼인 '성별'을 조건문에서 빼고 검색. (Index Skip Scan 작동 확인 목적)

Index Skip Scan을 유도하고자 할 때는 index_ss을, 방지코자 할 때는 no_index_ss를 힌트로 사용한다.

SQL> select /*+ index_ss(사원 사원_IDX)*/ *
2	from 사원
3	where 연봉 between 2000 and 4000;

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

 

Index Skip Scan은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성이 있는' 리프 블록만 골라서 액세스하는 스캔 방식이다.

 

• 인덱스 루트 블록에서 첫 번째 레코드가 가리키는 리프 블록은 [남&800] 이하인 레코드를 담고 있다. 이 블록은 엑세스 하지 않아도 될 것 같지만 해야 한다. '남' 보다 작은 성별값이 존재한다면 그 사원에 대한 인덱스 레코드는 모두 1번 리프 블록에 저장될 것이기 때문이다. (옵티마이저는 성별에 남, 녀밖에 존재하지 않는다는사실을 모른다)
• 두 번째 레코드가 가리키는 리프 블록은 남&800 이상이면서 남&1500 이하인 레코드를 담고 있으므로 액세스한다.
• 네 번째 레코드가 가리키는 리프 블록은 남&5000 이상이면서 남&8000 이하인 레코드를 담고 있으므로 skip
• 다섯 번째 레코드가 가리키는 리프 블록도 같은 이유로 skip
• 여섯 번째 리프 블록은 액세스. 남&10000 이상이므로 조건절의 구간을 초과하지만 여자 중에서 연봉<3000이거나 남과 여 사이에 다른 성별이 존재하면 이 블록에 저장되고, 연봉=3000인 여자 직원도 뒤쪽에 일부 저장될 수 있기 때문이다.
• 일곱 번째 레코드가 가리키는 리프 블록은 액세스. (????????????????????)
• 여덟, 아홉 번째 레코드가 가리키는 리프 블록은 skip.
• 열 번째 레코드가 가리키는 리프 블록은 액세스. [여&10000] 조건절 구간을 초과하지만 '여'보다 값이 큰 성별 값이 존재한다면 여기에 모두 저장될 것이므로 액세스 해야 한다.

 

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

 

Index Skip Scan은 Distinct Value 개수가 적은 선두 칼럼이 조건절에 없고, 후행 컬럼이 distinct value 개수가 많을 때 효과적이다. 그러나 인덱스 선두 컬럼이 조건절에 없을 떄만 작동하는 것은 아니다. 아래와 같은 인덱스가 있다고 가정.

일별업종거래_PK : 업종유형코드 + 업종코드 + 기준일자 -- 인덱스

1. 선두 컬럼에 대한 조건절이 존재하고 중간 컬럼에 대한 조건절이 없는 경우에도 Index Skip Scan 사용가능.

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


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

 

만약 위 SQL에

Index Range Scan을 사용한다면, 업종유형코드='01'인 인덱스 구간을 모두 스캔해야 한다.

Index Skip Scan을 사용한다면, 업종유형코드='01'인 구간에서 기준일자가 '20080501'보다 크고 '20080531'보다 작은 레코드를 포함할 가능성이 있는 리프 블록만 선택하여 액세스 가능.

 

2. Distinctive Value가 적은 두 개의 선두 컬럼이 모두 조건절에 없는 경우에도 사용 가능.

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


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

 

3. 선두 컬럼이 부등호, BETWEEN, LIKE와 같은 범위검색 조건일 때도 Index Skip Scan을 사용할 수 있다.

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

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

스킵 스캔 힌트를 붙이지 않았다면 위 쿼리는  Index Range Scan을 사용한다. 그렇다면 조건을 만족하는 인덱스 구간을 전부 Index Full Scan했을 것이다. Index Skip Scan을 사용한다면 조건을 만족하는 인덱스 구간에서 업종유형코드='01'인 레코드를 '포함할 가능성이 있는 리프 블록만' 골라서 액세스할 수 있다.

 

Index Range Scan이 불가능하거나 비효율적인 상황에서 Index Skip Scan은 효과적으로 사용될 수 있다. 부분범위 처리가 가능하다면 Index Full Scan이 도움이 될 수 있다.

 

그러나 이들 스캔 방식은 어디까지나 차선책이다. 수행횟수가 적은 SQL을 위해 인덱스를 추가하는 것이 비효율적일 경우 활용하는 방안일 뿐이다. 인덱스는 기본적으로 Index Range Scan이 가능하도록 설계해야 한다.

 

 

2.3.5 Index Fast Full Scan

Index Fast Full Scan이 Index Full Scan보다 빠른 이유는 논리적 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하기 때문이다.

관련 힌트는 index_ffs와 no_index_ffs이다.

 

화살표는 인덱스의 논리적 연결구조를 표시한 것. 즉 논리적 순서에 따라 배치한 것

리프 블록 간에는 실제로는 Double Linked List이지만 단순히 표현하려고 단방향 연결리스트 구조로 표현한 것!

Index Full Scan은 인덱스의 논리적 구조를 따라 

• (루트 →브랜치1→1→2→3→4→5→6→7→8→9→10) 순으로 블록을 읽는다.

 

 

아래는 물리적 순서에 따라 재배치 한 것이다.

Index Fast Full Scan은 물리적으로 저장된 순서대로 인덱스 리프 블록들을 읽어들인다.

Multiblock I/O 방식을 사용하여 아래의 순서대로 읽는다.

• 1번 익스텐트에서 (1→2→10→3→9) ,
• 2번 익스텐트에서 (8→7→4→5→6)
• 루트와 브랜치 블록도 읽지만 필요 없는 블록이므로 버린다.

Index Fast Full Scan은 Multiblock I/O 방식을 사용하므로 디스크로부터 대량의 인덱스 블록을 읽어야 할 때 유용하다.

속도는 빠르지만 인덱스 리프 노드가 갖는 연결리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키 순서대로 정렬되지 않는다.

쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때만 사용 가능하다.

병렬쿼리 시에는 Direct Path 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. 인덱스에 포함된 컬럼으로만 사용 가능

 

 

 

 

2.3.6 Index Range Scan Descending

Index Range Scan과 기본적으로 동일한 스캔 방식이다.

인덱스를 뒤에서부터 앞으로 스캔하기 때문에 내림차순(desc)으로 정렬된 결과집합을 얻는다는 점만 다르다.

 

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

EMPNO 컬럼에 인덱스가 있으면 옵티마이저가 알아서 인덱스를 거꾸로 읽는 실행계획을 수립한다.

 

SQL> select * from emp
2	where empno > 0
3	order by empno desc

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

 

만약 인덱스를 거꾸로 읽지 않는다면 index_desc 힌트를 이용해 유도할 수 있다.

 

MAX값을 구하고자 할 때 해당 컬럼에 인덱스가 있으면 인덱스를 뒤에서부터 한 건만 읽고 멈추는 실행계획이 자동으로 수립된다.

 

SQL> create index emp_x02 on emp(deptno, sal)

SQL> select deptno, dname, loc
2	,(select max(sal) from emp where deptno = d.deptno)
3 from dept d

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ㄴㅇㄹㄴㅇㄹ