select * from bad_customer a, customers b where a.CUSTID < 1000 and a.REGION=b.REGION and a.DEPTNO = b.DEPTNO
Table a has index on CUSTID field, table b has an index on fields REGION, DEPTNO and ZIP
Logically, no matter how big the table customers is, this statement should be performed very quickly, as fields REGION and DEPTNO are the first and second index of an index of the table customers. However, it took an eternity.
I've run the statement in STRSQL to see why this takes so long, I saw that an access path was being created for the table customers. Then, it took me 1 day to figure out the cause. On the table bad_customer, REGION was declared decimal(5, 0) although its value is always less than 10000. On the table customers however, the definition of the field REGION was decimal(4, 0). Due to this difference of field definition, the logical index could not be used to access table customers.
Replacing a.REGION with CAST(a.REGION as decimal(4, 0)) solved the issue.
select * from bad_customer a, customers b where a.CUSTID < 1000 and
cast(a.REGION as decimal(4, 0))
=b.REGION and a.DEPTNO = b.DEPTNO