SQLP 요점정리 #09 : 2과목 SQL - 3장 SQL 최적화 기본 원리

2과목 SQL 기본 및 활용

• 20문제
• 각 상황별로 SQL문 작성이 가능할 정도로 연습해야 합니다.
• 기본적인 SQL문법 및 함수는 생략하고 Tuning에 활용될수 있는 것 위주로 정리하고자 합니다.

3장 SQL 최적화 기본 원리

여기서는 간단히 다루고 3과목에서 자세히 다룰 예정이므로 그냥 넘어가도 됩니다. (아마 SQPD의 경우 2과목까지가 범위이므로 SQLD용 문제를 내기위한 용도인듯 합니다.)

Optimizer

• Optimizer : SQL문에 대한 최적의 실행 방법을 결정하는 역할
• Execution Plan (실행계획) : Optimizer가 생성한 최적의 실행 계획
• Optimizer 종류
  • RBO (Rule-based Optimizer) : 규칙(우선 순위)를 가지고 실행계획 생성
  • CBO (Cost-based Optimizer) : 통계정보를 기반으로 여러가지 실행계획을 생성하여 그중 최저비용의 실행계획을 선택
• Rule-based Optimizer 규칙
  1. Single row by rowid
  2. Sigle 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. Cluster join
  6. Hash cluster key
  7. Indexed cluster key
  8. Composite index
  9. Single column sindex
  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

요약하자면

Single row (rowid < unique or primary key) << Index ( Equal (composite < single) << Range (bounded < unbounded) ) << Full table scan

Index

1. B-Tree Index

• 구성
  • Root Block : Branch Block 중 가장 상위 Block
  • Branch Block : 다음 단계를 가리키는 Pointer를 가지고 있음
  • Leaf Block : Index를 구성하는 칼럼의 데이터와 rowid, 인접한 Leaf Block의 Pointer를 가지고 있음

2. Cluster Index (MS-SQL) , IOT (Oracle)

PK순으로 저장. Index의 Leaf Block에 Table 의 모든 데이터 저장

3. Bitmap Index

일종의 Hash + Linked List 형태

4. FBI (Function-Based Index)

함수값 순으로 Index에 저장

Full Table Scan vs Index Scan

• Optimizer 가 Full Table Scan을 하는 경우
  • SQL에 조건(WHERE)이 없을 경우
  • 조건은 있는데 사용 가능한 INDEX가 없는 경우
  • Optimizer가 판단하여 Full Table Scan이 더 유리할때 : Index는 Single Block I/O, Table은 Multi Block I/O
  • 기타 : 병렬처리, Hint 등...
• Index Scan 종류
  • Index Unique Scan : Unique Index에서 = 조건으로 검색
  • Index Range Scan : 조건이 = 가 아니거나 Non-unique Index를 이용
  • Index Range Scan Descending : MIN, MAX 값등을 쉽게 찾기 위해서 사용
  • Index Fast Scan : 검색하는 모든 컬럼이 Index에 포함된 경우 Table을 찾지않고 Index만으로 검색
  • Index Skip Scan : Index의 선두 컬럼이 검색 조건에 없을 경우 사용하는 방법

JOIN

• NL Join (Nested-loop)
  • Driving Table을 먼저 읽은 다음에 Outer Table과 JOIN
  • Driving Table에서 조건에 맞는 모든 row 만큼 JOIN이 이루어지므로 선행 테이블에서 작업량을 줄이는 것이 중요
  • 양쪽 중 한쪽만 Index가 있는 경우 Outer Table쪽의 Index를 사용하는 것이 유리 (Driving은 Full Scan)
  • JOIN 과정은 random access
• Sort Merge Join
  • Join 칼럼을 기준으로 데이터를 정렬한 뒤에 JOIN을 수행
  • 대량의 JOIN일 경우 NL Join의 random access보다 유리
• Hash Join
  • 선행 테이블(Build Input)에서 JOIN하는 컬럼의 Hash값을 메모리에 생성
  • 후행 테이블(Prove Input)에 Hash값을 메모리에서 찾아서 JOIN
  • 범위 검색은 불가능 하고 = 조건으로만 가능