운영되는게 기적적인 블로그

NL조인 -> 랜덤 I/O땜시 대량 데이터 처리가 안좋고 버퍼캐시 히트율에 따라 성능이 일정하지않음

소트 머지 조인 -> 항상 양쪽 테이블을 정렬해야함

그럼 해시 조인은 신인가?

기본 매커니즘

1. Build 단계 : 작은 쪽 테이블 (Build Input)을 읽어 해시 테이블(해시 맵)을 생성한다.

2. Probe 단계 : 큰 쪽 테이블 (Probe Input)을 읽어 해시 테이블을 탐색하면서 조인한다.

옵티마이저는 use_hash

1. Build 단계 : 조건절에 해당하는 사원 데이터를 읽어 해시 테이블을 생성한다. 이때 조인 컬럼인 사원번호를 해시 테이블 키 값으로 사용한다. 사원번호를 해시 함수에 입력해서 반환된 값으로 해시 체인을 찾고, 그 해시 체인에 데이터를 연결한다. 해시 테이블은 PGA 영역(꽉 찼으면 Temp)에 할당된 Hash Area에 저장

2. Probe 단계 : 조건에 해당 (최종주문금액 >= 20000) 하는 고객 데이터를 하나씩 읽어 생성한 해시 테이블을 탐색한다. 즉 관지사원번호를 해시 함수에 입력해서 반환된 값으로 해시 체인을 찾고, 그 해시 체인을 스캔해서 값이 같은 사원번호를 찾는다. 찾으면 혁명 못찾으면 반역

해시 조인이 빠른 이유

해시 테이블을 PGA 영역에 할당하기 때문에 소트 머지 조인과 같이 래치 획득 과정이 생략됨. 해시 조인도 Build Input과 Probe Input 각 테이블을 읽을 때는 DB 버퍼캐시를 경유하고 인덱스를 이용함. -> 버퍼캐시 탐색비용, 랜덤 액세스 부하 발생

근데 소트 머지보다 해시가 더 빠름

-> 소트 머지는 '양쪽' 집합을 모두 정렬해서 PGA에 담음. 두 집합 중 하나가 대형이면 Temp 테이블 스페이스 즉 디스크에 쓰는 작업을 반드시 수반한다.

-> 해시 조인은 '한쪽'을 읽는데 둘 중 작은 집합을 해시 맵 Build Input으로 선택하므로 왠만해서 Temp를 쓸일이 없다.

---> NL 조인처럼 조인 과정에서 랜덤 액세스 부하가 없고, 양쪽을 미리 정렬하는 부하도 없다. 훌륭하다.

대용량 Build Input 처리

두 테이블은 대용량이라 인메모리 해시 조인이 안된다.

-> 그러때 분할 정복 알고리즘을 사용한다. 

1. 파티션 단계

조인하는 양쪽 집합 (조인 이외 조건절을 만족하는 레코드)의 조인 컬럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝한다. 즉 독립적으로 처리할 수 있는 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝을 생성한다.

양쪽 집합을 읽어 디스크 Temp 공간에 저장해야하므로 인메모리 해시 조인보다 성능 떨어짐

2. 조인 단계

파티션 단계를 완료하면 각 파티션 짝에 대해 하나씩 조인을 수행한다 -> 이때 해시 조인과정(빌드 & 프루브)는 독립적으로 결정된다.

해시 테이블을 생성하고 나면 반대쪽 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색한다. 모든 파티션 짝에 대한 처리를 마칠때 까지 이 과정을 반복

실행계획

위쪽 사원 데이터로 해시 테이블 생성한후, 아래쪽 고객 테이블에서 읽은 조인 키 값으로 해시 테이블을 탐색하면서 조인한다.

(사진은 Index를 이용하여 Build Input, Probe Input을 읽었지만 Table Full Scan도 간능)

swap_join_inputs로 Build Input을 명시할 수 있다.

3개 이상 테이블이면?

경로 1 : A와 B를 조인하고 B와 C 조인

경로 2:  A와 B를 조인, A와 C를 조인 (근데 이건 복잡하니까 걍 경로 1로 고정하자)

leading 힌트를 통해 순서를 조정한다

이때 첫번째 파라미터로 지정한 테이블이 무조건 Build Input으로 선택됨

조인 메소드 선택 기준

대량의 데이터는 많고 적음이 아니라 NL 조인 기준으로 최적화했음에도 랜덤 액세스가 많아 성능이 적을때이다.

수행 빈도가 매우 높은 쿼리의 기준

1. NL조인과 해시 조인 성능이 같으면 NL 조인
2. 해시 조인이 약간 더 빨라도 NL 조인
3. NL 보다 해시 조인이 매우 빠르면 해시

어쨋뜬 NL조인이 항상 가장 먼저 고려되어야한다.

NL 조인에 사용하는 인덱스는 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조

<> 반면에 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸

--> 같은 쿼리를 100개 수행하면 해시 테이블도 100개 만들어짐 

----> 수행시간 짧으면서 수행빈도가 매우 높은것을 해시 조인으로 처리하면 CPU와 메모리 사용률이 크게 증가한다. 해시 맵을 만드는 과정에서 래치 경합도 발생한다.

즉 해시조인은

1. 수행 빈도가 낮고
2. 쿼리 수행 시간이 오래 걸리는
3. 대량 데이터 조인 할때

--> 배치 프로그램, DW, OLAP성 쿼리의 특징

배치 프로그램

대량의 데이터를 일괄적으로 처리하는 프로그램입니다 보통 정해진 시간(예: 매일 새벽)에 실행되어 대량의 데이터를 처리합니다 실시간 처리가 필요없는 데이터 정제, 통계 집계, 리포트 생성 등에 사용됩니다 장애 발생시 재처리가 가능하도록 멱등성을 보장해야 합니다 예: 일일 매출 집계, 월말 정산, 고객 세그먼트 분석 등

DW (Data Warehouse)

기업의 의사결정을 지원하기 위한 통합 데이터 저장소입니다 운영 데이터베이스(OLTP)의 데이터를 추출/변환하여 적재합니다 데이터는 주제별로 통합되고 시계열로 저장됩니다 일반적으로 Star Schema나 Snowflake Schema 모델을 사용합니다 데이터 중복을 허용하여 조회 성능을 최적화합니다

OLAP (Online Analytical Processing)

다차원 데이터 분석을 위한 기술입니다 주로 DW의 데이터를 사용하여 다양한 관점의 분석을 수행합니다 Drill-down, Roll-up, Slice, Dice 등의 분석 연산을 제공합니다 복잡한 집계 쿼리가 많고 대량의 데이터를 처리합니다 예: "지역별, 상품별, 기간별 매출 분석" 같은 다차원 분석

