According to Mohamed Zait's presentation last year in Zurich at Trivadis CBO Days the CBO does join search space optimizations like: - When number of tables <=5 it performs exhaustive search - When number of tables > 5 the it performs heuristic search. The search is repeated using various initial join order heuristics. An example of initial join order heuristic is to order tables on their number of rows. - Number of join permutations is limited to 2000 for each query block (each select statement within a statement) and the plan prefix of previous join permutations is reused and note performed once again - 1-Row tables are identified and fixed as join prefix (Equality predicate on unique column means 1-Row table) - Current partial plan is discarded as soon as it becomes more expensive than the best plan so far Regards, Joze ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still Sent: 7. junij 2007 19:58 To: Joel.Patterson@xxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: Oracle CBO question 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