Bryan Pendleton wrote:
So here's the question: as I understand it, the Optimizer Timeout mechanism kicks in during optimizer.getNextPermutation().
Yes, that's correct.
But what if the "inner" while loop has a very large number of decorated permutations to cost for this particular pass through the outer loop? Shouldn't we be checking for timeout in the inner loop as well?
Yes, I've often wondered about that, too, though I usually don't stop to give it much thought. I'm glad you brought it up.
I don't really have much to offer here except that Yes, I think it would be a good idea. Without having dug into it, the one question that I would ask is: Are the conglomerates for a given table always going to be retrieved in a fixed order? That is, if I create some table and then create a bunch of indexes on that table, will the order in which the optimizer iterates through the conglomerates be the same across databases?
Here's why I ask. Asume we create a table with indexes and we execute some query for which the optimizer times out after the d'th decorated permutation in the j'th join order permutation. Then we re-create the same table in a different database with the same indexes and execute the same query, and again we timeout after the d'th decorated permutation in the j'th join order permutation. Can we say with certainty that the decorated permutations that the optimizer saw before timing out will be the same in the two databases? If not, that would make it awfully hard to reproduce query plans across databases, which could lead to confusion for both users and developers.
I do not know what the answer to the conglomerate-ordering question is. The conglomerates through which the optimizer iterates are retrieved via a call to TableDescriptor.getConglomerateDescriptors(), but the comments for that method do not indicate whether the conglomerates are ordered (and if they are, what are they ordered on?). Maybe that information is given someplace else in the code--I admit I didn't look very hard.
Having said that, one may be inclined to ask how this applies to the join order permutations returned by getNextPermutation(). The answer is that for a given query, the order in which the various permutations are costed is consistent across all databasees--as described in more detail on the "JoinOrderPermutations" wiki page. So if we assume optimizer timeout after the j'th call to getNextPermutation() for the same query (with the same tables/data) on two different databases, we should get the same query plan every time. This consistency across databases with respect to the j'th join order permutation makes it easier to reproduce query plans. It also ensures that users with the same data and the same queries on different databases will see the same results (if we take hardware out of the equation).
That's a lot of quickly-written blabbering that is perhaps more confusing than anything. The short version is simply this:
1. Does TableDescriptor.getConglomerateDescriptors() always return the conglomerates for a table in a predictable order, and if so, what is the ordering based on?
2. Timeout logic within getNextDecoratedPermutation() seems like a good idea to me and is certainly something worth looking into more.
#1 is just an inquiry and should *not* be seen as a reason to block #2. As I said, this was just the first question that came to me when I read your email, so I thought I'd share it...
Army
