Re: Very long "WHERE" list.

Bear in mind the OP had a query:

    select {columns} from {table}
    where id in {list of values}

1. Oracle is perfectly capable of executing this as: in-list iterator table access by rowid index unique key by unique key.

    The requirement to search a linear list does not exist.     I suspect you are thinking of the case:

        acquire a row from the table (by tablescan ?)
        check if row matches with anything in the list.
    which would probably appear in Oracle as the plan
        Filter
            tablescan

    (In passing, there are various places in the code     where an execution plan may 'indicate' an o(n)     algorithm is running, when really an o(log(n)) algorithm     has been used.

b) Oracle sorts a literal list before starting the

    iteration - this eliminates duplicate work     early on, and introduces an element of     optimisation at the index level because of     caching effects. (Not a truly significant     benefit, perhaps, but it is there).

c) How would you go about rewriting this

    query as an 'exists' query ?

d) How could this query be any less efficient

    than 20,000 table accesses by primary key.     (In fact, for reasons of latching, pinning, and     network round-trips, it would be more efficient).

e) With regard to your comment about hashing -

    if the target table is a single-table hash cluster,     then you can forget about the path in (a), Oracle     will happily do a similar thing, but access unique     rows in the hash cluster by hash value.

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


"Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote in message
news:40f112b3$0$19155$afc38c87_at_news.optusnet.com.au...


> 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