SQL 개발자 (Sqld 자격검정) 내용 - 2과목 최적화

옵티마이저 실행계획

 SQL 실행 계획은 PLAN_TABLE에 저장된다

 계획 확인은 TOAD에서 Execution Plan Current SQL 메뉴를 통해 하는게 편하다

 실행 계획은 예상 정보로 액세스 기법, 질의 처리 예상 비용, 조인순서 등을 알수있다

 실행계획을 읽는 순서는 위에서 아래로, 안에서 밖으로 읽는다

 

옵티마이저 종류

규칙 기반 옵티마이저

 우선순위가 존재하면 1순위는 ROWID 활용이고 최하위순위인 15순위는 전체 테이블 스캔(FTS)이다

비용 기반 옵티마이저

 옵티마이저가 실행계획을 수립할때 기본적으로 사용한다

 

인덱스 스캔

 기본인덱스 UNIQUE & NOT NULL 제약

 오름차순(ASC) 내림차순(DESC) 탐색이 가능

 Root Block , Branch Block , Leaf Block으로 구성되어 Root Block이 최상위 노드

 Leaf Block은 인덱스 키와 ROWID로 구성되어 있다

 

인데스 유일 스캔(INDEX UNIQUE SCAN)

 인덱스 키값이 중복되지 않는 경우 사용할때 발생한다

인덱스 범위 스캔(INDEX RANGE SCAN)

 SELECT문에서 특정범위를 조회하는 WHERE문을 사용할때 발생한다

 LIKE와 BETWEEN이 그 예시로 인덱스의 Leaf Block 범위를 스캔한다

인덱스 전체 스캔(INDEX FULL SCAN)

 검색되는 인덱스 키가 많은 경우 Leaf Block의 전체를 읽는다

 

옵티마이저 조인

Nested Loop 조인 ( /*+ ordered use_nl()*/* )

 하나의 테이블(외부 테이블, Outer Table)에서 데이터를 찾고 그 다음 테이블(내부 테이블, Inner Table)을 조인하는 방식

 선행 테이블의 크기가 작은 것을 먼저 찾아야 스캔되는 범위를 줄일 수 있다

 Nested Loop 조인 실행시 Random Access가 발생하는데 이 양을 줄여야 성능이 향상된다

 주로 OLTP 목록 처리 업무에 많이 사용된다

 

Sort Merge 조인 ( /*+ ordered use_merge()*/* )

 두개의 테이블을 SORT_AREA 메모리 공간에 로딩하고 SORT를 진행한다

 SORT가 완료된 후 두 테이블을 병합한다

 데이터 양이 많으면 성능은 떨어지며, 정렬 데이터 양이 많으면 디스크 안 임시 영역에서 수행된다

 데이터 집계 업무에서 많이 사용된다

 

Hash 조인( /*+ ordered use_hash()*/* )

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

 EQUI JOIN에서만 동작가능하고 NON-EQUI 조인에서는 사용할 수 없다

 데이터 집계 업무에서 많이 사용된다