오라클 성능 고도화 원리와 해법 II (2010년)
I. 스칼라 서브쿼리
1. 스칼라 서브쿼리란?
- 스칼라 서브쿼리란 Select-List에서 서브쿼리가 사용될 때 이를 스칼라 서브쿼리라 칭함.
- 스칼라 서브쿼리의 특징은 다음과 같음.
- 하나의 레코드만 리턴이 가능하며, 두개 이상의 레코드는 리턴할 수 없다.
- 일치하는 데이터가 없더라도 NULL값을 리턴할 수 있다.
이는 원래 그룹함수의 특징중에 하나인데 스칼라 서브쿼리 또한 이 특징을 가지고 있다.
-- 1. 그룹함수 사용 안할 때는 리턴하는 값이 없음
SELECT 1
FROM DEPT
WHERE 1 = 2
;
no rows selected
-- 2. 그룹함수를 사용하면 값을 리턴함
SELECT MIN(DEPTNO)
FROM DEPT
WHERE 1 = 2
;
MIN(DEPTNO)
-----------
1 row selected.
-- 3. 스칼라 서브쿼리 테스트(일치하는 값이 없을 경우 NULL 리턴함)
-- EMP:DEPT = M:1 관계에서 M 대상을 올리다보니 GROUP BY 을 추가함
SELECT D.DEPTNO,
(SELECT MIN(EMPNO)
FROM EMP
WHERE DEPTNO = D.DEPTNO) EMPNO
FROM DEPT D
ORDER BY D.DEPTNO
;
DEPTNO EMPNO
---------- ----------
10 1
20 10
30 100
40 1000
50 10000
60 100000
70
80
90
9 rows selected.
2. 스칼라 서브쿼리와 Outer 조인 관계
- 위와 같이 스칼라 서브쿼리는 일치하지 않더라도 NULL를 리턴하므로 Outer 조인과 같은 형태이며,
이는 스칼라 서브쿼리와 Outer 조인은 서로 변형이 가능하다는 것을 알 수 있다.
SELECT D.DEPTNO,
E.EMPNO
FROM DEPT D,
(SELECT DEPTNO,
MIN(EMPNO) EMPNO
FROM EMP
GROUP BY DEPTNO) E
WHERE D.DEPTNO = E.DEPTNO(+)
ORDER BY D.DEPTNO
;
DEPTNO EMPNO
---------- ----------
10 1
20 10
30 100
40 1000
50 10000
60 100000
70
80
90
9 rows selected.
3. 성능상 Outer 조인을 스칼라 서브쿼리로 올려야 할 때
- 만약 Outer 조인을 하는 집합이 인라인뷰이면서 그룹함수를 사용해서 View Merging이 안될 때
-- 1. 2개의 그룹함수 인라인뷰와 Outer 조인이 되어 View Merging이 안된다.
SELECT T_1.SUM_EMPNO SUM_EMPNO_1,
T_2.SUM_EMPNO SUM_EMPNO_2,
E.HIREDATE,
E.DEPTNO
FROM (SELECT E_1.EMPNO,
SUM(E_1.EMPNO) SUM_EMPNO
FROM EMP E_1
GROUP BY E_1.EMPNO) T_1,
(SELECT E_2.EMPNO,
SUM(E_2.EMPNO) SUM_EMPNO
FROM EMP E_2
GROUP BY E_2.EMPNO) T_2,
EMP E
WHERE E.EMPNO = T_1.EMPNO(+)
AND E.EMPNO = T_2.EMPNO(+)
AND E.DEPTNO = 10
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.006 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.610 0.607 1 811 0 9
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.610 0.612 1 811 0 9
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
9 HASH JOIN OUTER (cr=811 pr=1 pw=0 time=493640 us)
9 HASH JOIN OUTER (cr=407 pr=1 pw=0 time=203353 us)
9 TABLE ACCESS BY INDEX ROWID EMP (cr=3 pr=1 pw=0 time=11482 us)
9 INDEX RANGE SCAN EMP_N1 (cr=2 pr=1 pw=0 time=11433 us)(Object ID 19837982)
100000 VIEW (cr=404 pr=0 pw=0 time=289825 us)
100000 HASH GROUP BY (cr=404 pr=0 pw=0 time=289818 us)
100000 TABLE ACCESS FULL EMP (cr=404 pr=0 pw=0 time=115 us)
100000 VIEW (cr=404 pr=0 pw=0 time=274989 us)
100000 HASH GROUP BY (cr=404 pr=0 pw=0 time=174985 us)
100000 TABLE ACCESS FULL EMP (cr=404 pr=0 pw=0 time=115 us)
;
-- 2. 이를 극복하기 위해 스칼라 서브쿼리로 올림
SELECT (SELECT SUM(E_1.EMPNO) SUM_EMPNO
FROM EMP E_1
WHERE E_1.EMPNO = E.EMPNO) SUM_EMPNO_1,
(SELECT SUM(E_2.EMPNO) SUM_EMPNO
FROM EMP E_2
WHERE E_2.EMPNO = E.EMPNO) SUM_EMPNO_2,
E.HIREDATE,
E.DEPTNO
FROM EMP E
WHERE E.DEPTNO = 10
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.004 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.066 2 27 0 9
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.010 0.070 2 27 0 9
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
9 SORT AGGREGATE (cr=11 pr=1 pw=0 time=50528 us)
9 INDEX UNIQUE SCAN EMP_U1 (cr=11 pr=1 pw=0 time=50440 us)(Object ID 19837981)
9 SORT AGGREGATE (cr=11 pr=0 pw=0 time=152 us)
9 INDEX UNIQUE SCAN EMP_U1 (cr=11 pr=0 pw=0 time=102 us)(Object ID 19837981)
9 TABLE ACCESS BY INDEX ROWID EMP (cr=5 pr=1 pw=0 time=15032 us)
9 INDEX RANGE SCAN EMP_N1 (cr=3 pr=1 pw=0 time=15172 us)(Object ID 19837982)
4. 성능상 스칼라 서브쿼리를 Outer 조인으로 내려야 할 때
- 만약 뷰 안에 스칼라 서브쿼리 컬럼이 존재하고 그 뷰 밖에서 서브쿼리 컬럼을 조건으로 사용할 경우.
이 때는 인덱스가 있다 하더라도 컬럼이 가공되어 있기 때문에 View Merging이 안되므로 이 때는
스칼라 서브쿼리를 Outer Join으로 내려야 함
-- 1. 인라인뷰 안의 스칼라 서브쿼리를 뷰 밖에서 조건으로 사용할 경우
SELECT EMPNO,
DEPT_NO
FROM (SELECT E.EMPNO,
(SELECT D.DEPTNO
FROM DEPT D
WHERE D.DEPTNO = E.DEPTNO) DEPT_NO
FROM EMP E)
WHERE DEPT_NO = 10
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.004 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.110 0.119 0 407 0 9
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.110 0.123 0 407 0 9
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
6 INDEX UNIQUE SCAN DEPT_U1 (cr=2 pr=0 pw=0 time=81 us)(Object ID 19837985)
9 FILTER (cr=407 pr=0 pw=0 time=186 us)
100000 TABLE ACCESS FULL EMP (cr=405 pr=0 pw=0 time=100112 us)
6 INDEX UNIQUE SCAN DEPT_U1 (cr=2 pr=0 pw=0 time=81 us)(Object ID 19837985)
;
-- 2. 스칼라 서브쿼리를 Outer 조인으로 내려서 극복
SELECT EMPNO,
DEPT_NO
FROM (SELECT E.EMPNO,
D.DEPTNO DEPT_NO
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+))
WHERE DEPT_NO = 10
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.004 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.017 1 6 0 9
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.010 0.021 1 6 0 9
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
9 NESTED LOOPS OUTER (cr=6 pr=1 pw=0 time=16862 us)
1 INDEX UNIQUE SCAN DEPT_U1 (cr=1 pr=0 pw=0 time=39 us)(Object ID 19837985)
9 TABLE ACCESS BY INDEX ROWID EMP (cr=5 pr=1 pw=0 time=16815 us)
9 INDEX RANGE SCAN EMP_N1 (cr=3 pr=1 pw=0 time=16904 us)(Object ID 19837982)
5. 스칼라 서브쿼리를 Outer 조인으로 내릴 때 IN절 처리 방법
- 만약 스칼라 서브쿼리에 IN절이 있을 경우 Outer 구문이 안먹히는데
이를 극복하기 위해서 DECODE() 구문을 사용하여 해결 가능
-- 1. 스칼라 서브쿼리에 IN절이 있는 경우
SELECT EMPNO,
DEPT_NO
FROM (SELECT E.EMPNO,
(SELECT D.DEPTNO
FROM DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND D.DNAME IN ('SALES_1', 'SALES_2', 'SALES_3')) DEPT_NO
FROM EMP E)
WHERE DEPT_NO = 10
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.004 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.110 0.110 0 413 0 9
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.110 0.114 0 413 0 9
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
3 TABLE ACCESS BY INDEX ROWID DEPT (cr=8 pr=0 pw=0 time=187 us)
6 INDEX UNIQUE SCAN DEPT_U1 (cr=2 pr=0 pw=0 time=77 us)(Object ID 19837985)
9 FILTER (cr=413 pr=0 pw=0 time=201 us)
100000 TABLE ACCESS FULL EMP (cr=405 pr=0 pw=0 time=100107 us)
3 TABLE ACCESS BY INDEX ROWID DEPT (cr=8 pr=0 pw=0 time=187 us)
6 INDEX UNIQUE SCAN DEPT_U1 (cr=2 pr=0 pw=0 time=77 us)(Object ID 19837985)
;
-- 2. 스칼라 서브쿼리의 IN절을 Outer 조인으로 내리지만 결국 구문 에러남
SELECT EMPNO,
DEPT_NO
FROM (SELECT E.EMPNO,
D.DEPTNO DEPT_NO
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
AND D.DNAME(+) IN ('SALES_1', 'SALES_2', 'SALES_3'))
WHERE DEPT_NO = 10
;
AND D.DNAME(+) IN ('SALES_1', 'SALES_2', 'SALES_3') )
*
ERROR at line 8:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
-- 3. DECODE 구문을 이용하여 해결
SELECT EMPNO,
DEPT_NO
FROM (SELECT E.EMPNO,
D.DEPTNO DEPT_NO
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
--AND D.DNAME(+) IN ('SALES_1', 'SALES_2', 'SALES_3')
AND DECODE(D.DNAME(+), 'SALES_1', 1,
'SALES_2', 1,
'SALES_3', 1) = 1)
WHERE DEPT_NO = 10
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.004 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.010 1 7 0 9
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.014 1 7 0 9
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
9 NESTED LOOPS OUTER (cr=7 pr=1 pw=0 time=10393 us)
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=70 us)
1 INDEX UNIQUE SCAN DEPT_U1 (cr=1 pr=0 pw=0 time=29 us)(Object ID 19837985)
9 TABLE ACCESS BY INDEX ROWID EMP (cr=5 pr=1 pw=0 time=10316 us)
9 INDEX RANGE SCAN EMP_N1 (cr=3 pr=1 pw=0 time=10390 us)(Object ID 19837982)
II. 스칼라 서브쿼리의 캐싱 효과
1. 스칼라 서브쿼리는 입력값이 같을 경우 반복수행 안함
- 오라클은 스칼라 서브쿼리에 수행횟수를 최소화하려고 그 입력 값과 출력 값을 내부 캐시에 저장함.
- 입력 값이 같을 경우 출력 출력 값도 같을 것으로 보고 캐싱된 출력 값을 보내주므로 입력 값의
NDV가 작으면 작을수록 펑션의 호출빈도를 많이 줄일 수 있다. - 사용방법은 펑션에 SELECT DUAL을 사용하면 된다.
SELECT EMPNO,
ENAME,
HIREDATE,
(SELECT GET_DNAME(DEPTNO) FROM DUAL) DNAME
FROM EMP E
WHERE HIREDATE :1 AND :2
;
III. 두 개 이상의 값을 리턴하고 싶을 때
1. 결합연산자를 사용하는 방법
SELECT DEPTNO,
DNAME,
TO_NUMBER(SUBSTR(EMPNO, 1, 7)) AVG_EMPNO,
TO_NUMBER(SUBSTR(EMPNO, 8, 15)) MIN_EMPNO,
TO_NUMBER(SUBSTR(EMPNO, 15)) MAX_EMPNO
FROM (SELECT D.DEPTNO,
D.DNAME,
(SELECT LPAD(AVG(EMPNO), 7) || LPAD(MIN(EMPNO), 7) || MAX(EMPNO)
FROM EMP
WHERE DEPTNO = D.DEPTNO) EMPNO
FROM DEPT D)
;
DEPTNO DNAME AVG_EMPNO MIN_EMPNO MAX_EMPNO
---------- ------- ---------- ---------- ----------
10 SALES_1 5 19 9
20 SALES_2 54.5 1099 99
30 SALES_3 549.5 100999 999
40 SALES_4 5499.5 10009999 9999
50 SALES_5 54999.5 1000099999 99999
60 SALES_6 100000 1.0000E+11 100000
70 SALES_7
80 SALES_8
90 SALES_9
9 rows selected.
2. 오브젝트 타입을 사용하는 경우
-- 1. 오브젝트 타입 생성
CREATE OR REPLACE TYPE EMPNO_TYPE AS OBJECT
(AVG_EMPNO NUMBER, MIN_EMPNO NUMBER, MAX_EMPNO NUMBER)
/
Type created.
-- 2. 오브젝트 타입 활용하여 멀티 컬럼 리턴
SELECT DEPTNO,
DNAME,
A.EMPNO.AVG_EMPNO,
A.EMPNO.MIN_EMPNO,
A.EMPNO.MAX_EMPNO
FROM (SELECT D.DEPTNO,
D.DNAME,
(SELECT EMPNO_TYPE(AVG(EMPNO), MIN(EMPNO), MAX(EMPNO))
FROM EMP
WHERE DEPTNO = D.DEPTNO) EMPNO
FROM DEPT D) A
;
DEPTNO DNAME EMPNO.AVG_EMPNO EMPNO.MIN_EMPNO EMPNO.MAX_EMPNO
---------- ------- --------------- --------------- ---------------
10 SALES_1 5 1 9
20 SALES_2 54.5 10 99
30 SALES_3 549.5 100 999
40 SALES_4 5499.5 1000 9999
50 SALES_5 54999.5 10000 99999
60 SALES_6 100000 100000 100000
70 SALES_7
80 SALES_8
90 SALES_9
9 rows selected.
문서에 대하여
- 최초작성자 : ~xsoft
- 최초작성일 : 2010년 04월 03일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*
"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3212
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.