⚡ 생각대로 살지 않으면 사는대로 생각한다.

⚡ 나는 어차피 잘 될 놈이다. 이미 잘 되고 있고, 계속해서 잘 되고 있다.

---

인덱스

• 정렬 되있음.
• 위치정보(rowId)를 가짐
• 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 엑세스하기 위해 사용하는 오브젝트
  • 대부분 책 뒤에 있는 색인과 같은 역할

---

인덱스 탐색 과정

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

---

인덱스 튜닝

• 인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용

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

• 인덱스 스캔 효율화 튜닝
  • 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것
• 랜덤 액세스 최소화 튜닝
  • 테이블 액세스 횟수를 줄임
  • 인덱스 스캔 후 테이블 레코드를 액세스할 때 랜덤 I/O방식을 사용한 튜닝

인덱스 스캔 효율화 튜닝과 랜덤 액세스 최소화 튜닝 둘 다 중요하지만, 더 중요한 하나를 고른다면 랜덤 액세스 최소화 튜닝이다. 성능에 미치는 영향이 더 크기 때문.

---

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

• 데이터 베이스 성능이 느린 이유 : 디스크 I/O
  • 읽어야 할 데이터량이 많고, 그 과정에 디스크 I/O가 많이 발생할 때 느림.
  • 인덱스를 많이 사용하는 OLTP 시스템이라면 디스크 I/O 중에서도 랜덤 I/O가 특히 중요!

성능을 위해 DBMS가 제공하는 많은 기능은 느린 랜덤 I/O를 극복하기 위해 개발되었다!

---

데이터베이스에서 인덱스 없이 데이터를 검색하려면, 테이블을 처음부터 끝까지 모두 읽어야 한다. 반면, 인덱스를 이용하면 일부만 읽고 멈출 수 있다. 즉, 범위 스캔(Range Scan)이 가능.
범위 스캔이 가능한 이유는 인덱스가 정렬돼 있기 때문.

DBMS는 일반적으로 B*Tree 인덱스를 사용한다.

아래는 고객 테이블에 고객명 컬럼 기준으로 만든 B*Tree 인덱스구조

✅예시)

• 표시는 안 되었지만, 루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 가짐.
• 키값은 하위 블록에 저장된 키값의 범위를 나타냄.
• 루트와 브랙치 블록에는 키값을 갖지 않는 특별한 레코드 존재
  • 이를 LMC(Leftmost Child의 줄임말)라고 함
  • LMC는 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킨다.
• 리프 블록에 저장된 각 레코드는 키값 순으로 정렬돼 있을 뿐만 아니라 ROWID를 갖는다.
• 인덱스 키값이 같으면 ROWID 순으로 정렬된다.
• 인덱스를 스캔하는 이유는 검색 조건을 만족하는 소량의 데이터를 빨리 찾고 거기서 ROWID를 얻기 위해서다.

✅용어정리

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

---

인덱스 수직적 탐색

• 인덱스 스캔 시작지점을 찾는 과정
  • 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정
• 인덱스 수직적 탐색은 루트(Root) 블록에서부터 시작
• 루트에서 시작해 리프(Leaf) 블록까지 수직적 탐색이 가능한 이유.
  • 루트를 포함해 브랜치(Branch) 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 가짐.

❗ 수직적 탐색은 '조건을 만족하는 레코드'를 찾는 과정이 아니라, 조건을 만족하는 첫 번째 레코드를 찾는 과정!
인덱스를 수직적으로 탐색할 때, 루트를 포함한 브랜치 블록은 등산 푯말과 같은 역할을 한다.

*등산 푯말 : 목적지를 안내하는 것처럼, 목표레코드를 안내하는 역할과 같다.

인덱스 수평적 탐색

• 수직적 탐색을 통해 스캔 시작점을 찾았다면, 인덱스 리프 블록을 수평적으로 스캔한다. 이는 인덱스에서 본격적으로 데이터를 찾는 과정.
• 인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 가짐.
  • 양방향 연결 리스트(Double linken list) 구조.
    • 수평적 탐색이 가능한 이유

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

  • 조건절을 만족하는 데이터를 모두 찾기 위해서
  • ROWID를 얻기 위해서

B*Tree에서의 B는 'Balanced'를 의미

