Unix/Linux Go Back    

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

Oracle 11g optimizer best join order question


Thread Tools Search this Thread Display Modes
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: 1 March 2018, 4:52 AM EST
Posts: 65
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
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: 24 February 2018, 9:04 PM EST
Posts: 2,084
Thanks: 21
Thanked 384 Times in 347 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 10:47 PM..
The Following User Says Thank You to durden_tyler For This Useful Post:
jim mcnamara (12-19-2015)
Sponsored Links

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

All times are GMT -4. The time now is 11:58 PM.