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

From: mitt <nospam_at_nospam.at>
Date: Fri, 29 Aug 2003 16:16:06 +0200
Message-ID: <3F4F6026.2060305@nospam.at>

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 ...

take additional care of having an index on bag_log.bag_id (FK); Received on Fri Aug 29 2003 - 09:16:06 CDT