Chapter 7. Diagramming and Tuning Complex SQL Queries
There is no royal road to geometry.
Euclid Proclus's Commentary on Euclid, Prologue
So far, you have seen how to diagram and tune queries of real tables when the diagram meets several expectations applicable to a normal business query:
The query maps to one tree.
The tree has one root, exactly one table with no join to its primary key. All nodes other than the root node have a single downward-pointing arrow linking them to a detail node above, but any node can be at the top end of any number of downward-pointing arrows.
All joins have downward-pointing arrows (joins that are unique on one end).
Outer joins are unfiltered, pointing down, with only outer joins below outer joins.
The question that the query answers is basically a question about the entity represented at the top (root) of the tree or about aggregations of that entity.
The other tables just provide reference data stored elsewhere for normalization.
I have called queries that meet these criteria simple queries, although, as you saw in Chapter 6, they can contain any number of joins and can be quite tricky to optimize in rare special cases of near-ties between filter ratios or when hidden join filters exist.
Queries do not always fit this standard, simple form. When they do not, I call such queries complex. As I will demonstrate in this chapter, some complex queries result from mistakes: errors in the database design, the application design, or in the implementation. Often, these types of mistakes make it easy to write incorrect queries. In this chapter, you'll learn about anomalies that you might see in query diagrams that can alert you to the strong possibility of an error in a query or in design. You will also see how to fix these functional or design defects, sometimes fixing performance as a side effect. These fixes usually convert the query to simple form, or at least to a form that is close enough to simple form to apply the methods shown earlier in this book.
Some complex queries go beyond any form I have yet explained how to diagram, using subqueries, views, or set operations such as UNION and UNION ALL. These complex queries are usually fine functionally and are fairly common, so you need a way to diagram and optimize them, which you can do by extending the earlier methods for simple queries.
