SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-55
Cross Product Join
This plan consists of three steps:
•
Plan step 1 includes a scan of the TENKTUP2 table.
•
The TENKTUP2 table is also scanned in step 2 and is then inner joined with the
TENKTUP1 table (cross product step).
•
Plan step 3 includes a scan of the TENKTUP1 table, which is then inner joined with
the result of step 2.
Access path 1 : Primary, partitioned, sequential cache
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
B.TWENTY = C.TWENTY
Pred. selectivity : Expect to select 5% of rows from table
Executor pred. : None
Table selectivity : Expect to select 5% of rows from table
Expected row count: 3734 rows after the join
Operation cost : 6322
---------------------------------------------------------------------------
Plan step 3 : Perform an Inner Join
Join strategy : Hash Join
Characteristic : Joins a row resulting from plan step 2
---------------------------------------------------------------------------
Operation 3.0 : Scan
Table : \SQL1.$DATA2.WISCREG.TENKTUP1
with correlation name A
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 16 out of 16 columns
Access path 1 : Primary, partitioned
SBB for reads : Virtual
Begin key pred. : None
End key pred. : A.UNIQUE2 < 10
Index selectivity : Expect to examine 0.0133% of rows from table
Index pred. : None
Base table pred. : None
Executor pred. : On hashed rows
A.TENPCT = C.TENPCT
Pred. selectivity : Expect to select 0% of rows from table
Table selectivity : Expect to select 0.0013% of rows from table
Expected row count: 5 rows after the join
Operation cost : 2
Operation 3.1 : Hash
Requested : By the optimizer
Hash rows in the : Current table
Purpose : To hash its rows before the Join
Hash key columns : A.TENPCT
Hash cost : 1
Total cost : 6227
Example 6-34. EXPLAIN Plan for Cross Product Join (page 2 of 2)