SQL은 구조적, 집합적, 선언적 언어입니다. 원하는 결과집합을 절차적으로 만들어 구조적이고 집합적으로 선언합니다. 쿼리를 절차적으로 처리하는 프로시저를 만들어내는 DBMS 내부엔진이 SQL 옵티마이저입니다.
SQL 처리과정
PARSER
SQL 문장을 이루는 개별 구성요소를 분석하고 파싱해서 파싱 트리를 만듭니다. 사용자 SQL의 문법상, 의미상 오류를 파악합니다.
OPTIMIZER
- QUERY TRANSFORMER : 파싱된 SQL을 표준형태로 변환합니다.
- ESTIMATOR : 오브젝트/시스템 통계정보를 활용해 쿼리 수행 단계별 선택도, 카디널리티, 비용을 계산합니다.
- PLAN GENERATOR : 후보군이 될 만한 실행계획을 생성합니다.
- ROW-SOURCE GENERATOR : 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드 형태로 포맷팅합니다.
- SQL ENGINE : SQL을 실행합니다.
SQL 옵티마이저
사용자가 원하는 작업을 가장 효율적으로 수행할 수 있도록 최적의 데이터 엑세스 경로를 선택해주는 핵심 엔진입니다. 최적화 단계는 다음과 같습니다.
- 사용자로부터 전달받은 쿼리에 대한 실행계획 후보군을 찾는다.
- 데이터 딕셔너리에 수집해 둔 오브젝트 통계 및 시스템 통계정보를 통해 예상비용을 산정한다.
- 최저 비용의 실행계획을 선택한다.
옵티마이저 힌트
부정확한 통계정보나 다른 이유로 옵티마이저가 잘못된 판단을 할 수 있습니다. 그럴때 프로그램이나 데이터 특성 정보를 잘 아는 개발자가 옵티마이저 힌트를 이용해 직접 인덱스를 지정하거나 조인방식을 변경하여 더 좋은 실행계획으로 유도할 수 있습니다.
ORACLE 힌트
힌트를 기술하는 방법은 다음과 같습니다.
SELECT /*+ LEADING(E2 E1) USE_NL(E1) INDEX(E1 EMP_EMP_ID_PK)
USE_MERGE(J) FULL(J */
E1.FIRST_NAME, E2.LAST_NAME, J.JOB_ID, SUM(E2.SALARY) TOTAL_SAL
FROM EMPLOYEES E1, EMPLOYEES E2, JOB_HISTORY J
WHERE E1.EMPLOYEE_ID = E2.MANAGER_ID
AND E1.EMPLOYEE_ID = J.EMPLOYEE_ID
AND E1.HIRE_DATE = J.START_DATE
GROUP BY E1.FIRST_NAME, E1.LAST_NAME, J.JOB_ID
ORDER BY TOTAL_SAL;
INDEX 관련 힌트에서는 인덱스명 대신 칼럼명을 지정할 수 있습니다.
SELECT /*+ LEADING(E2 E1) USE_NL(E1) INDEX(E1 (EMPLOYEE_ID))
...
다음과 같은 경우 ORACLE 옵티마이저는 힌트를 무시하고 최적화를 진행합니다.
- 문법적으로 맞지않는 힌트 기술
- 의미적으로 맞지않는 힌트 기술
- 잘못된 참조 사용
- 논리적으로 불가능한 엑세스 경로
무시되는 경우에 해당하지 않는 한 옵티마이저는 힌트를 우선적으로 따릅니다. 힌트가 잘못지정됐다고해서 에러가 발생하지는 않습니다. 하지만 성능이 저하된 것을 발견하지 못할 수 있습니다.
옵티마이저 힌트의 종류
최적화 목표
all_rows
first_rows(n)
엑세스 경로
full
cluster
hash
index/no_index
index_asc/index_desc
index_combine
index_join
index_ffs/no_index_ffs
index_ss/no_index_ss
index_ss_asc/index_ss_desc
쿼리변환
no_query_transformation
use_concat/no_expand
rewrite/no_rewrite
merge/no_merge
star_tranformation/no_star_transformation
fact/no_fact
unnest/no_unnest
조인순서
ordered
leading
조인방식
use_nl/no_use_nl
use_nl_with_index
use_merge/no_use_merge
use_hash/no_use_hash
병렬처리
parallel/no_parallel
pq_distribute
parallel_index/no_parallel_index
기타
append/noappend
cache/nocache
push_pred/no_push_pred
push_subq/no_push_subq
qb_name
cursor_sharing_exact
driving_site
dynamic_sampling
model_min_analysis