Re: SQL - long list in the where clause. Any ideas?

anon_at_anon.com (dev) wrote in message news:<3e3aa742.929000984_at_news-east.newscene.com>...
> I have an application that allows users to look at many tables using
> many filters. One of these filters is a list of part numbers. The
> SQL for this is something like:
> SELECT ....
> FROM ....
> WHERE part_number IN (1,2,3);
> Currently, this list of part numbers entered manually and very small.
>
> I now have a requirement that may cause this list to be huge. Oracle
> has a limitation of 2000 items in an IN statement. I have tried using
> many OR statements but perfomrance is terrible.

Sybrand suggests you put them in a table. That's one way to do it. Make sure you create global temporary table to hold the intermediate numbers. A better way is using a nested table as Tom Kyte often showed us. Search for "inlist bind variable" at asktom.oracle.com.

Yong Huang Received on Fri Jan 31 2003 - 17:34:43 CST