As shown in the IXSCAN descriptor in Figure 8, the leading column of the index exploited by optimizer is PRODUCT_NUMBER, which does not provide the order required for merge scan join.
To illustrate this, compare it to the predicate that is applied to the indexscan numbered 5 in the plan, which is on the one and only column of the index on the child table.
为了展示这一点,将它与计划中第5号索引扫描上使用的谓词相比较,它针对子表中惟一的索引列。
3
And DEPTNO should be listed second, allowing DB2 to do a direct index lookup on the first column (EMPNO) and then a scan on the second (DEPTNO) for the greater-than.