Re: DBA FIGHT

Daniel

Yes you are right there are exceptions, I was giving guidelines.

Your NOT IN is a reduction of the return values which is going to provide the primary query a much small set of values for comparison.

-GP

On Tue, 24 Jun 2003 13:56:58 -0700, Daniel Morgan <damorgan_at_exxesolutions.com> wrote:

>Gene wrote:
>
>> In terms of performance a direct table join will produce the best
>> performance, a correlated sub query with an EXISTS clause will produce
>> better results than a query using the IN function.
>
>> <snipped>
>
>Your statement is often true but not always true.
>
>Take the following two queries for example with 100 records in the Server
>table and 1000 records in the Serv_Inst table:
>
>SELECT srvr_id
>FROM servers s
>WHERE EXISTS (
> SELECT srvr_id
> FROM serv_inst i
> WHERE s.srvr_id = i.srvr_id);
>
>SELECT DISTINCT srvr_id
>FROM servers
>WHERE srvr_id NOT IN (
> SELECT srvr_id
> FROM servers
> MINUS
> SELECT srvr_id
> FROM serv_inst);
>
>Want to bet which is faster? On 8.1.7.3, on 9.2.0.1?
Received on Tue Jun 24 2003 - 16:56:53 CDT