서브쿼리 조인

안성은·2022년 5월 22일

sql 친절한SQL튜닝 튜닝

친절한SQL튜닝

목록 보기

6/9

서브쿼리 조인

서브쿼리 조인을 이해하는 출발점은 쿼리 변환에서 찾아야 한다

쿼리 변환: 옵티마이저가 SQL 분석해서 같은 결과 집합을 생성하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것

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

옵티마이저는 사용자로부터 전달받은 SQL을 쿼리 변환 후 Cost를 평가하고 실행계획 생성 => 쿼리 변환의 중요성
서브쿼리란 하나의 SQL문 안에 괄호로 묶은 별로의 쿼리 블록(Query Block)을 말한다.
서브 쿼리 종류
- 스칼라 서브쿼리: 한 레코드당 정확히 하나의 값을 반환하는 서브 쿼리다.
- 인라인 뷰: FROM 절에 사용한 서브 쿼리
- 중첩된 서브쿼리: 결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리
  - 서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 '상관관계 있는 서브 쿼리'
쿼리 변환을 하지 않았을 경우 예시
- 예시 1
  - 메인 쿼리와 EXISTS 서브 쿼리를 각각 최적화
- 예시 2
  - 메인 쿼리와 인라인 뷰를 각각 최적화

서브쿼리 별로 최적화한 쿼리가 전체적으로도 최적화됐다고 말할 수는 없다.

=> SQL을 최적화하기 위해서는 서브쿼리를 풀어서 전체적인 쿼리 관점에서 SQL을 최적화 해야한다.
=> 각각의 서브 쿼리는 독집적인 아닌 전체 쿼리의 종속성이라고 볼 수 있다.

4.4.2 서브쿼리와 조인

서브 쿼리를 풀어내지 말고 수행하면 옵티마이저는 필터 방식으로 서브 쿼리를 풀어낸다. => no_unnest 힌트 사용
- 필터 방식은 NL조인과 처리 루틴이 같지만 차이점이 존재한다.
1. 필터는 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 다음 로우를 계속한다는 점이다 다르다.
  => 메인 쿼리의 결과 집합(1)이 서브쿼리 집합(N) 수준으로 확장되는 현상을 막을 수 있다.
  => 중복현상 방지 => 1 : N가 아닌 1:1 구조
2. 필터는 캐싱기능을 갖는다.
  => 서브쿼리의 반환값을 캐시하는 기능
3. 서브쿼리는 메인쿼리의 종속적이기 때문에 항상 메인쿼리가 Outer Table 역할을 한다.
서브쿼리 Unnesting
- 서브쿼리를 그대로 두면 필터 방식을 사용할 수 밖에 없지만, Unnesting 하고 나면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있다.
  - unnesting을 하고 나면 필터와 마찬가지로 조인에 성공하는 순간 메인 쿼리의 다음 로우를 처리한다.
- unnesting을 하는 이유는 서브 쿼리를 메인 쿼리 집합보다 먼저 처리 될 수 있어서 서브 쿼리를 Outer 테이블로 사용할 수 있다.
  - 서브 쿼리에서 SORT(UNIQUE) 방식 사용 => 고객번호 중복 필터링
- unnesting 방식을 사용하면 조인 메소드 선택 가능, 조인 순서 선택 가능이라는 두가지 옵션을 가지게 된다. => 해시 조인 사용 예시
ROWNUM과 서브쿼리의 관계
조건절을 만족하는 레코드를 지정한 개수만큼 찾고 나면 조건 필터링을 더 진행하지 않고 멈추게 하고 싶을 때 rownum을 사용한다.
- 서브쿼리의 힌트(unnesting)보다 rownum이 더 강력한 명령어라서 서브쿼리에 rownum 명령어를 사용해서 필터를 적용하면 서브쿼리의 힌트가무시되는 것에 주의 해야한다.
서브쿼리 Pushing
- 서브쿼리는 항상 필터방식으로 처리되며, 대부분 실행계획 상에서 맨 마지막 단계에서 처리된다.
  => Pushing 서브쿼리를 사용해서 서브쿼리 필터링을 먼저 처리하도록 강제하는 기능, push_subq/no_push_subq 힌트로 제어
  => unnesting 되는 순간, push_subq 힌트는 무용지물이라서 push_subq와 no_unnest는 항상 같이 기술해야 한다.
- Pushing 힌트 예시
  
  1. 1000번의 조인 액세스
  2. 조인에 성공한 60000개의 주문 데이터
  3. (38002 + 95) = 38097 조인 과정에서 읽은 블록의 수
  4. 주문 데이터 필터링 후 3000개의 결과 집합
  => 대부분의 I/O가 조인 과정에서 발생.
  => 서브 쿼리 필터링을 먼저 처리 후 조인 단계로 처리하는 것이 효율적이라는 것은 직관적으로 파악 가능.
  => push_subq 힌트 사용을 통해서 서브 쿼리 필터링 먼저 가능.
  
  1. 서브쿼리 필터링한 결과 150건
  2. 주문 테이블과 조인 횟수도 150건
  3. 주문 데이터도 3000개만 읽은 것 확인
  4. 총 읽은 블록 수도 결과적으로 1903건으로 감소

