Introducing the Query Optimizer - Troubleshooting Oracle Performance - page 172

Simple View Merging
In the following example, based on the simple_view_merging.sql script, the query is based on three query blocks:
the top-level query and two inline views. Notice that the two inline views are plain, select-project-join query blocks:
SELECT *
FROM (SELECT t1.*
FROM t1, t2
WHERE t1.id = t2.t1_id) t12,
(SELECT *
FROM t3
WHERE id > 6) t3
WHERE t12.id = t3.t1_id
Because the inline views can be merged, simple view merging produces the following query:
SELECT t1.*, t3.*
FROM t1, t2, t3
WHERE t1.id = t3.t1_id AND t1.id = t2.t1_id AND t3.id > 6
Simple view merging can't always be performed when outer joins are involved. For example, view merging can be
performed on the previous query if the predicate of the top-level query is modified to t12.id = t3.t1_id(+) ,
but not if the predicate is changed to t12.id(+) = t3.t1_id .
Complex View Merging
The following example, based on the complex_view_merging.sql script, shows an inline view having a GROUP BY
clause. Such a query is executed in the following manner: the table referenced in the inline view is accessed, the
GROUP BY clause and the sum function are evaluated, and finally the result set of the inline view is joined to the table
referenced in the top-level query:
SELECT t1.id, t1.n, t1.pad, t2.sum_n
FROM t1, (SELECT n, sum(n) AS sum_n
FROM t2
GROUP BY n) t2
WHERE t1.n = t2.n
When it's beneficial to postpone the evaluation of the GROUP BY clause until after the join, complex view merging
generates the following query:
SELECT t1.id, t1.n, t1.pad, sum(n) AS sum_n
FROM t1, t2
WHERE t1.n = t2.n
GROUP BY t1.id, t1.n, t1.pad, t1.rowid, t2.n
Select List Pruning
The purpose of select list pruning is to remove unnecessary columns or expressions from the SELECT clause of
subqueries, inline views, or regular views. Top-level SELECT clauses aren't considered by this query transformation.