Observation: Hybrid Join Invokes Sort for Inner Join

You coded a hybrid join, requiring DB2 to sort at least two tables. DB2 combines the index entry RIDs from the qualifying inner table with the columns of the outer table to form an intermediate table. DB2 then sorts both the inner table RID list and the intermediate table.

The following example includes a hybrid join.

Example

SELECT 
A.deptno, A.deptname, A.mgrno, B.lastname 
FROM T1 A, 
T2 B 
WHERE A.mgrno = B.empno; 

Consider recoding the inner join as a full outer join, to remove the hybrid join, eliminating at least one sort.

Example

SELECT 
T1.deptno, T1.deptname, T1.mgrno, T2.lastname 
FROM T1 
FULL OUTER JOIN T2 
ON T1.mgrno = T2.empno;

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*