Re: Very long "WHERE" list.

Jonathan Lewis apparently said,on my timestamp of 11/07/2004 9:51 PM:
> Bear in mind the OP had a query:
>
> select {columns} from {table}
> where id in {list of values}
>
>
> a) Oracle is perfectly capable of executing this as:
> in-list iterator
> table access by rowid
> index unique key by unique key.

that assumes the db knows a-priori that "id" in above query is a PK column, indexed as such. What if it isn't? But yes, agreed: the db should be smart enough to turn the IN list search into an iteration if at all possible.

> The requirement to search a linear list does not exist.

a list of values in an IN operator is always a linear search, AFAIK. Because there is no assumption whatsoever about what the nature of the values are.

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

Not aware of this. 10g?

>
> c) How would you go about rewriting this
> query as an 'exists' query ?

Assuming the id is a PK/UK in some table: select {columns} from {table} alias1
where exists (select 1 from {some table} alias2

               where alias2.id = alias1.id)

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

I doubt it. IME, queries with very large IN lists are a total disaster for performance. Maybe it changed in 10g?

>
> e) With regard to your comment about hashing -
> if the target table is a single-table hash cluster,

I was talking about applying a hash function to the list of values, in the application code. Nothing to do with Oracle hash tables.

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