📚 Optimizer과 실행계획

📜 실행계획이란?

• SQL 처리를 위한 실행 절차와 방법을 표현 한 것이다.

1. 실행계획의 특징

• 실행계획은 조인 방법, 조인 순서, 액세스 기법 등이 표현된다.

• 동일 SQL문에 대해 실행계획 즉, 실행방법이 달라진다고 해서 결과가 달라지지는 않는다.

2. 실행계획 정보의 구성요소

📜 실행계획을 통해 'access 기법, 질의 처리 예상 비용(Cost), Join 순서'를 알 수 있다.

🧨 주의!! '실제 처리 건수'는 실행계획이 아닌 트레이스 정보를 통해서 알 수 있다.

• 조인 기법 : NESTED LOOPS

• 연산 : NESTED LOOPS, TABLE ACCESS, INDEX

• Access 기법 : TABLE ACCESS, INDEX

• 최적화 정보 : (Cost=n Card=n Bytes=n)

SELECT STATEMENT Optimizer=ALL_ROW (Cost=3 Card=2 Bytes=114)
    NESTED LOOPS(Cost=3 Card=2 Bytes=114)
        TABLE ACCESS(BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=2 Bytes=74)
            INDEX(UNIQUE SCAN) OF 'PK_DEPT'

3. 실행계획의 실행순서

📜 위에서 아래로, 안에서 밖으로

예시

3 -> 4 -> 2-> 6 -> 5 -> 1

1 NESTED LOOPS
2      HASH JOIN
3          TABLE ACCESS(FULL) TAB1
4          TABLE ACCESS(FULL) TAB2
5      TABLE ACCESS(BY ROWID) TAB3
6          INDEX(UNIQUE SCAN) PK_TAB3

4. CBO(Cost Based Optimizer), 비용기반 옵티마이저

📜 비용기반 Optimizer란?

• 테이블 및 인덱스 등의 통계 정보를 활용하여 SQL문을 실행하는데 소요 될 처리시간 및 CPU, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저이다.

📜 비용기반 Optimizer의 실행계획에 표시되는 정보

• 단계별 예상 비용
• 단계별 예상 건수

5. RBO(Rule Based Optimizer),규칙기반 옵티마이저

📜 규칙기반 Optimizer란?

• 통계정보가 아닌 미리 정해진 규칙에 의해 실행계획을 결정한다.

📜 규칙기반 Optimizer의 규칙 순위

규칙에 따라 적절한 index가 존재하면, full table scan 보다 항상 index를 사용하려고 한다.

1. Single Row by RowId 

2. Single Row by Cluster Join 

3. Single Row by Hash Cluster Key with Unique or Primary Key 

4. Single Row by Unique or Primary Key 

5. Clustered Join 

6. Hash Cluster Key 

7. Indexed Cluster Key 

8. Composite Index 

9. Single-Column Indexes 

10. Bounded Range Search on Indexed Columns 

11. Unbounded Range Search on Indexed Columns 

12. Sort-Merge Join 

13. MAX or MIN of Indexed Column 

14. ORDER BY on Indexed Column 

15. Full Table Scan

+ 👀 틈새 지식

💡 SQL 처리 흐름도(Access Flow Diagram)

: SQL 처리 흐름도는 SQL문의 내부적인 처리 절차를 시각적으로 표현한 것이다.

=> 액세스 기법이 표현된다. (실행 시간을 알 수 있는건 아니다.)