[SQL Tuning]Subquery operation(서브쿼리 오퍼레이션)

Subquery operation

Subquery

• SQL문 안에 select 문이 있는 쿼리
• Nested Subquery
• where 절에 사용
• Inline View 
• From 절에 사용
• Scalar Subquery
• "하나"의 컬럼 값 "하나"를 리턴

Nested Subquery

• Single Row Subquery
• Multiple Row Subquery
• Multiple Column Subquery

USER01@orcl2> select last_name, salary, department_id

              from employees

              where salary in (select min(salary)

                              from employees

                              group by department_id);

-- dept는 중복되서 나온다.

                 

-- mulitiple subquery(Pairwise)를 이용해 중복이 나오지 않도록 한다.

USER01@orcl2> select last_name, salary, department_id

              from employees

              where (salary,department_id) in (select min(salary), department_id

                                               from employees

                                               group by department_id);

-- Non-Pairwise 방식

USER01@orcl2> select last_name, salary, department_id

              from employees

              where salary in (select min(salary)

                               from employees

                               group by department_id)

              and department_id in (select department_id

                                    from employees

                                    group by department_id);

=> 중복이 나온다....

• Correlated Subquery
• 상호 연관된 서브쿼리

USER01@orcl2> create table emp2 as select empno, ename, deptno from emp;

USER01@orcl2> alter table emp2 add(dname varchar2(20));

     EMPNO ENAME          DEPTNO DNAME

---------- ---------- ---------- --------------------

      7369 SMITH              20

      7499 ALLEN              30

      7521 WARD               30

      7566 JONES              20

      7654 MARTIN             30

      7698 BLAKE              30

      7782 CLARK              10

      7788 SCOTT              20

      7839 KING               10

      7844 TURNER             30

      7876 ADAMS              20

      7900 JAMES              30

      7902 FORD               20

      7934 MILLER             10

USER01@orcl2> update emp2 M

              set dname = (select dname from dept where deptno = M.deptno);

-- 진한 부분이 서브쿼리

     EMPNO ENAME          DEPTNO DNAME

---------- ---------- ---------- --------------------

      7369 SMITH              20 RESEARCH

      7499 ALLEN              30 SALES

      7521 WARD               30 SALES

      7566 JONES              20 RESEARCH

      7654 MARTIN             30 SALES

      7698 BLAKE              30 SALES

      7782 CLARK              10 ACCOUNTING

      7788 SCOTT              20 RESEARCH

      7839 KING               10 ACCOUNTING

      7844 TURNER             30 SALES

      7876 ADAMS              20 RESEARCH

      7900 JAMES              30 SALES

      7902 FORD               20 RESEARCH

      7934 MILLER             10 ACCOUNTING

Simple View

• 조건절과 조인문만을 포함
• 언제든 Merging이 일어난다.
• 힌트 : merge, no_merge

USER01@orcl2> select *

              from (select * from emp where job ='SALESMAN') a,

                   (select * from dept where loc='CHICAGO') b

              where a.deptno = b.deptno;

USER01@orcl2> @xplan

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  8fa55s24wmfc3, child number 0

-------------------------------------

select *      from (select * from emp where job ='SALESMAN') a,

(select * from dept where loc='CHICAGO') b      where a.deptno =

b.deptno

Plan hash value: 2255583908

-------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |             |      1 |        |      4 |00:00:00.01 |       7 |

|   1 |  NESTED LOOPS                 |             |      1 |        |      4 |00:00:00.01 |       7 |

|   2 |   NESTED LOOPS                |             |      1 |      1 |      4 |00:00:00.01 |       5 |

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT        |      1 |      1 |      1 |00:00:00.01 |       3 |

|*  4 |     INDEX RANGE SCAN          | DEPT_LOC_IX |      1 |      1 |      1 |00:00:00.01 |       2 |

|*  5 |    INDEX RANGE SCAN           | EMP_JOB_IX  |      1 |      3 |      4 |00:00:00.01 |       2 |

|*  6 |   TABLE ACCESS BY INDEX ROWID | EMP         |      4 |      1 |      4 |00:00:00.01 |       2 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("LOC"='CHICAGO')

   5 - access("JOB"='SALESMAN')

   6 - filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

USER01@orcl2> select *

              from emp a, dept b

              where a.deptno = b.deptno

              and a.job = 'SALESMAN'

              and b.loc='CHICAGO';

Subquery transformation 방법

• Nested Subquery
• 단일행 서브쿼리 : unnest 하지 않는다.
• 다중행 서브쿼리 : unnest => join
• nounnest 힌트, 실패 시 => Filter
• Inline View, View
• View merging 이 발생
• view가 따로수행이 아니라, 뷰를 부른 문장이랑 섞여서 실행된다는 말.
• Simple View : 무조건 merging(simple view merging)
• Complex View 
• distinct 등....
• view merging를 못하는 경우
• set operation : 따로따로 수행해야 한다
• union 등...
• connect by
• rownum : stocky
• aggregation function중 일부
• sum, count 등...