SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-40
TRIM and CHAR_LENGTH
The plan consists of one step with one operation. The substring shows as a base table
predicate that is evaluated by the disk process. The total cost is 2.
TRIM and CHAR_LENGTH
This query concatenates the first and last names of employees whose last names have
more than five characters:
EXPLAIN
SELECT LAST_NAME, FIRST_NAME || LAST_NAME FROM EMPLOYEE
WHERE CHAR_LENGTH(TRIM(LAST_NAME)) > 5 ;
Example 6-24. EXPLAIN Plan for SUBSTRING
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
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 2 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. : Will be evaluated by the disk process
SUBSTRING ( LAST_NAME FROM 1 FOR 1 ) = "C"
Pred. selectivity : Expect to select 1.7544% of rows from table
Executor pred. : None
Table selectivity : Expect to select 1.7544% of rows from table
Expected row count: 1 row after the scan
Operation cost : 2
Total cost : 2