Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: performance of OR versus IN
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