스칼라 서브쿼리

스칼라 서브쿼리

select절에 사용된 서브쿼리를 스칼라 서브쿼리라 한다.

스칼라 서브쿼리의 특성

1. 스칼라 서브쿼리의 최대 실행 횟수는 메인 쿼리의 결과 건수와 같다.
스칼라 서브쿼리는 Deterministic(결정론적인) 속성을 가지고 있다. 동일한 입력값에 대한 결과값이 항상 같다는 것을 보장한다. 이러한 특성으로 인해, 스칼라 서브쿼리는 결과 값을 Multi Buffer에 저장해두고 이후 동일한 입력값으로 수행할 경우 Multi Buffer에 저장해놓았던 값을 가져와서 출력하게 된다.

SELECT ID, (SELECT NAME FROM COUNTRY WHERE CODE = 'ABW') AS COUNTRY_NAME
FROM CITY
LIMIT 10

위 쿼리에서 COUNTRY_NAME 을 추출하는 스칼라 서브쿼리는 CODE가 ABW인 COUNTRY테이블의 NAME을 항상 결과값으로 추출한다. 따라서, 이 NAME 컬럼의 값을 Multi Buffer에 저장해두고 이 값을 가져와서 9번 출력하게 된다.

2. 추출되는 데이터는 항상 1건만 유효하다.
스카라 서브쿼리에서 추출되는 데이터가 1건을 초과할 경우, 에러가 발생한다.

SELECT E.NAME, (SELECT D.NAME FROM DEPT D.EMPLOYEE_ID = E.ID) AS D_NAME
FROM EMP E

• SELECT D.NAME FROM DEPT D.EMPLOYEE_ID = E.ID 결과가 2개 이상이라면, 에러가 발생한다.
  
  

3. NULL값을 추출해도 된다.

SELECT E.NAME, NVL((SELECT D.NAME FROM DEPT D.EMPLOYEE_ID = E.ID), 'ISNULL') AS D_NAME
FROM EMP E

실무에서 흔히 마주치는 스칼라 서브쿼리의 성능 문제는 크게 두 가지 유형이다.

• 스칼라 서브쿼리의 수행 위치에 따른 성능 문제
• 스칼라 서브쿼리와 조인관계에서 발생하는 성능 문제

스칼라 서브쿼리를 수행할 메인 쿼리의 건수를 최대한 줄이자.

스칼라 서브쿼리는 메인 쿼리의 결과 건수만큼 실행한다는 특성을 통해 수행 위치를 바꿔서 성능을 향상시켜보자.

SELECT C1, C2, C3, (SELECT T2.C3 FROM TEST2 T2 WHERE T2.C1=T1.C1) AS T2_C3
FROM TEST1 T1
ORDER BY C1, C2
LIMIT 10
;

TEST1의 총 데이터 건수가 100건이라 했을 때, 위 쿼리에서 스칼라 서브쿼리는 100번 수행 후 나온 결과를 정렬 후에 10건을 추출한다.

이 쿼리를 개선해보자.

SELECT A.*, (SELECT T2.C3 FROM TEST2 T2 WHERE T2.C1=A.C1) AS T2_C3
FROM ( SELECT C1, C2, C3
	   FROM TEST1 T1
       ORDER BY C1, C2
       LIMIT 10) A
;

위 쿼리는 인라인뷰(FROM 절 서브쿼리)를 통해 TEST1의 데이터를 10건으로 줄인 후 스칼라 서브쿼리를 수행하기 때문에 10번만 수행한다.

JOIN VS 스칼라 서브쿼리

처리 데이터량, 제약조건, 인덱스 구성 유무 등을 고려하여 어느 방식이 성능상 유리한지 고민해봐야한다.

스칼라 서브쿼리 → JOIN

SELECT T1.C1, T1.C2, T1.C3
	 , (SELECT T2.C3 FROM TEST2 T2 WHERE T2.C1 = T1.C1) AS T2_C3
     , (SELECT T3.C3 FROM TEST3 T3 WHERE T3.C1 = T1.C1) AS T3_C3
FROM TEST1 T1
ORDER BY T1.C1, T1.C2
;

TEST1의 총 데이터 건수가 100건이라 했을 때, 위 쿼리에서 스칼라 서브 쿼리의 수행 횟수는 100 * 2 건이 된다.

이와 같이 최종 추출 건수가 많고, 스칼라 서브쿼리의 수행횟수가 많은 경우 스칼라 서브쿼리를 JOIN으로 변경하고, Hash Join으로 변경하면 성능을 개선할 수 있다.

이 쿼리를 개선해보자.

SELECT /*+ LEADING(T1) USE_HASH(T1 T2 T3) */ 
	  T1.C1, T1.C2, T1.C3, T2.C3 AS T2_C3, T3.C3 AS T3_C3
FROM  TEST1 T1, TEST2 T2, TEST3 T3
WHERE T1.C1 = T2.C1
  AND T1.C1 = T3.C1
ORDER BY T1.C1, T1.C2
;

단, 이때 조인 컬럼의 연결 컬럼값(C1)은 유니크해야 한다.

JOIN → 스칼라 서브쿼리
반대로, 최종 추출 건수가 적다면 스칼라 서브쿼리가 효율적이다.

SELECT ROWNUM AS NUM, X.*
FROM (SELECT /*+ LEADING(T1) USE_HASH(T1 T2 T3) */ 
	  T1.C1, T1.C2, T1.C3, T2.C3 AS T2_C3, T3.C3 AS T3_C3
FROM  TEST1 T1, TEST2 T2, TEST3 T3
WHERE T1.C1 = T2.C1
  AND T1.C1 = T3.C1
ORDER BY T1.C1, T1.C2 ) X
WHERE ROWNUM < 11
;

위 쿼리는 최종 추출 데이터가 10건에 불과하지만, TEST1~3의 전체 데이터를 처리하기 때문에 비효율적이다.

SELECT ROWNUM AS NUM, X.*,
	 , (SELECT T2.C3 FROM TEST2 T2 WHERE T2.C1 = X.C1) AS T2_C3
     , (SELECT T3.C3 FROM TEST3 T3 WHERE T3.C1 = X.C1) AS T3_C3
FROM (SELECT T1.C1, T2.C2 FROM TEST1 T1 ORDER BY T1.C1, T1.C2) X
WHERE ROWNUM < 11
ORDER BY T1.C1, T1.C2

다음과 같이 개선하면, 10건의 데이터에 대해서만 다루기 때문에 위 쿼리보다 효율적이다.

스칼라 서브쿼리 사용 시 주의할 점

스칼라 서브쿼리는 보통 FROM 절의 테이블과의 조인조건이 존재하는데, 연결 컬럼명이 동일하다면 Table Alias를 반드시 지정해야 한다. Alias를 지정하지 않을 경우, 서브쿼리 내의 테이블 컬럼명으로 인식하여 심각한 문제가 발생할 수 있기 때문이다.

참고
SQL 튜닝의 시작