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 | 114630 |
54 | 1. 테이블 파티셔닝 | 오라클잭 | 2011.06.21 | 28274 |
53 | 5. 병렬 처리에 관한 기타 상식 [1] | balto | 2011.06.26 | 28162 |
52 | 1. Nested Loops 조인 | 오라클잭 | 2011.03.23 | 23031 |
» | 6. 스칼라 서브쿼리를 이용한 조인 | balto | 2011.03.27 | 18959 |
50 | 2. 인덱스 기본 원리 | balto | 2011.02.18 | 18848 |
49 | 5. Outer 조인 | 휘휘 | 2011.03.28 | 17670 |
48 | 4. 조건절 Pushing | 오예스 | 2011.05.31 | 17496 |
47 | 6. 히스토그램 | 오예스 | 2011.04.25 | 17418 |
46 | 4. 통계정보 Ⅰ | 토시리 | 2011.04.25 | 16017 |
45 | 1. 인덱스 구조 | 운영자 | 2011.03.30 | 15954 |
44 | 4. PQ_DISTRIBUTE 힌트 | 휘휘 | 2011.06.27 | 15620 |
43 | 6. IOT, 클러스터 테이블 활용 | 오예스 | 2011.02.26 | 15604 |
42 | 5. 카디널리티 | 오라클잭 | 2011.04.27 | 12922 |
41 | 8. 고급 조인 테크닉-2 | 오라클잭 | 2011.04.05 | 12564 |
40 | 2. 파티션 Pruning | 휘휘 | 2011.06.19 | 11868 |
39 | 2. 병렬 Order By와 Group By | 휘휘 | 2011.06.30 | 10127 |
38 | 7. 조인을 내포한 DML 튜닝 | 오예스 | 2011.04.04 | 9955 |
37 | 10. 실체화 뷰 쿼리로 재작성 | 오라클잭 | 2011.06.08 | 9953 |
36 | 5. 테이블 Random 액세스 최소화 튜닝 | 휘휘 | 2011.02.26 | 9507 |