(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
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^