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

On Fri, 29 Aug 2003 13:40:24 GMT, Jérôme Lacoste - CoffeeBreaks <lacostej_at_coffebreaks.org> wrote:

>Q: Is the performance loss due to the NESTED LOOPS? or is it due to the
>JOIN in itself ?
>
>Q: I was wondering what kind of query should I use in order to manage to
>improve the design of my query (1) especially removing its limitations
>without introducing the performance loss as with my second query. Should
>I use a temporary table?

I would forget about the strategy you applied in 1. It sucks. It doesn't exploit the capabilities of sql. Your reference to using a temporary table shows you have brainwashed by B Gates and his Evil Empire.

The second variant doesn't perform for the following reasons - the distinct in the subquery is unnecessary as the IN operator works on a set and according to your primary school mathematics book, a set never has duplicates. However as you ask it to a DISTINCT you will get an extra redundant sort-operation
- You don't have a required indexed on the date column, hence you force it to conduct a full table scan
- You need to replace the >= and <= operators by a between, to have the optimizer recognize this is a bounded range scan.
>
>Q: [slightly unrelated] How one can enable the autotrace to also display
>(Cost, Card and Byte) as shown in most of Oracle examples?
>

Autotrace doesn't do that. TKPROF on a trace file does.

>Any help appreciated...

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Aug 29 2003 - 09:32:29 CDT