Neoview SQL Reference Manual (R2.3)

The FILE_SCAN operator is the only operator in this example that shows additional information
in the OPT and DESCRIPTION fields. 'fs' indicates that fast-scan optimization was used. 'fr'
indicates that fast-reply data-move optimization was used. The table name is shown in the
DESCRIPTION field. For information about these special optimization techniques, see the listing
of each operator in the Neoview Query Guide.
Normal User [OPTIONS 'n'] Considerations
The normal user option provides more information about the query plan than OPTIONS 'f'.
OPTIONS 'n' is the default option, so you can simply execute EXPLAIN query-text without
specifying OPTIONS 'n'.
OPTIONS 'n' provides two digits of precision for floating-point cost values.
OPTIONS 'n' provides a plan summary as well as values of the fields for the operators in the
query execution plan. Table 2-3 describes the plan summary.
Table 2-3 Plan Summary Information
Shows DYNAMICALLY COMPILED when a query statement or prepared
statement is supplied as the argument to the EXPLAIN statement.
MODULE_NAME
Name of the prepared statement. For example, in the prepared statement
PREPARE newstmt from SELECT * FROM T, the
STATEMENT_NAME field value is NEWSTMT.
STATEMENT_NAME
An identifier generated by the database.PLAN_ID
Estimated number of rows returned by the plan.ROWS_OUT
Estimated total cost of the plan.EST_TOTAL_COST
SQL query being explained in this query plan.STATEMENT
The EXPLAIN plan output provides the following information, as applicable, for each operator
of the query execution plan. The "NODE LISTING" heading means a list of the plan's operators
will follow.
Table 2-4 Node Listing Information
DescriptionField
Current operator type, such as FILE_SCAN, HASH_JOIN, NESTED_JOIN,
and so on.
Operator name
Sequence number of the current operator in the query execution plan;
indicates the sequence in which the query plan is generated.
SEQ_NO
Sequence number of the operator’s children (operators that feed data up to
this operator). There can be 0, 1, or 2 children shown as NO CHILDREN,
ONLY CHILD 6, or CHILDREN 5, 7.
Children information
Name of base table, shown only on operators that access the table.TABLE_NAME
Estimated number of requests for execution of this operator. The value is 1
except for the right child operator (inner scan) of a nested-loop join.
REQUESTS_IN
Estimated number of rows returned by the plan or, when REQUESTS_IN is
greater than one, the value is the estimated number of rows returned per
input request.
ROWS_OUT or ROWS/REQUEST
Estimated cost associated with executing the current operator.EST_OPER_COST
108 SQL Statements