서브쿼리 변환

#핵심 요약

옵티마이저는 IN 서브쿼리를 조인으로 풀어(Unnesting) 실행할 수 있음
조인을 쓰지 않았는데 실행계획에 조인이 보이면 쿼리 변환이 일어난 것임

서브쿼리로 두면 실행계획은 "서브쿼리부터"와 "메인 쿼리부터" 두 가지뿐이지만, 조인으로 풀면 여러 조인 방법을 쓸 수 있음

서브쿼리 튜닝의 큰 그림:

NOT IN 안티 조인에서 서브쿼리 쪽을 해시 테이블로 만들려면 양쪽 컬럼에 NULL이 없다는 것을 보장해야 함(NOT NULL 제약 또는 IS NOT NULL 조건)

#개념과 동작 원리

실습 FILTER 설명: no_unnest no_push_subq로 메인 쿼리부터 실행하면 실행계획에 FILTER가 나옴
메인 쿼리의 행마다 서브쿼리 결과와 비교하며, 한 번 찾은 입력값의 결과는 메모리에 캐시해 같은 입력값이 반복되면 재사용함

실습 세미 조인 설명: NL 세미 조인은 메인 쿼리 행마다 서브쿼리 쪽에 해당 값이 있는지만 확인하고 찾으면 멈추므로, 빨리 찾을 수 있으면 성능이 좋음
실습에서는 대용량이 아니면 굳이 해시 세미 조인을 할 필요가 없다고 적었음

NOT IN의 NULL 의미는 NOT IN과 NULL에서 다룸

#필수 명령어 또는 SQL

#Unnesting 막기와 실행 순서

SELECT ename, sal FROM emp
 WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'DALLAS');                         -- 조인으로 변환됨

SELECT ename, sal FROM emp
 WHERE deptno IN (SELECT /*+ no_unnest */ deptno FROM dept WHERE loc = 'DALLAS');        -- 서브쿼리로, 메인부터

SELECT ename, sal FROM emp
 WHERE deptno IN (SELECT /*+ no_unnest push_subq */ deptno FROM dept WHERE loc = 'DALLAS');  -- 서브쿼리부터

결과 해석:

설명: 수행 빈도가 높은 SQL을 일부러 서브쿼리로 썼는데 옵티마이저가 해시 조인으로 풀어 버리는 경우 no_unnest로 막음

#세미 조인 방법 고르기

SELECT * FROM emp
 WHERE deptno IN (SELECT /*+ unnest nl_sj */ deptno FROM dept WHERE loc = 'NEW YORK');    -- 실습 23버퍼

SELECT * FROM emp
 WHERE deptno IN (SELECT /*+ unnest hash_sj swap_join_inputs(dept) */ deptno
                    FROM dept WHERE loc = 'NEW YORK');                                    -- dept를 해시 테이블로

결과 해석: 개별 실행 출력은 기재 없음

#QB_NAME으로 쿼리 블록 구분

SELECT /*+ QB_NAME(main) */ ename
  FROM emp
 WHERE empno IN (SELECT /*+ QB_NAME(sub) swap_join_inputs(EMP@SUB) */ mgr FROM emp);

결과 해석: 메인과 서브쿼리가 같은 emp라 실행계획만으로는 구분이 어려움
QB_NAME을 주면 Query Block Name / Object Alias 절에 EMP@MAIN, EMP@SUB로 표시되고, 힌트에서도 EMP@SUB처럼 가리킬 수 있음
실습 기록상 swap_join_inputs 없이 실행하면 HASH JOIN SEMI의 첫 자식(해시 테이블)이 EMP@MAIN이었음
실행계획에 이 절이 나오는 데 필요한 DBMS_XPLAN 형식 옵션은 기재 없음

#NOT IN 안티 조인과 NULL

SELECT ename, sal FROM emp
 WHERE deptno NOT IN (SELECT /*+ unnest hash_aj swap_join_inputs(dept) */ deptno
                        FROM dept WHERE loc = 'DALLAS');

결과 해석:

실습 결과: HASH JOIN ANTI NA, 첫 자식이 EMP로 swap_join_inputs가 적용되지 않았음

