스칼라 서브쿼리 조인

운구름·2022년 7월 10일

친절한 SQL 튜닝 스터디

목록 보기

17/27

스칼라 서브쿼리 특징

create or replace function GET_DNAME(p_deptno number) return varchar2
is
    l_dname dept.dname%TYPE;
begin
    select dname into l_dname from dept where deptno = p_deptno;
    return l_dname;
exception
    where others then
    return null;
end;

위처럼 GET_DNAME 함수를 만들었는데 이 함수를 사용하면 SELECT 쿼리를 메인쿼리 건수만큼 재귀적으로 반복함.

SELECT EMPNO, ENAME, SAL, HIREDATE, GET_DNAME(E.DEPTNO) AS DNAME
 FROM EMP E
WHERE SAL >= 2000

아래의 서브쿼리는 메인 쿼리 레코드마다 하나의 값만 반환한다. 즉, 재귀적으로 실행하는 구조가 아니다.

SELECT EMPNO, ENAME, SAL, HIREDATE
      , (SELECT D.DNAME, FROM DEPT D WHERE D.DEPTNO = E.DEPTNO) AS DNAME
  FROM EMP E
 WHERE SAL >= 2000

서브쿼리를 사용한 위 쿼리문 아래 Outer 조인문처럼 NL조인을 사용한다. DEPT와 조인에 실패하는 EMP 레코드는 DNAME에 NULL 값을 출력하는 점도 같다.

SELECT E.EMPNO, E.ENAME, E.SAL, E.HIREDATE, D.DNAME
FROM EMP E, DEPT D
WHERE D.DEPTNO = E.DEPTNO
	AND SAL >= 2000

스칼라 서브쿼리 캐싱 효과

스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시(Query Execution Cashe)에 저장함.

조인할 때마다 일단 캐시에서 ‘입력 값’을 찾아보고, 찾으면 저장된 ‘출력 값’을 반환한다

캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해 둔다.

SELECT EMPNO, ENAME, SAL, HIREDATE,
	(SELECT D.DNAME --> 출력 값 : D.DNAME
		FROM DEPT D
		WHERE D.DEPTNO = E.EMPNO --> 입력 값 : E.EMPNO
	) 
FROM EMP E
WHERE SAL >= 2000

(스칼라 서브쿼리 캐싱 = 필터 서브쿼리 캐싱)

메인쿼리 집합이 아무리 커도 조인할 데이터를 대부분 캐시에서 찾는다면, 조인 수행횟수를 최소화할 수 있어, 캐싱 메커니즘은 조인 성능을 높이는 데 큰 도움이 된다.

캐싱은 쿼리 단위로 이루어짐.

쿼리를 시작할 때 PGA 메모리에 공간을 할당
쿼리를 수행하면서 공간을 채움
쿼리를 마치는 순간 공간을 반환

캐싱을 위한 좋은 튜닝기법

SELECT-LIST에 사용한 함수는 메인쿼리 건수만큼 반복 수행되서 안좋음.

근데 스칼라 서브쿼리 덧씌우면 스칼라 서브쿼리 캐싱 효과로 성능이 좋아짐.

그래서 함수에 내장된 SELECT 쿼리도 캐싱효과로 덜 수행됨.

SELECT EMPNO, ENAME, SAL, HIREDATE, **(SELECT GET_DNAME(e.deptno) FROM dual)**
FROM EMP E
WHERE SAL >= 2000

스칼라 서브쿼리 캐싱 부작용

스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 좋음.
(입력값의 도메인이 적어야 한다?)

입력 값의 종류가 많으면 캐시를 매번 확인하는 비용 때매 오히려 꾸졌음. CPU랑 메모리를 와구와구 잡아묵음

두 개 이상의 값 반환

스칼라 서브쿼리에는 치명적인 제약이 하나 있다. 고거슨 바로 두 개 이상의 값을 반환할 수 없다는 제약이다.

즉, 쿼리를 아래와 같이 작성할 수 없다. 부분범위 처리 가능하다는 스칼라 서브쿼리의 장점을 이용하고 싶을 때 고민이 생기게 마련이다.

SELECT c.고객번호, c.고객명
     , (SELECT **AVG(거래금액), MIN(거래금액), MAX(거래금액)**
        FROM 거래
        WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
          AND 고객번호 = c.고객번호)
FROM 고객 c
WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)

그렇다고 아래처럼 쓰면 같은 데이터 반복해서 읽는 비효율이 있음

SELECT c.고객번호, c.고객명
     , (SELECT MIN(거래금액)
        FROM 거래
        WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
           AND 고객번호 = c.고객번호)) 
     , (SELECT AVG(거래금액), MIN(거래금액), MAX(거래금액)
        FROM 거래
        WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
           AND 고객번호 = c.고객번호)) 
     , (SELECT MAX(거래금액)
        FROM 거래
        WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
           AND 고객번호 = c.고객번호)) 
FROM 고객 c
WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)

고래서 우짤것인가! 튜닝 장인들이 걸어온 역사속에서 대대손손 물려내려온 전통적인 비법이 있었으니! 고것이 아래의 방법이렸다!

SELECT 고객번호, 고객명
     , TO_NUMBER(SUBSTR(거래금액, 1, 10)) 평균거래금액
     , TO_NUMBER(SUBSTR(거래금액, 11, 10)) 최소거래금액
     , TO_NUMBER(SUBSTR(거래금액, 21)) 최대거래금액
FROM (SELECT C.고객번호, C.고객명
             , (SELECT LPAD(AVG(거래금액), 10 || LPAD(MIN(거래금액), 10 || LPAD(MAX(거래금액), 10
                FROM 거래
                WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
                   AND 고객번호 = c.고객번호) 거래금액
      FROM 고객 c
      WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)
)