Re: Very long "WHERE" list.

Jonathan Lewis apparently said,on my timestamp of 11/07/2004 1:00 AM:

>
> 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.

Disagree. A IN operator searches a linear list (linear does not necessarily mean consecutive). A linear search loses efficiency very quickly, it is a well known problem of searching algorithms. Allowing for very large linear searches is NOT the correct way to encourage good coding, and this has nothing whatsoever to do with Oracle or any other database. Linear searches are not efficient, they require 1/2*n comparisons on average.

In a case like this - 20K values - I'd always go for an alternative. Most likely an EXISTS query. In other cases there might be a better alternative using an algorithm-based search (hashing).

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam