SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-11
Generating an EXPLAIN Plan
The total cost represents the cost of doing all the operations to complete the
statement. In Example 6-4 on page 6-6, the DISPLAY STATISTICS command shows
the estimated cost for this query is 3. By using EXPLAIN, you can see that the sort cost
for this query is 1. By removing the ORDER BY clause (which causes the sort request),
you can reduce the total cost of the query to 2, as shown in Example 6-6.
>>PREPARE Q3 FROM
+> SELECT LAST_NAME, FIRST_NAME, SALARY
+> FROM EMPLOYEE ;
--- SQL command prepared.
>> EXPLAIN PLAN FOR Q3 ;
These examples show the cost determined for various operations, such as sorts. (If
you need rows returned in a specific order, however, and the sort cost is a problem,
you can create an index on the sorted column.)
Detailed analyses of several EXPLAIN plans appear later in this section.
Example 6-6. EXPLAIN Plan for Simple Query Without ORDER BY
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
--------------------------------------------------------------------
Plan step 1
--------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.PERSNL.EMPLOYEE
with correlation name EMPLOYEE
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 3 out of 6 columns
Access path 1 : Primary
SBB for reads : Virtual
Begin key pred. : None
End key pred. : None
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 100% of rows from table
Expected row count: 57 rows after the scan
Operation cost : 2
Total cost : 2