A subset of all possible execution plans can be described as robust. While such plans are not always quite optimum, they are almost always close to optimum in real-world queries, and they have desirable characteristics, such as predictability and low likelihood of errors during execution. (A nonrobust join can fail altogether, with an out-of-TEMP-space error if a hash or sort-merge join needs more space than is available.) Robust plans tend to work well across a wide range of likely data distributions that might occur over time or between different database instances running the same application. Robust plans are also relatively forgiving of uncertainty and error; with a robust plan, a moderate error in the estimated selectivity of a filter might lead to a moderately suboptimal plan, but not to a disastrous plan. When you use robust execution plans, you can almost always solve a SQL tuning problem once, instead of solving it many times as you encounter different data distributions over time and at different customer sites.
Robust execution plans tend to have the following properties:
Their execution cost is proportional to rows returned.
They require almost no sort or hash space in memory.
They need not change as all tables grow.
They have moderate sensitivity to distributions and perform adequately across many instances running the same application, or across any given instance as data changes.
They are particularly good when it turns out that a query returns fewer rows than you expect (when filters are more selective than they appear).
Robustness requirements imply that you should usually choose to:
Drive to the first table on a selective index
Drive to each subsequent table with a nested loop on the index of the full join key that points to a table that the database already read, following the links in the query diagram
Drive down to primary keys before you drive up to nonunique foreign keys
If you consider only robust plans, robustness rules alone answer the first two questions of finding the best execution plan, leaving only the question of join order:
You will reach every table with a single index, an index on the full filter condition for the first table, and an index on the join key for each of the other tables.
You will join all tables by nested loops.
I later discuss when you can sometimes safely and profitably relax the robustness requirement for nested-loops joins, but for now I focus on the only remaining question for robust plans: the join order. I also later discuss what to do when the perfect execution plan is unavailable, usually because of missing indexes, but for now, assume you are looking for a truly optimum robust plan, unconstrained by missing indexes.