Introducing the Query Optimizer - Troubleshooting Oracle Performance - page 180

WHEN max(t2.n) OVER (PARTITION BY t2.t1_id) THEN 1
END AS max
FROM t2, t1
WHERE t1.id = t2.t1_id) vw_wif
WHERE max IS NOT NULL
Join Elimination
The purpose of join elimination is to remove redundant joins—in other words, to completely avoid executing a join
even if a SQL statement explicitly calls for it. The key information used by the query optimizer to decide whether it's
sensible to implement this query transformation is the availability of a foreign key that's either enforced or marked
RELY . In addition, as of version 11.2, self-joins based on the primary key are also considered. This heuristic-based
query transformation is especially useful when views containing joins are used. Note, however, that join elimination
can also be applied to SQL statements without views.
Let's take a look at an example based on the join_elimination.sql script. The following SQL statement defines
a view. Note that between the two tables there is a parent-child relationship. In fact, table t2 , with its column t1_id ,
references the primary key of table t1 :
CREATE VIEW v AS
SELECT t1.id AS t1_id, t1.n AS t1_n, t2.id AS t2_id, t2.n AS t2_n
FROM t1, t2
WHERE t1.id = t2.t1_id
When a simple SELECT * FROM v is executed, simple view merging can be done, and the query is transformed
as follows:
SELECT t1.id AS t1_id, t1.n AS t1_n, t2.id AS t2_id, t2.n AS t2_n
FROM t1, t2
WHERE t1.id = t2.t1_id
However, as illustrated in the next example, when only columns defined in the child tables are referenced (for
example, SELECT t2_id, t2_n FROM v ), the query optimizer is able to eliminate the join to the parent table. It can do
so because there's a foreign key constraint that guarantees that all rows in table t2 reference one and only one row in
table t1 :
SELECT t2.id AS t2_id, t2.n AS t2_n
FROM t2
Join Factorization
The purpose of join factorization , which is available as of version 11.2, is to recognize whether part of the processing
of a compound query can be shared across component queries, with the goal being to avoid repetitive data accesses
and joins. In fact, without this query transformation, all component queries would be executed idependently
before applying the set operator. This is a cost-based query transformation that the query optimizer applies only to
compound queries based on the UNION ALL set operator.