SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-67
Left Join Transformed Into an Inner Join
Example 6-40. EXPLAIN Plan for Left Join Transformed Into an Inner Join
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.SALES.ORDERS
with correlation name O
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 1 out of 5 columns
Access path 1 : Alternate \SQL1.$DATA8.SALES.XORDREP, index only
SBB for reads : Virtual
Begin key pred. : None
End key pred. : None
Index selectivity : Expect to examine 100% of rows from index
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 100% of rows from table
Expected row count: 13 rows after the scan
Operation cost : 1
----------------------------------------------------------------------------
Plan step 2 : Perform an Inner Join
Join strategy : Key-Sequenced Merge Join
Characteristic : Joins a row resulting from plan step 1
---------------------------------------------------------------------------
Operation 2.0 : Scan
Table : \SQL1.$DATA8.PERSNL.EMPLOYEE
with correlation name E
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, unique
SBB for reads : Virtual
Begin key pred. : O.SALESREP = E.EMPNUM
End key pred. : None
Index selectivity : Expect to examine 1.7544% of rows from table
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 1.7544% of rows from table
Expected row count: 13 rows after the join
Operation cost : 1
Operation 2.1 : Hash
Requested : By the optimizer
Hash rows in the : Result of a Select
Purpose : To form groups of rows for a Group By
Hash key columns : E.LAST_NAME
Expected row count: 13 rows after the group by
Hash cost : 1
Total cost : 4