제 5-1절 옵티마이저와 실행계획

1. 옵티마이저

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

실행계획 : 최적의 실행방법, 다른 언어와 달리 요구사항만 기술할 뻔 처리과정에 대한 기술은 없어서 다양한 실행방법 존재

옵티마이저가 선택한 실행방법의 적절성 여부는 질의의 수행 속도에 가장 큰 영향 미침, 최소 일량으로 동일한 일 처리

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

 

가. 규칙기반 옵티마이저

우선순위를 가지고 실행계획을 생성

인덱스의 유무(유일, 비유일, 단일, 복합인덱스)종류,

SQL에서 사용하는 연산자의 종류,

SQL에서 사용하는 객체(힙테이블, 클러스터 테이블)의 종류

인덱스가 존재한다면 전체 테이블 인덱스 방식보다 인덱스 사용 실행계획 생성

조인시에는 인덱스의 유무가 중요한 판단 기준

양쪽 조인 칼럼에 모두 인덱스가 없는 경우는 Sort Merge Join

둘 중 하나라고 조인 칼럼에 인덱스가 존재하는 경우는 NL Join 사용

 

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 : 유일 인덱스를 통해서 하나의 행 엑세스하는 방식, 인덱스 이후 rowid 추출
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 : 양쪽범위를 한정하는 형태로 검색하는 방식 Between, like 등
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 : 전체 테이블을 엑세스함

 

나. 비용기반 옵티마이저

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

비용이란 SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량 의미

비용예측을 위해 규칙기반이 사용하지 않는 테이블, 인덱스, 칼럼 등 다양한 객체 통계정보와 시스템 통계정보등을 이용

정확한 통계정보를 유지하는 것이 최적화에 중요한 요소

• 질의변환기 : 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈
• 대안계획생성기 : 동일한 결과를 생성하는 다양한 대안계획을 생성하는 모듈, 연산의 적용순서변경, 연산방법변경, 조인순서변경 등 통해서 생성

 최적화를 위해 대안 계획수 제약가능하나 현실적인 제약으로 인해 포함되지 않을 수도 있음

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

   연산의 중간 집합의 크기, 결과 집합의 크기, 분포의 정확한 예측 필요, 정확한 통계자료 필요하고 연산에 대한 비용 계산식 정확해야함

 

통계정보, DBMS 버전, DBMS 설정 정보등의 차이로 서로 다른 실행계획이 생성될 수 있음

다양한 한계들로 인해 실행계획의 예측 및 제어가 어렵다는 단점

 

2. 실행계획

SQL에서 요구한 사항을 처리하기 위한 절차와 방법, 어떤 순서로 어떻게 실행할 지 결정하는 작업

구성요소

1. 조인순서 : FROM절에 있는 테이블 수의 N! 만큼 조인순서 가능
2. 조인기법 : NL JOIN, HASH JOIN, SORT MERGE JOIN
3. 엑세스기법 : 하나의 테이블을 엑세스할때 사용할 수 있는 방법, 인덱스 스캔, 전체테이블스캔 가능
4. 최적화 정보 : 각 단계마다 예상되는 비용 사항 표시
• COST : 상대적인 비용
• CARD : CARDNALITY 주어진 조건을 만족한 결과 집합, 조인조건을 만족한 결과집합의 건수
• BYTES : 메모리양, 예상치
5. 연산 : 여러가지 조작을 통해서 원하는 결과를 얻어내는 일련의 작업

  조인기법, 엑세스기법, 필터, 정렬, 집계, 뷰 등 다양한 종류 존재

 

 

3. SQL 처리 흐름도

조인순서(어떤 테이블을 먼저 읽었는지), 엑세스기법(인덱스, 전체 테이블 스캔 중 무엇을 수행했는지) 등으로 표현가능

엑세스건수, 조인시도건수, 테이블엑세스건수, 성공선수 등을 통해 작업건수 또는 처리결과 건수들을 표시할 수 있음

NL JOIN 흐름도