edit3.2.5. 기타 특수한 목적을 처리하는 실행계획

- 순환(Recursive) 전개 실행계획
- Update Set 절 서브쿼리 실행계획
- 특이한 형태의 실행계획
- 종합 실행계획 사례 연구

edit3.2.5.1. 순환(Recursive) 전개 실행계획

edit- connect by .. start with 구문을 사용했을때의 실행계획

    SELECT LPAD(' ', 2*(LEVEL-1))||ename, empno, sal, mgr, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
    FROM emp
(3) WHERE job = 'CLEAK'
(2) CONNECT BY mgr = PRIOR empno
(1) START WITH empno = :b1;

Execution Plan
------------------------------------------------------------
SELECT STATEMENT
  FILTER                                              3
   CONNECT BY (WITH FILTERING)
     NESTED LOOPS
       INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)       1-1
       TABLE ACCESS (BY USED ROWID) OF 'EMP'          1-2
     NESTED LOOPS
       BUFFER (SORT)                                  2-1
         CONNECT BY PUMP
       TABLE ACCESS (BY INDEX ROWID) OF 'EMP'         2-2
         INDEX (RANGE SCAN) OF 'MGR_IDX' (UNIQUE)

1-1. ROOT에 해당되는 start with 에 해당하는 로우를 버퍼에 저장
1-2. 1-1에서 찾은 로우를 하나식 ACCESS ( by userd rowid )
2-1. prior empno 값을 상수 값으로 해서 2-2를 실행할수 있도록 한다.
2-2. 값을 찾는다. 하나 이상 발생할수 있으므로 버퍼에 저장한다.
3. 2-1, 2-2를 계속 반복해서 값을 찾는다. where 구문은 단지 filter 역활만을 한다.


edit- siblings by

  connect by .. start with 에서 찾은 값을 정렬을 할때 사용한다.

edit- 해쉬조인을 이요한 내측루프 수행

  connect by에 사용되는 컬럼에 인덱스가 없거나, 테이블의 크기가 적을때
Execution Plan
------------------------------------------------------------
SELECT STATEMENT
  FILTER
    CONNECT BY (WITH FILTERING)
      NESTED LOOPS
        INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
        TABLE ACCESS (BY USER ROWID) OF 'EMP'
  해쉬조인을 이용하면 전체 테이블 스캔을 하기 때문에 테이블의 크기에 따라 부담이 될수 있다.
  가능하면 connect by 에 사용되는 필드는 인덱스 처리를 해야 한다.

edit- 서브쿼리가 있는 경우

  WHERE 구문에 있는 sub-query는 filter (체크조건) 처리가 된다.
SELECT * FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'BOSTON')
CONNECT BY mgr = PRIOR empno
START WITH empno = 7698;

Execution Plan
------------------------------------------------------------
SELECT STATEMENT
  FILTER
    CONNECT BY (WITH FILTERING)
      NESTED LOOPS
        INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
        TABLE ACCESS (BY USED ROWID) OF 'EMP'
      HASH JOIN
        CONNECT BY PUMP
        TABLE ACCESS (FULL) OF 'EMP'
    TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
      INDEX (RANGE SCAN) OF 'PK_DEPT' (UNIQUE)

edit- start with 절에 서브 쿼리

버전마다 실행계획이 조금식 다르며, 확인을 해봐야 한다.

SELECT * FROM emp
CONNECT BY mgr = PRIOR empno
START WITH empno IN (SELECT mgr_empno FROM department WHERE loc = 'HQ');

Execution Plan
------------------------------------------------------------
SELECT STATEMENT
  CONNECT BY (WITH FILTERING)
    NESTED LOOPS
      HASH JOIN(SEMI)
        INDEX (FULL SCAN) OF 'PK_EMP' (UNIQUE)
        TABLE ACCESS (BY USED ROWID) OF 'DEPARTMENT'
          INDEX (RANGE SCAN) OF 'DEPARTMENT_LOC_IDX' (NON-UNIQUE)
      TABLE ACCESS (BY USER ROWID) OF 'EMP'
    HASH JOIN
      CONNECT BY PUMP
      TABLE ACCESS (FULL) OF 'EMP'
