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.
 
Search WWH ::




Custom Search