SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-65
Left Join Not Transformed Into an Inner Join
Example 6-39. EXPLAIN Plan for Left Join Not Transformed Into an Inner
Join (page 1 of 2)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
--------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.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
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
---------------------------------------------------------------------------
Plan step 2 : Perform a Left Join
Join strategy : Hash Join
Characteristic : Joins a row resulting from plan step 1
---------------------------------------------------------------------------
Operation 2.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. : On hashed rows
O.SALESREP = E.EMPNUM
Pred. selectivity : Expect to select 11.1111% of rows from table
Executor pred. : On null augmented rows
O.SALESREP IS NULL
Pred. selectivity : Expect to select 1.2346% of rows from table
Table selectivity : Expect to select 11.1111% of rows from table
Expected row count: 1 row after the join
Operation cost : 1