?   TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT'
?     INDEX (RANGE SCAN) OF 'DEPARTMENT_X1' (NON-UNIQUE)

edit- 조인쿼리를 이용하는 경우

각 테이블을 전체 스캔후, start with 조건으로 검색하여 부하가 많이 발생할수도 있다.
인라인뷰를 이용하여 선 전개후, 그 결과를 조인하는 방식으로 사용해야 한다.
SELECT e.*, d.*
FROM (SELECT * FROM emp CONNECT BY mgr = PRIOR empno START WITH empno = 7839) e,
     dept d
WHERE e.deptno = d.deptno;

Execution Plan
--------------------------------------------------------------------
SELECT STATEMENT
  HASH JOIN
    VIEW
      CONNECT BY (WITH FILTERING)
        NESTED LOOPS
          INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
            TABLE ACCESS (BY USER ROWID) OF 'EMP'
        HASH JOIN
          CONNECT BY PUMP
          TABLE ACCESS (FULL) OF 'EMP'
    TABLE ACCESS (FULL) OF 'DEPT'


edit3.2.5.2. UPDATE 서브쿼리 실행계획

UPDATE emp e
SET sal = (SELECT AVG(sal) * 1.2 FROM bonus b
            WHERE b.empno = e.empno
              AND b.pay_date BETWEEN :b1 AND :b2)
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'BOSTON');

Execution Plan
---------------------------------------------------------------------
UPDATE STATEMENT
  UPDATE OF 'EMP'
    TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
      NESTED LOOPS
        TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
          INDEX (RANGE SCAN) OF 'LOC_IDX' (NON_UNIQUE)
        INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)
    SORT (AGGREGATE)
      TABLE ACCESS (BY INDEX ROWID) OF 'BONUS'
        INDEX (RANGE SCAN) OF 'BONUS_PK' (UNIQUE)

- 서브쿼리가 수행된 결과가 'No data found'이면 NULL로 갱신될수 있다.
  이경우는 nvl을 이용해 비교할 값도 없어 null로 갱신된다.
- SUM, AVG, MAX, MIN, COUNT 등과 같은 그룹함수는 실패가 발생하지 않는다.
- 수정가능 조인뷰(Modifiable Join View) 이용한 update
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT x.empno, x.ename, x.job, x.sal, y.loc, y.dname
FROM emp x, dept y
WHERE x.deptno = y.deptno;

UPDATE emp_dept_view e SET sal = decode(dname, 'SALES', 1.2, 1.1) * sal
WHERE loc LIKE 'AB%';

Execution Plan
------------------------------------------------------------------
UPDATE STATEMENT
  UPDATE OF 'EMP'
    HASH JOIN
      TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
        INDEX (RANGE SCAN) OF 'LOC_IDX' (NON-UNIQUE)
      TABLE ACCESS (FULL) OF 'EMP'

edit3.2.5.3. 특이한 형태의 실행계획

- 서브쿼리 팩토리(Factoring)
- 특이한 DELETE 문 서브쿼리
- 다중 테이블 입력(Multi-table Insert)
- Having 서브쿼리
- cube 처리
- grouping sets 처리
- rollup 처리
- merge 문

edit- 서브쿼리 팩토리(Factoring)

WITH 절을 사용하여 생성한 복잡한 쿼리 문을 임시 테이블이 실제로 저장을 해 두었다가 사용하는 기능
개념적으로 인라인뷰를 이용하여 사용하는 것과 유사하지만 장.단점이 있다.
WITH total_sal AS
     (SELECT d.deptno, d.loc, e.job, sum(e.sal) tot_sal
        FROM emp e, dept d
       WHERE e.deptno = d.deptno
         AND e.hiredate > :b1
       GROUP BY d.deptno, d.loc, e.job)
SELECT e.empno, e.ename, e.sal, e.sal/t.total_sal sal_percent
FROM emp e, total_sal t
WHERE e.deptno = t.deptno
AND e.sal > (SELECT max(total_sal) FROM total_sal WHERE job = 'CLERK');

Execution Plan
---------------------------------------------------------------
SELECT STATEMENT
  RECURSIVE EXECUTION OF 'SYS_LE_2_0'
  TEMP TABLE TRANSFORMATION
    TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
      NESTED LOOPS
        VIEW
          TABLE ACCESS (FULL) OF 'SYS_TEMP_0000'
        INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)
      SORT (AGGREGATE)
        VIEW
          TABLE ACCESS (FULL) OF 'SYS_TEMP_0000'

