Having trouble getting this accepted as a defect, would help to have more votes on the RFE:
https://ibm-data-and-ai.ideas.ibm.com/ideas/INFX-I-505
The contents follow.
Try this in the "stores_demo" database:
SET EXPLAIN ON;
SELECT {+USE_NL(customer)} *
FROM ext_customer AS e
JOIN customer AS c ON c.zipcode = e.zipcode;
That does return 3 rows, but the query plan details are:
DIRECTIVES FOLLOWED:
DIRECTIVES NOT FOLLOWED:
USE_NL ( customer ) Join Method and Ordered directives conflict or hash join cannot be enforced.
Estimated Cost: 11
Estimated # of Rows Returned: 3
1) informix.c: SEQUENTIAL SCAN
2) informix.e: SEQUENTIAL SCAN
DYNAMIC HASH JOIN
Dynamic Hash Filters: informix.c.zipcode = informix.e.zipcode
Whatever optimizer directives are applied (or none), it always uses the above plan.
This defect meant a customer had to load a 34 million row file into a raw table rather than using directly as an external table, which is a major functional failure.
------------------------------
Doug Lawry
Oninit Consulting
------------------------------