From: STELLAR@BCSC02.BITNET (Stellar Systems Group)
Newsgroups: bit.listserv.sqlinfo
Subject: Re: IN predicate
Date: 5 Nov 92 21:13:46 GMT
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU


 On Wed, 4 Nov 1992 09:06:42 PST, Larry Crawford said:

 >On Wed, 4 Nov 1992 11:47:02 EST Baerbel Allingham said:
 >>When coding a query with the IN predicate, what is the maximum number of
 >>values for comparison.
 >>
 >>e.g.:
 >>select * from table1, table2 where table1.field1 = table2.field2 and
 >>         table2.field2 in (select table2.field2 from table2
 >>                                  where table2.field4 = 'abc')
 >>
 >>  note: the IN would produce about 900 values.
 >>
 >>Since any command can only be 2048 bytes does this apply to the length after
 >>all values are substituted?
 >>
 >I don't think the 2k limit applies in this case, however and alternative
 >way to code the query is:
 >
 >select * from table1, table2 where table1.field1 = table2.field2 and
 >         table2.field4='abc'
 >
 >I believe this is equivalent and will perform better.

 I don't think there is any real limit to the number of values for the IN
 list.  The only limit you should run into is the size of your internal
 dbspaces, which is where the results of the subquery will be stored.

 I agree with Larry that you might want to rewrite your query, although I
 don't believe that Larry's query is always equivalent to the original query.

 If table2 has two rows, with the values
     (field1, 1234, field3, 'abc')
     (field1, 1234, field3, 'xxx')
 then the original query would select both rows from table2, while the modified
 query would only select the first row.

 You could try and rewrite the query as follows:

  select * from table1, table2 x
  where table1.field1 = table2.field2
    and exists (select 1 from table2 y
                 where table2.field4 = 'abc'
                   and y.field2 = x.field2)

 This MAY perform better, if table2 has an index on field2 (which I would
 expect it to since it is being used for the join condition).  You'll have
 to run EXPLAINs, and also test it, to see if it performs any better.

 In the original query, the subquery would be processed first, and an internal
 table would be built with the 900 results.  The internal table would then be
 scanned for each qualifying row for the table1.field1 = table2.field2.

 In the query with the exists clause, the subquery would be evaluated for each
 qualifying row for the table1.field1 = table2.field2.  Assuming there is an
 index on table2.field2, this MAY perform better.  The actual path chosen, and
 the performance characteristics depend upon the statistics, and actual data
 distribution.

 Please note that in the subquery, I coded it as y.filed2 = x.field2.  The
 order is CRITICAL.  If it is coded in the reverse order (x.field2 = y.field2)
 then it will not perform as well.  This is a common problem, since the
 intuitive way to write the statement is not the best way.  (I have written
 this type of query, given it to people to implement, and had them change the
 order to make it read better, but then it performs much worse.)  The reason
 you need to have the order correct, is because for the predicate to be
 sargable, it must be of the form <column> <operator> <column or value>.
 When the subquery is evaluated, the current value of x.field2 is used as a
 value.  If you code it in the wrong order (x.field2 = y.field2) then it is
 of the form <value> <operator> <column>, which is non-sargable, and
 non-indexable.

 Regards,
 John F. Griffin
 Stellar Systems Group
 306 - 1095 McKenzie Avenue
 Victoria, B.C.  Canada  V8P 2L5
 Phone:(604) 479-7722  Fax:(604) 479-6526