Query Optimization

Cost-based Query Optimizer

Query Plan

• Optimizer가 선택하는 access plan과 join plan 으로 결정
• 조회할 페이지를 줄이고 불필요한 정렬 작업을 최소화하도록 선택

Access Plan 작성 단계

• 각 필터를 통해 예상되는 데이터 출력 건수 산정 (selectivity)
• 인덱스 사용 가능 여부 검사
  

  - filter 컬럼
  - ORDER BY / GROUP BY컬럼

• 최적의 access 방안 선택
  

  - sequentially
  - by an index

Join Plan 작성 단계

• 테이블간의 join 조합 구성
• 각 조합별로 I/O 및 CPU cost 산정
• 부하가 많은 (cost가 크다. expensive) 조합은 plan에서 제거

Explain - Viewing the Query Plan

Query plan 확인

• SET EXPLAIN { ON | OFF } ;

EXPLAIN을 ON시키고 SQL구문을 실행하면 query plan을 확인 할 수 있는 텍스트 파일이 생성된다.

• 예상 비용 (estimate cost)
• 테이블 접근 순서
• 임시테이블 사용 정보
• access method / join method

Explain 파일

• UNIX : ${PWD}/ sqexplain.out 또는 ${ ${HOME}/ sqexplain.out
• NT : %INFORMIXDIR%\sqexpln\username.out

Explain - examples

• Sequential Scan with Temporary Table
• Sequential Scan with Filter
• Key-only Index Scan

• Index Scan with Lower Index Filter
• Index Scan with Lower and Upper Index Filters

• Dynamic Hash Join
• Key-First Index Scan

SQL에 직접적으로 optimizing 방법을 명시하는 구문을 optimizer directives 라고 한다

• Positive Directives
• Negative Directives

Optimizer Directives의 종류

• Access method directives
• Join order directives
• Join method directives
• Optimization goal directives
• Explain diretivies

SQL comment 문자 뒤에 + 기호를 붙여서 directives를 나타낸다

• --+ directives 구문
• {+ directives 구문 }
• /*+ directives구문 */

Access Method Directives

• INDEX
• AVOID_INDEX
• FULL
• AVOID_FULL

Join Method Directives

• USE_NL
• AVOID_NL
• USE_HASH
• AVOID_HASH

Join Order Directives

• ORDERED

Optimization Goal Directives

• FIRST_ROWS
• ALL_ROWS (default)

Explain Directives

• EXPLAIN
• EXPLAIN AVOID_EXECUTE

Update Statistics

UPDATE STATISTICS 구문을 사용하여 시스템 카타로그 테이블의 통계 정보를 갱신한다

구문

• 데이터베이스의 모든 오브젝트에 대하여 수행
  UPDATE STATISTICS [ LOW | MEDIUM | HIGH ] ;
• 특정 테이블과 그 테이블에 생성된 인덱스에 대하여 수행
  UPDATE STATISTICS [ LOW | MEDIUM | HIGH ] FOR TABLE [ table_name ] ;
• 특정 컬럼에 대하여 수행
  UPDATE STATISTICS [ LOW | MEDIUM | HIGH ] FOR TABLE table_name(column_name) ;

Update Statistics Guideline

데이터베이스 전체 또는 테이블별로 LOW 모드로 수행하는 것이 일반적

LOW 모드로 수행하였을 때 만족스럽지 못하다면 다음 단계 수행

• 대용량 테이블의 경우
  1. 테이블별로 MEDIUM 모드, distribution only 옵션으로 수행
  2. 인덱스의 첫번째 컬럼에 대하여 HIGH 모드 수행
  3. Multi-column으로 생성된 인덱스 고려
  4. 기타 컬럼에 대하여 컬럼 단위로 LOW 모드 수행
• 소규모 테이블의 경우
  테이블 단위로 HIGH 모드 수행

다음과 같은 경우 반드시e2">

• 데이터를 테이블에 대량 로드 한 후
• 데이터 수정이 상당히 많아서 데이터 분포가 변경 되었을 때
• 테이블의 대부분의 행에서 입력 또는 삭제 작업이 일어났을 때
• 인덱스를 추가하거나 삭제 또는 변경하였을 때