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

Oracle 11g optimizer best join order question

👤 Login to reply

Thread Tools Search this Thread Display Modes
Old 12-14-2015
Perlbaby Perlbaby is offline
Registered User
Join Date: Oct 2011
Last Activity: 6 June 2018, 4:00 AM EDT
Posts: 69
Thanks: 10
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 12-18-2015
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
Join Date: Apr 2009
Last Activity: 9 May 2018, 9:38 PM EDT
Posts: 2,091
Thanks: 23
Thanked 389 Times in 351 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
👤 Login to reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

All times are GMT -4. The time now is 06:42 AM.

Unix & Linux Forums Content Copyrightę1993-2018. All Rights Reserved.
Show Password

Not a Forum Member?
Forgot Password?