SQL Tuning

Chapter 4. Controlling Execution Plans

Saying is one thing and doing is another.

—Michel Eyquem de Montaigne Essays, Bk. II, Ch. 31

This chapter covers two classes of tuning techniques for controlling execution plans: universal techniques that work independently of your choice of database vendor, and techniques that are database-specific. Database-specific techniques are covered well by your own vendor’s documentation, so you might know them well already. In general, you need both types of techniques to get precisely the execution plan you want. Each of the vendor-specific sections stands alone, even repeating material from the other sections when applicable. Therefore, you can skip the vendor sections that you don’t need.

Much ado has been made over controlling execution plans, sometimes with elaborate tools. This chapter focuses on the simplest ways to control plans, with a strong emphasis on getting the types of plans you will need to optimize real-world SQL. I have found that when you know which execution plan you want in advance, getting it is easy and requires only simple tools.

Universal Techniques for Controlling Plans

This section describes a number of database-independent techniques you can use to control execution plans. The techniques are good for the following purposes:

• Enabling use of the index you want

• Preventing use of the wrong indexes

• Enabling the join order you want

• Preventing join orders you do not want

• Choosing the order to execute outer queries and subqueries

• Providing ...