SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
E-20
Examples—EXPLAIN
Examples—EXPLAIN

These examples shows four different ways to request an EXPLAIN report for the
same simple query:
Example 1.
>> EXPLAIN SELECT * FROM MYTABLE;
Example 2.
>> EXPLAIN "SELECT * FROM MYTABLE";
Example 3.
>> EXPLAIN 'SELECT * FROM MYTABLE';
Example 4.
>> PREPARE MYQUERY FROM SELECT * FROM MYTABLE;
..
--- SQL command prepared.
>> EXPLAIN MYQUERY;
Total cost The total cost of executing the statement. Cost is a relative measure
of the resources needed. Lower numbers indicate less costly
execution.
This measure is useful only for comparing different ways of
specifying the same query (for example, using a join instead of a
subquery). Total Cost cannot not be used to compare the efficiency
of executing different queries.
Type of [DELETE]
[UPDATE]
Indicates the type of DELETE or UPDATE operations: cursor, subset,
or unique.
For a cursor DELETE or UPDATE, the executor opens an internal
cursor to read the qualified rows and performs the requested
operation for each such row.
For a subset DELETE or UPDATE, the file system positions on the
first qualifying row (if the row has been determined) or on the first
row of the table. The file system sends a message to the disk
process to perform the operation on all rows that qualify. The file
system might have to send multiple requests because the disk
process performs a limited amount of work in a single request.
For a unique DELETE or UPDATE, the disk process performs the
operation on the specific row. Exactly one row is deleted or updated.
UPS workspace Indicates the size of the workspace needed for the User Process
Sort operation.
Table E-1. EXPLAIN Plan Header Lines (page5of5)
Item Action