(SQLD)옵티마이저 종류/인덱스(Index)/실행 계획(Execution Plan)/옵티마이저 조인(Optimizer Join)

1.     옵티마이저의 실행 방법

• 개발자가 SQL을 실행하면 파싱을 실행해서 SQL의 문법 검사 및 구문분석을 수행함

• 구문분석이 완료되면 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립함

• 옵티마이저는 기본적으로 비용 기반 옵티마이저를 사용해서 실행 계획을 수립함

• 비용 기반 옵티마이저는 통계정보를 활용해서 최적의 실행 계획을 수립함

• 실행 계획 수립이 완료되면 최종적으로 SQL을 실행하고 실행이 완료되면 데이터를 인출 함

*옵티마이저 엔진

• Query Transformer: SQL문을 효율적으로 실행하기 위해서 옵티마이저가 변환함(SQL이 변환되어도 그 결과는 동일함)

• Estimator: 통계정보를 사용해서 SQL 실행비용을 계산함(총비용은 최적의 실행 계획을 수립하기 위해서임)

• Plan Generator: SQL을 실행할 실행 계획을 수립함

2.     옵티마이저 엔진

• 규칙 기반 옵티마이저(Rule base Optimizer)는 실행 계획을 수립할 때 15개의 우선순위를 기준으로 실행 계획을 수립함

• 최신 Oracle 버전은 규칙 기반 옵티마이저 보다 비용 기반 옵티마이저를 기본적으로 사용함

• 옵티마이저 엔진 우선순위

(1)   ROWID를 사용한 단일 행인 경우

(2)   클러스터 조인에 의한 단일 행인 경우

(3)   유일하거나 기본키를 가진 해시 클러스터 키에 의한 단일 행인 경우

(4)   유일하거나 기본키에 의한 단일 행인 경우

(5)   클러스터 조인인 경우

(6)   해시 클러스터 조인인 경우

(7)   인덱스 클러스터 키인 경우

(8)   복합 칼럼 인덱스인 경우

(9)   단일 칼럼 인덱스인 경우

(10)  인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우

(11)  인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우

(12)  정렬-병합(Sort Merge) 조인인 경우

(13)  인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우

(14)  인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우

(15)  전체 테이블을 스캔(FULL TABLE SCAN) 하는 경우

3.     비용 기반 옵티마이저

• 비용 기반 옵티마이저는 오브젝트 통계 및 시스템 통계를 사용해서 총 비용을 계산함

• 총비용이라는 것은 SQL문을 실행하기 위해 예상되는 소요시간 혹은 자원의 사용량을 의미함

• 총비용이 적은 쪽으로 실행 계획을 수립함

• 비용기반 옵티마이저에서 통계정보가 부적절한 경우 성능 저하가 발생함

 

1.     인덱스(Index)

• 인덱스는 데이터를 빠르게 검색할 수 있는 방법을 제공함

• 인덱스는 인덱스 키로 정렬 되어 있기 때문에 원하는 데이터를 빠르게 조회함

• 인덱스는 오름차순(ASCENDING) 및 내림차순(DESCENDING) 탐색이 가능함

• 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고 하나의 인덱스는 여러 개의 칼럼으로 구성될 수 있음

• 테이블을 생성할 때 기본키는 자동으로 인덱스가 만들어지고 인덱스의 이름은 SYSXXXX임

• 인덱스의 구조는 Root Block, Branch Block, Leaf Block으로 구성됨

• Root Block은 인덱스 트리에서 가장 상위에 있는 노드를 의미함

• Branch Block은 다음 단계의 주소를 가지고 있는 포인터로 되어있음

• Leaf Block은 인덱스 키와 ROWID로 구성되고 인덱스 키는 정렬되어서 저장되어 있음

• Leaf Block은 Double Linked List 형태로 되어 있어서 양방향 탐색이 가능함

• Leaf Block에서 인덱스 키를 읽으면 ROWID를 사용해서 EMP 테이블의 행을 직접 읽을 수 있음

2.     인덱스 생성

• 인덱스 생성은 ‘CREATE INDEX’문을 사용해서 생성이 가능함

• 인덱스를 생성할 때는 한 개 이상의 칼럼을 사용해서 생성할 수 있음

• 인덱스 키는 기본적으로 오름차순으로 정렬하고 ‘DESC’ 구를 포함하면 내림차순으로 정렬함

3.     인덱스 스캔(Index Scan)

(1)   인덱스 유일 스캔(Index Unique SCAN)

• 인덱스의 키 값이 중복되지 않는 경우, 해당 인덱스를 사용할 때 발생됨

(2)   인덱스 범위 스캔(Index Range SCAN)

• SELECT문에서 특정 범위를 조회하는 WHERE문을 사용할 경우 발생

• 인덱스의 Leaf Block의 특정 범위를 스캔한 것임

• Like, Between이 대표적인 예(데이터 양이 적은 경우는 인덱스 자체를 실행하지 않고 TABLE FULL SCAN이 될 수 있음)

(3)   인덱스 전체 스캔(Index Full SCAN)

• 인덱스에서 검색되는 인덱스 키가 많은 경우에 Leaf Block의 처음부터 끝까지 전체를 읽어 들임

*High Watermark: 테이블에 데이터가 저장된 블록에서 최상위 위치를 의미하고 데이터가 삭제되면 High Watermark가 변경 됨

 

4. 실행 계획(Execution Plan)

• 실행 계획은 번호 순서대로 읽음

• 먼저 조회되는 테이블은 Outer Table, 그다음 조회되는 테이블은 Inner Table

 

5. 옵티마이저 조인(Optimizer Join)

1.     Nested Loop 조인

• 하나의 테이블에서 데이터를 먼저 찾고 그다음 테이블을 조인하는 방식

• Nested Loop 조인에서 먼저 조회되는 테이블을 외부 테이블이라고 함, 그다음 조회되는 테이블을 내부 테이블이라고 함

• Nested Loop 조인에서는 외부 테이블의 크기가 작은 것을 먼저 찾는 것이 중요함(데이터가 스캔되는 범위를 줄일 수 있기 때문)

• Nested Loop 조인은 RANDOM ACCESS가 발생하는데 RANDOM ACCESS가 많이 발생하면 성능 지연이 발생함(RANDOM ACCESS의 양을 줄여야 성능이 향상됨)

2.     Sort Merge 조인

• 두 개의 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩하고 SORT를 숭행함

• 두 개의 테이블에 대해서 SORT가 완료되면 두 개의 테이블을 병함 함

• Sort Merge 조인은 정렬이 발생하기 때문에 데이터양이 많아지면 성능이 떨어지게 됨

• 정렬 데이터양이 너무 많으면 정렬은 임시 영역에서 수행됨(임시 영역은 디스크에 있기 때문에 성능이 급격히 떨어짐)

3.     Hash 조인

• 두 개의 테이블 중에서 작은 테이블을 HASH 메모리에 로딩하고 두 개의 테이블의 조인 키를 사용해서 해시 테이블을 생성함

• 해시 함수를 사용해서 주소를 계산하고 해당 주소를 사용해서 테이블을 조인하기 때문에 CPU연산을 많이 함

• 특히 Hash조인 시에는 선행 테이블이 충분히 메모리에 로딩되는 크기여야 함