서브쿼리 동작방식
서브쿼리의 동작 방식은 크게 Filter 동작방식과 조인 동작방식이 있다.
어떤 동작방식으로 처리되느냐에 따라 SQL의 성능 차이가 크게 발생할 수 있다.
Filter 동작방식
Main SQL에서 추출된 데이터 건수만큼 서브쿼리가 반복적으로 수행되며 처리되는 방식이다.
예를 들어, Main SQL에서 추출된 데이터의 건수가 10만이라면 서브쿼리는 최대 10만번 수행된다.
- 항상 메인 쿼리가 먼저 수행되며, 서브쿼리는 메인 쿼리에서 추출된 데이터의 값을 전달받아 매번 확인하는 형태로 수행됨
- Oracle이 내부적으로 Filter 동작방식에 대해 Filter Optimization이라는 최적화 작업을 수행
따라서, Main SQL의 추출 결과가 많더라도 서브쿼리의 Input값(메인쿼리와 서브쿼리와의 where절 조건의 결과 개수)이 적다면 성능 문제가 발생하지 않는다. - 서브쿼리의 조인 연결 컬럼에 인덱스가 있다면, Table Full Scan을 하지 않는다.
메인 쿼리의 추출 결과가 많고, Input의 종류가 많다면 Filter 동작방식은 성능이 좋지 않다!
Join 동작방식
Filter 동작방식은 수행순서가 고정되어 있지만, Join 동작방식은 다양한 조인 방식 중 선택할 수 있어 '가변성'이라는 특징이 존재한다.
서브쿼리의 실행계획 제어하는 힌트
- NO_UNNEST: 서브쿼리를 Filter 동작방식으로 처리
- UNNEST: 조인 동작방식으로 처리
MINNUS 대신 NOT EXISTS 사용하기
MINUS
SELECT ...
FROM A
MINUS
SELECT ...
FROM B- 수행방식
- A에서 데이터 추출
- 추출된 데이터 SORT 연산
- B에서 데이터 추출
- 추출된 데이터 SORT 연산
- 2와 4 데이터 비교 후 최종 데이터 추출
- 수행 순서: A → B 순으로 고정되어 있음
NOT EXISTS
SELECT ...
FROM A
WHERE NOT EXISTS (
SELECT ...
FROM B
WHERE A.XXX = B.XXX)- 수행 방식
- A에서 데이터 추출
- 1에서 추출한 데이터와 서브쿼리의 B 데이터와 비교 후 최종 데이터 추출
- 수행 순서: 변경 가능(A → B, B → A)
서브쿼리 VS JOIN
데이터에 따라 JOIN을 사용하면 유리한 경우가 있고 반대로 서브쿼리를 사용하면 유리한 경우가 있다.
서브쿼리를 사용해야 할 때
확인자 역할을 하는 테이블은 서브쿼리를 사용해서 처리하는 것이 좋다.
- 확인자 역할?
SELECT절에 컬럼이 없고 단순히 WHERE 절의 조건으로만 사용되는 테이블
최대한 데이터가 뻥튀기 되지 않고 중복 값을 제거한 후에 사용하는 것이 좋다!
JOIN을 사용해야 할 때
WHERE절에 서브쿼리가 많을 땐 JOIN을 사용하자.
- 서브쿼리가 한 개만 포함된 SQL이라면, Optimizer가 하나의 서브쿼리에 대한 cost를 계산하기 때문에, 비교적 효율적인 실행계획을 세울 수 있다.
- 서브쿼리가 많다면, Optimizer가 SQL에 대한 최적의 실행계획을 수립하기 어렵다.
서브쿼리들이 가질 수 있는 모든 조합에 대해 cost를 계산해야 하기 때문에 이러한 과정이 과부하가 될 수 있어 과감하게 정확도를 포기하고, 모든 서브쿼리를 unnest 수행한 것과 모두 수행하지 않은 것 두 가지의 cost만 계산하기 때문
참고
SQL 튜닝의 시작
