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?

To begin with, the sub-query itself is totally different (the subquery in case 1 is a three table join while the subquery in case 2 is only a two table join). If in case 1, the join condition requires a full table scan of configuration, I can guarantee that the performance would be very slow.

Secondly, the "where exists" clause is similar to a boolean for the given set of predicates. The "in (select..." clause actually causes the subquery to expand to a regular "in (1,2,3...)" type list and subsequently would search through the list to find a match. The caveat is the type of subquery is the size of the expanded list; if the subquery returns 1000 rows, you will then be searching through a list of 1000 items for each row in the outer select.

The moral of this story: if you are not sure about the size of the expanded list, using "where exists (" would be a safer bet in terms of performance.

--
 Alvin W. Law .............................................. Oracle Corporation
 Senior Software Engineer ...................... 300 Oracle Parkway, Box 659306
 Manufacturing Applications .......................... Redwood Shores, CA 94065
 Email: alaw_at_oracle.com ...... Voice: +1 415.506.3390 .... Fax: +1 415.506.7299