실습 - 뷰 Merging

Merge 힌트 없이 옵티마이저가 View Merging 실행

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


select /*+ gather_plan_statistiscs */ d.dname, avg_sal_dept
from dept d
		,(select deptno, avg(sal) avg_sal_dept
		  from emp
		  group by deptno) e
where d.deptno = e.deptno
and   d.loc = 'CHICAGO'

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |        |       |       |          |
|   1 |  HASH GROUP BY                |                |      3 |   983K|   983K|  521K (0)|
|   2 |   NESTED LOOPS                |                |      5 |       |       |          |
|   3 |    NESTED LOOPS               |                |      5 |       |       |          |
|*  4 |     TABLE ACCESS FULL         | DEPT           |      1 |       |       |          |
|*  5 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      5 |       |       |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP            |      5 |       |       |          |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("D"."LOC"='CHICAGO')
5 - access("D"."DEPTNO"="DEPTNO")

no_merge 힌트로 View Merging 방지했을 때 조건절 Pushing 시도한 옵티마이저의 트레이스 결과

select /*+ gather_plan_statistiscs */ d.dname, avg_sal_dept
from dept d
		,(select /*+ no_merge */ deptno, avg(sal) avg_sal_dept
		  from emp
		  group by deptno) e
where d.deptno = e.deptno
and   d.loc = 'CHICAGO'

-------------------------------------------------------------------
| Id  | Operation                       | Name           | E-Rows |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |        |
|   1 |  NESTED LOOPS                   |                |      1 |
|*  2 |   TABLE ACCESS FULL             | DEPT           |      1 |
|   3 |   VIEW PUSHED PREDICATE         |                |      1 |
|*  4 |    FILTER                       |                |        |
|   5 |     SORT AGGREGATE              |                |      1 |
|   6 |      TABLE ACCESS BY INDEX ROWID| EMP            |      5 |
|*  7 |       INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      5 |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("D"."LOC"='CHICAGO')
4 - filter(COUNT(*)>0)
7 - access("DEPTNO"="D"."DEPTNO")