서브쿼리 필터링을 먼저 처리함으로써 조인 단계로 넘어가는 로우 수를 줄여서 성능을 향상 시킬 수 있다라는 결과 도출

4.4.3 뷰 조인

최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다.
- 뷰 조인을 사용하는 예시
  - 문제 상황: 거래 테이블에서 '당월 발생한 거래'의 모든 데이터를 읽은 후 그룹화 진행 후 '전월 이후 가입한 고객'을 필터링
  - merge 힌트를 이용해 뷰를 메인 쿼리와 머징
  - 고객 테이블을 먼저 읽은 후 인덱스를 이용해 전월 이후 가입한 고객만 읽고, 해당 고개만 거래 테이블과 조인
    => 조인에 성공한 전체 집합을 Group By 하고서야 데이터를 출력할 수 있어서 부분범위 처리가 불가능
    => 부분범위 처리가 불가능해서 대량의 데이터가 존재한다면 해시 조인 사용이 효율적

조인 조건 Pushdown(11g 버전 이후 출시)
- 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능이다.
- 실행계획
- 인라인 서브쿼리에서 조인 후 Groub By를 수행하기 때문에 부분범위 처리가 가능하다.
  - 변환된 쿼리

4.4.4 스칼라 서브쿼리 조인

스칼라 서브쿼리의 특징

SELECT 쿼리를 메인쿼리 건수 만큼 재귀적으로 반복 실행

select empno, ename, sal, hiredate
     , GET_DNAME(e.deptno) as dname
from emp e
where sal >= 2000

스칼라 서브쿼리의 캐싱을 이용해서 튜닝하는 방법

select empno, ename, sal, hiredate
     , (select GET_DNAME(e.deptno) from dual) as dname
from emp e
where sal >= 2000

스칼라 서브쿼리는 메인쿼리 레코드마다 정확히 하나의 값만 반환한다.
컨텍스트 스위칭 없이 메인쿼리와 서브쿼리를 한 몸체처럼 실행

select empno, ename, sal, hiredate
     , (select d.dname from dept d where d.deptno = e.deptno) as dname
from emp e
where sal >= 2000

select /*+ ordered use_nl(d) */ e.empno, e.ename, e.sal, e.hiredate, d.dname
from emp e, dept d
where  d.deptno(+) = e.deptno
and    sal >= 2000

join되는 모든 emp의 값을 조회하고 값이 없는 dname은 NULL로 표시

스칼라 서브쿼리 캐싱 효과
- 스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시에 저장해둔다.
  - 캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장
    => 스칼라 서브쿼리 캐싱은 필터 서브쿼리 캐싱과 같은 기능
    => 메인쿼리 집합이 아무리커도 조인할 데이터를 대부분 캐시에서 찾아서 조인 수행횟수를 최소화할 수 있다.
스칼라 서브쿼리 캐싱 부작용
- 스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과적이다.
  => 캐시 충돌이 잦으면 캐시를 매번 확인하는 비용 때문에 오히려 성능이 나빠지고, 메모리와 CPU 사용률 증가
- 스칼라 캐시 공간은 9i 버전아래는 256개의 엔트리, 10g 이후로는 입력과 출력 값의 크기와 파라미터에 의해 사이즈를 결정한다.
- 스칼라 서브쿼리 캐싱의 좋은 예시
  1. 거래구분코드가 20개 존재 -> 캐시 모두 저장 가능
  2. 메인쿼리에서 거래 데이터를 읽을 때 첫 조회빼고 모두 캐시에서 데이터를 찾기 때문에 조인 성능 향상
- 스칼라 서브쿼리 캐싱의 나쁜 예시
  1. 고객 데이터가 100만건이면 캐시에 담을 수 없다.
  2. 캐시 충돌 자주 발생
  3. 일반 조인문 보다 불필요한 자원 낭비 => 성능 악화
- 스칼라 서브쿼리는 캐싱이 장점인데 메인 쿼리 집합이 작다면 큰 효과를 볼 수없다.
두 개 이상의 값 반환
- 스칼라 서브쿼리는 두 개 이상의 값을 반환할 수 없다는 제약이 존재한다.
  - 거래 테이블에서 같은 데이터를 반복해서 읽는 비효율 발생
  - 튜닝1 => 구하는 값을 문자열 치환 후 바깥쪽 액세스 쿼리에서 substr 함수로 다시 분리
  - 튜닝2 => 스칼라 서브쿼리를 인라인 뷰로 변경
  - 튜닝3 => 뷰를 사용할 경우 11g 이후로 Pushdown 기능 사용
    (뷰 머징X) -> 당월 거래 전체 데이터 읽어야하는 문제
    (뷰 머징O) -> 조인 후 Group By 처리 = 부분 범위 처리 X
스칼라 서브쿼리 Unnesting
- 스칼라 서브쿼리도 NL 방식으로 조인하므로 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있다.
- 병렬 쿼리에서는 스칼라 서브쿼리가 효율적이지 않다.
  - 쿼리 단위로 캐시를 처리하기 때문에 캐시 데이터 중복 및 싱글 쿼리보다 더 많은 랜덤 I/O가 발생할 확률이 놓다.
- 오라클 12c 버전 부터는 스칼라 서브쿼리도 unnesting이 사용가능해서 대용량 데이터에 조인 방식에 Hash Join 사용이 가능하다.