옵티마이저, index, nested loop, sort merge, hash join

<SQL 최적화의 원리>

<옵티마이저(Optimizer)>
- SQL 실행 계획(Execution Plan)을 수립하고 SQL을 실행한다
- 옵티마이저는 SQL의 실행 계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어이다

<옵티마이저 특정>
- 옵티마이저는 데이터 딕셔너리(Data Diectionary)에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서
  예상되는 비용을 산정한다

<옵티마이저의 실행 방법>
- 개발자가 SQL을 실행하면 파싱(Parsing)을 실행해서 SQL의 문법 검사 및 구문 분석을 수행한다
- 구문 분석이 완료되면 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립한다
- 옵티마이저는 기본적으로 비용 기만 옵티마이저를 사용해서 실행 계획을 수립한다
  비용 기반 옵티마이저는 통계정보를 활용해서 최적의 실행 계획을 수립하는 것이다
- 실행 계획 수립이 완료되면 최종적으로 SQL을 실행하고 실행이 완료되면 데이터를 인출(Fetch)한다

<옵티마이저 엔진>
1. Query Transformer
  - SQL문을 효율적으로 시행하기 위해서 옵티마이저가 변환한다
  - SQL이 변한되어도 그 결과는 동일하다

2. Estimator
  - 통계정보를 사용해서 SQL 실행비용을 계산한다
  - 총 비용은 최적의 실행 계획을 수립하기 위해서이다

3. Plan Generator
  - SQL을 실행할 실행 계획을 수립한다


<인덱스(Index)>
- 인덱스는 데이터를 빠르게 검색할 수 있는 방법을 제공한다
- 인덱스는 인덱스 키로 정렬(Sort)되어 있기 때문에 원하는 데이터를 빠르게 조회한다
- 인덱스는 오름차순(Ascending) 및 내림차순(Desending) 탐색이 가능하다
- 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고 하나의 인덱스는 여러 개의 칼럼으로 구성될 수 있다
- 테이블을 생성할 때 기본키(Primary Key)는 자동으로 인덱스가 만들어지고 인덱스의 이름은 SYSXXX이다
- 인덱스의 구조는 Root Block, Branch Block, Leaf Block으로 구성되고 Root Block은 인덱스 트리에서 
  가장 상위에 있는 노드를 의미하며 Branch Block은 다음 단계의 주소를 가지고 있는 포인터(Pointer)로 되어 있다
- Leaf Block은 인덱스 키와 ROWID로 구성되고 인덱스 키는 정렬되어 저장되어 있다
- Leaf Block은 Double Linked List 형태로 되어 있어서 양방향 탐색이 가능하다
- Leaf Block에서 인덱스 키를 읽으면 ROWID를 사용해서 EMP 테이블의 행을 직접 읽을 수 있다


<인덱스 생성>
- 인덱스 생성은 "CREATE INDEX"문을 사용해서 생성이 가능하다
- 인덱스를 생성할 때는 한 개 이상의 칼럼을 사용해서 생성할 수 있다
ex)  CREATE INDEX IND EMP ON
               EMP(ENAME ASC, SAL DESC); 


<인덱스 스캔(Index Scan)>
1. 인덱스 유일 스캔(Index Unique Scan)
  - Index Unique Scan은 인덱스의 키 값이 중복되지 않은 경우, 해당 인덱스를 사용할 때 발생된다
 
2. 인덱스 범위 스캔(Index Range Scan)
  - Index Range Scan은 SELECT문에서 특정 범위를 조회하는 WHERE문을 사용할 경우 발생한다
  - LIKE, BETWEEN이 그 대표적인 예이다
    데이터 양이 적은 경우는 인덱스 자체를 실행하지 않고 TABLE FULL Scan이 될 수 있다
  - Index Range Scan은 인덱스의 Leaf Block의 특정 범위를 스캔한 것이다

3. 인덱스 전체 스캔(Index Full Scan)
  - Index Full Scan은 인덱스에서 검색되는 인덱스 키가 많은 경우에 Leaf Block의 처음부터 끝까지 전체를 읽어 들인다

<Table Full Scan시에 High Watermark의 의미>
- Table Full Scan은 테이블의 데이터를 모두 읽은 것을 의미한다
- 테이블을 읽을 때 High Watermark 이하까지만 Table Full Scan을 한다
- High Watermark는 테이블에 데어터가 저장된 블록에서 최상위 위치를 의미하고 데이터가 삭제되면
  High Watermark가 변경된다


<Nested Loop 조인>
- 하나의 테이블에서 데이터를 먼저 찾고 그 다음 테이블을 조회하는 방식으로 실행된다
- 먼저 조회되는 테이블을 외부 테이블(Outer table)이라고 하고 그 다음 조회되는 테이블을 내부 테이블(Inner Table)이라고 한다
- 외부 테이블(선행 테이블)의 크기가 작은 것을 먼저 찾는 것이 중요하다
  그래야 데어터가 스캔되는 범위를 줄일 수 있기 때문이다
- Nested Loop 조인은 RANDOM ACCESS가 발생하는데 조인은 RANDOM ACCESS가 많이 발생하면 성능 지연이 발생한다
  그러므로 Nested Loop 조인은 RANDOM ACCESS의 양을 줄여야 성능이 향상한다


<Sort Merge 조인>
- 두 개의 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩(Loading)하고 SORT를 수행한다
- 두 개의 테이블에 대해서 SORT가 완료되면 두 개의 테이블을 병합(Merge)한다
- Sort Merge 조인은 정렬(SORT)가 발생하기 때문에 데이터양이 많아지면 성능이 떨어지게 된다
- 정렬 데이터양이 너무 많으면 정렬은 임시 영역에서 수행된다
  임시 영역은 디스크에 있기 때문에 성능이 급격히 떨어진다

<Hash 조인>
- 두 개의 테이블 중에서 작은 테이블을 HASH 메모리에 로딩하고 두 개의 테이블의 조인 키를 사용해서 해시 테이블을 생성한다
- Hash  조인은 해시 함수를 사용해서 주소를 계산하고 해당 주소를 사용해서 테이블을 조인하기 때문에 CPU 연산을 많이 한다
- 특히 Hash 조인 시에는 선행 테이블이 충분히 메모리에 로딩되는 크기여야 한다