View Merging

In order to generate the best execution plan for the view (inline view), the optimizer may need to transform the SQL query. There are two ways for the transformation:

1. View Merging: merge the view query and the query condition (predicates)
2. Predicate Pushing: If the view merge cannot be performed, push the predicates into the view query

Please note the “direction” of the two methods above is different. The former is to “rewrite” the outer query using the view query (inner query), while the latter on is to push the query condition of the outer query into the view query.
If the outer query includes the following operations, then the “View Merging” will not be applicable…

1. SET operations, like UNION, UNION ALL, INTERSECT, MINUS, etc
2. CONNECT BY
3. ROWNUM
4. Aggregation function in SELECT-list, like SUM, AVG, MAX, MIN, etc
5. GROUP BY ( can use hint MERGE to instruct the optimizer to choose view merging)
6. DISTINCT in SELECT-list ( can use hint MERGE to instruct the optimizer to choose view merging)

If the outer query has many query conditions that can reduce the query range and merge the query condition into the view can reduce the data volume that need to be processed, the view merge is preferable, otherwise the view merging is not necessary.
For example,

CREATE VIEW emp_10(e_no, e_name, job, manager, hire_date, salary) AS SELECT empno, ename, job, mgr, hiredate, sal, comm FROM emp WHERE deptno = 10;
SELECT e_no, e_name, salary, hire_date FROM emp_10 WHERE salary > 10000000;

Can be transformed using view merging as follows,

SELECT empno, ename, sal, hiredate FROM emp WHERE deptno=10 AND sal > 10000000;

Another example,

CREATE VIEW emp_group_by_deptno AS SELECT deptno, AVG(sal) avg_sal, min(sal) min_sal, max(sal) max_sal FROM emp GROUP BY deptno; SELECT * FROM emp_group_by_deptno WHERE deptno=10;
Can be transformed as follows… SELECT deptno, AVG(sal) avg_sal, min(sal) min_sal, max(sal) max_sal FROM emp WHERE deptno=10 GROUP BY deptno;