Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: performance of OR versus IN

Re: performance of OR versus IN

From: Nuno Souto <wizofoz2k_at_yahoo.com.au>
Date: 21 May 2003 18:25:57 -0700
Message-ID: <73e20c6c.0305211725.54994e92@posting.google.com>


dfult_at_econs.umass.edu (Doug) wrote in message news:<358a7114.0305211340.2a34c550_at_posting.google.com>...

> Here's the query:
>
> select count(p.personOID)
> from Address a, AddressXPerson axp, Person p
> where a.postalCode in
> ('07010','07020','07022','07024','07030','07032','07047')
> AND axp.addressOID = a.addressOID
> AND p.personOID = axp.personOID;

that looks a LOT better.

>
> AddressXPerson has 188432 rows; it has an index on addressOID and I
> ran "analyze table AddressXPerson compute statistics" after adding the
> index.
>
> Thoughts?

is addressOID precisely and exactly the same data type in both AXP and Address tables? If not, Oracle may be doing an implicit type conversion which stops the index being used. Are they both NOT NULL?

If all above true, then try a hint. Something like: /*+ ORDERED FIRST_ROWS */
right after the SELECT keyword and see if that solves the problem. If so, then you may need to fiddle with stats to sove the problem if you don't want to use hints.

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Wed May 21 2003 - 20:25:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US