Introducing the Query Optimizer - Troubleshooting Oracle Performance - page 181

The following is an example based on the join_factorization.sql script. Notice that both component queries
not only access the same tables, they also apply a restriction to the same table ( t2 ). Without this query transformation,
both component queries would be executed idependently, and both tables would be accessed twice:
SELECT *
FROM t1, t2
WHERE t1.id = t2.id AND t2.id < 10
UNION ALL
SELECT *
FROM t1, t2
WHERE t1.id = t2.id AND t2.id > 990
To avoid the repetitive processing of accessing each table twice, join factorization can transform the query, as
shown in the following example. Because table t1 is factorized, it's accessed only once. Depending on the table's size
and the access path chosen to extract data from it, the savings in term of I/O and CPU utilization can be huge:
SELECT t1.*, vw_jf.*
FROM t1, (SELECT *
FROM t2
WHERE id < 10
UNION ALL
SELECT *
FROM t2
WHERE id > 990) vw_jf
WHERE t1.id = vw_jf.id
Outer Join to Inner Join
The purpose of outer join to inner join is to convert superfluous outer joins into inner joins. This is done because outer
joins might prevent the query optimizer from choosing a specific join method or order. This is a heuristic-based query
transformation.
The following example, based on the outer_to_inner.sql script, illustrates this query transformation. Notice
that the restriction ( t2.id IS NOT NULL ) conflicts with the outer join condition ( t1.id = t2.t1_id(+) ):
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id(+) AND t2.id IS NOT NULL
The query transformation removes the outer join operator as well as the redundant predicate and produces the
following query:
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id