4.4 서브쿼리 조인

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

최근 옵티마이저는 비용을 평가하고 실행계획을 생성하기에 앞서, 사용자로부터 전달받은 SQL을 최적화에 유리한 형태로 변환하는 작업, 쿼리변환부터 진행한다. 쿼리변환은 옵티마이저가 SQL을 분석해 같은 결과 집합을 생성하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다.

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

1. 인라인 뷰(Inline View)
   : FROM 절에 사용한 서브쿼리.
2. 중첩된 서브쿼리(Nested Subquery)
   : WHERE 절에 사용한 서브쿼리. 메인쿼리 컬럼을 참조하는 형태를 상관관계 있는(Correlated) 서브쿼리 라고 부른다.
3. 스칼라 서브쿼리(Scalar Subquery)
   : 한 레코드 당 정확히 하나의 값을 반환하는 서브쿼리. 주로 SELECT-LIST에서 사용하지만 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다.

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

---

4.4.2 서브쿼리와 조인

필터 오퍼레이션

no_unnest는 서브쿼리를 풀어내지말고 그대로 수행하라고 옵티마이저에 지시하는 힌트다. 필터 오퍼레이션은 기본적으로 NL조인과 처리 루틴이 같다. 따라서 위 실행계획에서 'FILLTER' 를 'NESTED LOOPS' 로 치환하고 해석하면 된다.

차이가 있다면 첫째, 필터는 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 다음 로우를 계속 처리한다. 둘째, 필터는 캐싱기능을 갖는다. 이는 필터 처리 결과, 즉 서브쿼리 입력 값에 따른 반환 값(true 또는 false)를 캐싱하는 기능이다. 서브쿼리를 수행하기 전에 항상 캐시에서부터 true/false 여부를 확인함으로써 서브쿼리를 수행하지 않아도 되므로 성능을 높이는 데 큰 도움이 된다. 마지막으로, 필터 서브쿼리는 일반 NL조인과 달리 메인쿼리에 종속되므로 조인 순서가 고정된다. 항상 메인쿼리가 드라이빙 집합이다.

서브쿼리 Unnesting

그냥 두어도 옵티마이저가 대개 Unnesting을 선택하지만, unnest 힌트를 사용하여 명시할 수 있다. 서브쿼리 Unnesting은 메인과 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨로 만들어 준다는 의미에서 서브쿼리 Flattening이라고도 한다. 필터방식은 항상 메인쿼리가 드라이빙 집합이지만, Unnesting된 서브쿼리가 드라이빙되도록 leading 힌트를 사용할 수 있다.(자세한 건 page 305 참고)
-> rownum은 조건절을 만족하는 레코드를 지정한 개수만큼 찾고 나면 조건 필터링을 더 진행하지 않고 멈추기 때문에 잘 사용하면 쿼리 성능을 높이는 데 아주 효과적이다. 하지만 rownum을 서브쿼리에 쓰면 옵티마이징 기능을 사용하지 못하게 막는 효과(부작용)가 있다. 따라서 독이 될 수 있으므로 서브쿼리에 rownum은 함부로 쓰지 않는게 좋다.

서브쿼리 Pushing

위에서 말한 것처럼 Unnesting 되지 않은 서브쿼리는 항상 필터 방식으로 처리되며, 대개 실행계획 상에서 맨 마지막 단계에 처리된다.(관련 예는 page308 참고) Pushing 서브쿼리는 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능이며, push_subq/no_push_subq 힌트로 제어한다. 이 기능은 Unnesting 되지 않은 서브쿼리에만 작동한다. 따라서 push_sbq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 사용법이다.

---

4.4.3 뷰(View)와 조인

최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다.
(자세한건 311page 참고)

조인조건 Pushdown

11g 이후로 '조인 조건 Pushdown'이라는 쿼리 변환 기능이 작동한다. 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능이다. 이 기능을 제어하는 힌트는 push_pred 이다. 옵티마이저가 뷰를 머징하면 힌트가 작동하지 않으니 no_merge 힌트를 함께 사용하는 습관이 필요하다.(자세한건 313page 참고)

---

4.4.4 스칼라 서브쿼리 조인

(1) 스칼라 서브쿼리의 특징

page317 참고

(2) 스칼라 서브쿼리 캐싱 효과

스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시(Query Excecution Cache)에 저장해 둔다. 조인할 때마다 일단 캐시에서 '입력 값'을 찾아보고, 찾으면 저장된 '출력 값'을 반환한다. 캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해 둔다. 이런 캐싱 메커니즘은 조인 성능을 높이는 데 큰 도움이 된다. 메인쿼리 집합이 아무리 커도 조인할 데이터를 대부분 캐시에서 찾는다면, 조인 수행횟수를 최소화할 수 있기 때문이다. 

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

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

SELECT (SELECT 거래구분명 FROM ....)
FROM .....
-- 거래구분명이 20개 이하일 경우 캐시에 모두 저장하고도 남으므로 메인쿼리에서 데이터를 읽는 동안 조인 액세스는 최초 한 번씩만 발생한다.

SELECT (SELECT 고객명 FROM....)
FROM....
-- 고객이 100만 명일 경우 데이터를 읽는 동안 캐시를 매번 탐색하지만 대부분 데이터를 찾지 못해 결국 조인을 해야만한다.
-- 불필요한 캐시 탐색 떄문에 일반 조인문보다 느리고 불피료하게 자원만 낭비하는 셈이다.

(4) 두 개 이상의 값 반환

스칼라 서브쿼리에는 치명적인 제약이 하나 있다. 두 개 이상의 값을 반환할 수 없다는 제약이다.
(자세한 건 page321 참고)

(5) 스칼라 서브쿼리 Unnesting

스칼라 서브쿼리도 NL 방식으로 조인하므로 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있다. 특히 대량 데이터를 처리하는 병렬(Parallel)쿼리에선 해시 조인으로 처리해야 효과적이기 때문에 스칼라 서브쿼리를 사용하지 않아야 한다. 오라클 12c부터 스칼라 서브쿼리도 Unnesting이 가능해졌다. 옵티마이저가 사용자 대신 자동으로 쿼리를 변환해 주는 것이다.(힌트를 통해 제어할 수 있다. 자세한 건 page326 참고)