SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-146
Examples of EXPLAIN
Table 2-3 lists the cost factors of the DETAIL_COST column:
Examples of EXPLAIN
Consider a table ‘part’ with a unique index px1. Run the following commands:
Create table part (
p_partkey int not null not droppable,
p_name varchar(55) not null not droppable,
p_mfgr char(25) not null not droppable,
p_brand char(10) not null not droppable,
p_type varchar(25) not null not droppable,
p_size int not null not droppable,
p_container char(10) not null not droppable,
p_retailprice numeric(12,2) not null not droppable,
TOTAL_COST REAL Estimated cost associated with the current
operator to execute the operator, including the
cost of all subtrees in the operator tree.
DETAIL_COST VARCHAR
(200)
Cost vector of five items, which are described
in detail in Table 2-3, Cost Factors of
DETAIL_COST column.
DESCRIPTION VARCHAR
(3000)
Additional information about the operator. For
more information about the DESCRIPTION of
all operators, see the SQL/MX Query Guide.
Table 2-3. Cost Factors of DETAIL_COST column
Cost Factor Description
CPU_TIME An estimate of the number of seconds of processor time it might take
to execute the instructions for this operator. A value of 1.0 is 1
second.
IO_TIME An estimate of the number of seconds of I/O time (seeks plus data
transfer) to perform the I/O for this operator.
MSG_TIME An estimate of the number of seconds it takes for the messaging for
this operator. The estimate includes the time for the number of local
and remote messages and the amount of data sent.
IDLETIME An estimate of the number of seconds to wait before an event. The
estimate includes the amount of time to open a table or start an
Executor Server Process (ESP) process.
PROBES The number of times the operator will be executed. Usually, the value
is 1, but it can be greater when you have, for example, an inner scan
of a nested-loop join.
Table 2-2. Fields of OPTIONS 'm' Output
Column name Data Type Description