1. 비용기반 옵티마이저
- Cost Based Optimizer, CBO, 테이블 및 인덱스 등의 통계정보를 활용하여 SQL문을 실행할때 처리시간, CPU, I/O자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저
2. 실행계획
- 액세스 기법
- 조인기법
- 연산
- 최적화 정보
- 동일 SQL이라면 결과는 동일하다.
3. 실행계획 실행순서
- 안 -> 밖
- 위 -> 아래
4. SQL 처리흐름도(Access Flow Diagram)
- SQL의 실행시간은 기재하지 않으면서, 인덱스 스캔 및 테이블 전체 스캔 등과 같은 액세스 기법을 표기한 흐름도
- SQL 내부적인 처리 절차를 시각적으로 표현한 흐름도
- 성능적인 부분을 표현하므로, 성능적인 부분도 고려한다.
5. 규칙기반 옵티마이저
- 제일 높은 우선순위는 ROWID를 통하여 테이블에 액세스하는 것이고, 가장 낮은 순위는 전체 테이블 스캔이다.
- 인덱스 범위 스캔의 경우 결과가 없다면, 한 건도 반환하지 않을 수 있다.
- 적절한 인덱스가 있다면 항상 인덱스를 사용하려고 한다.
6. 보조 인덱스(Secondary index)
- unique 특징이 아니라면, 키값 중복이 가능한 인덱스
7. 인덱스 유의사항
- 조회 성능을 향상
- 대량의 데이터 삽입 및 마이그레이션 시 인덱스 제거후 삽입
- 인덱스 존재시 UPDATE할때마다 매번 인덱스를 재정렬하므로, 인덱스가 많다면 update 성능이 저하될 수 있다.
- SQL Server의 CLUSTERED 인덱스는 ORACLE IOT와 매우 유사하다.
- INSERT, DELETE와는 달리 인덱스로 구성한 column 이외의 데이터가 update될 경우 updatea 부하가 거의 없을 수 있다.
8. 인덱스 종류
-
BTREE : 브랜치 블록과 리프 블록으로 구성, 브랜치 블록은 분기를 목적으로 하고 리프 블록은 인덱스를 구성하는 컬럼의 값으로 정렬된다. OLTP 환경에서 사용한다. -> 일치 및 범위 검색에 활용
-
CLUSTERED : 인덱스의 리프 페이지가 곧 데이터 페이지이며, 리프 페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 저장됨
-
BITMAP : 시스템에서 사용될 질의를 시스템 구현시에 모두 알 수 없는 경우인 DW/AD-HOC 질의 환경을 위해 설계되었으며, 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조.
9. 인덱스 스캔 유의사항
- 인덱스 스캔을 한다고 해서 무조건적으로 full table scan보다 유리하다고 할 수 없다.
10. 옵티마이저 실행계획 유의사항
- Oracle 규칙기반 옵티마이저의 최우선순위 규칙은 Single ROW by ROWID이다.
- 비용기반 옵티마이저는 테이블, 인덱스, 컬럼 등 객체의 통계정보를 사용하여 실행계획을 수립하므로 통계정보가 변경시 SQL 실행계획이 달라질 수 있다.
- Oracle 실행계획에 나타나는 기본적은 join 기법은 Nested Loop(NL), Hash, Sort Merge join 등이 있다.
- NL join은 OLTP, 목록처리 업무에 적합하고 DW영역에서는 hash 및 sort merge join이 적합하다.
11. Nested Join Loop
- 조인 컬럼에 적당한 인덱스가 있어서 자연조인(Natural join)이 유리할 경우
- Driving table의 조인 데이터 양이 큰 영향을 주는 조인 방식이다.
- 유니크 인덱스를 활용하여 수행시간이 적게 걸리는 소량 테이블을 온라인 조회할 경우
- OLTP
- 랜덤액세스 방식
12. hash join
- sort merge join하기에 두 테이블이 너무 커서 sort 부하가 심할때 사용
- hash join도 hash table을 사용하여, table을 사용하는 join 방식의 일환이다.
- equi join에서만 동작한다.
- DW
- 한 쪽 테이블이 주 메모리의 가용 메모리에 담길 정도로 충분히 작고, 해시 키 속성에 중복 값이 적을때 유리하다.
- 조인 컬럼에 적당한 인덱스가 없어 Natural join이 비효율적일때 유리하다.
- Natural join시 driving table에 액세스 량이 많아 random 부하가 심할때 유리하다.
- sort merge join을 하기에는 두 테이블이 너무 커서 sort 부하가 심할때 유리하다.
- 다만 결과 행의 수가 작은 테이블을 선행 테이블로 사용해야 성능적으로 유리하며, 일반적으로 hash join이 sort merge join보다 우수한 성능을 보인다.
- 컬럼의 인덱스를 사용하지 않는다.
13. EXIST 절에 사용하는 join
- Nested Loop SEMI Join
14. Sort merge join
- join 컬럼에 적당한 인덱스가 없어서 NL조인하기 비효율적일때 사용
- Driving table의 조인 데이터 및 개념이 중요하지 않는 join 방식이다.
- 조인 조건의 인덱스 유무에 영향을 받지 않는 join이다.
- 조인 컬럼을 기준으로 데이터를 정렬하여 조인을 수행한다.
- NL이 주로 랜덤액세스 방식이라면, sort merge join은 주로 스캔 방식으로 데이터를 읽는다.
- NL join의 데이터 범위가 부담이 된다면 sort merge join을 이용한다.
- non equi join에서도 동작하며(equi, non-equi 모두 동작), 반면 hash join은 반드시 equi join에서만 동작한다.
- 정렬할 데이터가 많아 메모리에서 정렬 작업을 수행하기 어려울 경우 성능이 저하될 수 있다.
