Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

The Importance of Table Join Order

Oracle Tips by Burleson Consulting

 

Also see these important notes on 11g extended optimizer statistics, Oracle histogram analysis with dbms_stats, and Dynamic Sampling for fast SQL performance.


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.


 

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational