Re: Separate Index or Composite Indice ?

From: <fitzjarrell_at_cox.net>
Date: Thu, 30 Aug 2007 09:43:44 -0700
Message-ID: <1188492224.970376.193670@19g2000hsx.googlegroups.com>

On Aug 30, 11:15 am, "userjohn" <userj..._at_mailinator.com> wrote:
> 10gR2
>
> If you have :
> 2 columns on a hi-traffic transactional table
> - one ("some_id") high cardinality
> - and one ("yes_no") low cardinality
>
> You use both columns in SQL - "some_id" is used in JOIN and "yes_no" as a
> predicate
>
> Would you create normal index on BOTH , just "some_id" or a composite indice
> on "some_id" + "yes_no" ?
>
> thanks

I would be testing the possibilities and determine which one provides the 'best' execution plan. 10gR2 doesn't say much, as that covers the base release (10.2.0.1) through 10.2.0.3; optimizer behaviour may have changed between the base and the most current release.

David Fitzjarrell Received on Thu Aug 30 2007 - 11:43:44 CDT