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

From: Jérôme Lacoste - CoffeeBreaks <lacostej_at_coffebreaks.org>
Date: Mon, 01 Sep 2003 07:56:33 GMT
Message-ID: <RYC4b.18409$BD3.2787694@juliett.dax.net>

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

I've kept my sub-queries but remove the index. This gave me an improvement from 18 to 6 secs. Thanks again Sybrand!

I saw that there was still a full table access on the BAG_LOG table, and now that I understand how thing works, I've added an index on the BAG_ID Foreign Key in the BAG_LOG table as it wasn't there. Strange as it was my understanding that Foreign Keys always have indexes on them in Oracle...

Now I have fast (about 0.02 sec) and correct results!

Great.

Thanks all, and sorry for answering to myself.

J. Received on Mon Sep 01 2003 - 02:56:33 CDT