Re: performance tuning questions: replace IN (values) by JOIN

From: Jérôme Lacoste - CoffeeBreaks <lacostej_at_coffebreaks.org>
Date: Sat, 30 Aug 2003 18:04:36 GMT
Message-ID: <UG54b.17416$BD3.2770216@juliett.dax.net>

mitt wrote:
> Jérôme Lacoste - CoffeeBreaks wrote:
>

>> select BAGS.ID as BAGS__ID, [...],
>>        BAG_LOG.* FROM BAGS, BAG_LOG
>>        WHERE (BAGS.ID = BAG_LOG.BAG_ID AND
>>        BAGS.ID IN (select DISTINCT(BAG_ID) FROM BAG_LOG
>>                    WHERE DTM_TIMESTAMP >= (TO_DATE('2003-19-08 
>> 00:00:00', 'yyyy-dd-mm HH24:MI:SS'))
>>                      AND DTM_TIMESTAMP <= (TO_DATE('2003-19-08 
>> 12:00:00', 'yyyy-dd-mm HH24:MI:SS'))
>>                   )
>>              ) ORDER BY BAGS.ID DESC, BAG_LOG.DTM_TIMESTAMP DESC;
>>
>> replace [...] by the full list of fields from the BAGS table one wants 
>> to retrieve. In my case all.
>>

>
> I see no need for a sub-query;
>
> ...WHERE bags.id = bag_log.bag_id and
> bag_log.dtm_timestamp >= ... and
> bag_log.dtm_timestamp <= ...
> order by ...

and that was obvious of course. Thanks for lightening me up.

> take additional care of having an index on bag_log.bag_id (FK);

this one was already there as BAG_ID is a foreign key.

Thanks a lot. See also my other answer to Sybrand. Received on Sat Aug 30 2003 - 13:04:36 CDT