Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> [NOT SOLVED] performance tuning questions: replace IN (values) by JOIN
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;
> ...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
![]() |
![]() |