조인 튜닝 - 서브쿼리 조인

실무에서는 복잡한 서브쿼리와 조인하는 형태를 흔히 보게 된다.

 

서브쿼리 조인을 어떻게 처리하는지 이해하고, 원하는 방식으로 실행계획을 제어할 수 있어야 튜닝도 가능하다.

 

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

 

옵티마이저는 비용을 평가하고 실행계획을 생성하기에 앞서 전달받은 SQL을 최적화에 유리한 형태로 변환하는 쿼리 변환을 진행한다. 

 

쿼리 변환은 옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다.

 

서브쿼리는 세 가지로 분류할 수 있다. 이는 아래 그림과 같다.

 

친절한 SQL 튜닝

 

1. 인라인 뷰 : FROM 절에 사용한 서브쿼리 

 

2. 중첩된 서브쿼리 : 결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리 (특히, 메인쿼리 칼럼을 참조하는 형태를 상관 서브쿼리라고 한다)

 

3. 스칼라 서브쿼리 : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리이다. 주로 SELECT-LIST에서 사용하지만, 몇 가지 예외사항을 제외하면 칼럼이 올 수 있는 대부분 위치에 사용할 수 있다.  (보통 상관느낌)

 

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

 

예를 들어, 쿼리 변환하지 않고 그대로 수행하면, 메인 쿼리와 EXISTS 서브쿼리를 각각 최적화한다. 서브쿼리별로 최적화한 쿼리가 전체적으로 최적화됐다고 말할 수는 없다. SQL을 최적화할 때 옵티마이저가 나무가 아닌 숲 전체를 바라보는 관점에서 쿼리를 이해하려면 먼저 서브쿼리를 풀어내야만 한다.

 

---

 

2. 서브쿼리와 조인

 

메인쿼리와 서브쿼리 간에는 부모와 자식이라는 종속적이고 계층적인 관계가 존재한다.

메인쿼리 건수만큼 값을 받아 반복적으로 필터링하는 방식으로 실행해야 한다.

 

2.1 필터 오퍼레이션

 

- 서브쿼리를 필터 방식으로 처리할 때 no_unnest 힌트를 사용한다.

친절한 SQL 튜닝

 

필터 오퍼레이션은 기본적으로 NL조인과 처리 루틴이 같다. 

NL조인처럼 부분범위 처리도 가능하다. 

 

NL 조인과 차이점

