- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4948083&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
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.
문서에 대하여
- 최초작성자 : 강정식
- 최초작성일 : 2010년 04월 03일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4948083&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.