조인 컬럼에

• 대량 데이터 조인이며
• 조인 컬럼에 인덱스가 없고
• 조건절이 =이 아닐때

SGA vs PGA

공유 메모리 영역인 SGA에 캐시된 데이터는 여러 프로세스가 공유할 수 있다. 대신 동시에 액세스 할 수 없다. 프로세스 간 액세스를 직렬화하기 위한 Lock 메커니즘으로서 Latch(래치)가 존재한다.

-> 데이터 블록과 인덱스 블록을 캐싱하는 DB 버퍼캐시는 SGA의 핵심요소, 또한 블록을 읽으려면 버퍼 Lock을 얻어야함

오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서, 동시에 자신만의 고유 메모리 영역을 갖는다 -> PGA

PGA는 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용되며 공간이 작아 모두 저장할 수 없으면 Temp 테이블 스페이스를 이용

독립적인 공간이므로 래치 메커니즘이 불필요하여 SGA 버퍼캐시보다 훨씬 빠르다

기본 메커니즘

소트 (양쪽 집합을 조인 컬럼 기준으로 정렬) -> 머지

옵티마이저는 use_merge 사용

1. 사원 데이터를 읽어 조인컬럼인 사원번호 순으로 정렬, 정렬한 결과집합은 PGA 영역에 할당된 Sort Area에 저장. 데이터가 너무 많으면 Temp 테이블 스페이스에 저장
2. 고객 데이터를 읽어 조인컬럼이 관리사원번호 순으로 정렬, 그 뒤는 똑같이
3. PGA (or Temp TableSpace)에 저장한 사원 데이터를 스캔하면서 저장한 고객 데이터를 조인

1,2가 소트 3이 머지

사원 데이터를 기준으로 고객 데이터를 매번 Full Scan하지 않는다 -> 데이터가 정렬되어있으므로

Sort Area에 저장한 데이터 자체가 인덱스 역할

소트 머지 조인이 빠른 이유

NL 조인은 건건이 DB 버퍼캐시를 경유한다. 그 과정에서 래치 획득, 캐시버퍼 체인 스캔 과정을 거친다.

소트 머지 조인은 조인 대상 집합을 '일괄적으로' 읽어 PGA(Temp)에 저장후 조인한다. PGA는 래치 획득 과정이 없다.

소트 머지 조인도 양쪽 테이블로부터 조인 대상 집합을 읽을 때는 DB 버퍼캐시를 경유하고 이때 인덱스를 사용하기도 한다. 버퍼캐시 탐색, 랜덤 액세스 부하도 있다 (? 그럼 이거 왜씀, 왜 빠름)

주 용도

해시 조인이 더 빠른데. 해시 조인은 조인 조건식이 = 조건이 아닐때 사용못한다.

즉 대용량, 조인 조건 = 아닐때 사용

제어

요약

조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다. 하지만 PGA 영역에 저장한 데이터를 이용하며 소트 부하만 감수하면 NL조인 보다 빠르다.

조인의 기본은 NL (Nested Loop Join)이다. 인덱스를 이용한 조인이다.

기본 매커니즘

사원 테이블로부터 1996년 1월 1일 이후 입사한 사원을 일일히 고객 테이블에서 사원번호가 일치하는 레코드를 찾는다 -> NL 조인 알고리즘

일반적으로 Outer, Inner 양쪽 테이블 모두 인덱스를 이용한다.

Outer 쪽 테이블은 사이즈가 크지 않으면 인덱스를 이용하지 않아도 된다. -> 어차피 Table Full Scan해도 한번) 사원

Inner 쪽 테이블은 인덱스를 이용해야한다. -> (Outer 루트에서 읽은 건수만큼 Table Full Scan 반복) 고객

 NL 조인 실행계획 제어

NL 조인을 제어할때는 use_nl 힌트를 사용한다.

ordered 힌트는 from절에 기술한 순서대로 조인하라는 뜻

FROM절이 아니라 내가 커스텀하고 싶으면 leading

옵티마이저야 알아서 판단해라

  NL 조인 수행 과정 분석

2 -> 3 -> 1 -> 4

먼저 사원_X1 인덱스에서 입사일자 조건에 맞게 Range Scan -> 인덱스에서 읽은 ROWID로 사원테이블을 액세스해서 부서코드를 필터 -> 사원 테이블에서 읽은 사원번호 값으로 조건을 만족하는 고객 테이블 Range Scan -> 나머지 필터링

요기서

• 부서코드를 필터링하는 단계에서 필터링되는 비율이 높으면 X1인덱스에 부서코드를 추가하는 방안 고려
• 조인 액세스 횟수 많을 수록 성능 느려짐 -> Outer 테이블 사원을 읽고 필터링한 결과 건수에 의해 결정
• 고객 테이블 액세스할때 고객X1 인덱스에 최종주문금ㅇ액 컬럼 추가하는 방안 고려
• 사원 X1 인덱스를 스캔하면서 추출한 레코드가 ㅁ낳으면 사원 테이블로 랜덤액세스하는 횟수, 고객 X1인덱스를 탐색하는 횟수, 고객 테이블로 랜덤 액세스하는 횟수가 많아짐

OLTP 에서 튜닝할때 NL 조인부터 고려

  특징요약

• 랜덤 액세스 위주의 조인방식
• 한 레코드씩 순차적으로 진행
• 인덱스 구성 전략이 특히 중요

  튜닝 실습

여기서 사원 X1 인덱스를 스캔하고서 사원 테이블을 액세스한 횟수가 2780건인데 테이블에서 부서코드를 필터링한 조건은 3건이다. 불필요한 테이블 액세스가 많다. -> 이때 인덱스에 컬럼 추가 고려

사원 X1에서 읽은 블록이 102개, 만약 한 블록에 500개 레코드가 있다면 3건을 읽기위해 50000레코드를 읽은 셈

-> 부서코드 + 입사일자로 컬럼 구성

사원테이블 읽는 부분에서 비효율이 없다

사원 테이블을 읽고 조인하는 횟수가 문제다 -> 2780번 조인 시도후 최종 결과 집합은 5건뿐 이땐 조인 순서를 변경

  확장 매커니즘

테이블 Prefetch - 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면 곧 읽게될 블록까지 미리 읽어서 버퍼캐시에 적재

nlj-prefetch, no_nlj_prefetch 옵티마이저 사용

배치 I/O - I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능

nlj_batching, no_nlj_batching 힌트 사용

일부 디스크에서 읽게되면 성능 차이가 날수도있음

배치I/O 실행계획이 나타나면 결과집합의 정렬 순서가 달라일 수 있음

