SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-53
Cross Product Join
This plan consists of two steps:
•
Plan step 1 includes a scan of the TENKTUP1 table.
•
Plan step 2 includes a scan of the TENKTUP2 table, which is then inner joined with
the TENKTUP1 table.
Cross Product Join
A cross product join is a nested join without any join predicates. Example 6-34 on
page 6-54 shows the EXPLAIN plan for the following join.
EXPLAIN SELECT * FROM TENKTUP1 A,TENKTUP2 B,TENKTUP2 C
WHERE A.UNIQUE2 <10 AND B.UNIQUE1 = 3
AND A.TENPCT=C.TENPCT
AND B.TWENTY=C.TWENTY;
The plan includes a cross product as an intermediate step.
---------------------------------------------------------------------------
Operation 2.0 : Scan
Table : \SQL1.$DATA2.WISCREG.TENKTUP2
with correlation name B
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 : Alternate \SQL1.$DATA2.WISCREG.TKTUP2I, unique
SBB for reads : Not used
Begin key pred. : B.UNIQUE1 = 3
End key pred. : B.UNIQUE1 = 3
Index selectivity : Expect to examine 0.0013% of rows from index
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 0.0013% of rows from table
Expected row count: 10 rows after the join
Operation cost : 3
Total cost : 174
Example 6-33. EXPLAIN Plan for Nested Inner Join (page 2 of 2)