Have you ever wondered how hard the optimizer has to work to produce an execution plan. Consider a simple query with four tables in a straight join, where each table has a few B-tree indexes. How many different executions plans could the optimizer produce.
I won’t guarantee that I’ve covered all the options in the following list (and I’ve deliberately ignored some special cases), even so it’s an extraordinary result.
First: How many join orders are there. The answer is 4! (That’s 4 factorial, not 4 with a following exclamation mark – i.e. 4 x 3 x 2 x 1 = 24).
Second: What about join mechanisms. There are three join mechanisms (nested loop, hash, and merge joins), and given there are 4 tables there are three joins, so we have to multiply the 24 join orders by 3 x 3 x 3 for a total of 27 * 24 = 648.
Finally, we have to worry about the access mechanisms - how do we get the data from each table. There are a surprising number of options here, and I think it’s probably fair to count: full table scan, index scan (I am not going to count full scan, range scan, unique scan and skip scan as different), index fast full scan, index hash join, and-equal (deprecated in 10g, alas), and bitmap conversion - for a total of 6. (Again, I’m not going to distinguish between all the cases of hash join, and-equal, and bitmap conversion)
Since we have 4 tables to acquire data from we have to multiple our current total by 6 x 6 x 6 x 6 = 1,296, for a grand total of 1,296 * 648 = 839,808.
And that’s just a 4-table, simple join! (And that is an exclamation mark).
Isn’t it lucky that the optimizer has all sorts of clever mechanisms for minimising the work it does to optimize SQL.
Footnote: I’ve highlighted the key steps: join order, join mechanism, access mechanism. If you’re going to put hints into SQL, you need to cover all three stages if you want to ensure that the only path available to the optimizer is the one you want – and this means you need at least two hints per table to specify the execution plan completely. And that doesn’t include considerations of the extra hints relevant to subqueries, non-mergeable views, and set operations.