액세스 조건이 되는 경우:

범위 검색 가능한 연속된 선두 컬럼들
등호(=) 조건
BETWEEN, IN 조건

필터 조건이 되는 경우:

선두 컬럼이 없는 후행 컬럼만의 조건
범위 검색 이후의 컬럼들
함수나 연산이 포함된 조건

예: 인덱스(A+B+C)에서

WHERE A=1 AND B=2 AND C>3: A,B는 액세스, C는 필터
WHERE A>1: A는 액세스, B,C는 필터

Index Skip Scan 활용

BETWEEN 조건을 IN-LIST조건으로 굳이 안바꿔도 Index Skip Scan을 활용하면 같은 효과를 낼수있다.

옵티마이저 INDEX_SS(인덱스)

이 쿼리를 최적으로 수행하려면 인덱스를 판매구분 + 판매월로 구성

테이블 액세스 없이 인덱스 레인지 스캔타고 281개의 블록 I/O 발생

만약 반대로 판매월 + 판매구분으로 둔다면 A와 B는 1:11 이므로 멀리 떨어진다.

옵티마이저로 인덱스2를 타도록 유도한 후 실행계획을 확인했었을때 인덱스 스캔했지만 3090 블록 I/O가 발생하였다.

테이블을 전혀 방문하지 않았지만 매우 비효율 적인 것이다.

In-List로 변환하면?

314개의 블록 I/O가 발생하여 성능이 10배 좋아졌다

Index Skip Scan으로 변환하면

BETWEEN임에도 300블록만 읽었다.

IN 조건은 '='인가

고객별가입상품 테이블에서 고객번호의 평균 카디널리티는 3일때

인덱스를 상품Id + 고객번호 순으로 생성하면 같은 상품은 고객번호 순으로 정렬된 상태로 리프 블록에 저장. 그럼 고객 번호 기준으로는 같은 고객번호가 상품ID가 따라 뿔뿔이 흩어진 상태

이러면 상품ID 조건절이 IN-List Iterator방식으로 풀리는 것이 효과적이다.

그 말은

고객번호와 상품ID 둘 다 인덱스 액세스 조건으로 사용된다. 인덱스를 수직적으로 세번 탐색하여 그 과정에서 아홉개의 블록을 읽는다;

이번엔 고객번호 + 상품Id로 생성하면

상품 ID 조건절을 IN-List Iterator방식으로 풀지 않으면 상품 ID 조건절은 필터로 처리한다. 고객은 한 블록에 모여있으므로 총 3번만 블록I/O가 발생한다.

IN 조건이 '='이 되려면 IN-List Iterator 방식으로 풀려야한다. 아니면 IN 조건은 필터 조건이다. 데이터가 아주 많으면 =이 효과적이다. 아니라면 차라리 필터방식으로 처리되는게 낫다.

num_index_keys 옵티마이저

IN-List를 액세스 조건 또는 필터 조건으로 유도하는 옵티마이저

첫번째 컬럼까지만 액세스 조건으로 사용하라는 뜻

두번째 컬럼도 액세스 조건이 된 것을 볼 수 있다.

인덱스 컬럼을 가공하는 방법도 있다

BETWEEN과 LIKE 스캔 범위

LIKE보다 BETWEEN이 낫다.

판매월 + 판매구분 순으로 구성할때

범위검색 조건을 남용할 때 생기는 비효율

조건절을 모두 LIKE로 구사할때 해당 컬럼이 인덱스 구성 컬럼일 때는 주의가 필요하다.

인덱스 : 회사코드 + 지역코드 + 상품명

액세스 조건이던 상품명이 필터 조건으로 바뀌면서 이렇게 변했다.

인덱스 컬럼에 범위검색 조건을 남용하면 인덱스 스캔 비효율이 생긴다.

다양한 옵션 조건 처리 방식의 장단점 비교

1, OR 조건

옵션 조건 컬럼을 선두에 두고 고객Id+거래일자 순으로 인덱스를 구성해도 인덱스를 사용할 수 없다.

-> 인덱스 선두 컬럼에 대한 옵션 조건에 OR 조건을 사용해선 안된다.

거래일자 + 고객 ID로 구성하면? 고객ID가 필터 조건으로 상태 완전 비효율

즉

• 인덱스 액세스 조건으로 사용 불가
• 인덱스 필터 조건으로도 사용 불가
• 테이블 필터 조건으로만 사용 가능
• 18c부터 인덱스 필터 조건으로, 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼일때

그니까 OR쓰지 말고 OR-Expansion (OR을 Union All로 변환)을 통해 인덱스 사용 가능

2. LIKE/BETWEEN 활용

변별력이 좋은 필수 조건이 있는 상황에서 활용하면 좋다. 필수 조건 컬럼을 인덱스 선두에 두고 액세스 조건으로 사용하면 LIKE/BETWEEN이 인덱스 필터 조건이어도 좋은 성능을 낼 수 있다.

=이면 더 욱 좋다.

단 Table Full Scan이 유리한데 옵티마이저는 Index Range Scan을 선택한다. 그럼 문제가 생긴다.

1. 인덱스 선두 컬럼

인덱스 선두 컬럼에 대한 조건을 LIKE/BETWEEN으로 처리는 지양해야한다.

사용자가 고객ID를 입력하면 둘다 범위조건이지만 고객 ID 변별력이 매우 좋아 성능은 크게 안나빠지지만 입력안하면 모든 거래 데이터를 조회하면서 거래일자를 필터링한다.

이렇게 sql짤꺼면 거래일자 + 고객ID순으로 구성해야한다. -> 고객ID 값을 입력할때 생기는 비효율 감수

-> 특정 고객의 거래를 조회하고 싶지만 거래일자 범위에 속한 모든 거래 데이터를 스캔하면서 고객 ID조건을 필터링하기 때문

2. NULL 허용 컬럼

NULL 허용 컬럼에 대한 옵션 조건을 LIKE/BETWEEN 연산자로 처리하는 것도 금물. 성능을 떠나 결과 집합에 오류

고객 ID가 널 허용이고 실제 널값이 입력되어 있으면 데이터가 결과 집합에서 누락된다.

3. 숫자형 컬럼

숫자형이면서 인덱스 액세스 조건으로도 사용 가능한 컬럼에 대한 옵션 조건 처리는 LIKE 방식쓰면 안된다.

고객ID가 숫자형 컬럼이면 to_char 자동 형변환이 발생하여 고객 ID가 필터조건으로 사용된다.

즉 고객ID+거래일자 순으로 구성한 인덱스는 사용못함

4. 가변 길이 컬럼

LIKE 옵션조건에 사용할 때는 컬럼 값 길이가 고정적이어야 한다.

