[SQLD : Ⅴ. SQL 최적화 기본 원리] 1. 옵티마이저와 실행계획

* 옵티마이저

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

 - 관계형 데이터베이스는 궁극적으로 SQL문을 통해서만 데이터를 처리할 수 있음

 - 다양한 실행 방법들 중에서 최적의 실행 방법을 결정하는 것이 바로 옵티마이저의 역할

 - 대부분의 관계형 데이터베이스는 비용기반 옵티마이저만을 제공

 - 규칙기반 옵티마이저의 규칙은 보편 타당성에 근거한 것

 

 - 규칙기반 옵티마이저

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

  이용 가능한 인덱스 유무와 (유일, 비유일, 단일, 복합 인덱스)종류,

  SQL문에서 사용하는 연산자(=, <, <>, LIKE, BETWEEN 등)의 종류

  그리고 SQL문에서 참조하는 객체(힙 테이블, 클러스터 테이블 등)의 종류 등

  규칙 1. Single row by rowid : ROWID를 통해서 테이블에서 하나의 행을 액세스하는 방식

                                        ROWID는 행이 포함된 데이터 파일, 블록 등의 정보를 가지고 있기 때문에

                                        다른 정보를 참조하지 않고도 바로 원하는 행을 액세스

  규칙 4. Single row by unique or primary key : 유일 인덱스(Unique Index)를 통해서 하나의 행을 액세스하는 방식

                                                              인덱스 먼저 액세스, 인덱스에 존재하는 ROWID를 추출&행을 액세스

  규칙 8. Composite index : 복합 인덱스에 동등(‘=’ 연산자) 조건으로 검색하는 경우

                                    A+B 칼럼으로 복합 인덱스 -> 조건절에서 WHERE A=10 AND B=1 형태로 검색하는 방식

  규칙 9. Single column index : 단일 칼럼 인덱스에 ‘=’ 조건으로 검색하는 경우

                                         A 칼럼에 단일 칼럼 인덱스가 생성되어 있고, 조건절에서 A=10 형태로 검색하는 방식

  규칙 10. Bounded range search on indexed columns : 인덱스가 생성된 칼럼 양쪽 범위를 한정하는 형태로 검색

                                                                        A BETWEEN ‘10’ AND ‘20’ 또는 A LIKE '1%' 형태로 검색

  규칙 11. Unbounded range search on indexed columns : 인덱스 생성된 칼럼에 한쪽 범위만 한정하는 형태로 검색

                                                                             >, >=, <, <=

  규칙 15. Full table scan : 전체 테이블을 액세스하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출

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

 

 - 비용기반 옵티마이저

 

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

  대안 계획의 정확한 비용 예측을 위해 연산의 중간 집합의 크기, 결과 집합의 크기, 분포도 등의 예측이 정확해야 함

  대안 계획을 구성하는 각 연산에 대한 비용 계산식이 정확해야 함

  통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 인해 동일 SQL문도 서로 다른 실행계획이 생성

 

* 실행 계획

 - 옵티마이저는 다양한 처리 방법들 중에서 가장 효율적인 방법을 찾아줌

 - 옵티마이저는 최적의 실행계획을 생성

 - 실행계획을 구성하는 요소에는 조인 순서, 조인 기법, 액세스 기법, 최적화 정보, 연산 등이 있음 

 

* SQL 처리 흐름도

SELECT … 
FROM TAB1 A, TAB2 B 
WHERE A.KEY = B.KEY AND A.COL1 = :condition1 AND B.COL2 = :condition2

액세스 건수는 SQL 처리를 위해 TAB1을 액세스한 건수

TAB1의 A.COL1 칼럼에 이용 가능한 인덱스가 존재하지 않아 전체 테이블 스캔을 수행했음을 의미

액세스 건수는 TAB1 테이블의 총 건수와 동일

테이블에서 읽은 해당 건에 대해 A.COL1 = :condition1 조건을 만족한 건만이 TAB2와 조인을 시도

TAB1을 액세스한 후 A.COL1 = :condition1 조건을 만족하지 않는다면 더 이상 조인 작업을 진행할 필요가 없다

조인 시도 건수는 TAB1에 주어진 조건인 A.COL1 = :condition1을 만족한 건수

테이블 액세스 건수는 B.KEY 칼럼만으로 구성된 인덱스 조건을 만족한 건만이 TAB2 테이블을 액세스

조인 시도한 건들 중에서 B.KEY = A.KEY 조건까지 만족한 건과 같다

TAB2 테이블을 액세스해서 B.COL2 = :condition2 조건까지 만족해야 비로서 사용자에게 보여질 수 있다