A Lava query plan is built as an upside down tree of Lava operators: The top Lava 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 Lava query plan for this query is shown in Figure 1-2:
Select o.id from sysobjects o, syscolumns c where o.id <= 1 and o.id < 2
The Lava query plan for this query consists of four Lava 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 (NL Join)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 Lava query plans and executed by the Lava 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 Lava query plan. An example is the select into statement; select into is compiled into a two-step query plan:
create table creates the target table of the statement.
A Lava query plan to inserts 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 Lava query execution engine to execute the Lava 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 Lava query plan is also generated and executed to support bcp. The support for bcp in Adaptive Server has always been in the bcp utility. Now, in version 15.0 and later, the bcp utility generates a Lava query plan and calls the Lava query execution engine to execute the plan.See Chapter 2, “Using showplan” for more examples of Lava query plans.
