[Oracle] 오라클 스칼라 서브쿼리를 이용한 JOIN

스칼라 서브쿼리

- 함수처럼 한 레코드 당 정확히 하나의 값만을 리턴하는 서브쿼리

- 주로 select-list에서 사용, 컬럼이 올 수 있는 대부분 위치에 사용 가능 

SELECT EMPNO, ENAME, SAL, HIREDATE,

(SELECT D.DNAME FROM DEPT D WHERE D.DEPTNO = E.DEPTNO) DNAME

   FROM EMP E

WHERE SAL >= 2000;

-- Outer JOIN과 같은 결과

-- DEPT와 JOIN에 실패하는 EMP 레코드가 있다면 DNAME으로 NULL 값 출력

스칼라 서브쿼리의 캐싱 효과

- 오라클은 스칼라 서브쿼리의 입력 값과 출력 값을 내부 캐시(Query Execution Cache)에 저장

- 스칼라 서브쿼리가 수행될 때 일단 '입력 값'을 캐시에서 찾아보고 거기 있으면 저장된 '출력 값'을 리턴

- 캐시에서 찾지 못한 경우 쿼리 수행, 결과값은 캐시에 저장

-- 반복 수행되는 함수 때문에 쿼리 성능이 크게 저하될 경우 스칼라 서브쿼리를 이용하여 튜닝

SELECT EMPNO, ENAME, SAL, HIREDATE,

           (SELECT GET_DNAME(DEPTNO) FROM DUAL) DNAME

   FROM EMP E

WHERE SAL >= 2000;

- 스칼라 서브쿼리의 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 적을 때 큼 

- 입력 값의 종류가 많으면 캐시를 확인하는 비용 때문에 오히려 성능은 저하되고 CPU 사용률만 높아짐

- 버퍼 Pinning 효과X

두 개 이상의 값을 리턴하고 싶을 때

SELECT D.DEPTNO, D.DNAME, AVG_SAL, MIN_SAL, MAX_SAL

   FROM DEPT D,

            (SELECT DEPTNO, AVG(SAL) AVG_SAL, MIN(SAL) MIN_SAL, MAX(SAL) MAX_SAL

FROM EMP GROUP BY DEPTNO) E

WHERE D.DEPTNO = E.DEPTNO(+)

    AND D.LOC = 'CHICAGO';

-- EMP 테이블 전체를 다 읽어야 하는 비효율 발생

▼

SELECT D.DEPTNO, D.DNAME,

(SELECT AVG(E1.SAL) FROM EMP E1 WHERE E1.DEPTNO = D.DEPTNO) AVG_SAL,

(SELECT MIN(E1.SAL) FROM EMP E1 WHERE E1.DEPTNO = D.DEPTNO) MIN_SAL,

(SELECT MAX(E1.SAL) FROM EMP E1 WHERE E1.DEPTNO = D.DEPTNO) MAX_SAL

  FROM DEPT D

WHERE D.LOC = 'CHICAGO';

-- EMP 테이블에서 같은 범위를 반복적으로 access하는 비효율 발생

▼

SELECT DEPTNO, DNAME,

      TO_NUMBER(SUBSTR(SAL, 1, 7)) AVG_SAL,

 TO_NUMBER(SUBSTR(SAL, 8, 7)) MIN_SAL,

 TO_NUMBER(SUBSTR(SAL, 15)) MAX_SAL

   FROM (

SELECT D.DEPTNO, D.DNAME,

(SELECT LPAD(AVG(SAL), 7) || (LPAD(MIN(SAL), 7) || MAX(SAL)

   FROM EMP E WHERE E.DEPTNO = D.DEPTNO) SAL

   FROM DEPT D

WHERE D.LOC = 'CHICAGO'

);