Unix/Linux Go Back    


Programming Post questions about C, C++, Java, SQL, and other programming languages here.

Oracle 11g optimizer best join order question

Programming


Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 12-14-2015   -   Original Discussion by Perlbaby
Perlbaby's Unix or Linux Image
Perlbaby Perlbaby is offline
Registered User
 
Join Date: Oct 2011
Last Activity: 19 September 2017, 2:57 AM EDT
Posts: 58
Thanks: 9
Thanked 0 Times in 0 Posts
Oracle 11g optimizer best join order question

Hi team

Optimizer max permutations and search limit are set to 3.5 million and 10 respectively.The parameter instruct optimizer to evaluate up to 3.5 million permutations for max 10 table joins
I have query with 9 tables joined.
How many combinations will optimizer perform to find best join order? within parameter bounds
Please share me the answer . Is it 9! or 10! or 3.5 million or something else.
Sponsored Links
    #2  
Old Unix and Linux 12-18-2015   -   Original Discussion by Perlbaby
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
Join Date: Apr 2009
Last Activity: 9 September 2017, 1:30 PM EDT
Posts: 2,083
Thanks: 21
Thanked 383 Times in 346 Posts
Mathematically, the total number of permutations for joining 9 tables would be 9! or 362880.
However, the parameters - optimizer_max_permutations and optimizer_search_limit are undocumented (deprecated) in Oracle version 10g and higher.

Oracle hasn't mentioned or documented the algorithm it uses to derive the value, so I wouldn't rely too much on that number.

Besides, since they are deprecated: (a) use them at your own risk and (b) Oracle will not provide any support in case you run into problems using them.

Last edited by durden_tyler; 12-18-2015 at 11:47 PM..
The Following User Says Thank You to durden_tyler For This Useful Post:
jim mcnamara (12-19-2015)
Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes



All times are GMT -4. The time now is 08:40 PM.