Re: Very long "WHERE" list.

gumenyuk_at_canada.com (Serguei) wrote in message news:<c1e8f29a.0407080842.557330e1_at_posting.google.com>...
> Dear Experts,
> I have to execute a SELECT statement with a very long "IN" list with
> several thousands of values:
>
> SELECT something FROM table WHERE id IN (1,3,5, ...., 20001)
>
> Unfortunately Oracle can not process this request directly. Is there
> any way to by path this limitation?
> Regards,
> Serguei.

A comma-delimited list of expressions can contain no more than 1000 expressions. A comma-delimited list of sets of expressions can contain any number of sets, but each set can contain no more than 1000 expressions.

So, you're limited by Oracle to 1000 values in a single IN list. Having 20,001 elements (expressions) in a single IN list is not only foolish, it exceeds the limitations Oracle imposes on such constructs.

This truly screams of bad design, and needs to be seriously rethought before you try to implement this madness.

It has been suggested to use multiple IN lists to break up this impossibly long string of values:

SELECT something FROM table WHERE id IN (1,3,5, ....,1999)
                            OR id IN (2001, 2003, 2005, ....,2999)
                            OR id IN (3001, 3003, 3005, ....,3999)
                            OR id IN (.....)

BETWEEN won't work since you're using odd values and any even numbers present will return unwanted data. The best you're going to get is to use something like the example above and simple live with it, if you don't heed the advice of myself and of others to redesign this monstrosity now while you still have time. I wouldn't want to be called in six months from your go-live date and have to explain why it would be easier to take this application out of service, redesign the database and the application and then go through a second go-live period.

Heed the advice you've been given.

David Fitzjarrell Received on Fri Jul 09 2004 - 20:41:56 CDT