SQL Tuning

Chapter 9. Special Cases

Defer no time; delays have dangerous ends.

—William Shakespeare King Henry the Sixth, Part I

This chapter covers a collection of special cases that occasionally arise. Chapter 6 and Chapter 7 cover standard solutions to standard classes of the problem “What execution plan do I want?” Here, I extend those solutions to handle several special cases better.

Outer Joins

In one sense, this section belongs in Chapter 6 or Chapter 7, because in many applications outer joins are as common as inner joins. Indeed, those chapters already cover some of the issues surrounding outer joins. However, some of the questions about outer joins are logically removed from the rest of the tuning problem, and the answers to these questions are clearer if you understand the arguments of Chapter 8. Therefore, I complete the discussion of outer joins here.

Outer joins are notorious for creating performance problems, but they do not have to be a problem when handled correctly. For the most part, the perceived performance problems with outer joins are simply myths, based on either misunderstanding or problems that were fixed long ago. Properly implemented, outer joins are essentially as efficient to execute as inner joins. What’s more, in manual optimization problems, they are actually easier to handle than inner joins.

Justification of Outer Joins

I often hear discussions of outer joins that follow lines like this: “Justify making this join an outer join: prove that the expense of an outer ...