SQLP - 옵티마이저와 실행계획

@markdown

# 옵티마이저(Optimizer)

____

- 옵티마이저는 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행

- 최적의 실행 방법을 `실행계획`이라함

- 다양한 실행 방법들 중에서 최적의 실행 방법을 결정하는 역할

- `Rule Based` / `Cost Based` `Optimizer`가 있다.(RBO, CBO)


## 규칙기반 옵티마이저(RBO)

____

- 규칙(우선순위)을 가지고 실행계획을 생성

- 판단기준 : 인덱스 유무, 인덱스 종류, SQL 연산자(`=`, `<`, `<>`, `LIKE`, `BETWEEN` 등), 참조 객체(힙 테이블, 클러스터 테이블)

- 우선순위가 높은 규칙이 적은 일량으로 해당 작업을 수행하는 방법이라고 판단

- `Index Scan` >>> `Full Table Scan`(인덱스가 존재하면, 항상 인덱스를 사용하는 실행계획을 생성)

### 규칙기반 옵티마이저의 조인순서

- 조인 칼럼 인덱스의 존재 유무가 판단 기준이 됨

- 조인 칼럼에 대한 인덱스가 양쪽 테이블에 모두 존재한다면, 우선 순위 규칙을 따라 선행 테이블(Driving Table) 결정

- 한쪽에만 인덱스 있다면, 있는 쪽이 선행 테이블(NL Join)

- 둘다 없다면, FROM절 위 테이블을 선행테이블로 결정(Sort Merge Join)

### 규칙 우선순위


<pre><code class="sql" style="font-size:14px">INDEX 정보

-----

EMP_JOB : JOB(규칙9. 단일 칼럼 인덱스에 '=' 조건으로 검색하는 경우) 

EMP_SAL : SAL(규칙10. 양쪽 범위를 한정하여 검색하는 경우) 

PK_EMP : EMPNO (UNIQUE)

INDEX 생성

-----

CREATE INDEX JOB ON EMP;

CREATE INDEX SAL ON EMP;

INDEX 예제

-----

SELECT  ename

FROM emp

WHERE job='salesman'

AND sal between 3000 and 6000

</code></pre>

- 실행순서 : 우선순위가 높은 EMP_JOB 인덱스를 이용해 조건을 만족하는 행에 대해 EMP 테이블을 접근할 것

## 비용기반 옵티마이저(CBO)

____

- 규칙기반 옵티마이저로 모든 사항을 예측할 수 없기 때문에, 이를 보완할 수 있는 방법이다.

- 비용기반 옵티마이저는 SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식

- 비용 : SQL 처리 소요시간, 자원 사용량

- CBO는 정확한 비용 예측을 위해 테이블, 인덱스, 칼럼 등의 통계정보와 시스템 통계정보를 이용한다.

- 통계정보 유지가 중요


- 비용기반 옵티마이저는 질의 변환기, 대안 계획 생성기, 비용 예측기 등의 모듈로 구성

- 질의변환기 : 사용자가 작성 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈

- 대안생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈

- 비용예측기 : 대안 계획생성기에 의해 생성된 대안 계획의 비용을 예측하는 모듈

- 대안 계획의 생성이 너무 많으면 최적화 수행시간이 오래 걸리는 단점이 있다.

# 실행계획

____

- SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미

- SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 작업

- 구성요소 - 조인 순서, 조인 기법, 액세스 기법, 최적화 정보, 연산 등

## 실행계획 구성요소

____


- 조인 순서 : 조인작업을 수행할 때 참조하는 테이블의 순서(FROM A, B에서 A테이블 읽고 B테이블 읽는다면 조인순서는 A->B)

- 조인 기법 : 두 개의 테이블을 조인할 때 사용할 수 있는 방법(NL Join, Hash Join, Sort Join, Merge Join)

- 액세스 기법 : 하나의 테이블을 액세스할 때 사용할 수 있는 방법(Index Scan, Full Table Scan)

- 최적화 정보 : 옵티마이저가 각 실행계획의 단계마다 예상되는 비용을 표시한 것(Cost, Card, Bytes 등)

- 연산 : 여러 조작을 통해 원하는 결과를 얻는 일련의 작업(조인 기법, 액세스 기법, 필터, 정렬, 집계, 뷰 등)

# SQL 처리 흐름도

____

- SQL의 내부적인 처리 절차를 시각적으로 표현한 도표

- 실행계획의 시각화

- 어떤 테이블을 먼저 읽었는지, 어떤 인덱스 스캔을 수행했는지, 조인 기법 등을 표현할 수 있다.


- 테이블1은 Full Scan, 테이블2는 I01_TAB2 인덱스를 통한 Index Scan

- 테이블1에 대한 액세스는 스캔 방식이고 조인시도 및 I01_TAB2 인덱스를 통한 테이블2 액세스는 랜덤 방식이다.

>SQL 전문가 가이드 : 제3장 SQL 최적화 기본 원리 - 제1절 옵티마이저와 실행계획(p.446 ~ p.453)

> 사진 출처 : 구루비넷