The art of SQL: Recognizing classic SQL patterns

The following is an excerpt from Chapter 6 of The Art of SQL by Stephane Faroult with Peter Robson, published by O'Reilly Media Inc. in 2006.

Stephane Faroult first discovered relational databases and the SQL language in 1983. He joined Oracle France in their early days and soon developed an interest in performance and tuning topics. He founded RoughSea Ltd., a database consultancy, in 1998.

Peter Robson has worked with databases since 1977, relational databases since 1981 and Oracle since 1985. He has lectured widely in the U.K. on geological aspects of databases and has specialized on aspects of the SQL system as well as data modelling from the corporate architecture down to the departmental level. He is currently a director on the board of the U.K. Oracle Users Group.

To read the rest of this chapter click here.

The nine situations

Any SQL statement that we execute has to examine some amount of data before identifying a result set that must be either returned or changed. The way that we have to attack that data depends on the circumstances and conditions under which we have to fight the battle. Our attack will depend on the amount of data from which we retrieve our result set and on our forces (the filtering criteria), together with the volume of data to be retrieved.

Any large, complicated query can be divided into a succession of simpler steps, some of which can be executed in parallel, rather like a complex battle is often the combination of multiple engagements between various distinct enemy units. The outcome of these different fights may be quite variable. But what matters is the final, overall result.

When we come down to the simpler steps, even when we do not reach a level of detail as small as the individual steps in the execution plan of a query, the number of possibilities is not much greater than the individual moves of pieces in a chess game. But as in a chess game, combinations can indeed be very complicated.

This chapter examines common situations encountered when accessing data in a properly normalized database. Although I refer to queries in this chapter, these example situations apply to updates or deletes as well, as soon as a where clause is specified; data must be retrieved before being changed. When filtering data, whether it is for a simple query or to update or delete some rows, the following are the most typical situations—I call them the nine situations—that you will encounter:

• Small result set from a few tables with specific criteria applied to those tables.
• Small result set based on criteria applied to tables other than the data source tables.
• Small result set based on the intersection of several broad criteria.
• Small result set from one table, determined by broad selection criteria applied to two or more additional tables.
• Large result set.
• Result set obtained by self-joining on one table.
• Result set obtained on the basis of aggregate function(s).
• Result set obtained by simple searching or by range searching on dates.
• Result set predicated on the absence of other data.

This chapter deals with each of these situations in turn and illustrates them with either simple, specific examples or with more complex real-life examples collected from different programs. Real-life examples are not always basic, textbook, one- or two-table affairs. But the overall pattern is usually fairly recognizable.

As a general rule, what we require when executing a query is the filtering out of any data that does not belong in our final result set as soon as possible; this means that we must apply the most efficient of our search criteria as soon as possible. Deciding which criterion to apply first is normally the job of the optimizer. But, as I discuss in Chapter 4, the optimizer must take into account a number of variable conditions, from the physical implementation of tables to the manner in which we have written a query. Optimizers do not always "get it right," and there are things we can do to facilitate performance in each of our nine situations.

Read more about these nine situations here.