Introduction

The query optimizer takes queries as input and through a process described in this chapter produces an execution plan as output. I like to describe query optimization as being similar to GPS navigation:

1. You enter an address as a destination:
   • 3294 Main Street
2. It tells you how to get there in the most efficient way:
   • Continue straight for 2 miles
   • Turn left on Markham Street
   • Continue for 500ft
   • Turn right
   • Continue for 1000ft
   • Your destination is on the right

An address is a destination. You do not specify how to get there, but you expect the navigation system to evaluate potential routes and advise you on the most efficient one.

SQL queries are similar to addresses, in that the SQL language is declarative. It conveys a final state rather than a procedure of how to get there. Similarly, as a database system will have many indexes (and with joins, many tables), there are also many routes that can achieve the same result.

As a final use of this analogy, just as GPS navigation doesn’t always direct you on the absolutely fastest route, the same can be said of query optimization. Just as there is not always traffic data for all streets, the optimizer must work with an incomplete model. For experienced operators this can create situations where overriding and tuning may be required.