Re: same results, but one takes longer

In article <1993Mar17.194047.3233_at_ibx.com> mlim_at_ibx.com (Mike Lim) writes:
>The following two nested select statements give the same results.
>However, one (i.e. statment 1) takes much less time to process
>than the other:
>
>SQL statement 1:
>
>select * from system where system.oidval in (select system.oidval from system, configuration,
>sysuser where system.oidval=configuration.config_makes_system and configuration.serialnumber='DT3405');
>
>SQL statement 2:
>
>select * from system where exists (select * from configuration, sysuser where
>system.oidval=configuration.config_makes_system and system.system_has_user=sysuser.oidval and
>configuration.serialnumber='DT3405');
>
>
>Assumptions at time of observation:
>
>Oracle v6.
>System's table has approx. 481 records
>Configuration's table has approx 1900 records
>Sysuser's table has approx. 242 records
>
>
>I guess it must be the difference between the inner workings of the keywords "where exists"
>versus "where <field> in". Can anyone shed some light on this issue/question?

This is simple - statement 1 is a 2-way join, statement 2 is a 3-way join. EXISTS is going to be faster than IN, in general, since there is one less operation to perform.

I find this post confusing, since you are comparing apples and oranges (the table sysuser is not used in the first statement). Perhaps one of the two statements is not correct as written?

Mark Received on Wed Mar 17 1993 - 21:45:46 CET