SQL의 실행 절차
Parsing ▶ Execution plan 수립 ▶ SQL 실행 ▶ fetch
옵티마이저의 역할
- SQL에 대한 실행 계획(Execution plan)을 수립
- PLAN_TABLE에 실행 계획을 저장한다.
옵티마이저 분류
1. 규칙 기반 옵티마이저
- 설정된 규칙에 따라 실행
- 옵티마이저 힌트를 /*+RULE*/ 로 주면 규칙 기반 옵티마이저를 실행한다.
2. 비용 기반 옵티마이저
- 일반적으로 사용되는 옵티마이저
- 통계정보를 활용해 최적의 실행계획을 수립(총 비용이 적은 쪽으로 옵티마이징)
옵티마이저 조인
1. Nested loop 조인
- 하나의 테이블(외부 테이블)에서 데이터를 찾고 -> 다음 테이블(내부 테이블)을 조인하는 방식
- 크기가 작은 것이 외부 테이블로 사용된다. (RANDOM ACCESS양을 줄이기 위해)
- 중첩 for 문 형태이다.
- RANDOM ACCESS양을 줄이는 것이 성능향상의 핵심이다.
2. Sort merge 조인
- 조인할 두 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩한다.
- 그 후 SORT 한다
- 그 후 MERGE 한다.
- 정렬 데이터 양이 많으면 성능이 급격히 저하된다.(메모리 공간이 부족해 디스크공간 사용 + SORT 비용 증가)
3. Hash 조인
- 두 테이블 중 작은 테이블을 hash 메모리에 로딩한다.(선행 테이블은 메모리에 로드될 만큼 충분히 작아야 한다.)
- 두 테이블의 조인 키를 통해 hash table을 생성한다.
- hash 연산을 위한 cpu 자원 소모가 많다.
인덱스
1. 인덱스 구조
2. 인덱스 생성
CREATE INDEX 인덱스명 ON 테이블명(인덱싱할칼럼목록)
CREATE INDEX IND_EMP
ON EMP (ENAME ASC, SAL DESC); // ENAME과 SAL에 대해 각각 오름차순, 내림차순으로 인덱스를 생성한다.
[tip] PK 칼럼은 자동으로 인덱스가 생성된다.
3. 인덱스 스캔
3.1 unique scan
- 하나의 특정한 값을 스캔할 때 발생한다. ex) EMPNO = 111 조회
- 인덱스 키 값이 중복되지 않는 경우에만 발생한다.
SELECT * FROM EMP WHERE EMPNO=111; //EMPNO는 PK로 중복되지 않으므로 유니크 스캔이 발생한다.
3.2 range scan
- 범위를 조회할 때 발생한다. (ex. LIKE, BETWEEN)
- 특정 범위의 leaf node를 스캔하는 것이다.
- 데이터 양이 적을 경우 full scan과 동일하게 작동한다.
SELECT EMPNO FROM EMP WHERE EMPNO >= 1000;
3.3. full scan
- 검색 범위가 넓을 경우 range scan이 아니라 full scan이 일어난다.
- leaf node를 모두 스캔하는 것이다.
