A query plan is built as an upside down tree of operators: The top operator can have one or more child operators, which in turn can have one or more child operators, and so on, thus building a bottom-up tree of operators. The exact shape of the tree and the operators in it are chosen by the optimizer.
An example of a query plan for the following query is shown in Figure 1-2 below:
select o.id from sysobjects o, syscolumns c where o.id = c.id and o.id < 2
The query plan for this query consists of four operators. The top operator is an Emit (also called Root) operator that dispatches the results of query execution either by sending the rows to the client or by assigning values to local variables.
The only child operator of the Emit is a NestedLoopJoin (NLJoin) that uses the nested-loop-join algorithm to join the rows coming from its two child operators, (1) the scan of sysobjects and (2) the scan of syscolumns.
Since the optimizer optimizes all select, insert, delete, and update statements, these are always compiled into query plans and executed by the query engine.
Some SQL statements are compiled into hybrid query plans. Such plans have multiple steps, some of which are executed by the utility modules and a final step that is a query plan. An example is the select into statement; select into is compiled into a two-step query plan. The first step is a create table step to create the target table of the statement. The second step is a query plan to insert the rows into the target table. To execute this query plan, the procedural execution engine calls the create table utility to execute the first step to create the table. Then the procedural engine calls the query execution engine to execute the query plan to select and insert the rows into the target table. The two other SQL statements that generate hybrid query plans are alter table (but only when data copying is required) and reorg rebuild.
A query plan is also generated and executed to support bcp. The support for bcp in Adaptive Server has always been in the bcp utility. In version 15.0 and later, the bcp utility generates a query plan and calls the query execution engine to execute the plan.More examples of query plans can be found in Chapter 2, “Using showplan.”
