SQLD 5-1 ] 옵티마이저와 실행계획
❏ 옵티마이저
- 옵티마이저는 사용자가 질의한 SQL 문에 대해 최적의 실행 방법을 결정하는 역할을 수행한다
여러개의 방법 중.. 음.. 이게 제일 좋겠군! 하고 결정하는애.
그런데, SQL 문을 실행 해보고 이게 빠르군. 저게 빠르군 하는게 아니라서.. 실행 하기 전에 결정해야 하는 어려움이 있다.
결정하는 방식에는 규칙기반 옵티마이저 / 비용기반 옵티마이저 로 구분할 수 있다. 현재 대부분은 비용기반 옵티마이저만 제공한다고 한다.
❏ 규칙기반 옵티마이저
- 규칙기반 옵티마이저는 규칙(우선 순위) 를 가지고 실행계획을 생성한다.
| 순위 | 엑세스 기법 | 설명 |
|---|---|---|
| 1 | Single Row By Rowid | ROWID에 의한 단일 로우 |
| 2 | Single Row By Cluster Join | 클러스터 조인에 의한 단일 로우 |
| 3 | Single Row By Hash Cluster Key whit Unique or Primary Key | 유일하거나 PK를 가진 해시 클러스터키에 의한 단일 로우 |
| 4 | Single Row By Unique or Primary Key | 유일하거나 PK에 의한 단일 로우 |
| 5 | Clustered Join | 클러스터 조인 |
| 6 | Hash Cluster Key | 해시 클러스터 키 |
| 7 | Indexed Cluster Key | 인덱스 클러스터 키 |
| 8 | Composite Index | 복합 컬럼 인덱스 |
| 9 | Single-Column Indexes | 단일 컬럼 인덱스 |
| 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 | 인덱스가 구성된 열에서 MAX 또는 MIN |
| 14 | ORDER BY on Indexed Column | 인덱스가 구성된 열에서 ORDER BY |
| 15 | Full Tabel Scan | 풀 테이블 스캔 |
http://wiki.gurubee.net/pages/viewpage.action?pageId=27428156
순서는
1. ROWID 를 통해 하나의 행을 접근하는게 제일 빠르겠지 생각해도. 다이렉트로.
2. 유일 인덱스(Unique Index). 인덱스를 먼저 액세스하고 인덱스에 존재하는 ROWID 를 추출하여 테이블의 행을 액세스한다
3. 복합 인덱스에 동등(=) 연산자 조건으로 검색하는 경우. WHERE A=10 AND B=1 이런거
4. 단일 인덱스에 동등(=) 연산자 조건으로 검색하는 경우. A=10 이런거. AND 로 두개 묶는게 더 빠른거였네..?!?
5. BETWEEN, LIKE 같은 범위. A BETWEENN 10 AND 20, A LIKE '1%'. 이런거.
6. 한쪽 범위만 검색. <, >, >=, <= .
7. 전체 테이블 검색
조인 순서를 결정할 때는 조인 칼럼 인덱스가 있는지 없는지 부터 판단한다
조인칼럼 인덱스가 양쪽 테이블에 있다면.. 위에서 설명한 규칙대로 우선 순위가 높은 테이블을 선행테이블 로 선택해서 먼저 살펴보게 된다
만약 한쪽에만 있다면.. 인덱스가 없는 테이블을 선행해서 살펴보고
둘다 없다면! FROM 절의 뒤에 있는 테이블이 선행테이블이다.
순위가 동일하다면 FROM 절에 나열된 테이블의 역순으로 선행테이블을 선택한다.
조인 기법은 양쪽에 모두 인덱스가 없으면 SORT MERGE JOIN 기법을 사용하고. 인덱스가 하나라도 있다면 NL JOIN 기법을 사용한다
select ENAME
from EMP
where JOB = 'SALESMAN'
and SAL between 3000 and 6000;
칼럼을 보자.
JOB 칼럼의 조건은 =
SAL 칼럼의 조건은 BETWEEN 3000 AND 6000
각각의 단일 칼럼에 단일 칼럼 인덱스가 존재한다.
JOB 은 규칙 9, SAL 은 규칙 10의 검색을 만족한다.
우선순위가 높은 JOB 인덱스를 이용해서 조건을 만족하는 행에 대해 EMP 테이블을 액세스 한다.
❏ 비용기반 옵티마이저
- 규칙기반 에서는 규칙 대로 우선순위가 정해져있고.. = 연산자와 BETWEEN 연산자가 사용되면. 당연히 = 이 우선순위 높으니 더 적은 처리 범위로
작업할거라고 판단한다. 그렇지만 실제로는 BETWEEN 이 더 일의 양이 적을 수 있다!! 단순히 규칙만으로는 단정짓기는 좀 그렇다.
그래서 비용기반 옵티마이저는 SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다
여기서 비용은 돈이 아니라.. 소요되는 시간, 자원 사용량 등을 의미한다.
비용기반 옵티마이저는 질의 변환기, 대안 계획 생성기, 비용 예측기 등의 모듈로 구성되어 있다.
질의 변환기 : 사용자가 작성한 SQL 문을 좀 더 처리하기 좋은 형태로 변환
대안 계획 생성기 : 동일한 결과를 생성하는 다양한 계획을 생성한다. 동일한 결과를 모든 경우의 수로 생각해야하니 성능은 좋겠지만 좀 오래 걸릴 수 있다
비용 예측기 : 대안 계획 생성기에 의해서 생성된 계획의 비용을 예측한다. 나온 결과들 비용 예측하는거네. 보다 나은 예측을 위해 정확한 정보들이 필요하겠다.
❏ 실행 계획
- SQL 에서 요구한 사항을 처리하기 위한 절차와 방법을 의미한다.
그러니까. SQL 을 어떤 순서로 어떻게 실행할지 결정하는 작업. 옵티마이저는 다양한 처리 방법(실행계획) 중에서 가장 효율적인 방법을 찾아서 생성한다.
❏ SQL 처리 흐름도
- SQL 의 내부적인 처리 절차를 시각적으로 표현한 도표이다. 시각적으로 표현..? 이게 왜필요한거야. 보고 이해하라는건가
TABLE 1 (Outer Table 또는 Driving Table 이라고 한다. 시작점 이니까. 바깥에서 안쪽. 또는 운전해서 찾아가는 .. ?;;)
TABLE 2 (Inner Table 또는 Lookup Table 이라고 한다. ..)
TAB1 -> TAB2 의 순서
액세스 방법
TAB1 : 테이블 전체 스캔
TAB2 : I01_TAB2 라는 인덱스를 통한 인댁스 스캔
조인방법
NL JOIN
TAB1 에 대한 액세스는 SCAN 방식 / TAB2 에 대한 액세스는 랜덤 방식
대량의 데이터를 랜덤 방식으로 액세스하게 되면 많은 I/O 가 발생하여 성능상 좋지 않다
..;;;