[SQL] SQL 최적화

1. 옵티마이저와 실행계획

- 옵티마이저

: 데이터 딕셔너리에 있는 통계정보를 활용해 예상되는 비용을 산정, 최저비용의 계획을 선택해 SQL을 실행시킨다.

   * 힌트를 부여해 실행계획을 변경시킬 수 있다.

   * PLAN_TABLE을 조회해 실행계획을 볼 수 있다.

 

- 종류: 규칙기반 or 비용기반(default)으로 실행계획 수립

- 옵티마이저 엔진

• Query Transformer: sql문을 효율적으로 실행하기 위해 옵티마이저가 변환한다.
• Estimator: SQL실행비용을 계산
• Plan Generator: 실행계획 수립

 

2. 인덱스

- 특징

• 인덱스는 정렬되어 있다. 
• 여러 개의 칼럼으로 구성
• 인덱스 구조는 Root Block, Branch Block, Leaf Block으로 구성되어 있다.
• Leaf Block: 인덱스 키와 ROWID로 구성, 인덱스 키는 정렬되어 저장, Double linked list로 되어 있어 양방향 탐색 가능

CREATE INDEX INDEX_EMP
    ON EMP(ENAME ASC, SAL DESC);

- 종류

• Index Unique SCAN(PK를 써야한다)
• Index Range SCAN: LIKE, BETWEEN
• Index Full SCAN: High Water Mark 까지만 스캔한다.

* 인덱스를 사용할 경우, 인덱스가 저장되어 있는 테이블과 본 테이블 둘다 엑세스를 해야한다. 이때 디스크 I/O가 커질 수 있으니 주의해야한다. 이를 위해 고안된 것이 Index-Organized Table인데, 한번의 엑세스로 인덱스와 데이터에 접근할 수 있다.

CREATE TABLE emp_test (
	empno VARCHAR2(20) PRIMARY KEY,
    ename VARCHAR2(50)
) ORGANIZATION INDEX;

 

3. 옵티마이저 조인

- 종류

• Nested Loop 조인: Outer Table(크기가 작을수록 좋다)에서 Inner Table로 조회 된다, Random Access를 줄여야 성능이 향상된다.
• Sort Merge 조인: 두개의 테이블을 SORT_AREA라는 메모리 공간에 로딩하고 SORT를 수행, 이후 병합한다. sort를 하기 때문에 데이터가 많아지면 성능저하 발생, use_merge힌트는 ordered와 함께 사용해야 한다.
• Hash 조인: 작은 테이블을 Hash메모리에 로딩, 조인키를 해시함수에 넣어 해시테이블 생성, 이후 후행 테이블의 조인키값을 해시함수에 넣어 해시값을 추출, 추출된 값과 해시테이블을 비교해 매칭하는 방식으로 조인이 진행된다.

* NL조인은 결과를 가능한 빨리 화면에 보여줘야 하는 인터넷 서비스 등에 유리한 조인 방식이다.
* 배치작업에 많이 사용되는 조인 방식은 Sort Merge 조인 방식이다.

* Sort merge는 기본키와 외래키 관계에서 외래키에 인덱스가 없을 경우 효율적이다.