Re: NOT IN is very inefficient

I would like to thank everyone who replied to my question. My favourite answer (the one I am using) is MINUS, which is really simple.

> The ORACLE Optimizer is a program, so by definition it is stupid.

This sounds like a case against declarative languages.

> But you can get it to behave better. The NOT IN clause is one of the
> easiest performance problems to fix. Simply rephrase the query to
> use NOT EXISTS:
>
> SELECT my_field FROM my_table1
> WHERE NOT EXISTS
> ( SELECT 'ANYTHING' FROM my_table2
> WHERE my_table2.my_field = my_table1.my_field ) ;

I actually tried this before I posted, but retried after your suggestion. Unfortunately the Oracle optimiser does not do any better with exists.

> Now the subquery can use any index that exists on my_field for my_table2
> to speed the lookup. You can also rephrase the query using an Outer Join:
>
> SELECT a.my_field FROM my_table1 a, my_table2 b
> WHERE a.my_field = b.my_field(+)
> AND b.my_field IS NULL ;

This is works well. Very fast. The query plan does not use the index at all, it just does the query in a sensible way. Since my full query has 4 fields and needs to calls rtrim this gets quite complicated, but this still works.

> The syntax looks convoluted, but if you read it carefully you can see
> the meaning. Just think about what Outer Join does. Again, this enables
> the Optimizer to use indices on the lookup.
>
> Keep these forms of query handy. You never know when you'll need them.
>
> Happy Optimizing!
>
> --
> Ed Prochak

Thank you and the others who responded.

Brendan Johnston

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Sep 25 1998 - 00:00:00 CDT