Hinting
around with the ordered
hint
Oracle must spend a great
deal of time parsing
multiple table joins to
determine the optimal
order to join the tables.
SQL statements with table
joins involving seven or
more tables can sometimes
take more than 30 minutes
to parse because Oracle
must evaluate all possible
table join orders. This
can add up to more than
40,000 orders with only
eight tables. The ordered
hint is commonly used in
conjunction with other
hints to suggest a proper
join order.
Also see my notes on the
related
ordered_predicates
hint.
The ordered hint
requests that the tables
listed in the FROM clause
of a SQL statement be
joined in the order
specified, with the first
table in the FROM clause
specifying the driving
table. The driving table
should be the table that
returns the smallest
number of rows. Using the ordered
hint will bypass the very
expensive and
time-consuming parsing
operation and speed the
execution of Oracle SQL.
In Listing A is an example of
a complex query that is
forced to perform a nested
loop join with a parallel
query on the emp
table. Note that I�ve
used the ordered
hint to direct Oracle to
evaluate the tables in the
order they are presented
in the WHERE clause.
Listing A:
select /*+
ordered use_nl(bonus) parallel(e, 4) */
e.ename,
hiredate,
b.comm
from
emp e,
bonus b
where
e.ename = b.ename
;
Also see the related
leading_hint.