 |
|
Oracle table join order tips
Oracle Tips by Burleson Consulting
February 8, 2008
|
For manually determining the best table join order
(to add an “ordered” or “leading” hint), the WHERE clause of the query can be
inspected along with the execution plan for the original query. Also see my
notes on how
10g dynamic sampling
influences table join order.
The problem with forcing the sequence of table joins with the ordered
hint is that it can be impossible to predict a priori the optimal table-join
order (the one that has the smallest intermediate baggage).
Reducing the size of the intermediate row-sets can
greatly improve the speed of the query.
select
stuff
from
customer
natural join
orders
natural join
item
natural join
product
where
credit_rating * extended_credit > .07
and
(qty_in_stock * velocity) /.075 < 30
or
(sku_price / 47) * (qty_in_stock / velocity) > 47;
In this example, the four-way table join only
returns 18 rows, but the query carries 9,000 rows in intermediate result sets,
slowing-down the SQL execution speed.
Sub-optimal intermediate row sets.
If we were somehow able to predict the sizes of the
intermediate results, we can re-sequence the table-join order to carry less
“intermediate baggage” during the four-way table join, in this example carrying
only 3,000 intermediate rows between the table joins.
Optimal intermediate row sets.
Table join order issues:
See my related notes on having the CBO determine the
optimal table join order:
Table join order in Oracle 8i
In Oracle8i the undocumented parameter "_new_initial_join_orders"
was set to FALSE by default, by setting it to TRUE a better determination of
join orders is implemented in some cases. Under 9i and 10g this parameter has
been defaulted to TRUE. However, resetting any undocumented parameter should not
be undertaken lightly, however Oracle itself recommends setting this parameter
to TRUE as a result of bug 1002975 in 8.1.6 and 8.1.7.
Our final tip is to set the "_new_initial_join_orders"
undocumented parameter to TRUE in 8i. Note that setting event 10131 to level 1
or higher will have the same result if you are squeamish about using
undocumented parameters and utilizing the tip to reset
optimizer_max_permutations may achieve the same result.
In 8i and beyond, consider resetting the "_new_initial_join_orders"
undocumented parameter to TRUE or set event 10131 to 1 or greater.