> InLine 뷰를 사용하게 되면 with 부분을 쿼리문에 대치시켜서 실행해보면 된다( 이중으로 실행된다 ).
> 쿼리 변형이 불가능하다.
> 인덱스를 가질 수 없다( 결과가 소량이 아니면 비효율적 ).
> 복잡한 가공이 필요한 곳에서 한번의 쿼리로 여러 번 사용하는 곳에서는 가장 큰 장점
> 다중으로 쿼리를 생성해도 하나의 임시 테이블로 처리함( p.288 참조 )


edit- 특이한 DELETE 문 서브쿼리

DELETE FROM (SELECT * FROM emp
              WHERE job = 'CLERK'
                AND comm > 10000 AND deptno IN (SELECT deptno FROM dept WHERE loc = 'BOSTON')
            );

Execution Plan
---------------------------------------------------
DELETE STATEMENT
  DELETE OF 'EMP'
    NESTED LOOPS
      TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
        INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
        INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

> SELECT 문처럼 조인을 사용하여 삭제대상을 액세스 함


edit- 다중 테이블 입력(Multi-table Insert)

INSERT ALL
  WHEN order_total < 100000
    THEN INTO small_orders
  WHEN order_total > 100000 AND order_total < 200000
    THEN INTO medium_orders
  WHEN order_totla > 200000
    THEN INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;

Execution Plan
-------------------------------------------
INSERT STATEMENT
  MULTI-TABLE INSERT
    INTO OF 'SMALL_ORDERS'
    INTO OF 'MEDIUM_ORDERS'
    INTO OF 'LARGE_ORDERS'
      TABLE ACCESS (FULL) OF 'orders'


edit- Having 서브쿼리

SELECT department_id, manager_id
  FROM employees
 GROUP BY department_id, manager_id
 HAVING (department_id, manager_id) IN (SELECT e.deptno, e.mgr FROM emp e, dept d
                                         WHERE e.deptno = d.deptno
                                           AND d.loc = 'BOSTON');

Execution Plan
----------------------------------------------------
SELECT STATEMENT
  FILTER
    SORT (GROUP BY)
      TABLE ACCESS (FULL) OF 'EMPLOYEES'
    NESTED LOOPS
      TABLE ACCESS (BY INDEX ROWID) OF 'dept'
        INDEX (RANGE SCAN) OF 'LOC_IDX' (NON-UNIQUE)
      AND-EQUAL
        INDEX (RANGE SCAN) OF 'EMP_MGR_IDX' (NON-UNIQUE)
        INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)


edit- cube, grouping sets, rollup 처리

SELECT co.country_region, co.country_subregion, SUM(s.amount_sold) "Revenus", GROUP_ID() g
FROM sales s, customers c, countries co
WHERE s.cust_id = c.cust_id AND c.country_id = co.country_id AND s.time_id = :b1
AND co.country_region IN ('Americas', 'Europe')
GROUP BY ROLLUP (co.country_region, co.country_subregion);

Execution Plan
------------------------------------------------------
SELECT STATEMENT
  SORT (GROUP BY ROLLUP)
    NESTED LOOPS
      NESTED LOOPS
        PARTITION RANGE (SINGLE)
          TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES'
            BITMAP CONVERSION (TO ROWID)
              BITMAP INDEX (SING VALUE) OF 'SALES_TIME_BIX'
        TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'
          INDEX (UNIQUE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)
      INDEX (UNIQUE SCAN) OF 'COUNTRY_C_ID_PK' (UNIQUE)


edit- merge 문

MERGE INTO bonuses D
USING (
       SELECT employee_id, salary, department_id
         FROM employees
        WHERE department_id = 80
      ) s
ON (d.employee_id = s.employee_id)
WHEN MATCHED THEN
  UPDATE SET d.bonus = d.bonus + s.salary * 0.1;
  DELETE where (s.slary > 8000)
WHEN NOT MATCHED THEN
  INSERT (d.employee_id, d.bonus) VALUES (s.employee_id, s.salary*0.1)
;