[NOT SOLVED] performance tuning questions: replace IN (values) by JOIN

From: Jérôme Lacoste - CoffeeBreaks <lacostej_at_coffebreaks.org>
Date: Mon, 01 Sep 2003 07:19:04 GMT
Message-ID: <IpC4b.18408$BD3.2787593@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 ...

I see now why I had put a sub-query in the first place...

The initial goal was "to find all BAGS who have had a TIMESTAMP within a specific period."

If I don't use a sub-query, I will only have those BAG_LOG within the period, but not all the bag logs of the Bags that have had a bag log within the period...

If I don't use a sub-query I will get incomplete bag histories.

Concrete example: bag # 1 has 2 bag logs, one at 01:am and one at 02:am If I make a search between 00:30 and 01:30, I want to retrieve the full history of that bag.

So I am back to my original problem.
I.e back from a 18 sec perf instead of 0.02. but the results are different.

Any idea again?

Jerome Received on Mon Sep 01 2003 - 02:19:04 CDT