Database Reference
In-Depth Information
A compound query based on the
INTERSECT
and
MINUS
set operators is basically carried out in the following way:
1.
Every component query is independently executed, the result set is sorted, and duplicates
are eliminated.
2.
Then the set operations are executed, and the final result set is determined.
This way of executing a query involving an
INTERSECT
or
MINUS
operation isn't always efficient. For example,
when the component queries return a lot of data, but the majority of data is eliminated by the set operator, most of
the data that's later eliminated ends up being unnecessarly sorted. The
set to join conversion
avoids that inefficiency
by transforming the query in a way that allows rows to be thrown out
prior
to the sort rather than after it. In addition,
since the set operator is replaced by a join, additional access paths are enabled. This is a heuristic-based query
transformation that, by default, isn't enabled.
2
The
set_to_join
hint has to be specified to take advantage of it.
The following is an example based on the
set_to_join.sql
script. The compound query is based on the
INTERSECT
set operator:
SELECT *
FROM t1
WHERE n > 500
INTERSECT
SELECT *
FROM t2
WHERE t2.pad LIKE 'A%'
The query transformation converts the set operator to a join. Furthermore, to ensure that only the required rows
are returned, the query transformation also adds several predicates and a
DISTINCT
operator. Here is the final query
after set to join conversion is complete:
SELECT DISTINCT t1.*
FROM t1, t2
WHERE t1.id = t2.id AND t1.n = t2.n AND t1.pad = t2.pad
AND t1.n > 500 AND t1.pad LIKE 'A%'
AND t2.n > 500 AND t2.pad LIKE 'A%'
Star Transformation
Star transformation
is a cost-based query transformation used for queries extracting data from a star schema. Chapter
14 provides detailed information about star schemas and optimizing the joins of queries against such schemas.
Query Rewrite with Materialized Views
Query rewrite with materialized views
is an optimization technique allowing the database engine to access data stored
in materialized views even when no materialized view is directly referenced in the query to be optimized. Chapter 15
discusses this type of query transformation in detail.