RFE - Implement nested loop join of external and indexed tables
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
------------------------------
Just to make sure I understand: ext_customer is an external table and so only a sequential scan is possible. customer is a standard table and has an index on zipcode. You want the query to drive off the external table and join 'customer' using an index but this is not possible? Ben. ------------------------------ Benjamin Thompson ------------------------------
Hi Ben. Exactly so. There are workarounds using subqueries for some purposes, but there is no reason it shouldn't use the index directly. ------------------------------ Doug Lawry Oninit Consulting ------------------------------