6. 스칼라 서브쿼리를 이용한 조인
2011.03.27 08:38
(1) 스칼라 서브쿼리
■ 스칼라 서브쿼리 의미
- 스칼라 서브쿼리란 Select-List에서 서브쿼리가 사용될 때 이를 스칼라 서브쿼리라 칭함.
- 스칼라 서브쿼리의 결과는 하나의 레코드만 리턴이 가능하며, 두개 이상의 레코드는 리턴할 수 없다.
(일치하는 데이터가 없더라도 NULL값을 리턴할 수 있다.)
-- 예제 select empno, ename, sal, hiredate, (select, d.dname from dept d where d.deptno=e.deptno) dname from emp e where sal >= 2000; |
-- 동일한 질의, Outer 조인으로 100% 같은 의미이다. -- 스칼라 쿼리는 내부적으로 캐싱 기법이 작용한다. select /*+ ordered use_nl(d) */ empno, ename, sal, hiredate, dname from emp e, dept d where d.deptno(+)=e.deptno and sal >= 2000; |
(2) 스칼라 서브쿼리의 캐싱 효과
- 오라클은 스칼라 서브쿼리에 수행횟수를 최소화하려고 그 입력 값과 출력 값을 내부 캐시에 저장함.
- 입력 값이 같을 경우 출력 출력 값도 같을 것으로 보고 캐싱된 출력 값을 보내주므로 입력 값의 NDV가
작으면 작을수록 펑션의 호출빈도를 많이 줄일 수 있다.
-- 스칼라 쿼리의 입력 값은? select empno, ename, sal, hiredate, (select d.dname from dept d where d.deptno=e.deptno) dname (출력값) (입력값) from emp e where sal >= 2000; |
- 스칼라 서브 쿼리를 사용한 튜닝 사례 : 함수에 스칼라 서브 쿼리를 사용함.
--함수 호출횟수를 줄이기위해 스칼라 쿼리의 캐싱 기능을 사용함. -- 캐시크시는 _query_execution_cach_max_size 로 조절 select empno, ename, sal, hiredate, (select, get_name(deptno) from dual) dname from emp e where sal >= 2000; |
(설명) 참고 -> 비용기반 오라클 원리(Jonathan) durinuri study -> CBO 오라클원리 -> 9장 |
(3) 두 개 이상의 값을 리턴하고 싶을 때
- 예제( 첨부파일 2-09.hwp )
-- 예제 1 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 e.deptno(+)=d.deptno and d.loc='CHICAGO'; |
-- 예제 1을 아래와 같이 바꿀수는 없다. (반환되는 값이 3개이다) select d.deptno, d.dname, (select avg(sal), min(sal), max(sal) from emp where deptno=d.deptno) from dept d where d.loc='CHICAGO'; |
-- 예제 1을 아래와 같이 바꾸면 emp의 같은 범위를 반복적으로 접근하는 비효율이 있다. select d.deptno, d.dname, (select avg(sal) from emp where deptno=d.deptno) avg_sal, (select min(sal) from emp where deptno=d.deptno) min_sal, (select max(sal) from emp where deptno=d.deptno) max_sal from dept d where d.loc='CHICAGO'; |
-- 예제 1을 아래와 같이 바꾼다. substr 함수로 분리 select d.deptno, d.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.name, (select lpad(avg(sal), 7) || lpad(min(sal), 7) || max(sal) from emp where deptno=d.deptno) sal from dept d where d.loc='CHICAGO'); |
-- 예제 1을 아래와 같이 바꾼다. -- 오브젝트 TYPE 사용, TYPE을 미리 선언하는 불편은 있음 create or replace type sal_type as object ( avg_sal number, min_sal number, max_sal number) / select deptno, dname, a.avl.svg_sal, a.sal.min_sal, a.sal.max_sal from ( select d.deptno, d.name, (select sal_type( avg(sal), min(sal), max(sal) ) from emp where deptno=d.deptno) sal from dept d where d.loc='CHICAGO') a / |
[참고자료]
1. The Secrets of Oracle Scalar Subqueries!!
http://www.articles.freemegazone.com/oracle-scalar-subqueries.php
2. Subquery
http://www.akadia.com/services/ora_scalar_subquery.html
- 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
- 작성자: 박우창 (balto)
- 최초작성일: 2011년 3월 27일
- 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
- 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
55 |
Front Page
![]() | 운영자 | 2011.02.16 | 114629 |
54 | 2. 인덱스 기본 원리 | balto | 2011.02.18 | 18844 |
53 | 1장. 인덱스 원리와 활용 | 휘휘 | 2011.02.21 | 6796 |
52 |
4. 테이블 Random 액세스 부하
![]() | 휘휘 | 2011.02.26 | 6942 |
51 | 5. 테이블 Random 액세스 최소화 튜닝 | 휘휘 | 2011.02.26 | 9503 |
50 | 6. IOT, 클러스터 테이블 활용 | 오예스 | 2011.02.26 | 15597 |
49 |
7. 인덱스 스캔 효율
![]() | 휘휘 | 2011.03.06 | 8449 |
48 | 9. 비트맵 인덱스 | 휘휘 | 2011.03.07 | 5014 |
47 | 8. 인덱스 설계 | AskZZang | 2011.03.09 | 5726 |
46 | 2. 소트 머지 조인 | 오예스 | 2011.03.21 | 7912 |
45 | 3. 해시 조인 | 휘휘 | 2011.03.21 | 6486 |
44 | 2장. 조인 원리와 활용 | 운영자 | 2011.03.23 | 3307 |
43 |
1. Nested Loops 조인
![]() | 오라클잭 | 2011.03.23 | 23030 |
» |
6. 스칼라 서브쿼리를 이용한 조인
![]() | balto | 2011.03.27 | 18958 |
41 |
5. Outer 조인
![]() | 휘휘 | 2011.03.28 | 17669 |
40 |
1. 인덱스 구조
![]() | 운영자 | 2011.03.30 | 15951 |
39 | 4. 조인 순서의 중요성 | AskZZang | 2011.03.30 | 5328 |
38 | 7. 조인을 내포한 DML 튜닝 | 오예스 | 2011.04.04 | 9946 |
37 | 8. 고급 조인 테크닉-2 | 오라클잭 | 2011.04.05 | 12560 |
36 |
8. 고급 조인 테크닉-1
![]() | 휘휘 | 2011.04.05 | 6460 |