Generating a query plan can help you understand the execution plan developed by the optimizer.
Before it executes any query, the query optimizer creates a query execution plan. A query execution plan represents the set of steps the database server uses to access information in the database related to a statement. The execution plan for a statement can be saved and reviewed, regardless of whether it was just optimized, whether it bypassed the optimizer, or whether its plan was cached from previous executions. Although a query execution plan may not correspond exactly to the syntax used in the original statement, operations described in the execution plan are semantically equivalent to the original query.
Query plans generate an execution tree that consists of a series of nodes that represent a processing stage. The lowest nodes on the tree are leaf nodes. Each leaf node represents a table in the query. At the top of the plan is the root of the operator tree. Information flows up from the tables and through any operators representing joins, sorts, filters, stores, aggregation, and subqueries.
Load execution plans detail the steps that the database engine uses to insert data into a table. Load plans use the same database and output options as query execution plans. The Data Flow Object (DFO) tree identifies the number of rows processed at each stage of the load. Different SQL statements may generate different DFO trees and the same statement may generate different trees for different kind of tables (un-partitioned, range partitioned, hash partitioned, hash-range partitioned, etc.).
To generate a query plan, set the appropriate evaluation options, then execute the query. Text versions of the plan are written to the .iqmsg file. HTML versions can be displayed in the Interactive SQL Plan Viewer or in most Web browsers.