Re: Oracle CBO question

On 6/7/07, Joel.Patterson_at_crowley.com <Joel.Patterson_at_crowley.com> 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

--
http://www.freelists.org/webpage/oracle-l