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

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.

Does anyone know of a better way to accompish this?

This is the reason we need to do this:
A user looks at all orders shipped from a certain warehouse in a month. There maybe be 250,000 orders shipped. Of these 10% (12,500) had some type of problem. These problem orders were made up of 6500 distinct parts.
Our new requirement is to take these 6500 'bad parts' and move through the system with them. In each area (backordered, shpping, etc.) we will see the status of these bad parts. So now I need to select from a table where the part_number is in this list of bad parts.

Any ideas?

Don Received on Fri Jan 31 2003 - 20:06:59 CET