1. 메인쿼리의 한 로우가 서브쿼리 한 로우와 조인에 성공하는 순간 진행을 멈춘다.
2. 캐싱기능을 갖는다. 서브쿼리 입력 값에 따른 반환 값(true or false)을 캐싱하는 기능이다. -> 따라서 서브쿼리 수행하기 전에 항상 캐시부터 확인한다.  (캐싱은 쿼리 단위로 이루어짐, 쿼리를 시작할 때 PGA 메모리 공간을 할당하고, 쿼리를 수행하면서 공간을 채워나간다.
3. 필터 서브쿼리는 일반 NL 조인과 달리 메인쿼리에 종속되므로 조인 순서가 고정된다 (메인쿼리가 드라이빙 집합)

 

2.2 서브쿼리 Unnesting

 

- 서브쿼리를 Unnesting하려면, unnest 힌트를 사용하자 

친절한 SQL 튜닝

 

Unnesting은 중첩한 상태를 풀어내는 서브쿼리 Flattening 기능이다.

메인과 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨로 만들어 준다는 의미이다. 

서브쿼리를 그대로 두면 필터 방식 밖에 사용할 수 없지만, Unnesting하면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있다. 

 

NL 세미 조인은 기본적으로 NL 조인과 같은 프로세스다.

조인에 성공하는 순간 진행을 멈추고 메인 쿼리의 다음 로우를 계속 처리한다는 점만 다르다.

 

이는 앞서 필터 오퍼레이션 기능이기도 하다. (오라클 10부터는 캐싱기능도 갖게 되어, 사실 큰 차이가 없다)

Unnesting의 좋은점은 NL 세미 조인 외에도 다양한 방식으로 조인을 할 수 있기 때문이다. 

(필터는 메인쿼리가 드라이빙 집합이지만, Unnesting은 이를 설정할 수 있다)

 

친절한 SQL 튜닝

친절한 SQL 튜닝

-> 서브쿼리가 조금 변환되었는데, 고객번호 중복을 제거하기 위해 distinct가 추가된 것을 볼 수 있다. 

 

이와 같이 Unnesting을 사용해서 서브쿼리와 메인쿼리를 같은 레벨로 만들면, 다양한 조인 메서드를 선택할 수 있고, 순서도 정할 수 있다. 옵티마이저는 많은 조인 테크닉을 가지기 때문에 조인 형태로 변환 했을 때 필터 오퍼레이션보다 더 좋은 실행경로를 찾을 가능성이 높아진다. 

 

2.3  서브쿼리 Pushing

 

Unnesting 되지 않은 서브쿼리는 항상 필터 방식으로 처리되며, 대개 실행계획 상에서 맨 마지막 단계에 처리된다.

서브쿼리 필터링을 먼저 처리하여 조인 단계로 넘어가는 로우 수를 크게 줄일 수 있다면 성능이 향상될 것이다. 

이를 위한 기능이 Pushing이다.

 

서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능이다. 

push_subq/no_push_subq와 같은 힌트로 사용할 수 있다. (Unnesting 되지 않은 서브쿼리에만 작동함 따라서 no_nunest 힌트와 같이 기술)

 

---

2. 인라인 뷰와 조인 

 

2.1 merge

 

최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰쿼리 변환하지 않으면, 뷰 쿼리 블록을 독립적으로 최적화한다. 

뷰를 독립적으로 최적화하니, 테이블과 조인은 다음에 처리한다.

 

문제는 뷰 안에서 조인에 필요하지 않은 모든 데이터를 불러올 수 도 있다는 말이다. 이때 머징을 사용하면, 인라인뷰 밖에 있는 조건을 인라인뷰 안에서 작동하도록 강제할 수 있다. 

 

힌트는 merge를 사용한다 (no_merge도 있다)

 

select c.고객번호 --이하 생략
from 고객 c
, (select 고객번호, avg(거래금액) 평균거래, min(거래금액) 최소거래, max(거래금액) 최대거래
   from 거래 
   where 거래일시 >= trunc(sysdate,'mm')
   group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c. 고객번호

 

뷰를 독립적으로 최적화하니, 당월 거래 전체를 읽어 고객번호로 Group by를 처리한다. 조인은 그 다음이다. 

 

친절한 SQL 튜닝

문제는, 고객 테이블에서 전월 이후 가입한 고객을 필터링하는 조건이 인라인 뷰 바깥에 있다는 것이다. 

이 조건이 있는데도 인라인 뷰 안에서는 당월 거래한 모든 고객의 거래 데이터를 먼저 처리한다.

 

merge를 사용하면 쿼리가 다음과 같이 변환된다.

select c.고객번호, c.고객명 --이하 생략
from 고객 c, 거래 t
where c.가입일시 >= trunc(add_monts(sysdate,-1),'mm')
and t.고객번호 = c.고객번호
and t.거래일시 >= trunc(sysdate, 'mm')
group by c.고객번호, c.고객명

 

가장 먼저 엑세스하는 고객_X01인덱스는 가입일시가 선두 컬럼이다. 이를 먼저 처리하고, 다음에 조인을 하도록 풀었다. 

단점은 조인에 성공한 전체 집합을 Group by 해야 데이터를 출력할 수 있다 (즉 부분범위 처리가 불가능 하다) 

부분범위 처리가 불가능한 상황에서 NL조인은 좋은 선택이 아니다.

 

따라서, 뷰를 머징하고, 조인은 해쉬로 푸는 방법이 제일 좋다. 

 

2.2 Pushdown

 

11g 이후 조인 조건 Pushdown 쿼리 변환 기능이 작동한다. 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능이다. 

 

힌트는 push_pred이다. (no_merge와 같이 사용한다) 

 

select c.고객번호 --이하 생략
from 고객 c
, (select 고객번호, avg(거래금액) 평균거래, min(거래금액) 최소거래, max(거래금액) 최대거래
   from 거래 
   where 거래일시 >= trunc(sysdate,'mm')
   group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c. 고객번호

이 쿼리를 

select c.고객번호 --이하 생략
from 고객 c
, (select 고객번호, avg(거래금액) 평균거래, min(거래금액) 최소거래, max(거래금액) 최대거래
   from 거래 
   where 거래일시 >= trunc(sysdate,'mm')
   and 고객번호 = c.고객번호 -- 추가됨
   group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c. 고객번호

 

이와 같은 형태로 변환해준다. 

 

이와 같이 처리하면, 건건이 조인하며, Group By를 수행하고, 중간에 멈출수도 있다. 즉, 부분범위 처리가 가능하다. 

 

뷰를 독립적으로 실행할때 처럼 모두 읽거나, 뷰를 머징할 때 처럼 조인에 성공한 전체 집합을 Group By하지 않는다.

 

---

 

4. 스칼라 서브쿼리와 조인

 

4.1 스칼라 서브쿼리의 특징 

 

스칼라 서브쿼리는 메인쿼리 레코드마다 정확하게 하나의 값만 반환한다. 

메인쿼리 건수만큼 서브쿼리 테이블을 반복해서 읽는다. 

스칼라 서브쿼리는 마치 아우터 조인처럼 작동한다. (상관 조건에 누락된 데이터는 null 처리되는 부분)

 

4.2 스칼라 서브쿼리 캐싱 효과

 

스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시에 저장해 둔다. 

조인할 때마다 캐시에서 입력 값을 찾아보고, 찾으면 저장된 출력 값을 반환한다.

(스칼라 서브쿼리의 입력 값은, 그 안에서 참조하는 메인쿼리의 컬럼 값이다)

 

캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해둔다.

캐싱은 쿼리단위로 이루어진다!

 

스칼라 서브쿼리 캐싱은 필터 서브쿼리 캐싱과 같은 기능이다. (조인 성능을 높이는 데 큰 도움이 된다.) 

 

-> 사용자  함수 호출 팁

(select GET_DNAME(e.deptno) from dual) -- 스칼라 서브쿼리 캐싱 사용

 

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

 

스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과가 있다.

 

반대의 경우라면 캐시를 매번 확인하는 비용 때문에 오히려 성능이 나빠지고 CPU 사용률만 높게 만든다.