스칼라 서브쿼리와 인라인 뷰
스칼라 서브쿼리 캐싱과 Result Cache, View Merging 및 no_merge와 push_pred의 적용 실습
#핵심 요약
SELECT 절에서 사용자 정의 함수를 부르면 결과 행 수만큼 함수 안의 SQL이 반복 실행됨(recursive calls)
같은 내용을 스칼라 서브쿼리로 쓰면 입력값별 결과를 캐싱해 재사용함
스칼라 서브쿼리 캐싱은 코드명 조회처럼 입력값 종류가 적고 반복될 때 좋음
고객번호처럼 값이 거의 매번 다르면 캐시 효과가 없고 확인 비용만 늘어 일반 조인이 나을 수 있음
옵티마이저는 FROM 절 서브쿼리(인라인 뷰)를 풀어 일반 조인으로 바꿀 수 있음(View Merging)
실행계획에 VIEW가 없으면 해체된 것임no_merge로 막고 merge로 유도함
해체하지 않은 뷰에 WHERE 조건이 없으면 뷰 전체를 만든 뒤 조인함push_pred는 메인 쿼리의 조인 조건 값을 건건이 뷰 안으로 밀어 넣음(조인 조건 Pushdown)
#개념과 동작 원리
스칼라 서브쿼리 캐싱의 부작용:
- 캐시 공간이 작아 입력값 종류가 많으면 기존 값이 계속 밀려남
- 매번 캐시에서 못 찾으면 캐시 확인 비용만 추가됨
- 값을 저장, 탐색, 교체하는 데 메모리와 CPU를 씀
- 메인 쿼리 결과 건수가 적으면 같은 값이 다시 나올 기회 자체가 적음
#필수 명령어 또는 SQL
#사용자 정의 함수와 스칼라 서브쿼리
CREATE OR REPLACE FUNCTION get_dname (p_deptno NUMBER)
RETURN VARCHAR2
IS
l_dname VARCHAR2(10);
BEGIN
SELECT dname INTO l_dname FROM dept WHERE deptno = p_deptno;
RETURN l_dname;
END;
/결과 해석:
실습에서는 같은 함수를 dept_t(2000건)를 보도록 만든 뒤 emp_t(20만 건)에서 비교했음emp_t, dept_t 생성 스크립트는 아래 조인 조건 Pushdown 절에 있음
SELECT empno, ename, sal, hiredate, deptno, get_dname(deptno)
FROM emp_t WHERE sal >= 2000;
-- 결과: recursive calls 399619, consistent gets 1203021
SELECT empno, ename, sal, hiredate, deptno,
(SELECT dname FROM dept_t d WHERE d.deptno = e.deptno) AS dname
FROM emp_t e WHERE sal >= 2000;
-- 결과: recursive calls 0, consistent gets 229450결과 해석:
설명: 함수가 20만 번 반복되었고, 스칼라 서브쿼리로 바꾸자 consistent gets가 크게 줄었음
실습에서는 "거의 61% 감소"라고 적었지만 기록된 수치로 계산하면 약 81% 감소임(산술 계산, 실행 결과 아님)
#함수 결과를 Result Cache에
ALTER SYSTEM SET result_cache_max_size = 1180800K SCOPE = BOTH;결과 해석:
실습에서는 이 변경 뒤 SHUTDOWN ABORT와 STARTUP으로 인스턴스를 다시 올렸음SCOPE = BOTH에서 재시작이 필요한지는 수업 자료에 근거가 없고(확인 불가), SHUTDOWN ABORT는 강제 종료라 실습 DB에서만 씀
실습 환경의 기본값은 result_cache_max_size 11808K, result_cache_mode MANUAL이었음
CREATE OR REPLACE FUNCTION get_loc (p_deptno NUMBER)
RETURN VARCHAR2
RESULT_CACHE RELIES_ON (dept_t)
IS
l_loc VARCHAR2(10);
BEGIN
SELECT loc INTO l_loc FROM dept_t WHERE deptno = p_deptno;
RETURN l_loc;
END;
/결과 해석: 실습 기록상 튜닝 전 get_loc 호출은 recursive calls 200000, consistent gets 603021이었고, RESULT_CACHE를 붙인 뒤 recursive calls 1901, consistent gets 8724였음RELIES_ON 절이 19c에서 필요한지는 기재 없음
Result Cache 조회 뷰는 통계정보와 실행계획 변화에 있음
#View Merging 제어
SELECT ename, sal, job
FROM (SELECT ename, sal, job FROM emp)
WHERE job IN ('SALESMAN', 'ANALYST'); -- VIEW 없음: 해체됨
SELECT /*+ no_merge(t) */ ename, sal, job
FROM (SELECT ename, sal, job FROM emp) t
WHERE job IN ('SALESMAN', 'ANALYST'); -- 해체 막기 (뷰 안에 /*+ no_merge */도 가능)
SELECT /*+ no_merge(v) leading(s) use_nl(v) */ v.ename, v.sal, v.loc, s.grade
FROM salgrade s,
(SELECT e.ename, e.sal, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno) v
WHERE v.sal BETWEEN s.losal AND s.hisal; -- 결과: VIEW를 NL 이너로 5번 실행, 68버퍼결과 해석:
DENSE_RANK 결과를 바깥에서 거르는 인라인 뷰는 해체되지 않고 VIEW가 남았음
실습 6번 문제는 "인라인 뷰를 해체하지 못하도록" 하라면서 답에 merge(v)를 썼고, 함께 실린 실행계획에는 VIEW가 남아 있음
#조인 조건 Pushdown
SELECT /*+ no_merge(e) no_push_pred(e) */ d.deptno, d.loc, e.empno, e.ename
FROM dept d, (SELECT empno, ename, deptno FROM emp) e
WHERE e.deptno = d.deptno AND d.loc = 'CHICAGO'; -- 튜닝 전
SELECT /*+ no_merge(e) push_pred(e) */ d.deptno, d.loc, e.empno, e.ename
FROM dept d, (SELECT empno, ename, deptno FROM emp) e
WHERE e.deptno = d.deptno AND d.loc = 'CHICAGO'; -- 튜닝 후결과 해석:
실습에서는 실제 뷰(CREATE VIEW emp_view)에도 같은 힌트가 적용되는 것을 보였음
연습 문제(부서 2000건 dept_t, 사원 20만 건 emp_t, 인덱스 emp_x02(deptno, hiredate)):
CREATE TABLE dept_t AS
SELECT ROWNUM AS deptno, 'DEPT_' || LPAD(ROWNUM, 5, '0') AS dname,
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(1, 1096)) AS open_date
FROM dual CONNECT BY LEVEL <= 2000;
ALTER TABLE dept_t ADD CONSTRAINT pk_dept_t PRIMARY KEY (deptno);
CREATE TABLE emp_t AS
SELECT ROWNUM AS empno, 'EMP_' || LPAD(ROWNUM, 7, '0') AS ename,
TRUNC(DBMS_RANDOM.VALUE(1, 2001)) AS deptno,
TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(1, 1096)) AS hiredate,
ROUND(DBMS_RANDOM.VALUE(2500, 9000), -2) AS sal
FROM dual CONNECT BY LEVEL <= 200000;
CREATE INDEX dept_x01 ON dept_t (open_date);
CREATE INDEX emp_x02 ON emp_t (deptno, hiredate);SELECT d.deptno, d.dname, t.avg_sal, t.min_sal, t.max_sal
FROM dept_t d,
(SELECT /*+ no_merge push_pred */ deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal
FROM emp_t
WHERE hiredate >= TRUNC(SYSDATE, 'MM')
GROUP BY deptno) t
WHERE d.open_date >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
AND t.deptno = d.deptno;| 시도 | 실습 실행계획 | Buffers |
|---|---|---|
no_merge만 |
HASH JOIN, EMP_T Full Scan 뒤 HASH GROUP BY(Bloom 필터 JOIN FILTER USE) |
1041 |
no_merge push_pred |
NESTED LOOPS 아래 VIEW PUSHED PREDICATE(95번), EMP_X02 Range Scan |
439 |
결과 해석: dept_t에서 조건에 맞는 95개 부서마다 부서번호를 뷰 안으로 밀어 넣어, 20만 건 전체를 GROUP BY하지 않고 해당 부서의 이번 달 입사 사원만 인덱스로 읽었음
#같은 테이블을 여러 번 읽는 스칼라 서브쿼리 고치기
부서별로 최근 1년 입사자의 평균, 최소, 최대 급여를 스칼라 서브쿼리 세 개로 구하면 같은 emp_big을 세 번 읽음(실습 환경: dept_big 1,000개, emp_big 100만 건, 인덱스 (deptno), (hiredate, sal), (hiredate, deptno))
-- 인라인 뷰에서 한 번 집계한 뒤 아우터 조인
SELECT d.deptno, d.dname, v.평균급여, v.최소급여, v.최대급여
FROM dept_big d,
(SELECT /*+ no_merge */ e.deptno,
ROUND(AVG(e.sal)) 평균급여, MIN(e.sal) 최소급여, MAX(e.sal) 최대급여
FROM emp_big e
WHERE e.hiredate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12)
GROUP BY e.deptno) v
WHERE d.loc IS NOT NULL
AND v.deptno (+) = d.deptno;
-- 12c LATERAL 인라인 뷰
SELECT d.deptno, d.dname, v.평균급여, v.최소급여, v.최대급여
FROM dept_big d,
LATERAL (SELECT ROUND(AVG(e.sal)) 평균급여, MIN(e.sal) 최소급여, MAX(e.sal) 최대급여
FROM emp_big e
WHERE e.hiredate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12)
AND e.deptno = d.deptno) v
WHERE d.loc IS NOT NULL;결과 해석:
| 실습 방법 | consistent gets |
|---|---|
| 스칼라 서브쿼리 세 개 | 20403 |
값 세 개를 RPAD로 이어 붙인 스칼라 서브쿼리 하나를 SUBSTR로 다시 자르기(예전 방식) |
1010727 |
GROUP BY 인라인 뷰 + 아우터 조인 |
6805 |
LATERAL 인라인 뷰 |
6805 |
이 실습에서는 값을 이어 붙이는 예전 방식이 오히려 훨씬 많이 읽었음
연습 문제: product_big 1,000건과 order_big 100만 건에서 최근 1년 상품별 주문 건수, 금액 합계, 최대 단건 금액을 스칼라 서브쿼리 세 개로 구한 SQL(실습 662151)을 고침
실습 답은 GROUP BY 인라인 뷰와 아우터 조인으로 4215였음
#뷰 안의 조인 순서 바꾸기
CREATE OR REPLACE VIEW emp_dept AS
SELECT e.ename, e.sal, d.loc, e.deptno FROM emp e, dept d WHERE e.deptno = d.deptno;
SELECT /*+ no_merge(v) leading(s) use_nl(v) leading(v.e, v.d) use_nl(v.d) */
v.ename, v.sal, v.loc, s.grade
FROM salgrade s, emp_dept v
WHERE v.sal BETWEEN s.losal AND s.hisal;결과 해석:
no_merge(v)로 뷰를 해체하지 않게 하고, leading(s) use_nl(v)로 salgrade를 먼저 읽어 뷰를 Nested Loops로 조인함
뷰 안의 조인 순서와 방법은 leading(v.e, v.d) use_nl(v.d)처럼 뷰별칭.테이블별칭으로 가리킴
실습 기록: 뷰 안이 해시 조인이면 Buffers 68, leading(v.e, v.d) use_nl(v.d)로 뷰 안도 Nested Loops로 바꾸면 DEPT를 70번 읽어 Buffers 459였음
옵티마이저가 인라인 뷰를 풀어 단일 쿼리 블록으로 합치면 실행계획에서 VIEW 오퍼레이션이 사라짐
#수정 가능 조인 뷰로 한 번에 갱신
다른 테이블 값으로 갱신할 때 SET 절의 상관 서브쿼리와 WHERE EXISTS로 쓰면 같은 데이터를 여러 번 읽음
예를 들어 매일 밤 최근 한 달 거래를 집계해 고객 테이블을 갱신하는 배치에서 MAX, COUNT, SUM을 서브쿼리 세 개로 쓰고 EXISTS까지 붙이면 거래 테이블을 네 번 읽음
조인한 결과에 직접 UPDATE하면 한 번만 읽음
UPDATE (SELECT /*+ ordered use_hash(c) no_merge(t) */
c.최종거래일시, c.최근거래횟수, c.최근거래금액,
t.거래일시, t.거래횟수, t.거래금액
FROM 고객 c,
(SELECT 고객번호, MAX(거래일시) 거래일시, COUNT(*) 거래횟수, SUM(거래금액) 거래금액
FROM 거래
WHERE 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1))
GROUP BY 고객번호) t
WHERE c.고객번호 = t.고객번호)
SET 최종거래일시 = 거래일시, 최근거래횟수 = 거래횟수, 최근거래금액 = 거래금액;결과 해석:
조인 뷰에서는 1쪽 집합과 조인하는 M쪽 집합(키 보존 테이블)만 입력, 수정, 삭제할 수 있음
키 보존 테이블은 조인 결과에서도 중복 없이 유일하게 식별되는 테이블임
옵티마이저가 1쪽을 알 수 있도록 1쪽 조인 키에 PK나 Unique 인덱스가 있어야 함
없으면 ORA-01779: cannot modify a column which maps to a non key-preserved table이 남
실습: dept(deptno)에 PK를 건 뒤 emp_dept_view에서 emp 쪽(sal) 갱신은 됐고 dept 쪽(loc) 갱신은 안 됐음salgrade와 BETWEEN으로 조인한 뷰는 grade에 PK를 걸어도 키 보존이 성립하지 않아 ORA-01779가 났음
설명: 10g까지는 bypass_ujvc 힌트로 강제했고, 11g에서는 이 힌트가 막혀 MERGE를 썼으며, 12c부터는 인라인 뷰에서 GROUP BY로 만든 1쪽 집합을 키가 보존된다고 인정함
연습 문제: 상품과 주문상세에서 상품별 최근 판매일, 누적 수량, 누적 금액을 서브쿼리 세 개와 EXISTS로 갱신하는 UPDATE를 수정 가능 조인 뷰로 고침