Re: SQL Performance Tuning--do the right thing

The best approach depends on version, which you don't mention. The CBO in Oracle 8i has the in-list iterator, which breaks up the IN and processes all elements seperately. IN ... in Oracle 7 would result in a full table scan, OR would use the index, provided the selectivity of the index is sufficient. As companion to the Harrison book you may consider obtaining the book of Richard Niemic on performance tuning published by Osborne/Oracle Press.

Hth,

Sybrand Bakker, Oracle DBA

"mr_oatmeal" <mr_oatmeal_at_hotmail.com> wrote in message news:t1lmqto65icb14_at_corp.supernews.com...
> Hi I'm taking over some code that was writen by a contractor...and I need
> to go through and improve the embedded sql stmnts. I've been doing so to
> the best of my knowledge...then i run accross something like this. For
> performance, would it be more efficient to use the in clause rather than
> the OR. (both return the same data)
>
> Are there any other hints/tips that you can give me for improving
> performance, things that kill the use of the index, anything would help--
> we don't have a dba. I've been reading a book called Oracle SQL High
> Performance Tuning. If you know of any others please send 'em.
>
> SELECT author_code, first_name
> FROM table
> WHERE status = 'author'
> OR status = 'columnist'
> OR status = 'deputy bureau chief'
> OR status = 'bureau chief'
>
> ---------
>
> SELECT author_code, first_name
> FROM table
> WHERE status IN ('author','columnist','deputy bureau chief','bureau
> chief')
>
>
> mr_oatmeal
>
> --
> Posted via CNET Help.com
> http://www.help.com/
Received on Tue Nov 21 2000 - 15:13:30 CST