By Mike Ault,
In Oracle, the number of possible paths a join can
take is based on the number of tables participating in the join. See
these important details on determining the
optimal table join order and how to tune SQL to
join tables together in the correct order.
The raw number of possible combinations is
determined using the n! (factorial) of the number of tables. For those a bit
rusty with what a factorial is, basically you take each integer up to "n"
times each other, for example, 4!=(1*2*3*4) which is equal to 24. Now this
doesn't seem to bad, until you do 8 and above tables. The value for 8! is
40,320 possible join paths.
The parameter for setting the maximum allowed number
of paths for the optimizer to consider is
optimizer_max_permutations and it is set to 80,000 by default.
In and beyond this parameter remains the same but
it has been reduced to 2,000, under the assumption that if you don't find
the proper path in the first 2000 you probably won't find it. However in
empirical testing the join orders are evaluated left-to-right in the list of
tables, therefore if you are doing a 9 table join in 8i and the important
(most limiting) table is at the end of the list, it won't be considered.
Likewise in if it is placed 7th or greater in the list. What this leads
us to is the first tip for join order evaluation: Place the most limiting
tables for the join first in the FROM clause.
It has been found that by changing the default value
of the optimizer_max_permutations
setting to a value less than the original setting that join orders are
evaluated first. For example just reducing the setting by one in 8i to
79,999 improves the consideration of join orders. In setting it to 1,999
has similar results. So this leads to our second tip: Set the
optimizer_max_permutations parameter to slightly less than the
default value to improve join order consideration.
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 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.
So 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 wil have the same result if you are squemish about using
undocumented parameters and utilizing the tip to reset
optimizer_max_permutations may
achieve the same result.
Another parameter of interest is the
optimizer_search_limit. The
optimizer_search_limit parameter
defaults to 5.
The default value of five means that 5 factorial
(5!) possibilities will be considered.
If the number of tables returning more than a single row in
the query is less than
optimizer_search_limit then the full factorial number of possible joins
will be used in determining the path. If the number of tables returning more
than a single row is greater than the
optimizer_search_limit then Cartesian products are eliminated from the
possible joins considered. Therefore the maximum number of joins considered
for a given query with less than
optimizer_search_limit+1 of involved tables can be expressed by either
the value of optimizer_max_permutations or the
optimizer_search_limit_factorial, whichever is larger.
If the number of non-single row tables in a query is
greater than optimizer_search_limit,
then the maximum number of permutations to consider is the larger of:
(optimizer_max_permutations or
optimizer_search_limit factorial)
divided by (number of possible start tables + 1)
Usually you don't touch the setting of the
optimizer_search_limit. However,
the setting of 5 for optimizer_search_limit is the default setting and it
can be set to any integer value. It is unlikely that resetting this value
will have much effect unless you have a significant number of large joins
such as in a DSS or data warehouse. If the STAR join is enabled, the
optimizer_search_limit sets the threshold above which the STAR
algorithm will be considered for tables joins.
In 10g and beyond both the
optimizer_max_permutations and optimizer_search_limit
are being deprecated and made into undocumented settings defaulting to 2000
and 5 respectively.
So to summarize our tips:
1. Place the most limiting tables first in the FROM
clause.
2. Reduce the setting of
optimizer_max_permutations by at least one.
3. In 8i consider resetting the "_new_initial_join_orders"
undocumented parameter to TRUE or set event 10131 to 1 or greater.
4. In DSS and DWH environments the
optimizer_search_limit can be used
to alter the threshold for use of STAR optimization paths.
and include the URL for the page.
