As of this writing ( LUW V10.1 or older), there is a class of query that does not play nice with DB2 optimizer, which takes the form of a disjunctive predicate such as below:
SELECT 1 FROM
T1 JOIN T2
ON ( C1 = D1 OR C2 = D2)
Currently, the optimizer can only come up with a plan that uses a NLJOIN, *regardless of the data volume*. Imagine a scenario where you have a couple of million rows in T1 and T2 and there is no suitable covering index, you will have plenty of free time in your hand to do other things such as taking a shower and going for a swim, because the query will take a very long time to complete.
Is there a workaround? Fortunately yes. Just take a look at how Oracle handle this sort of query.
I shall demo an example on this in future post.
No comments:
Post a Comment