김훈을 검색했는데 김훈남도 같이 조회된다.

3. UNION ALL 활용

cust_id에 어떤 값을 입력했느냐에 따라 SQL중 하나만 실행되게 하는 방식이다.

cust_id에 변수를 입력하든 안하는 인덱스를 가장 최적으로 사용한다.

LIKE 패턴도 인덱스 사용은 가능하지만 필수 조건인 거래일자가 BETWEEN이면 옵션 조건 컬럼을 필터 조건으로 사용한다.

UNION ALL 방식은 옵션 조건 컬럼도 인덱스 액세스 조건으로 사용한다

4. NVL/DECODE 함수 활용

cust_id 변수에 값을 입력하지않으면 위쪽 브랜치에서 거래일자가 선두인 인덱스를 사용하고 입력하면 고객ID + 거래일자 인덱스를 사용한다.

고객ID를 함수 인자(가공)했는데도 인덱스를 쓸수있는 이유는 OR-Expansion 쿼리 변환이 발생했기때문. 

장점 : 옵션 조건 컬럼을 인덱스 액세스 조건으로 사용할 수 있다 (UNION ALL보다 단순한데 같은 성능)

단점 : NULL 허용 컬럼에 사용할 수 없다 - NULL을 입력하면 값이 NULL인 레코드가 결과집합에서 누락

가장 변별력이 좋은 컬럼 기준으로 한번만 OR Expansion이 일어난다.

NVL, DECODE 뭘 사용해도 똑같다.

함수호출부하 해소를 위한 인덱스 구성

PL/SQL 사용자 정의 함수는 좀 느리다.

대량 데이터를 조회하면 성능 차이를 좀 느낀다.

느린 이류는

• 가상머신 상에서 실행되는 인터프리터 언어
• 호출시마다 컨텍스트 스위칭 발생
• 내장 SQL에 대한 Recursive Call 발생 (이놈이 제일 문제)

인덱스 설계에 필요한 여러가지 판단 기준, 공식을 초월한 전략적 설꼐의 필요성, 방법론 등을 설명한다.

인덱스 설계가 어려운 이유

인덱스를 생성하면 관리비용뿐만 아니라 시스템 부하를 증가시키는 요인이 된다.

• DML 성능 저하 (TPS 저하 TPS는 Transaction Per Second의 약자로, 1 초당 처리할 수 있는 트랜잭션)
• 데이터베이스 사이즈 증가
• 데이터베이스 관리 및 운영 비용 상승

인덱스를 삽입할때 수직적 탐색을 통해 입력할 블록을 찾고 찾은 블록에 여유 공간이 없으면 인덱스 분할도 발생한다.

지울때도 레코드를 일일이 찾아 지워야한다. 테이블 DML 성능저하는 TPS 저하로 이어진다. 디스크 공간을 낭비하고 데이터베이스 사이즈만큼 백업, 복제, 재구성 등의 운영 비용도 발생한다.

기존 인덱스 구성을 변경하는 것도 시스템 변경 영향도가 매우 크다. 영향받은 SQL을 모두 찾아 성능 검증을 해야하기 때문이다.
신규 인덱스 추가는 비교적 변경 영향도가 적다. 하지만 그럴수록 TPS는 점점 나빠진다.
즉 초반 설계가 굉장히 중요하다.

1. 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.

2. '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.

스캔 효율성 이외의 판단 기준

• 수행 빈도
• 업무상 중요도
• 클러스터링 팩터
• 데이터량
• DML 부하
• 저장 공간
• 인덱스 관리 비용

Outer 테이블(①번)의 인덱스:

예시에서는 거래 테이블이 Outer

비효율적이어도 큰 문제가 되지 않음

거래일자 + 거래구분코드로 구성

Inner 테이블(②번)의 인덱스:

예시에서는 상품 테이블이 Inner

이 부분의 비효율이 있으면 성능에 큰 문제 '=' 조건이 선두에 오는 것이 중요

가능하면 테이블 액세스 없이 인덱스에서 필터링되도록 구성

핵심: Inner 쪽 인덱스가 더 중요 Outer는 한 번만 액세스하지만, Inner는 Outer의 결과 수만큼 반복 액세스 BETWEEN 조건이 있어도 Outer로 두면 비효율적인 스캔을 반복하게 됨

공식을 초월한 전략적 설계

조건절 패턴이 열개가 있을때 패턴마다 인덱스를 하나씩 만들순없다

-> 최적을 달성해야할 가장 핵심적인 액세스 경로 한두 개를 전략적으로 선택해서 최적 인덱스를 설계하고, 나머지 액세스 경로는 약간의 비효율이 있더라도 목표 성능을 만족하는 수준으로 인덱스를 구성할 수 있어야한다.

일자/일시 조건을 선두에 두고 자주 사용하는 필터 조건을 모두 뒤쪽에 추가하는 방식

1. 일자 조회구간이 길지않으면 인덱스 스캔 비효율이 성능에 미치는 영향이 크기않음

2. 인덱스 스캔효율보다 테이블 액세스가 더 큰 부하요소

소트 연산을 생략하기 위한 컬럼 추가

인덱스는 항상 정렬 상태를 유지하므로 ORDER BY, GROUP BY를 위한 소트 연산을 생략할 수 있게 해준다.

성능을 고려하지 않고 소트 연산을 생략하려면 인덱스를 "청약일자 + 입력자 ID"로 구성하면 된다.

'=' 조건절 컬럼은 ORDER BY절에 없더라도 인덱스 구성에 포함할 수 있다. 위 SQL에서는 취점지점 ID가 =조건이다.

-> 청약일자 + 취급지점ID + 입력자ID 순으로 구성해도 소트연산을 생략할 수 있다. 위츠는 앞뒤 중간 어디에 두어도 상관없다. '='이 아닌 조건절 컬럼은 반드는 ORDER BY 컬럼보다 뒤쪽에 뒤어야 소트 연산을 생략할 수 있다. (ex. 청약일자 + 입력자 ID + 입력일자 + 계약상태코드)

문제는 조건을 만족하는 데이터를 빨리 만날수 있냐가 관겅이다.

앞에서 만나면 결과집합이 빨리 출력되기 시작하겠지만. 뒤쪽에서 만나면 많은 I/O가 발생할 것이다.

즉

• '=' 연산자로 사용한 조건절 컬럼 선정
• ORDER BY 절 기술한 컬럼 추가
• '=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

또한 소트 연산을 생략하려면 In 조건절이 In-List Iterator 방식으로 풀리면 안된다 (인덱스 엑세스 조건). 즉 필터 조건으로 사용되어야한다.

