서브쿼리 조인

서브쿼리

• 하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록을 말한다.

1. 인라인 뷰

   • From 절에 사용한 서브쿼리

2. 중첩된 서브쿼리

   • WHERE 절에 사용한 서브쿼리
   • 서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 '상관관계 있는 서브쿼리'라 함

3. 스칼라 서브쿼리

   • 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리
   • SELECT-LIST에서 사용하지만 몇 가지 예외사항을 제외하면 컬럼이 올 수 있는 대부분 위치에서 사용할 수 있다.

서브쿼리 변환이 필요한 이유

• 옵티마이저는 사용자로부터 전달받은 SQL을 최적화에 유리한 형태로 변환하는 작업, 즉 쿼리 변환부터 진행한다.
• 쿼리 변환 : 옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말함.
• 서브쿼리를 참조하는 메인쿼리도 하나의 쿼리 블록이며, 옵티마이저는 쿼리 블록 단위로 최적화를 수행한다.
• SQL을 최적화할 때도 옵티마이저가 나무가 아닌 숲 전체를 바라보는 관점에서 쿼리를 이해하려면 먼저 서브쿼리를 풀어내야만 한다!

서브쿼리와 조인

메인쿼리와 서브쿼리 간에는 부모와 자식이라는 종속적이고 계층적인 관계가 존재한다.
서브쿼리는 메인쿼리에 종속되므로 단독으로 실행할 수 없다.

• 필터 오퍼레이션

  • no_unnest : 서브쿼리를 풀어내지 말고 그대로 수행하라고 옵티마이저에 지시하는 힌트
  • 기본적으로 NL 조인과 처리 루틴이 같다.(NL 조인처럼 부분 범위 처리도 가능)
  • 차이점
    1. 필터는 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 다음 로우를 계속 처리한다는 점
    2. 필터는 캐싱기능을 갖는다는 점
       • 서브쿼리 입력 값에 따른 반환 값(true or false)을 캐싱하는 기능
       • 이 기능이 작동하므로 서브쿼리를 수행하기 전에 항상 캐시부터 확인한다.
    3. 필터 서브쿼리는 일반 NL 조인과 달리 메인쿼리에 종속되므로 조인 순서가 고정된다.(항상 메인쿼리가 드라이빙 집합)

• 서브쿼리 Unnesting (= 서브쿼리 Flattening)

  • unnest 힌트 사용
  • 메인과 서브쿼리 간 계층 구조를 풀어 서로 같은 레벨로 만들어 준다.
  • 서브쿼리를 그대로 두면 필터 방식을 사용
  • Unnesting 하고 나면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있다.
  • NL 세미 조인
    • unnest, nl_sj 힌트 사용
    • NL 조인과 같은 프로세스
    • 조인에 성공하는 순간 진행을 멈추고 메인 쿼리의 다음 로우를 계속 처리한다는 점만 다르다.
  • Unnesting된 서브쿼리는 NL 세미조인 외에도 다양한 방식으로 실행될 수 있다.
  • 필터방식은 항상 메인쿼리가 드라이빙 집합이지만,
  • Unnesting된 서브쿼리는 메인 쿼리 집합보다 먼저 처리될 수 있다.

ROWNUM - 잘 쓰면 약, 잘못 쓰면 독

• 서브쿼리에 rownum을 쓰면 옵티마이저에게 "이 서브쿼리 블록은 손대지 말라"고 선언하는 것과 다름없다.
• 서브쿼리 Unnesting을 방지하려는 목적이 아니면 서브쿼리에 함부로 쓰지 말자.

• 서브쿼리 Pushing

  • 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능
  • push_subq, no_push_subq 힌트로 제어
  • 이 기능은 Unnesting 되지 않은 서브쿼리에만 작동
  • 따라서 push_subq 힌트는 항상 no_unnest 힌트와 같이 기술해야 한다.

뷰와 조인

• 최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다.
• merge 힌트를 이용해 뷰를 메인 쿼리와 머징하도록 함.
• 부분처리가 불가능한 상황에서 NL 조인은 좋은 선택이 아니다. (이런 상황에선 보통 해시 조인이 빠름)

조인 조건 Pushdown

• 메인쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능이다.
• 'VIEW PUSHED PREDICATE'오퍼레이션을 통해 이 기능의 작동 여부를 알 수 있음.
• 이 방식을 사용하면 '건건이' 데이터만 읽어서 조인하고 Group By를 수행할 수 있다.
• 부분범위 처리가 가능하다.
• push_pred 힌트를 사용
• 옵티마이저가 머징하면 힌트가 작동하지 않으니 no_merge 힌트와 함께 사용하는 습관이 필요

스칼라 서브쿼리 조인

스칼라 서브쿼리의 특징

• Outer 조인문처럼 NL 조인 방식으로 실행된다.
• 처리과정에서 캐싱 작용이 일어난다.

스칼라 서브쿼리 캐싱 효과

• 필터 서브쿼리 캐싱과 같은 기능이다.(조인 성능을 높이는 데 큰 도움)
• 캐싱은 쿼리 단위로 이루어진다.

  쿼리를 시작할 때 PGA 메모리에 공간을 할당하고,
  쿼리를 수행하면서 공간을 채워나가며,
  쿼리를 마치는 순간 공간을 반환한다.

• 많이 활용되는 튜닝 기법
  • SELECT-LIST에 사용한 함수는 메인쿼리 결과 건수만큼 반복 수행되는데, 스칼라 서브쿼리를 덧씌워서 호출 횟수를 최소화할 수 있다.

스칼라 서브쿼리 캐싱 부작용

• 스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과가 있다.
• 반대의 경우라면 캐시를 매번 확인하는 비용 때문에 오히려 성능이 나빠지고 CPU 사용률만 높게 만들며 메모리도 더 사용한다.

두 개 이상의 값 반환

• 인라인 뷰 사용
  • 뷰를 사용하면, 전체를 읽어야 하거나, 뷰가 머징될 때 Group By 때문에 부분범위 처리가 안 되는 문제가 있다.

스칼라 서브쿼리 Unnesting

• 스칼라 서브쿼리도 NL 방식으로 조인하므로 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있다.
• 그래서 다른 조인 방식을 선택하기 위해 스칼라 서브쿼리를 일반 조인문으로 변환하고 싶을 때가 있다.
• 특히, 병렬 쿼리에선 될 수 있으면 스칼라 서브쿼리를 사용하지 않아야 한다.
  • 대량 데이터를 처리하는 병렬 쿼리는 해시 조인으로 처리해야 효과적이기 때문
• _optimizer_unnest_scalar_sq 파라미터
  • true로 설정
    • 스칼라 서브쿼리를 Unnesting 할지 여부를 옵티마이저가 결정
  • false로 설정
    • 옵티마이저가 이 기능을 사용하지 않지만, 사용자가 unnest 힌트로 유도할 수 있다.