[SQLP] 서브쿼리와 성능

서브쿼리 동작 수행 방식

1. Filter 동작 방식

-메인 쿼리에서 추출되는 데이터의 수 만큼 서브쿼리가 반복적으로 수행되어 처리

-메인 쿼리의 추출 건수가 100건이면, 서브쿼리는 최대 100번 수행한다.

-서브 쿼리에 입력되는 메인 쿼리의 input 값의 number of distinct value 가 작다면 조인 수행 방식보다 더 효율적일 수 있다. (스칼라 서브쿼리 캐싱)

 

예시)

사원 정보 테이블(emp)에서 자신이 속한 부서의 평균 급여보다 많은 급여를 받는 사원들의 직원 이름, 급여, 부서번호를 출력한다.

select a.ename, a.sal, a.deptno
from emp a 
where a.sal>(select avg(b.sal)
                from emp b
                where a.deptno = b.deptno);
                
===================================                
KING	5000	10
JONES	2975	20
SCOTT	3000	20
FORD	3000	20
ALLEN	1600	30
BLAKE	2850	30
===================================    
##스미스 사원의 출력 여부를 결정하기 위해서 서브쿼리를 실행하여, 
##스미스 의 급여가 부서번호 20인 사원들의 평균 급여보다 큰지를 확인한다


##explain  filter 발생한 것을 알 수 있다.
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    39 |     7  (29)| 00:00:01 |
|   1 |  MERGE JOIN                  |                |     1 |    39 |     7  (29)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |    14 |   182 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | IDX_EMP_DEPTNO |    14 |       |     1   (0)| 00:00:01 |
|*  4 |   FILTER                     |                |       |       |            |          |
|*  5 |    SORT JOIN                 |                |     3 |    78 |     5  (40)| 00:00:01 |
|   6 |     VIEW                     | VW_SQ_1        |     3 |    78 |     4  (25)| 00:00:01 |
|   7 |      SORT GROUP BY           |                |     3 |    21 |     4  (25)| 00:00:01 |
|   8 |       TABLE ACCESS FULL      | EMP            |    14 |    98 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

 

메인쿼리 기준으로 봤을 때,

## 메인 쿼리 emp a
SMITH	800		20
ALLEN	1600	30
WARD	1250	30
JONES	2975	20
MARTIN	1250	30
BLAKE	2850	30
CLARK	2450	10
SCOTT	3000	20
KING	5000	10
TURNER	1500	30
ADAMS	1100	20
JAMES	950		30
FORD	3000	20
MILLER	1300	10

##서브쿼리 b
SMITH	800		20
ALLEN	1600	30
WARD	1250	30
JONES	2975	20
MARTIN	1250	30
BLAKE	2850	30
CLARK	2450	10
SCOTT	3000	20
KING	5000	10
TURNER	1500	30
ADAMS	1100	20
JAMES	950		30
FORD	3000	20
MILLER	1300	10

 

b테이블에서 급여 총 평균을 구한 다음 a 테이블에서 그 평균 구한 값과 사원 하나하나를 비교하여 평균값보다 높은 사원을 구한다. 

근데 첫번째 행이 20 이여서 평균값을 계산하고 3번째 행에 왔을 때 동일한 20인데, 첫번 째 행처럼 계산하지는 않는다.

옵티마이저는 알아서 동일한 값인걸 기억하고 계산을 넘긴다 

이를 스칼라 서브쿼리 캐싱이다. 

캐싱 정보는 쿼리 단위로 이루어지고 쿼리 시작 시 pga 메모리에 공간을 할당한다.

 

- 서브쿼리 unnesting 가 일어나지 않는 경우 반드시 필터는 메인 쿼리의 테이블부터 진행한다.