Notes in-line.
--
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:40f132fc$0$25464$afc38c87_at_news.optusnet.com.au...
> Jonathan Lewis apparently said,on my timestamp of 11/07/2004 9:51 PM:
> >
> > 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?
That's why I said "is perfectly capable of" - I was
interpreting (by reasonable guess) the intent of the
OP when using the ID column.
>
> a list of values in an IN operator is always
> a linear search, AFAIK.
But in the case that the list is the driver, there
is no search.
>
>
> > 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?
8i (if not earlier, but I can only prove it for
8i at present)
>
> >
> > 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)
>
Big assumption - which really is the crux of
why I posted anything on this one at all. It's
too easy to say something is "wrong" simply
because one has never had the same requirement
and therefore has always had a better way of doing
something SIMILAR.
The OP has a requirement for 20,000 lookups
(for reasons that I can't guess) and people tell
him he's wrong because he should be doing a join
(which is what your example might turn into given
the correct constraints).
> > 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?
>
Queries with very large IN lists MAY be a 'total
disaster' - but that doesn't stop a query with a
very large IN list from being more efficient than
any other option.
Received on Sun Jul 11 2004 - 08:40:58 CDT
