SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-48
EXPLAIN Plans for Join Queries
The EXPLAIN plan has one step. SQL expects that approximately 61 percent of the
rows have JOBCODE values between 150 and 500. SQL computed this value by
calculating selectivities separately for the >= 150 predicate and the <=500 predicate
and applying the rules provided in Selectivity for Range Predicates on page 5-5.
EXPLAIN Plans for Join Queries
This subsection shows EXPLAIN plans for join queries.
Join queries involve two or more tables. The optimizer determines the outer (left) and
inner tables based on the number of rows selected. Usually, the table with the most
rows selected is chosen as the outer table. This table is listed first in the EXPLAIN
plan.
The plans chosen show both types of join queries (left and inner), the four types of join
methods (hash, nested, sort merge, and key-sequenced merge), and parallel execution
of queries.
For a plan involving parallel execution, each partition is read by an ESP in a separate
processor. The EXPLAIN plan indicates which partitions will be read by each of the
ESPs and in which processors.
Example 6-31. EXPLAIN Plan for Selectivity for Range Predicates
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
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 6 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
( JOBCODE >= 150 ) AND ( JOBCODE <= 500 )
Pred. selectivity : Expect to select 61.425% of rows from table
Executor pred. : None
Table selectivity : Expect to select 61.425% of rows from table
Expected row count: 35 rows after the scan
Operation cost : 2
Total cost : 2