Optimizer(최적화의 원리)

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

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

옵티마이저가 선택한 실행 방법의 적절성 여부는 질의의 수행 속도에 큰 영향을 끼친다.

최적의 실행 결정: 어떤 방법으로 처리하는 것이 최소 일량으로 동일한 일을 처리할 수 있을지 결정하는 것이다.

규칙기반 옵티마이저(RBO, Rule Based Optimizer)와 비용기반 옵티마이저(CBO, Cost Based Optimizer)로 구분한다.

---

규칙기반 옵티마이저(RBO, Rule Based Optimizer)

규칙(우선 순위)를 가지고 실행 계획을 생성하는 방식

• SQL문 실행하기 위해 이용 가능한 인덱스 유무(유일, 비유일, 단일, 복합 인덱스) & 종류
• SQL문에서 사용하는 연산자 종류(=,<,<>,LIKE, BETWEEN 등)
• SQL문에서 참조하는 객체의 종류(힙 테이블, 클러스터 테이블 등)

• 순위가 높은 규칙이 적을 일량으로 해당 작업을 수행하는 방법이라 판단한다. 
• 단순한 몇 개의 규칙만으로 현실의 모든 사항을 정확히 예측할 수는 없다. 

순위	액세스 기법
1	Single row by rowid
2	Single row by cluster join
3	Single row by hash cluster key with unique or primary key
4	Single row by unique or primary key
5	Cluster join
6	Hash cluster join
7	Indexed cluster key
8	Composite index
9	Single column index
10	Bounded range search on indexed columns
11	Unbounded range search on indexed columns
12	Sort merge join
13	Max or Min of indexed column
14	ORDER BY on indexed column
15	Full table scan

주요 규칙들

• 규칙 1: rowid를 동해서 하나의 행을 액서스 하는 방식
• 규칙 4: 유일한 인덱스(Unique Index)를 통해서 하나의 행을 액서스
• 규칙 8: 복합 인덱스에 동등('='연산자) 조건으로 검색하는 경우 
• 규칙 9: 단일 칼럼 인덱스에 '='조건으로 검색하는 경우 
• 규칙 10: 인덱스가 생성되어 있는 칼럼에 양쪽 범위를 한정하는 형태로 검색하는 방식(연산자: BETWEEN, LIKE등)
• 규칙 11: 인덱스가 생성되어 있는 칼럼에 한쪽 범위만 한정하는 형태로 검색하는 방식(연산자: >, >=.<.<=)
• 규칙 15: 전체 테이블을 액세스 하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출 

 

최적화 과정

SELECT ENAME FROM EMP WHERE JOB = 'SALESMAN' AND SAL BETWEEN 3000 AND 6000 INDEX-
-----------------------------EMP_JOB : JOB EMP_SAL : SAL PK_EMP : EMPNO (UNIQUE)

JOB 조건('=')은 단일 칼럼 인덱스 만족(규칙 9)

SAL 조건('BETWEEN')은 인덱스상의 양쪽 한정 검색을 만족(규칙10) 

 

규칙 기반 옵티마이저가 생성한 실행 계획

Execution  Plan  -----------------------------------------------------------  SELECT
STATEMENT Optimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'EMP' INDEX (RANGE SCAN)
OF 'EMP_JOB' (NON-UNIQUE)

---

비용기반 옵티마이저(CBO, Cost Based Optimizer)

SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식

• 비용: SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량
• 비용예측을 위해 객체 통계정보(테이블, 인덱스, 칼럼 등)와 시스템 통계정보 사용
• 통계 정보가 없으면 정확한 비용예측이 불가능하여 비효율적인 실행계획을 생성
• 통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 인해 동일 SQL문도 서로 다른 실행 계획이 될 수 있음
• 현재 대부분의 관계형 데이터베이스는 비용기반 옵티마이저만을 제공

비용기반 옵티마이저의  구성 요소 - 모듈로 구성되어 있다

• 질의 변환기: 사용자가 작성한 SQL문을 처리하기 용이한 형태로 변환하는 모듈
• 대안 계획 생성기: 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈

대안 계획: 연산의 적용 순서 변경, 연산방법 변경, 조인 순서 변경 등을 통해 이루어짐. 가능한 많이 생성해야 보다 나은 최적화를 수행할 수 있음. 하지만 너무 많으면 수행시간 증가

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

---

출처: 'SQL' 개발자 이론서'