결합 인덱스 선택도

인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 중요한 판단기준

선택도 : 전체 레코드 중에서 조거절에 의해 선택되는 레코드 비율

* 선택도 x 총 레코드 수 = 카디널리티

인덱스 선택도 : 인덱스 컬럼을 모두 '='으로 조회할 때 평균적으로 선택되는 비율을 의미

선택도가 높은 (카디널리티가 높음) 인덱스는 생성해봐야 효용가치가 없다. -> 테이블 액세스가 많이 발생

컬럼 순서 결정시, 선택도 이슈

결합 인덱스를 구성할때 선택도가 낮은 컬럼을 앞에 두는 것이 유리하진 않다. 인덱스 스캔 효율에 전혀 차이가 ㅇ벗다.

인덱스 설계할 때 우리가 할 일은 '항상 사용하는' 컬럼을 앞쪽에 두고 그 중 '=' 조건을 앞쪽에 위치시키는 것 뿐. 선택도가 낮은 컬럼을 앞쪽에 두려는 노력은 의미없거나 오히려 손해 -> Index Skip Scan이나 In List 조건을 활용할 수 있기 때문, 인덱스를 압축할 경우, 고객등급을 앞쪽에 두면 압축률이 더 좋을수도있다.

즉, 선택도가 매우 중료하지만 컬럼 간 순수를 결정할 때는 각 컬럼의 선택도보다 필수 조건 여부, 연산자 형태가 더 중요한 판단 기준이다.

중복 인덱스 제거

X03이 X01 X02를 완전히 포함하고 있기 때무에 X01 X02는 삭제해도 된다.

만약 계약ID의 평균 카디널리티가 매우 낮다면 사실상 중복이다. 그러니까 걍

이렇게 구성해도된다.

거래일자가 항상 BETWEEN 또는 부등호 조건이면 N3와 N4 인덱스는 둘다 거래일자가 인덱스 엑세스 조건이다. 그러므로 그냥 N4를 삭제하고 '거래일자 + 종목코드 + 계좌번호'로 구성하면된다.

혹은 N3인덱스 변경없이 N4인덱스를 제거하고 N1 인덱스를 사용하면 된다.

--

인덱스 설계도

호옹이

IOT, 클러스터, 파티션은 테이블 랜덤 액세스를 최소화하는데 매우 효과적인 저장 구조이지만!

성능 검증을 위해 많은 테스트를 해야하므로 물리 설계가 중요한 이유이다.

인덱스 탐색

조건절 1 : WHERE C1 = 'B'

수직적 탐색은 스캔 시작점을 찾는 과정이다.

수직적 탐색을 통해 C1 = 'B'이고 C2 = 3인 첫번째 레코드를 찾았다

그후 수평적 탐색을 하면서 C1 = 'B'이면서 C2 = 4인 레코드에서 스캔을 멈춘다.

스캔의 시작과 끝을 결정하였고 스캔량이 줄어들었다.

조건절 2 : WHERE C1 = 'B' AND C2 = 3

수직적 탐색에서 C1 = 'B'이고 C2 = 3인 레코드를 찾았다. 그래서 그 직전 레코드가 가리키는 2번째 리프블록으로 내려가서 탐색한다.

이 역시 스캔 범위를 줄였다.

조건절 3 : WHERE C1 = 'B' AND C2 >= 3

C >= 3 조건절이 스캔을 멈추는데 역할은 하지 못했으나 스캔의 시작점을 결정하였다.

스캔 범위를 줄였다.

조건절 4 : WHERE C1 = 'B' AND C2 <= 3

수직적 탐색에서 C1 = 'B'인 첫번째 레코드를 찾고 거기서 스캔하다가 C2 > 3인 첫번째 레코드를 만나는 순간 스캔을 멈췄다. C2 <= 3 조건절은 수직적 탐색에는 안쓰였지만 스캔 종료지점을 찾는데 역할을 하였다

스캔량을 줄였다.

조건절 5 : WHERE C1 = 'B' AND C2 BETWEEN 2 AND 3

C1 ='B' 이고 C2 >= 2인 첫번째 레코드를 찾고 C2 > 3인 첫번째 레코드를 만나는 순간 스캔을 멈췄다.

시작과 종료를 찾았으니 스캔 범위는 줄었다.

조건절 6 : WHERE C1 BETWEEN 'A' AND 'C' AND C2 BETWEEN 2 AND 3

C1 조건절은 스캔 시작과 끝 지점을 결정하였지만 C2는 그렇지 못하였다.

C2는 스캔량을 줄이는데 역할을 못했다.

인덱스 스캔 효율성

1. '성능검' 으로 시작하는 용어는 어디까지 스캔할까

-> '성능검사'에서 시작하여 '성능계수'까지 스캔한다. -> 2건을 얻기위해 세건을 읽었다.

2. '성능'으로 시작하고 네번째가 '선'인 용어는 어디서 시작하고 끝날까

-> '성능'으로 시작하는 용어를 모두 스캔한다. 똑같은 2건인데 스캔 효율이 아주 ㅎㅌㅊ이다.

한 단씩 쪼개서 인덱스로 만들어보자

3. 성능검으로 시작하는 레코드를 검색하면 스캔 범위는?

-> '성능검사'에서 시작하여 '성능계수'까지 3개를 스캔하고 멈춘다. -> 두건을 위해 세건을 스캔함.

4. 성능으로 시작하고 선으로 끝나는 레코드를 검색할때 스캔범위는?

똑같이 효율이 아주 하타친다. 왜? 인덱스 선행 컬럼이 조건절에 없기때문 C3가 없다.

SQL 트레이스에서 10건의 레코드를 얻기위해 7463블록을 읽었다. 한 블록당 평균 500개 레코드가 있다고 가정하면 7.7643 x 500개 레코드를 읽었다. 아주 비효율적이라는 거다.

액세스 조건과 필터 조건

엑세스 조건 : 인덱스 스캔 범위를 결정하는 조건절, 수직적 탐색으로 스캔 시작점을 결정하고 수평적 탐색으로 리프 블록 스캔하면서 언제 멈출지 영향

WHERE 절에서 인덱스 컬럼을 사용한 조건이 액세스 조건이 됨

필터 조건 : 테이블로 액세스할지를 결정하는 조건절, 인덱스든 테이블이든 테이블 액세스 단계에서 처리되는 조건절은 모두 필터 조건이다.

비용 = 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 액세스 비용
        = 인덱스 루트와 브랜치 레벨에서 읽은 블록 수 +
            인덱스 리프 블록을 스캔하는 과정에 읽은 블록 수 +
             테이블 액세스 과정에 읽은 블록 수

