Army wrote:
In order to get Derby to recognize the equijoin predicate, the subquery
has to appear in the FROM list. So I created a view for the subquery:
<snip view>
and then I changed the query to select and join with that view:
<snip>
Working off the modified query above I used "optimizer overrides", which
were added as part of Derby 10.2, to 1) fix the join order so that the
subquery becomes the "inner" table, and 2) to force ("guarantee") a hash
join using the equijoin predicate, thereby allowing materialization of
the inner table (i.e. of the subquery). The query to do that is as
follows:
select count(*) from --DERBY-PROPERTIES joinOrder=FIXED
time, v_hash --DERBY-PROPERTIES joinStrategy=HASH
where time.id = v_hash.id;
Just one thing to mention as an afterthought: you do not have to create a
separate view for this to work. I did that for the sake of clarity, but you
could just as easily write:
select count(*) from --DERBY-PROPERTIES joinOrder=FIXED
time,
(select distinct
time.id from time, double_sample, band
where band.id = double_sample.fk_band_id
and double_sample.fk_time_id = time.id and
band.id in (11, 12)
) v_hash --DERBY-PROPERTIES joinStrategy=HASH
where time.id = v_hash.id;
Of course after writing all of that I realized that you said your queries are
generated (as opposed to hand-written) and that they "are only representative of
my problem. [...] While the above sample query can be easily modified to run
well, I'm not sure how to do it in the general case."
So maybe this isn't going to work for you, after all...
Army
