Overview

Alberto Lerner, DBA, Doctoral Candidate

DBMSs do not execute SQL queries in the order they are written. Rather, they first capture a query's meaning, translate it into a corresponding access plan, and then execute the set of operations contained in this plan in what you hope is an optimal way given the data structures available. So when you ask something like select C_NAME, N_NAME from CUSTOMER join NATION on C_NATIONKEY = N_NATIONKEY show me all the customers' names and their corresponding nations to an orders database, ^[1] DB2 would execute instead a plan like the one in Figure D.1. Other DBMSs' explainers would present similar plans though the display would differ a little.

Figure D.1: Query access plan obtained using DB2's Visual Explain for the query select C_NAME, N_NAME from CUSTOMER join NATION on C_NATIONKEY = N_NATIONKEY. The query's answer is produced by a nested-loops join in which CUSTOMER is the outer table and NATION is the inner one. A table scan operation reads the rows of the former, while the nonclustered index NATION_PK is used to retrieve the latter.

Plans are usually depicted as upward-pointing trees in which sources at the bottom are tables or indexes (squares and diamonds, respectively, in Figure D.1), and internal nodes are operators (octagons). Each operator represents a particular transformation, an intermediate stage in the production of a resulting node.

A plan in fact denotes a complex assembly (or production) line complex because it's an inverted tree rather than a line. "Raw" rows...