비교 연산자 종류와 컬럼 순서에 따른 군집성

인덱스는 '같은 값'을 갖는 레코드들이 서로 군집해 있다.

인덱스 컬럼을 앞쪽부터 누락없이 '=' 연산자로 조회하면 조건절을 만족하는 레코드는 모여있다. 어느 하나를 누락하거나 '=' 조건이 아닌 연산자를 조회하면 조건절을 만족하는 레코드가 서로 흩어진 상태가 된다.

where C1 = 1
and     C2 = 'A'
and     C3 = '나'
and     C4 = 'a'

인덱스 액세스 조건 : C1, C2, C3, C4

인덱스 필터 조건 : 

where C1 = 1
and     C2 = 'A'
and     C3 = '나'
and     C4 >= 'a'

인덱스 액세스 조건 : C1, C2, C3, C4

인덱스 필터 조건 : 

where C1 = 1
and     C2 = 'A'
and     C3 between '가' and '다'
and     C4 = 'a'

인덱스 액세스 조건 : C1, C2, C3

인덱스 필터 조건 : C4

where C1 = 1
and     C2 <= 'B'
and     C3 = '나'
and     C4 between 'a' and 'b'

인덱스 액세스 조건 : C1, C2

인덱스 필터 조건 : C3, C4

where C1 between 1 and 3
and     C2 = 'A'
and     C3 = '나'
and     C4 = 'a'

인덱스 액세스 조건 : C1

인덱스 필터 조건 : C2, C3, C4

