4.4장 서브쿼리 조인

4.4장 서브쿼리 조인

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

• 서브쿼리의 종류

1. 인라인 뷰 : FROM 절에 사용한 서브쿼리
2. 중첩된 서브쿼리 : 결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리. 서브쿼리가 메인쿼리 컬럼을 참조할 때 상관관계 있는(Correlated) 서브쿼리라고 한다.
3. 스칼라 서브쿼리 : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리. 주로 SELECT 절에 사용하지만 컬럼이 올 수 있는 대부분 위치에 사용가능.

메인쿼리도 하나의 쿼리 블록이며 옵티마이저는 쿼리 블럭 단위로 최적화 수행.

하지만, 서브쿼리별로 따로 최적화했다고 해서 전체 쿼리가 최적화 됐다고 할 수 없기 때문에 전체를 바라보는 관점에서 쿼리를 이해하려면 서브쿼리는 풀어내야 한다.

4.4.2 서브쿼리와 조인

메인쿼리와 서브쿼리간에는 부모와 자식같은 종속적이고 계층적인 관계가 형성된다. 서브가 메인에 종속되기 때문에 단독으로 실행할 수 없고 메인에서 값을 받아서 반복적으로 필터링하는 방식을 사용한다.

필터 오퍼레이션

unnest 하지 않고 NL 조인 처럼 서브쿼리 하는 방식

• 필터방식으로 처리하기 위해 no_unnest 힌트 사용
• no_unnest : unnest(풀어내지) 말고 그대로 수행하라는 뜻.
• 필터 오퍼레이션은 NL 조인과 처리 루틴이 같다.
• 다른 점은
  • 조인에 성공하는 순간(exist) 진행을 멈추고 다음 로우 진행
  • 필터가 각 서브쿼리 수행에 대해서 캐싱 한다.

서브쿼리 Unnesting

unnest : 중첩된 상태를 풀어낸다

서브쿼리를 unnest 할 때는 unnest 힌트 사용

서브쿼리를 풀지 않고 쓰면 필터를 사용해야 하지만 unnest 하게 되면 일반 조인 처럼 다양한 최적화 기법을 사용할 수 있게 된다.

• NL 세미조인
  • 필터처럼 조인에 성공하는 순간 메인 쿼리의 다음 로우로 넘어간다
  • 
• leading
  • 필터를 쓰면 메인쿼리가 항상 드라이빙 집합이지만 unnest 하게되면 leading 힌트를 통해서 순서를 바꿀 수 있다.
  • 
  • 아래 쿼리로 변환된것과 같은 효과
  • 
• hash 조인
  • unnest 하고 해쉬 조인 적용
  • 

서브쿼리 Pushing

필터방식에서 서브쿼리는 순서가 정해지고 맨 마지막 단계에 처리된다. 하지만, push_subq 힌트를 사용하여 서브쿼리 필터링을 먼저 처리하게 해서 처리량을 줄일 수 있다. 따라서, push는 필터링 상태에서만 적용이 가능하다.

뷰와 조인

옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화 하기 때문에 전체 최적화가 안될 수 있다.

전월 이후 가입 고객 필터 조건이 뷰 바깥에 있기 때문에 뷰 안에서는 전체 고객에 대한 데이터를 읽어야 한다.

이 문제는 merge 힌트를 사용하여 해결 할 수 있다.

조인 조건 Pushdown

메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능이다.

아래와 같이 최적화 되었다고 생각하면 됨(문법은 틀림)

이렇게 되면 부분범위 처리 가능.

스칼라 서브쿼리 조인

스칼라 서브쿼리의 특징

select 쿼리를 품은 사용자 함수가 있다면 쿼리 건수만큼 재귀적으로 반복 실행된다.
하지만 스칼라 서브쿼리는 정확히 하나의 값만 반환한다. 함수처럼 재귀적이지 않으면서 컨텍스트 스위칭도 발생하지 않는다.

스칼라 서브쿼리 캐싱 효과

스칼라 서브쿼리로 조인할 때 입력값과 출력값은 캐싱된다.

입력값 : 서브쿼리에서 참조하는 메인 쿼리의 컬럼 값

필터 서브쿼리 캐싱관 같은 기능.
쿼리를 시작할 때 PGA 메모리에 공간을 할당하고 쿼리가 끝나면 반환된다.

아래와 같이 캐싱을 사용하여 내장함수의 비효율성을 상쇄시킬 수 있다.

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

스칼라 서브쿼리 캐싱도 캐싱이 가지는 단점을 그대로 가지고 있다.

• 입력 값의 종류가 적어서 해시 충돌 가능성이 적을 때 효과가 있다. 충돌이 많다면 당연히 성능이 낮아진다.

  • 좋은예) 거래구분코드가 20건이라서 입력값(t.거래구분코드)의 종류가 적다
    
  • 나쁜예) 고객이 100만명이라서 입력값(t.고객번호)의 종류가 많다.
    

• 메인 쿼리 집합이 매우 작은 경우

  • 메인쿼리 집합이 클수록 재사용성이 높아서 캐싱 효율이 좋다.
  • 나쁜예) 고객당 계좌가 많지 않은 경우
  • 

두개 이상의 값 반환

NL 조인 처럼 처리되기 때문에 부분범위 처리도 가능하고 캐싱의 효과를 쓸 수도 있어서 좋아보이지만 스칼라 서브쿼리는 두 개 이상의 값을 반환할 수가 없는 문제가 있다.

이런 경우 아래와 같은 방법을 사용한다.

구하는 값들을 문자열로 결합하고 바깥쪽 쿼리에서 다시 분리한다.

다른 방법은 인라인 뷰를 사용한다.

물론 앞에서 설명한 부분범위처리가 안되는 등의 인라인 뷰 머지의 문제가 있기 때문에 '조인 조건 pushdown' 기능을 통해서 문제를 해결할 수 있다.

스칼라 서브쿼리 Unnesting

스칼라 서브쿼리도 캐싱 효과가 미미하면 다른 조인 방식을 사용하기 위해 unnesting 해야 할 때가 있다.
쿼리가 길고 복잡하면 쉽지 않은데 다행히 오라클 12c부터 옵티마이저가 자동으로 쿼리를 변환해주는 기능이 생겼다.

`_optimizer_unnest_scalar_sq = true'

false로 설정하더라도 unnest 힌트로 유도 가능.

Unnesting 되었기 때문에 NL 조인이 아닌 해시조인 실행됨.