SQL 전문가가이드 [과목3] 1장 2절 SQL 처리 과정

구조적, 집합적, 선언적 질의 언어

SQL은 'Structured Query Language'의 줄임말이다. 

구조적 질의언어다. 

SQL은 기본적으로 구조적이고 집합적이고 선언적인 질의 언어다.

 

SQL 처리과정

서브엔진 별 역할

엔진		역할
Parser		SQL문장을 이루는 개별 구성요소를 분석하고 파싱해서 파싱 트리(내부적인 구조체)를 만든다. 이 과정에서 사용자 SQL에 문법적 오류가 없는지(-> Syntax 체크), 의미상 오류가 없는지(->Semantic체크) 확인한다.
Optimizer	Query Transformer	파싱된 SQL을 좀 더 일반적이고 표준적인 형태로 변환한다.
	Estimator	오브젝트 및 시스템 통계정보를 이용해 쿼리 수행 각 단계의 선택도, 카디널리티, 비용을 계산하고, 궁극적으로는 실행계획 전체에 대한 총 비용을 계산해 낸다.
	Plan Generator	하나의 쿼리를 수행할 때, 후보군이 될만한 실행계획들을 생성해 낸다.
Row-Source Generator		옵티마이저가 생성한 실행계획을 SQL엔진이 실제 실행할 수 있는 코드(또는 프로시저)형태로 포맷팅한다.
SQL Engine		SQL을 실행한다.

 

 

SQL옵티마이저

SQL옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심 엔진

 

옵티마이저 최적화 단계

• 사용자로부터 전달받은 쿼리를 수행하는데 후보군이 될만한 실행계획들을 찾아낸다.
• 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
• 최저 비용을 나타내는 실행계획을 선택한다.

 

실행계획과 비용

• 옵티마이저가 특정 실행 계획을 선택하는 근거는 비용이다.
• 비용(Cost)은 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O횟수 또는 예상 소요시간을 표현한 값이다.
• 실행경로를 선택하기 위해 옵티마이저가 여러 통계정보를 활용해서 계산해 낸값이다.

 

옵티마이저 힌트

통계정보가 정확하지 않거나 다른 이유로 옵티마이저가 잘못된 판단을 할 수 있는데 이 때 프로그램이나 데이터 특성 정보를 정확히 알고 있는 개발자가 직접 인덱스를 지정하거나 조인방식을 변경함으로써 더 좋은 실행계획으로 유도하는 메커니즘 

 

1. Oracle힌트

1) 힌트 기술 방법 

SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk) 
           USE_MERGE(j) FULL(j) */
    e1.first_name, e1.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 (emp_emp_id_pk))

 

2) 힌트가 무시되는 경우

• 문법적으로 안맞게 힌트 기술
• 의미적으로 안 맞게 기술 
  • 서브쿼리에 unnest와 push_subq를 같이 기술한 경우(unnest되지 않은 서브쿼리만 push_subq힌트 적용의 대상임)
• 잘못된 참조 사용
  • 없는 테이블이나 별칭, 인덱스명 지정한 경우 
• 논리적으로 불가능한 액세스 경로
  • 조인절에 맞는 조건이 없는데 조인으로 유도하거나 null허용 컬럼에 대한 인덱스를 이용해 전체 건수를 세려고 시도할 때 

select /*+ index(e emp_ename_idx) */ count(*) from emp e

• 버그

 

위의 경우가 아니라면 옵티마이저는 힌트를 우선적으로 따른다. (명령어로 인식)

Oracle은 사용자가 힌트를 잘못 기술하거나 잘못된 참조를 해도 에러가 나지 않는다. (주의!)

 

3) 힌트 종류

 

분류	힌트
최적화 목표	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 start_transformation, no_start_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 cursor_sharing_exact driving_site dynamic_sampling model_min_analysis

 

 

2. SQL Server 힌트

• 테이블 힌트 
  • 테이블명 다음에 WITH절을 통해 지정(fastfirstrow, holdlock, nolock)
• 조인 힌트
  • FROM 절에 지정하며 두 테이블 간 조인 전략에 영향을 미친다.
  • loop, hash, merge, remote등
• 쿼리 힌트
  • 쿼리당 맨 마지막에 한 번만 지정할 수 있는 쿼리힌트는 다음과 같이 OPTION절을 이용한다.