Avoiding non-equality join predicates
Join predicates that use comparison operators other than equality should be avoided because the join method is limited to nested loop.
Additionally, the optimizer might not be able to compute an accurate selectivity estimate for the join predicate. However, non-equality join predicates cannot always be avoided. When they are necessary, ensure that an appropriate index exists on either table, because the join predicates will be applied to the inner table of the nested-loop join.
SELECT… FROM PRODUCT P, SALES F WHERE P.PROD_KEY = F.PROD_KEY AND F.SALE_DATE BETWEEN P.START_DATE AND P.END_DATE
In this situation, ensure that there is an index on (SALES.PROD_KEY, SALES.SALE_DATE).
Specialized star schema joins, such as star join with index ANDing and hub joins, are not considered if there are any non-equality join predicates in the query block. (See
CREATE VIEW V_PROD_FACT AS SELECT P.* FROM PRODUCT P, SALES F WHERE P.PROD_KEY = F.PROD_KEY AND F.SALE_DATE BETWEEN P.START_DATE AND P.END_DATE ALTER VIEW V_PROD_FACT ENABLE QUERY OPTIMIZATION RUNSTATS ON TABLE DB2USER.V_PROD_FACT WITH DISTRIBUTION
Ensuring that queries fit the required criteria for the star schema join.)