SQL Tuning

Chapter 8. Why the Diagramming Method Works

It is well to moor your bark with two anchors.

—Publilius Syrus (a.k.a. Publius) Maxim 119

Chapter 5-Chapter 7 covered how to tune SQL with the diagramming method but did not discuss why the method leads to well-tuned SQL. With a lot of faith and good memory, you could likely get by without knowing why the method works. However, even with unbounded, blind faith in the method, you will likely have to explain your SQL changes occasionally. Furthermore, the method is complex enough that an understanding of why it works will help you remember its details better than blind memorization ever could.

The Case for Nested Loops

Throughout this book, I have asserted that nested-loops joins on the join keys offer more robust execution plans than hash or sort-merge joins. Let’s examine why. Consider a two-table query diagram, as shown in Figure 8-1.

Figure 8-1. A prototype two-table query

Sight-unseen, it is possible to say a surprising amount about this query, to a high probability, if you already know that it is a functionally useful business query that has come to you for tuning:

• The top table, at least, is large or expected to grow large. Because Jd, the detail join ratio, is greater than 1.0 (as is usual), the bottom table is smaller than the top table by some factor, but that factor could be moderate or large. Queries that read only small tables are ...