옵티마이저

옵티마이저 (Optimizer)

• 정의
  - 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할 수행 (실행방법 : Execution plan)
  - 옵티마이저가 선택한 실행 방법의 적절성 여부는 쿼리 수행 속도에 가장 큰 영향을 미침
  - 현재는 비용기반 위주로 신규 기능에 지원
  
  
• 방법
  - 규칙기반 옵티마이저 (RBO, Rule Based Optimizer)
  - 비용기반 옵티마이저 (CBO, Cost Based Optimizer)
  
  

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

• 특징
  - 규칙(우선순위)를 가지고 실행계획을 생성
  - 되도록이면 인덱스를 사용하는 실행계획을 생성
  - 참조하는 정보에는 인덱스 유무, 인덱스 종류, 연산자의 종류, 참조하는 객체의 종류 등
  - 인덱스를 이용한 엑세스 방식이 전체 테이블 엑세스 방식보다 우선순위가 높음
  - JOIN 컬럼에 대한 인덱스가 양쪽에 존재 : 우선순위가 높은 테이블이 선행 테이블(Driving Table)
  - 한 쪽에만 인덱스가 존재 : 인덱스가 없는 테이블이 Driving Table
  - 모두 인덱스가 존재X : From 절 뒤의 테이블이 Driving Table
  - 우선순위가 동일 : From 절에 나열된 테이블의 역순으로 Driving Table 선택
  
  
• 규칙
  1. Single Row By Rowid : ROWID에 의한 단일 로우
     - ROWID(행에 대한 고유주소)를 통해서 하나의 행을 엑세스하는 방식
     - ROWID는 행이 포함된 데이터 파일, 블록 등의 정보를 가지고 있어 바로 원하는 행을 엑세스 가능
     - 하나의 행을 엑세스하는 가장 빠른 방법
  
  4. Single Row By Unique or Primary Key : 유일하거나 PK에 의한 단일 로우
     - 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
     - 인덱스가 생성되어 있는 컬럼에 양쪽 범위를 한정하는 형태로 검색하는 방식
     - 연산자에는 BETWEEN, LIKE 등이 있다.
  
  11. Unbounded Range Search on Indexed Columns
     - 인덱스가 생성되어 있는 컬럼에 한쪽 범위만 한정하는 형태로 검색하는 방식
     - 연산자에는 >, >=, <, <= 등이 있다.
  
  15. Full Table Scan : 전체 테이블을 엑세스하면서 조건절에 주어진 조건을 만족하는 행만 추출
  
  

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

• 특징
  - 비용(시간, 자원)이 가장 적은 실행계획을 생성
  - RBO의 단점을 극복하기 위해 출현
  - 인덱스를 사용하는 비용 > 전체 테이블 스캔 비용, 테이블 스캔을 수행하는 방법으로 실행 계획 생성
  - 객체 및 시스템 통계정보를 이용 (통계정보가 없을 때, 비효율적인 실행계획을 생성할 수 있음)
  - 통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 동일한 SQL문도 서로 다른 실행계획이 생성될 수 있음
  

실행계획 (Execution Plan)

• 정의
  - SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 표현
  - 다양한 실행계획마다 성능은 서로 다를 수 있음
  - 동일 SQL문에 대해 실행계획이 달라도 결과는 같음 
  - 위에서 밑으로 안에서 밖으로 읽음
  
  
• 구성 요소
  1. 조인 순서 : JOIN을 할 때, 참조하는 테이블의 순서
  2. 조인 기법
  3. 액세스 기법 : 인덱스 스캔, 풀 테이블 스캔 등
  4. 최적화 정보 (SQL 처리 예상 비용 등)
  5. 연산
  
  
• 특징
  - EXISTS 절은 주로 SEMI JOIN으로 나타남

SQL 처리 흐름도

- Outer Table / Driving Table (선행 테이블)

- Inner Table / Lookup Table (후행 테이블)

- 조인 순서 (TAB1 -> TAB2)

- 랜덤 스캔 방식 (대량의 데이터를 랜덤 방식으로 엑세스 하면 많은 I/O가 발생, 성능이 떨어짐)
- TAB1 (풀 테이블 스캔)
- TAB2 (IO1_TAB2(인덱스)를 통해 인덱스 스캔)

• 특징
  - 실행계획을 시각화 (성능적인 부분도 나타낼 수 있음)
  - SQL의 실행 시간을 알 수 없음

• 코드
  SELECT *
  FROM TAB1 A, TAB2  B
  WHERE A.KEY = B.KEY
  AND A.COL1 =: condition1
  AND B.COL2 =: condition2;

1. TAB1에 접근
2. A.COL1 =: condition1의 결과를 찾음
   - 풀 테이블 스캔
   - 조인 시도 건수는 A.COL1 =: condition1을 만족하는 결과 수
3. I01_TAB2 인덱스를 통해 A.KEY = B.KEY를 만족하는 결과를 찾음
4. 위의 결과를 만족하는 결과를 통해 TAB2에 접근
   - TAB2에 접근하여 B.COL1 =: condition2을 만족하는 결과를 찾음
5. 사용자에게 보여줌