[SQL] 튜닝 - 서브쿼리 조인🔥

서브쿼리 조인

 

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

서브쿼리를 DBMS마다 조금씩 다르게 분류하는데, 오라클을 기준으로 하겠다.

 

select  c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
      , (select 고객분류명 from 고객분류 where 고객분류코드 = 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.고객번호
and   exist ( select 'x'
              from   고객변경이력 h
              where  h.고객번호 = c.고객번호
              and    h.변경사유코드 = 'ZCH'
              and    c.최종변경일시 between h.시작일시 and h.종료일시 );  /* 중첩된 서브쿼리 */

1. 인라인 뷰(Inline View)
   FROM 절에 사용한 서브쿼리
   
   
2. 중첩된 서브쿼리(Nested Subquery) 
   결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리
   서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 '상관관계(Correlated) 서브쿼리'라고 한다.
3. 스칼라 서브쿼리(Scalar Subquery)
   한 레코드당 정확히 하나의 값을 반환하는 서브쿼리
   주로 SELECT-LIST에서 사용

 

필터 오퍼레이션

서브쿼리는 단독으로 실행할 수 없기 때문에 필터를 사용해 메인쿼리에서 값을 받아 실행해야 한다.

• 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 다음 로우를 계속 처리한다.
• 필터는 캐싱기능을 갖는다.

 

서브쿼리 Unnesting

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

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

select c.고객번호, c.고객명
from   고객 c
where  c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and    exist (
         select /*+ unnest nl_sj */ 'x'
         from 거래
         where 고객번호 = c.고객번호
         and 거래일시 >= trunc(sysdate, 'mm'));
         
Execution Plan
------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=384 Card=190 Bytes=11K)
1   0   NESTED LOOPS (SEMI) (Cost=384 Card=190 Bytes=11K)
2   1    TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=4 Card=190 ...)
3   2     INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=2 Card=190)
4   1    INDEX (RANGE SCAN) OF '거래_X01' (INDEX) (Cost=2 Card=427K Bytes=9M)

 

 

스칼라 서브쿼리 캐싱

• 스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화 하기위해 값을 내부 캐시에 Map으로 저장한다. ( 빠르다 )
• 스칼라 서브쿼리에 사용하는 캐시도 메모리 공간이기 때문에 많이 쌓이면 오히려 성능이 나빠진다.