여기서 실행계획을 보면 access("C1" > 1 AND "C2" > 'A' AND "C3"='나' AND 'C4"='a'

로 인덱스 컬럼에 대한 조건절은 모두 액세스 조건으로 표시된다. 스캔 범위를 줄이는데 역할을 못했는데

그러니까 실행계획을 믿지말고 나머지는 필터 조건이라고 이해하자

인덱스 선행 컬럼이 등치(=) 조건이 아닐때 생기는 비효율

인덱스 컬럼을 조건절에 모두 등치(=)조건으로 사용할 때 가장 좋다. 리프 블록을 스캔하면서 읽은 레코드는 하나도 걸러지지 않고 모두 테이블 액세스로 이어지므로 비효율이 없다.

반면 선행 컬럼이 조건절에 없거나, 부등호, BETWEEN, LIKE 같은 범위검색 조건이면 스캔하는 단계에서 비효율이 생긴다.

아파트시세코드 + 평형 + 평형타입 + 인터넷 매물 인덱스 순으로 구생했을ㄸ

인터넷매물 between '1' and '3'

선행 컬럼들은 모두 '='조건이라 비효율없이 조건을 만족하는 컬럼을 찾았다.

인터넷 매물 + 아파트시세코드  + 평형 + 평형타입 으로 인덱스 순서를 바꾸면

인터넷 매물이 between '1' and '3' 일때 스캔 범위가 굉장히 넓어진다.

BETWEEN을 IN_List로 전환

BETWEEN 조건을 IN-List로 바꿔지면 큰 효과를 얻을수있다.

IN-List 개수만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 컬럼을 '=' 조건으로 검색하므로 선두 컬럼에 BETWEEN을 사용할 때와 같은 비효율이 사라진다. Index Skip Scan 방식으로 유도해도 비슷한 효과를 얻을 수 있다.

레코드들이 서로 멀리 떨어져 있을때 유용하다. 인덱스 스캔량이 유의미하게 줄어들기 때문이다.

IN_LIST 항목 개수가 늘어날 수 있다면 BETWEEN을 In_List로 전환하지 말고 NL 방식의 조인문이나, 서브쿼리로 구현하면 된다.

In-List 개수가 많지 않아야한다. 수직적 탐색이 많이 발생하기 때문이다.루트에서 브랜치 블록까지 Depth가 깊을때 특히 그렇다.

일단 여기까지 쓰고 다음으로 넘겨야겠다

테이블 랜덤 액세스로 인한 인덱스 손익분기점의 한계를 극복하기위해 부분범위 처리 활용한다.

DBMS가 클라이언트에게 데이터를 전송할 때도 일정량 나누어 전송한다. 전체 결과집합 중 아직 전송하지 않은 분량이 많이 남아있어서 서버 프로세스는 클라이언트로 부터 추가 Fetch Call 받기전까진 기다린다.

즉 데이터가 1억건있어도 데이터를 일정량 전송하고 멈춘다. 그 후 서버 프로세스는 CPU를 OS에 반환하고 대기 큐에서 잠을 잔다. 다음 Fetch Call을 받으면 그 작업을 반복한다 -> 부분범위 처리

정렬 조건이 있으면

order by가 있으면 서버는 일단 전체 데이터를 읽어 order by순으로 정렬하고 클라한테 보낸다. 즉 전체 범위처리이다.

만약 order by할 컬럼이 선두에 있으면 부분범위 처리가 가능한다.

OLTP 환경에서 부분범위 처리

OLTP라고 항상 소량 데이터만 조회하는 것은 아니다.

버퍼캐시히트율이 좋으면 빠른 성능을 보이지만 아니라면 메우 느림

정렬 상태를 유지하는 인덱스를 이용하면, 정렬 작업을 생략하고 앞쪽 일부 데이터를 아주 빠르게 보여줄 수 있다.

• 웹사이트의 페이지네이션(pagination)
• 무한 스크롤(infinite scroll)
• 대용량 데이터 내보내기/가져오기

문제는 앞쪽 일부만 출력하고 멈출 수 있는가이다.

쿼리 툴은 대부분은 그렇게 구현되어있다.

그런데 클라이언트와 DB사이에 WAS, AP 서버등이 존재하는 n-Tier아키텍처에서는 클라이언트가 특정 DB 커넥션을 독점할 수 없다. 단위 작업을 마치면 DB 커넥션을 곧바로 커넥션 풀에 반환해야하므로 그 전에 SQL 조회 결과를 클라이언트에게 모두 전송하고 커서를 닫아야 하므로 결과 집합을 조금씩 나눠 전송하기 어렵다.

그래도 부분범위 처리는 n-Tier에서도 유효하다.

배치 I/O

읽는 블록마다 건건이 I/O Call을 발생하는 비효율을 줄이기 위해. 버퍼캐시에서 블록을 찾지 못하면 일반적으로 디스크 블록을 바로 읽는다. 이 기능이 작동하면 테이블 블록에 대한 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리한다. 12g부터 인덱스 ROWID로 테이블을 액세스하는 어떤 부분에서든 이 기능이 작동할 수 있다.

대신 인덱스를 이용해서 출력하는 데이터 정렬 순서가 매번 다를수도 있다.

* 파티션 Pruning : 파티션된 테이블에서 불필요한 파티션을 쿼리 실행 계획에서 제외하는 과정

SQL이 참조하는 컬럼을 인덱스가 모두 포함하는 경우가 아니라면, 인덱스를 스캔한 후 반드시 테이블을 엑세스한다. => TABLE ACCESS BY INDEX ROWID 

왜냐하면 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 ROWID를 찾아 테이블 레코드를 찾는다.

오라클은 테이블 블록이 수시로 버퍼캐시에서 밀려났다가 다시 캐싱되며, 그때마다 다른 공간에 캐싱되기 때문에 인덱스에서 포인터로 직접 연결될 수 없는 구조이므로, 메모리 주소 정보가 아닌 디스크 주소 정보(DBA, DATA BLOCK Address)를 이용해 해시 알고리즘으로 버퍼 블록을 찾는다.

* 복습

DBA는 디스크 상에서 블록을 찾기 위한 주소 정보. 매번 디스크에서 블록을 읽으면 I/O성능 줄어듬 (I/O call)
그래서 버퍼캐시를 먼저 본다. 그후 DBA를 해시 함수에 입력해서 해시 체인을 찾고 거기서 버퍼 헤더를 찾는다.
캐시를 적재할 때와 읽은 때 같은 함수 함수를 사용하므로 버퍼 헤더는 항상 같은 해시 체인에 연결된다.
하지만 실제 데이터가 담긴 버퍼블록은 매번 다른 위체 캐싱되는데, 그 메모리 주소값을 버퍼 헤더가 가지고 있다.
즉, 해싱 알고리즘으로 버퍼 헤더를 찾고 거기 얻은 포인터로 버퍼 블록을 찾는다.

인덱스로 테이블 블록을 액세스할때는 리프 블록에서 읽은 ROWID를 분해해서 DBA 정보를 얻는다.

테이블을 Full Scan할때는 익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 읽는다.

데이터가 캐싱되어있어도 테이블 레코드를 찾기위해 DBA 해싱과 래치 획득 반복하고 동시성 심하면 Lock도 발생하니 ROWID를 이용한 테이블 액세스는 생각보다 고비용이다.

디스크 DB가 사용하는 ROWID : 우편주소 -> 일일히 우체부가 돌아다녀야하므로 느림

메인 메모리 DB 포인터 : 전화번호

인덱스 클러스터링 팩터 (CF)

특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도

CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다.

어차피 메모리니까 블록 I/O 량은 차이가 없어야하지 않나? ㄴㄴ

오라클은 래치 획득과 해시 체인 스캔과정을 거쳐 어렵게 찾아간 테이블 블록에 대한 포인터를 바로 해제하지 않고 유지한다 -> 버퍼 Pinning
그 후 다음 데이터를 읽을때 '직전과 같은' 테이블 블록을 가리킨다 -> 해치 획득, 해시 체인 스캔 과정 생략하니까 더 빠르다.

인덱스 손익분기점

인덱스 ROWID를 이용한 테이블 엑세스는 고비용이니까 데이터가 일정량을 넘기는 순간 테이블 전체를 스캔하는 것보다 오히려 느려진다.

인덱스 손익분기점 : Index Range Scan에 의한 테이블 엑세스가 Table Full Scan보다 느려지는 지점을 '인덱스 손익분기점'이라고 부른다.

Table Full Scan은 성능이 일정하기 때문에 인덱스를 이용해 테이블을 엑세스할때 전체 건중 몇건을 추출하느냐에 따라 성능이 크게 달라진다. (당연히 많으면 느려진다.)

인덱스 CF가 따라 성능이 결정되기도 한다.

CF가 나쁘면 테이블을 여러번 반복엑세스하니까, 논리적 I/O 물리적 I/O보다 상승한다.

일반적으로 5~20% 수준에서 결정된다 (1만건이면 500 ~ 2000)

* 만건만 먹어도 시퀀셜 엑세스와 Multiblock I/O방식, Table Full Scan이 빠를수도있다.

온라인 프로그램 vs 배치 프로그램 튜닝

• 온라인 프로그램은 소량 데이터를 읽고 갱신
  • NL 조인
• 배치 프로그램은 대량 데이터를 읽고 갱신
  • Full Scan, 해시조인

초대용량 테이블을 Full Scan하면 상당히 부담스러움. 그래서 배치 프로그램에서 파티션 활용 전약, 병렬처리를 고려해야한다.

인덱스 컬럼 추가

테이블 엑세스 최소화를 위해 인덱스 컬럼을 추가하는 것

인덱스를 새로 만드는 것은 인덱스 관리 비용이 증가함은 물론 DML 부하에 따른 트랜잭션 성능 저하 발생

인덱스만 읽고 처리

테이블 랜덤 액세스가 아무리 많아도 필터 조건에 의해 버려지는 레코드가 거의 없다면 비효율은 없지만 테이블 엑세스가 많이 발생하므로 성능이 느리다.

절대 일량이 많아서 느린거니 어쩔수 없지만 성능을 개선해야한다면, 쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 액세스가 아예 발생하지 않게 하는 방법을 고려 -> Covered 쿼리 -> Covered 인덱스

SQL Server 에서는 Include 인덱스가 있다. 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장한다. 이래서 테이블 랜덤 액세스 횟수를 줄일 수 있다.

인덱스 구조 테이블

랜덤 엑세스가 아예 발생하지 않는 테이블도 있을까

IOT (Index-Organized Table), [MS-SQL Server에서는 클러스터형 인덱스]가 있다.

ROWID를 가지는게 아니라 인덱스 리프 블록이 곧 데이터 블록

index 만들때 뒤에 organization index;

힙 구조로 만들면 organization heap 을 붙힌다.

IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지한다.

인위적으로 클러스터링 팩터를 좋게 만드는 방법이다.

정렬되어있으니까 시퀀셜 방식으로 데이터를 엑세스하고 -> Between, 부등호 조건으로 넓은 범위를 읽을 때 유리

데이터 입력과 조회 패턴이 서로 다른 테이블에도 유용한다.

인덱스 클러스터 테이블

인덱스 클러스터 테이블은 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장한다. 한 블록에 저장할 수 없으면 새로운 블록을 할당하여 클러스터 체인을 만든다.

해시 클러스터 테이블

인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾는다.

create cluster c_dept# () hashkeys 4

인덱스 스캔기법은

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

이 있다.

Index Range Scan

WHERE 절에서 선두컬럼을 스캔할때 발생

CREATE INDEX idx_emp_salary ON employees(salary);

SELECT *
FROM employees
WHERE salary BETWEEN 3000 AND 5000;

-----------------------------------------------------------------------
| Id | Operation                    | Name          | Rows | Cost       |
-----------------------------------------------------------------------
|  0 | SELECT STATEMENT            |               |   50 |     4      |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |   50 |     4      |
|* 2 |   INDEX RANGE SCAN          | IDX_EMP_SALARY|   50 |     2      |
-----------------------------------------------------------------------

Index Full Scan

INDEX FULL SCAN은 인덱스 처음부터 끝까지 모두 스캔하는 방식

1. WHERE 절에 인덱스의 선두 컬럼이 조건절로 사용되지 않은 경우
2. 인덱스로 정렬된 결과가 필요한 경우 (ORDER BY)
3. 테이블 전체 건수 대비 조회하는 건수가 적은 경우

CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);

