 |
|
The Ordered Hint
Oracle Tips by
Burleson
|
As I noted in detail in Chapter 12, Oracle must
spend a great deal of time parsing n-way table joins to determine the
optimal order to join the tables. The ordered hint can be used
to reduce the parse time for queries that join more than five tables,
and the ordered hint tells the CBO to join the tables in the same
order in which they appear in the from clause. Hence, the first
table after the from clause becomes the driving table, and the
driving table should be the table that returns the smallest number of
rows.
In the cost-based optimizer, the ordered
hint requests that the tables should be joined in the order specified
in the from clause, with the first table in the from
clause specifying the driving table.
The ordered hint is commonly used in
conjunction with other hints to ensure that multiple tables are joined
in their proper order. For example, we may have a query that joins
five tables together, and we want several of the joins to use a hash
join and other tables to use a nested loop join. The ordered
hint is very common in tuning data warehouse queries that join more
than four tables together.
TIP: Large n-way table joins with seven or
more tables can often take more than 30 minutes to parse the SQL. This
is because Oracle must evaluate all possible table join orders. For
example, with eight tables, Oracle must evaluate 8!, or 40,320,
possible join combinations. Most people use the ordered hint to
bypass this very expensive and time-consuming SQL parsing operation.
The ordered_predicates Hint
The ordered_predicates hint is a
specialized hint that is specified in the where clause of a
query; it directs the order in which the Boolean predicates in the
where clause are evaluated. As we may know, Oracle should always
evaluate the most restrictive predicate first, thereby reducing the
size of the intermediate result sets.
Without the ordered_predicates hint,
Oracle uses the following steps to evaluate the order of SQL
predicates.
1.
Subqueries are evaluated before the outer Boolean conditions in
the where clause.
2.
All Boolean conditions without built-in functions or subqueries
are evaluated in their reverse order in the where clause, going
from bottom-up, with the last predicate being evaluated first.
3.
With Boolean predicates with built-in functions, the optimizer
computes the cost of each predicate and evaluates them in increasing
order of their costs.
Whenever the CBO makes a mistake in the
evaluation order of your predicates, these default evaluation rules
can be overridden by using the ordered_predicates hint. This
hint is the equivalent of resequencing Booleans in the where
clause for the RBO, where the where clause items are evaluated
in the order that they appear in the query. This hint is very useful
in cases where you know the most restrictive predicates and you want
to control the order in which Oracle evaluates conditions in the where
clause.
The ordered_predicates hint is commonly
used in cases where a PL/SQL function is used inside the where
clause of a query.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
