Re: Oracle CBO question
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: Joel.Patterson@xxxxxxxxxxx
- Date: Thu, 7 Jun 2007 10:58:29 -0700
On 6/7/07, Joel.Patterson@xxxxxxxxxxx <Joel.Patterson@xxxxxxxxxxx> wrote:
I have received a question from a consultant developer – below. Can
anyone answer?
I was once told that the CBO can only handle a certain number of tables in
a query (16 iirc). Do you know what the CBO does when there are more? Try
to optimize based on knowledge of the first 16 tables in the Where clause?
Perhaps what he heard was that the number of join orders considered
by the optimzer could not accomodate checking all possible join orders.
Though I'm not sure why that would matter, as it seems very unlikely
you would ever want to consider all possible join orders.
With 16 tables all possible join orders is !16 which is 20,922,789,888,000
possibilities.
Having seen what happens when the 8i optimizer tries to consider all 80k
possibilities when considering join orders I would say it might be a
problem.
(setting optimizer_max_permutations = 2000 fixed that app. thankfully the
default on newer incarnations of Oracle)
Why the number 16 was chosen I am not sure.
Perhaps someone speculated there was a 4 bit limitation?
With the 8i default of 80k permutations the number of possibile join orders
runs out somewhere between 8! and 9!.
With OMP = 2k it runs out somewhere between 6! and 7!.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
