[DATABASE] 옵티마이저와 실행계획이란?

옵티마이저

---

사용자가 질의한 SQL 문에 대해 최적의 실행 방법을 결정하는 역할을 수행합니다.

이러한 최적의 실행 방법을 실행계획(Execution Plan)이라고 합니다.

옵티마이저가 최적의 실행을 결정하는 방식에 따라 규칙기반(RBO, Rule Based Optimizer)와 비용기반(CBO, Cost Based Optimizer) 방식이 있습니다.

규칙기반 옵티마이저

규칙(우선 순위)를 가지고 실행계획을 생성합니다.

규실행계획을 생성할 때 참조하는 정보에는 인덱스 유무, 연산자, 객체 등의 종류가 있습니다.

ORACLE 의 규칙기반 옵티마이저 순위이며, 숫자가 낮을수록 우선순위가 높습니다.

1. Single row by rowid

2. Single row by clusterjoin

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 key

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. 유일 인덱스를 통해서 하나의 행을 액세스 하는 방식입니다. 이 방식은 인덱스 접근 후 인덱스에 존재하는 ROWID 를 추출하여 행을 액세스합니다.

8. 복합 인덱스에 동등 조건으로 검색하는 경우입니다.

9. 단일 컬럼인덱스에 = 조건으로 검색하는 경우입니다.

10. 인덱스가 생성되어 있는 컬럼에 양쪽 범위를 한정하는 형태로 검색하는 방식입니다. BETWEEN 이나 LIKE 가 있습니다. LIKE 는 '문자열%' 형태인 경우입니다.

11. 인덱스가 생성되어 있는 컬럼에 한쪽 범위만 한정하는 형태로 검색하는 방식입니다.

15. 테이블 전체를 액세스하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 출력합니다.

비용기반 옵티마이저

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

비용기반 옵티마이저는 비용을 예측하기 위해서 규칙기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 컬럼등의 다양한 객체 통계정보와 시스템 통계정보를 이용합니다.

통계정보가 없는 경우 비효율적인 실행계획을 생성할 수 있으므로, 정확한 통계정보를 유지하는 것이 중요합니다.

대안 계획 생성기

동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈입니다.

대안 계획 생성이 너무 많아지면 최적화를 수행하는 시간이 그만큼 오래 걸리기 때문에, 대부분의 상용 옵티마이저들은 대안 계획의 수를 제약하는 방법을 사용합니다.

이러한 현실적인 제약으로 인해 생성된 대안 계획들 중에서 최적의 대안 계획이 포함되지 않을 수도 있습니다.

비용 예측기

대안 계획 생성기에 의해서 생성된 대안 계획의 비용을 예측하는 모듈입니다.

실행계획

---

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

실행계획 형태는 조인순서, 조인기법, 액세스 기법, 최적화 정보, 연산 등이 있습니다.

조인 기법은 두 개의 테이블을 조인할 때 사용할 수 있는 방법으로 NL, Hash, Sort Merge 등이 있습니다.

액세스 기법은 하나의 테이블에 액세스 할때 사용할 수 있는 방법으로, Table Access, Index Access 가 있습니다.

최적화 정보는 옵티마이저가 실행계획의 각 단계마다 예상되는 비용 사항을 표시한 것입니다.

이 내용은 'SQL 전문가 가이드, 한국데이터베이스 진흥원' 에서 공부한 내용입니다.