🔑 SQL 튜닝 - CH 4. 조인 튜닝 - 서브쿼리 조인

 

😁 서브쿼리 조인

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

옵티마이저는 비용을 평가하고 실행계획을 생성하기에 앞서 사용자로부터
전달받은 SQL을 최적화에 유리한 형태로 변환하는 작업

쿼리변환부터 진행한다

 

옵티마이저 엔진은 무거워지고 최적화에 소요되는 시간도 점점 늘어나고 있다

 

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

 

서브쿼리는 하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록을 말한다.
DBMS마다 조금씩 서브쿼리를 다르게 분류하는데 하단 그림에있는 오라클은 3가지로 분류한다

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. 인라인 뷰 : FROM 절에 사용한 서브쿼리

2. 중첩된 서브쿼리 : 결과집합을 한정하기 위한 WHERE 절에 사용한 서브쿼리
서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 '상관관계 있는 서브쿼리' 라고 말한다.

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

 

---

 

1 - 2 서브쿼리와 조인

 

필퍼 오퍼레이션

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

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

캐시 - 자주 필요한 데이터나 값의 복사본을 일시적으로 저장 하기 위해 사용하는 공간

캐싱 - 캐시 + ing  캐시를 사용하는것

 

서브쿼리 Unnesting

'nest' ,  "상자등을 차곡차곡 포개넣다"
반대의 의미인 'un'을 하용해 unnest는 "중첩된 상태를 풀어내라"

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

서브쿼리를 그래도 두면 필터 방식을 사용 할수 밖에 없지만
Unnestring 하고 나면 일반 JOIN문 처럼 다양한 최적화 기법을 사용 할수 있다

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'));

---

서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화 한다면

메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 

이때 서브쿼리에 필터 오퍼레이션이 나타난다. 

---

 

1 - 3 스칼라 서브쿼리 캐싱

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

◾ 조인 성능을 높이는데 큰 도움이 되는데 ,메인쿼리 집합이 아무리커도 조인할 데이터를 대부분 캐시에 찾는다면
   조인 수행횟수를 최소화 할수 있다

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

◾ 단점으로 스칼라 서브쿼리에 사용 하는 캐시도 메모리 공간이어서 캐시 공간이 늘 부족하고 쌓이면 성능이 나빠진다.