Re: Very long "WHERE" list.

There is an important difference between:

    I've never had to do anything like this and

    No-one should ever do this.

It is very unusual to need an extremely long in-list, but when does an in-list become extremely long ?

Why is 1,000 items okay and 1,001 bad ?
Did the move from 250 to 1,000 make Oracle move from okay to ridiculous, or from inadequate to okay ?

Turkbear's questions are very revealing:

    > But, what kind of query would need that many IN values
    > and how are they entered?
    > Does someone actually type in several thousand values?

Certainly no end user would type in 20,000 values, or even pick 20,000 rows from a list. If your background is screen-based and transactional, then obviously you will think the idea insane.

But what if the client is a machine - what if it's doing some sort of hardware monitoring and there really is a need to do 20,000 look-ups every 10 seconds ? Why insert a row into a database if you want to do a lookup - you wouldn't; so why (as another poster suggested) insert 20,000 rows to do 20,000 look-ups.

There are always special cases which will fall outside your current experience. Don't be too quick to condemn something that looks like a silly question - it might be a very sensible question.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1089337198.558261_at_yasure...


> There are various ways of solving the problem but the thing that comes

> to mind first is that if you have this requirement you have a system

> desparately in need of redesign.

>

> I'd suggest fixing the architecture as this is not just an Oracle

> nightmare ... it precludes using most tools to do most things.

>

> Daniel Morgan

>