SELECT ename, sal FROM emp
 WHERE deptno NOT IN (SELECT /*+ unnest hash_aj swap_join_inputs(dept) */ deptno
                        FROM dept WHERE loc = 'DALLAS' AND deptno IS NOT NULL)
   AND deptno IS NOT NULL;

결과 해석:

실습 결과: HASH JOIN RIGHT ANTI, 첫 자식이 DEPT(해시 테이블)
실습 답은 IS NOT NULL 대신 두 테이블 deptno에 NOT NULL 제약을 거는 방법도 제시했음

ALTER TABLE emp  MODIFY deptno CONSTRAINT emp_deptno_nn NOT NULL;
ALTER TABLE dept MODIFY deptno CONSTRAINT dept_deptno_nn NOT NULL;

결과 해석:

실행계획의 NA가 무엇의 약자인지는 기재 없음

#데이터 양에 따른 세미 조인 선택 (실습 수치)

실습 환경: members 1,000명, orders 1만 건, 조건 amount BETWEEN 0 AND 500

SELECT * FROM members m
 WHERE m.member_id IN (SELECT /*+ unnest hash_sj */ member_id
                         FROM orders o
                        WHERE amount BETWEEN 0 AND 500);

결과 해석:

메인 쿼리와 서브쿼리가 모두 크면 해시 세미 조인이 유리함
운영 중 데이터가 바뀌어 좋은 실행계획이 흔들릴 수 있으므로 unnest hash_sj로 고정하는 방법을 씀
서브쿼리를 조인문으로 고치지 않고도 해시 조인의 장점을 쓸 수 있음

push_subq만 쓰면 쿼리 변환기가 서브쿼리를 풀어 세미 조인으로 바꿔 버려 의미가 없어지므로 no_unnest와 짝으로 씀

해시 조인은 작은 테이블이 PGA의 해시 테이블(build)이 돼야 함
orders 1만 건과 members(city = 'Busan')에서 no_unnest만 주면 FILTER로 MEMBERS를 4037번 읽어 Buffers 56955였음
unnest hash_sj swap_join_inputs(members)로 작은 쪽을 해시 테이블로 바꾸면 HASH JOIN RIGHT SEMI가 됨

건수가 적은 emp, dept 같은 경우 서브쿼리보다 조인으로 쓰고 leading(d) use_nl(e)로 Nested Loops를 유도하는 것이 시스템 전체에 가장 좋다고 수업 자료는 설명함

#IN을 EXISTS로, 또는 nl_sj로

emp(deptno) 인덱스가 없으면 IN과 EXISTS 모두 해시 세미 조인이라 차이가 없음
인덱스를 만들면 둘 다 Nested Loops 세미 조인으로 바뀌어, 서브쿼리에서 값을 찾으면 바로 멈춤
메인 쿼리에서 읽는 건수가 적고 서브쿼리 데이터가 많으면 EXISTS가 가장 좋고, IN을 고치지 않으려면 unnest nl_sj로 같은 효과를 냄

-- 인덱스 idx_members_city_id (city, member_id)
SELECT COUNT(*) FROM orders o
 WHERE EXISTS (SELECT /*+ unnest nl_sj */ 1
                 FROM members m
                WHERE m.member_id = o.member_id
                  AND m.city = 'Busan');

결과 해석:

실습 기록: IN + no_unnest는 FILTER 아래 INDEX RANGE SCAN을 4098번 반복해 Buffers 4156, EXISTS + unnest nl_sj는 Buffers 61이었음

연습 문제: bonus(emp의 empno, sal*2)에서 comm2 = 6000인 사원을 IN 서브쿼리로 찾을 때, EMP 전체 스캔 아래에서 BONUS를 14번 읽는 실행계획이 나오게 힌트를 줌

#사용 조건과 주의사항

실습 중간 요약의 첫 SQL은 WHERE deptno = 'DALLS'로 적혀 있어 의도(loc = 'DALLAS')와 다름
같은 페이지 8번 문제(직속 부하가 없는 사원)의 답은 IN을 그대로 써서 문제와 맞지 않음

실습 점심 문제(회원, 주문 테이블의 NOT IN을 해시 안티 조인으로, 회원을 해시 테이블로)의 답은 기재 없음
실습 기록상 튜닝 전(no_unnest) 버퍼는 57888임