Programming

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.

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.