db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: query with subquery problem
Date Mon, 07 Sep 2009 08:49:35 GMT
Jim Reynolds <jreynolds@perfExperts.com> writes:

> This query seems to go into an infinite loop. It runs for hours but never
> completes. The sub query runs in a few milliseconds when is not a sub query.
>
> Is this a bug or have I missed something fundamental.
>
> SELECT BFNAME FROM BFTABLE
>
> WHERE BFNUMBER IN
>
> (              SELECT BFNUMBER
>
>                 FROM BFDETAILTABLE
>
>                 GROUP BY BFNUMBER
>
>                 HAVING COUNT(*) > 1
>
> )

Hi Jim,

The query unfortunately doesn't satisfy the conditions[1][2] for being
flattened into a join, so I think Derby will need to look at every row
in BFTABLE and compare it to the results from the subquery. It shouldn't
go into an infinite loop, but it could take quite a while if BFTABLE is
large.

[1] http://db.apache.org/derby/docs/10.5/tuning/ctuntransform36368.html
[2] http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25868.html

Derby may be able to do something cleverer with this equivalent query:

SELECT BFNAME FROM BFTABLE
JOIN
(              SELECT BFNUMBER
                FROM BFDETAILTABLE
                GROUP BY BFNUMBER
                HAVING COUNT(*) > 1
) DT ON BFTABLE.BFNUMBER = DT.BFNUMBER;

-- 
Knut Anders

Mime
View raw message