[개념 정리] 조인 튜닝 - 서브 쿼리 조인

쿼리 변환

옵티마이저는 SQL을 최적화에 유리한 형태로 변환, SQL을 의미적으로는 동일 하면서도 더 나은 성능이 기대되는 형태로 재작성

쿼리 블록 단위로(select 절 단위로) 최적화 수행 - 메인쿼리와 서브쿼리를 각각 최적화 하는 형식 -> 서브커리를 독립적으로 최적화 하는 작업이 전체 쿼리 실행에는 부조화 일 수도.

 

 

서브쿼리 조인

• 필터 오퍼레이션(중첩 서브쿼리-exists 해당) 
  • NL조인과 동일 처리 루틴(NL 처럼 부분 범위 처리도 가능)
  • NL조인과 차이
    • 메인쿼리 결과 집합이 서브쿼리쪽 집합 수준으로 확장되는 것을 방지하도록, 메인쿼리의 row가 서브쿼리의 한 row 와 조인에 성공하는 순간 진행 멈추고, 메인쿼리 다음 row 계속 처리. (Exists조건절일 때만 이겠지?)
    • 캐싱 기능 : 쿼리 단위로 캐싱(메인쿼리 단위겠지?), 서브 쿼리 입력값에 따른 반환 값 캐싱하기 때문에 서브쿼리 수행 전, 캐시부터 확인
    • 서브쿼리 NL조인은 조인 순서 고정 : 메인쿼리가 드라이빙 집합
• Unnesting
  • 서브쿼리에 힌트 unnest사용시 메인쿼리와 서브쿼리 간 계층 구조를 풀어 같은 레벨로 처리 -> 필터 방식은 계층 구조 그대로 nl조인 해야 하지만, unnesting 하고 나면, 다양한 최적화 기법 사용 가능(다양한 조인 방식 사용 가능 - 서브쿼리 선 처리 가능)
  • Unnest한 서브쿼리를 leading 지정하고 use_nl(메인 쿼리) 조인 지정 시 옵티마이저에서 서브쿼리를 Sort Unique 오퍼레이션 처리(중복 조인 키 제거 -> 메인쿼리 서브쿼리로 확장 방지)
  • Unnest한 서브쿼리에 다양한 조인 형태로 변환 가능
    • 병렬 쿼리 혹은 Exists 서브쿼리에 rownum을 사용하는 경우 서브쿼리 Unnesting을 방지하는 효과 -> 성능 문제로 이어짐
• 서브쿼리 Pushing
  
  • 서브쿼리 필터링 방식은 메인쿼리가 항상 드라이빙 집합 -> 서브쿼리를 먼저 필터링함으로써 다음 조인 단계로 넘어가는 결과 집합을 줄일 수 있음(메인쿼리 2개 이상 조인 + 중첩 서브쿼리 조인일 경우) 
    
    • 중첩 서브쿼리에 힌트 no_unnest, push_subq 선언 필요.
  • Unnest 된 서브쿼리에는 push_subq 작동 X

 

뷰 조인

• 메인쿼리 조건절에 필터링 집합을 줄일 수 있는 조건절이 있음에도 인라인 뷰에서 전체 데이터 집합을 처리 하는 실행계획일 경우 -> 인라인 뷰에 merge힌트 사용 해서 메인 쿼리와 merging 처리가 가능하다. 
  • 인라인 뷰를 메인 쿼리와 merge 할 경우, 메인쿼리와 인라인 뷰를 nl 조인 하는 형식으로 옵티마이징 -> 메인쿼리 조건절로 조인 대상 줄인 후 인라인 뷰와 조인 진행 가능
• Group by, 혹은 Sort 집계로 인라인 뷰에 대해 nl '부분 처리'가 안될 경우(인라인 뷰에 group by, sort가 적용되 merging하면 nl조인 진행 결과 전체 집합에 group by, sort 가 진행될 경우) -> hash 조인이 빠를 수 있다. 
  
  • Hash조인 혹은 조인 조건 pushdown 사용.
    • Pushdown : 메인 쿼리를 실행 하면서 조인 조건절 값을 건건이 뷰안으로 밀어넣는 기능. 

 

스칼라 서브쿼리 조인

• Select 절에서 함수 호출 시 재귀적 호출, 반면 스칼라 서브 쿼리는 반복적으로 테이블을 탐색하지만, 함수처럼 재귀적으로 실행하는 구조는 X. (컨텍스트 스위칭 없이 메인쿼리와 서브쿼리를 조인문처럼 하나로 실행)
• 스칼라 서브쿼리 사용시 NL 조인 방식으로 실행.
• NL 조인과는 다르게 캐싱 작용이 일어남
  • 스칼라 서브쿼리의 입력 값(퀴리에서 참조하는 메인쿼리의 컬럼 값)에 대한 출력 값을 내부 캐시에 저장. 조인 시(스칼라 서브쿼리는 조인방식으로 실행) 입력 캐시에서 탐색 후 찾지 못할 경우에만 조인 수행.
  • 메인 집합이 크더라도 조인할 데이터를 캐시에서 대부분 찾을 수 있다면 조인 수행 횟수 최소화 가능.
  • 캐싱은 쿼리단위. 퀴리 시작시 PGA 공간 할당, 쿼리 수행시 공간에 출력값 입력, 쿼리 완료시 공간 반환.
    • select 절에 사용하는 함수는 메인 쿼리 결과 건수 만큼 반복 수행 -> 스칼라 서브쿼리로 씌우면 호출 횟수 최소화 가능
  • 스칼라 서브쿼리에 사용하는 캐시공간이 작아,(_query_execution_cache_max_size 파라미터로 사이즈 결정) 입력값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과적.(입력값 종류가 많을 경우, 캐시를 매번 확인하는 비용 때문에 성능이 나빠지고 cpu 사용률, 메모리 사용률이 높아진다 ->캐시에 담을 수 없을 정도로 수가 많을 경우, 캐시를 매번 탐색해도, 출력 값을 찾지 못해 계속해서 조인을 진행하기 때문)
  • 메인 쿼리 집합이 매우 작은 경우에도 캐싱 성능 효과를 기대할 수 없음. (메인쿼리 집합이 클수록 캐시 재사용성이 높고 반대 경우, 캐시 재사용성도 낮음 -> 결과 집합 건수가 1개일 때 캐시에 재사용되지 않는 데이터가 저장됨)
  • 스칼라 서브쿼리는 2개 이상 값을 반환 -> 2개 이상 값을 반환할때는 인라인뷰로 튜닝.
  • 스칼라 서브쿼리의 캐싱효과가 크지 않고 NL조인으로 인해 (인덱스) 랜덤 I/O 부담이 있을 경우 스칼라 서브쿼리를 unnesting 해 다른 조인 사용 가능.(오라클 12c 부터는 옵티마이저가 자동으로 스칼라서브쿼리를 unnesting 할지 말지 결정 -> _optimizer_unnest_scalar_sq파라미터 설정)