Rick Hillegas wrote: >> I would have thought that the DISTINCT is not redundant and actually >> allows for the transformation into a normal join. >> > I think that, for instance, a problem arises when there are M tuples in > PROVIDES with name='Fred' but there are < M distinct ids in those > tuples. In that case, you can imagine data sets on which the original > query returns < M tuples but the transformed query returns M tuples.
You are right. I goofed up. > It may be that Derby did apply the EXISTS transformation--and that > degraded rather than improved the performance. If you are interested in > seeing the plan which Derby selected, please consult the following wiki > pages: > > http://wiki.apache.org/db-derby/PerformanceDiagnosisTips > http://wiki.apache.org/db-derby/StmtExecutionPlan I tried looking at the optimiser query plan, but it is beyond my understanding right now. Thanks for all your help and the links. I will use them as reference later. cheers, Harshad
