SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-39
EXPLAIN Plans for String Functions
The plan contains two steps. Plan step 1 is a scan of the table. The plan shows that
the HAVING clause is an executor predicate and the GROUP BY clause is an executor
aggregate. The expected row count is 204 after the scan and 100 after the HAVING
clause. The operation cost is 6.
Plan step 2 is a nested inner join that scans the same table and joins the rows that
resulted from plan step 1. Its expected row count is 41,616 after the join.
The total cost is 1162.
EXPLAIN Plans for String Functions
This subsection contains EXPLAIN examples for queries that contain the string
functions SUBSTRING, TRIM, and CHAR_LENGTH.
SUBSTRING
This query uses a substring to search for the letter “C” in the first position of each
employee’s last name.
EXPLAIN
SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE
WHERE SUBSTRING(LAST_NAME FROM 1 FOR 1) = 'C' ;
--------------------------------------------------------------------------
Plan step 2 : Perform an Inner Join
Join strategy : Nested Join
Characteristic : Joins a row resulting from plan step 1
---------------------------------------------------------------------------
Operation 2.0 : Scan
Table : \SQL1.$DATA8.PUBS.DATA
with correlation name Y
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 1 out of 1 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: 41616 rows after the join
Operation cost : 6
Total cost : 1162
Example 6-23. EXPLAIN Plan for Ignoring the Largest and Smallest Values in a
Set (page 2 of 2)