SELECT *
FROM employees
WHERE salary = 5000
ORDER BY dept_id;

-- ORDER BY를 위한 INDEX FULL SCAN
SELECT emp_id, name, salary
FROM employees
ORDER BY dept_id, salary;

• Full Table Scan보다 비용이 적게 들 때 선택
• 인덱스가 정렬되어 있어 ORDER BY를 별도로 수행x
• 인덱스 리프 블록만 읽으면 되므로, 테이블 전체를 읽는 것보다 효율적

Index Unique Scan

UNIQUE 인덱스나 PRIMARY KEY를 통해 단 하나의 행을 찾는 방식. Equal(=) 조건으로 값을 검색할 때 발생하며, 인덱스를 구성하는 모든 컬럼이 조건절에 있어야 함

CREATE UNIQUE INDEX idx_emp_dept_id ON employees(dept_id, emp_id);

SELECT *
FROM employees
WHERE dept_id = 10
AND emp_id = 100;

Index Skip Scan

CREATE INDEX idx_emp_gender_sal ON employees(gender, salary);

-- 선두 컬럼(gender => M, F)없이 salary만으로 조회
SELECT *
FROM employees
WHERE salary BETWEEN 3000 AND 5000;

• 옵티마이저는 선두 컬럼(gender)의 Distinct Value를 확인 (M, F)
• 각 Distinct Value에 대해 다음과 같은 조건으로 변환하여 실행

SELECT * FROM employees

WHERE

(gender = 'M' AND salary BETWEEN 3000 AND 5000)

OR

(gender = 'F' AND salary BETWEEN 3000 AND 5000)

인덱스 스킵 스캔이 좋은 경우

• 선두 칼럼의 Distinct Value 수가 적을 때 (보통 10~15개 이하)
• 후행 칼럼의 선택도(Selectivity)가 좋을 때
• 인덱스 크기가 테이블 크기에 비해 충분히 작을 때

• 복합 인덱스의 선두 컬럼이 조건절에 없어도 인덱스를 활용할 수 있습니다
• 선두 컬럼의 Distinct Value가 적어야 효율적입니다
• Oracle 9i부터 도입된 기능입니다

Index Fast Full Scan

INDEX FAST FULL SCAN은 인덱스 블록을 Multi Block I/O 방식으로 읽어 들이는 스캔 방식

• 인덱스 트리 구조를 무시
• 인덱스 세그먼트 전체를 스캔
• 정렬 순서를 보장하지 않음
• 필요한 컬럼이 모두 인덱스에 포함된 경우에만 사용 가능
• 병렬 처리가 가능

CREATE INDEX idx_emp_sal_dept ON employees(salary, dept_id);

-- 인덱스에 포함된 컬럼만 조회하는 경우
SELECT /*+ INDEX_FFS(e idx_emp_sal_dept) */
salary, dept_id
FROM employees e;

-- COUNT(*) 조회
SELECT COUNT(*)
FROM employees
WHERE salary > 5000;

Index Range Scan Descending

걍 order by desc하면 생김

MAX 값 구할때도 실행계획이 알아서 저걸로 만듬

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

인덱스 컬럼(선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.

Index Range Scan은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는, 즉 리프 블록 일부만 스캔한다.

Index Full Scan 은 스캔 시작점을 찾을 수 없고 멈출 수 도 없어 리프 블록 전체를 스캔하는 것이다.

LIKE

where 이름 like '이%' 는 Range Scan이 가능하지만 

where 이름 like '%이' 나 where 이름 like '%이%'는 시작범위 끝범위를 찾을 수 없기 때문에 Range Scan 할수없다.

OR

where (전화번호 like '010%' or 이름 like  '이%') 역시 시작 지점을 찾을 수 없기 때문에 Range 스캔할 수 없다.

이럴땐 OR Expansion을 사용한다.

Select *
FROM 고객
WHERE 전화번호 like '010%'
union all
Select *
FROM 고객
WHERE 이름 like '이%'

SELECT /*+ use_concat */ * FROM 고객
where (전화번호 like '010%' or 이름 like  '이%')  
이 SQL이 CONCATENATION가 실행계획에 나타나면

이러면 Index Range Scan을 활용할 수 있다.

IN

IN역시 OR 조건을 표현하는 다른 방식이므로 위와 같이 UNION ALL 방식으로 작성한다.

그래서 SQL 옵티마이저가 IN-List Iterator 방식을 사용하여 IN-List 개수만큼 Index Range Scan을 반복한다.

가장 중요한것은

선두컬럼 인덱스가 가동되면 안되는것이다. 선두컬럼 인덱스만 가공안되면 무조건 Index Range Scan 가능하다.

Index Range Scan탄다고 성능이 좋은건 아니다. (이건 3장에서)

인덱스는 정렬되어있으므로 소트 연산을 생략할 수도있다. (오름차순, 내림차순 모두 가능)

ORDER BY 절에서 컬럼 가공

조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 있다.

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

이러면 정렬 연산을 생랼할 수없다. 가공한 값 기준으로 정렬해달라고 했기때문에

SELECT-LIST에서 컬럼가공

SELECT에서 MAX나 MIN값을 할때 정렬연산을 따로 할 필요가 없다. 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽이 MIN이고 DESCENDING이면 가장 오른쪽이다. 근데 이 값을 가공하면 정렬 연산을 생략할 수 없다.

자동 형변환

오라클에서 숫자형과 문자형이 만나면 숫자형이 이긴다. 숫자형 컬럼 기준으로 문자형 컬럼을 변환한다는 것

LIKE 연산자면 문자열 비교 연산자이므로 문자형 기준으로 숫자형 컬럼이 변환한다.

TO_CHAR, TO_DATE, TO_NUMBER 같은 애들은 아무리 해도 성능에 별 영향이 없다. 생략한다고 해도 옵티마이저가 알아서 하기 때문에 연산횟수가 줄어드는것도 아니다.