"Dustin T. Clifford" <[email protected]> writes:
> Michael, > > I agree with you that it is not ‘simple’ and we do have plans to > optimize this query amongst others. > > The question still remains to me though, why after adding 1 or 2 > tables (the query had 8 tables joined before) does the compile time > jump so drastically when the execution time remains, approximately, > the same. Hi Dustin, If you go from 8 tables to 10 tables in the join the number of possible join orders increases 90 times (from 40320 to 3628800). In addition to the join orders, the optimizer will have to pick table scan or index scan for each of the tables, which increases the difference in the complexity further as you add more tables. It is this combinatorial explosion that increases the compilation time. However, even if it requires a lot more work from the compiler to find the best execution plan, the actual plan doesn't have to be that much more expensive. So I don't think it's unreasonable that the execution time increases less than the compilation time as the complexity increases. 12 minutes does sound like a long time, though. Perhaps not a long time if the compilation performs an exhaustive search for the best plan given the large number of possible combinations, but I was under the impression that the optimizer would time out if the compilation took too long and just take the best plan that it has found so far. I'm not sure exactly how it determines that it should time out. Perhaps someone else knows? -- Knut Anders