delete 작업으로 인해서 인덱스가 뷸균형(Unbalanced) 상태에 놓일 수 있다고 설명한 자료들을 볼 수 있다.
하지만, B_Tree 인덱스에서 이런 현상은 절대 발생하지 않는다. B_Tree의 B는 Balanced의 약자.
Balanced는 어떤 값으로 탐색하더라도 인덱스 루트에서 리프 블록에 도달하기까지 읽는 블록 수가 같음을 의미한다. 즉, 루트로부터 모든 리프 블록까지의 높이(height)는 항상 같다!

---

인덱스 기본 사용법

• 인덱스는 선두 컬럼(선두 컬럼 또는 인덱스 컬럼 : 조건절에 적용되는 첫 번째 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용 가능.
  • 인덱스를 정상적으로 사용할 수 있다.는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미한다. 즉, 리프 블록 일부만 스캔하는 Index Range Scan을 의미
  • 인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고 멈출 수도 없어 리프 블록 전체를 스캔해야만함!
    • 이때는 일부가 아닌 전체를 스캔하는 Index Full Scan 방식으로 작동.

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

• 인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문
• Index Range Scan에서 Range는 범위를 의미
  • Index Range Scan은 인덱스에서 일정 범위를 스캔한다는 의미
    • 일정 범위를 스캔하려면 시작 지점과 끝 지점이 있어야 함.

✅정리

• 인덱스를 정상적으로 사용한다는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미함!

조건절들은 인덱스를 정상적으로 사용할 수 없는 경우.

// 가공 된 값을 기준
where substr(생년월일, 5, 2) = '05
where nvl(주문수량, 0) < 100

// 시작점 찾기 어려움
where 업체명 like '%대한%'
where (전화번호 = :tel_no or 고객명 = :cust_nm)

// or과 같음
where 전화번호 in ( :tel_no1, :tel_no2 )

시작점을 찾을 수 있도록 해야한다!!!

인덱스를 정상적으로 사용하는데 있어 중요한 선행조건

• 인덱스 선두 컬럼이 조건절에 있어야 함! 가공하지 않은 상태로..!!
• 반대로 말하면, 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능!!
• 인덱스를 Range Scan 한다고 해서 항상 성능이 좋은 건 아님!

인덱스를 이용하면 소트 연산을 생략할 수 있다.

• 인덱스를 Range Scan 할 수 있는 이유는 데이터가 정렬돼 있기 때문이다. 찾고자 하는 데이터가 정렬된 상태로 서로 모여있기 때문에 전체가 아닌 일정 부분만 읽다가 멈출 수 있음!

우리가 인덱스를 사용하는 이유

• 인덱스는 정렬돼 있음.
  • 인덱스가 정렬돼 있기 때문에 Range Scan이 가능!!

• 옵티마이저는 인덱스의 정렬된 속성을 활용해서 SQL에 ORDER BY가 있어도 정렬 연산을 따로 수행하지 않음.
• 오름차순 혹은 내림차순 정렬시 인덱스를 사용 가능.
  • 오름차순 정렬일 때는 조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적 탐색 후 우측으로 수평적 탐색.
  • 내림차순 정렬일 때는 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 수평적 탐색.
• 최소 또는 최대값을 빠르게 찾을 수도 있다.
  • 인덱스 리프 블록의 왼쪽, 또는 오른쪽에서 레코드 하나(FIRST ROW)만 읽고 멈춘다.

자동 형변환

• 각 조건절에서 양쪽 값의 데이터 타입이 서로 다르면 값을 비교할 수 없다. 그럴 때 타입체크를 엄격히 함으로써 컴파일 시점에 에러를 내는 DBMS가 있는가 하면, 자동으로 형변환 처리해주는 DBMS도 있는데, 오라클은 후자에 속함.

• 오라클에서 숫자형과 문자형이 만나면 숫자형이 이김.

  • 숫자형 컬럼 기준으로 문자형 컬럼을 변환한다는 의미
  • 그런데 이때 연산자가 LIKE일 때는 다름.
    • LIKE 자체가 문자열 비교 연산자이므로 이때는 문자형 기준으로 숫자형 컬럼이 변환됨.

• 날짜형과 문자형이 만나면 날짜형이 이김.

자동 형변환 주의

• 자동 형변환이 작동하면 편리하다고 생각할 수 있지만, 이 기능으로 성능과 애플리케이션 품질에 종종 문제가 생김.

자동 형변환 기능에 의존하지말고, 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변환해 주어야 한다!

SQL 성능은 "블록 I/O를 줄일 수 있느냐 없느냐"에 의해 결정된다는 것을 기억하자!

---

인덱스 기능 종류

Index Range Scan

• Index Range Scan은 B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식.
• 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 필요한 범위(Range)만 스캔.
• 인덱스를 Range Scan 하려면 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 한다.
• 반대로 선두 컬럼을 가공하지 않은 상태로 조건절에 사용하면 Index Range Scan은 무조건 가능하다!

Index Full Scan

• 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
• 최적의 인덱스가 없을 때 차선책으로 선택됨.

효용성

• 선두 컬럼이 조건절에 없으면 옵티마이저는 먼저 Table Full Scan을 고려함.
  • 만약, 조회하려는 테이블이 대용량 테이블로 Table Full Scan에 따른 부담이 크다면 옵티마이저는 인덱스 활용을 다시 고려.
• 인덱스를 Range Scan 할 수 없을 때, 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 아주 일부만 테이블을 액세스하는 상황이라면, 면적이 큰 테이블보다 인덱스를 스캔하는 쪽이 유리하고, 이 때 옵티마이저는 Index Full Scan 방식을 선택함!
• 찾고자 하는 데이터의 조건이 전체 중 극히 일부라면 Table Full Scan보다는 Index Full Scan을 통한 필터링이 효과적이다.
  • 해당 방식은 적절한 인덱스가 없어 Index Range Scan의 차선책으로 선택한 것!

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

• 인덱스를 Full Scan하면 Range Scan과 마찬가지로 결과집합이 인덱스 컬럼 순으로 정렬됨.
  • 이는 Sort Order By 연산을 생략할 목적으로 사용할 수도 있는데, 해당 상황은 차선책이 아니라, 전략적으로 선택한 경우에 해당.

Index Fast Full Scan

• Index Full Scan보다 빠름.
  • 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O방식으로 스캔하기 때문.
  • 관련 힌트 index_ffs, no_index_ffs
• Multiblock I/O방식을 사용하므로 디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과를 발휘
  • 속도는 빠르지만, 인덱스 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키 순서대로 정렬되지 않는다.
• 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때만 사용가능!!!
• Index Range Scan 또는 Index Full Scan과 달리, 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능하다.
  • 병렬 쿼리시 Difect Path I/O 방식을 사용하므로 I/O 속도가 더 빠르다.

Index Full Scan VS Indes Fast Full Scan

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

Index Unique Scan

• 수직적 탐색만으로 데이터를 찾는 스캐 방식으로서, Unique 인덱스를 =조건으로 탐색하는 경우에 작동.
  • 중복이 없기 때문에 가능.
• Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성을 관리해주는데, 인덱스 키 컬럼을 모두 =조건으로 검색할 대는 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없음!
• Unique 인덱스라고 해도 범위검색 조건(between, 부등호, like)으로 검색할 때는 Index Range Scan으로 처리되는데, 이 떄는 수직적 탐색만으로 조건에 해당하는 레코드를 모두 찾을 수 없기 때문이다.
• Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan이 나타남.

Index Skip Scan

• 오라클은 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔방식을 선보인 것이 Index Skip Scan이다.
• 인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택한다.
• Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면, Index Full Scan을 사용하기도 한다.
• 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용함!
  • 선두 컬럼이 없을 때만 Index Skip Scan이 작동하는 것은 아니다.
  • 선두 컬럼에 대한 조건절은 있고, 중간 컬럼에 대한 조건절이 없는 경우에도 Skip Scan을 사용할 수 있다.
  • 선두 컬럼이 부등호, BETWEEN, LIKE같은 범위검색 조건일 때도 Index Skip Scan을 사용할 수 있다.
• 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에서 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 "골라서 액세스"하는 스캔 방식

Index Range Scan이 불가능하거나 효율적이지 못한 상황에서 Index Skip Scan이 종종 빛을 발한다..

인덱스는 기본적으로 최적의 Index Range Scan을 목표로 설계해야 한다!!

Index Range Scan Descending

• Index Range Scan과 기본적으로 동일한 스캔 방식
• 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.
• 힌트 - index-desc