Re: Separate Index or Composite Indice ?

From: Jan Krueger <jk_at_stud.uni-hannover.de>
Date: Fri, 31 Aug 2007 20:58:00 +0200
Message-ID: <46d86431$0$29373$4c56b896@news-read1.lambdanet.net>

xhoster_at_gmail.com wrote:
> "userjohn" <userjohn_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

>
> In the same SQL, or in separate SQLs?
>

>> Would you create normal index on BOTH , just "some_id" or a composite
>> indice on "some_id" + "yes_no" ?

>
> I generally wouldn't bother with a single column index on "yes_no".
> I'd make one on (some_id,yes_no) and forget it. If it forces itself
> back into your attention due to performance issues, then I'd look
> at explain plans.
>

I would consider creating it the other way around (yes_no, some_id) because it
allows an index skip scan to be performed if you need to look only for some_id. Don't forget to have histograms on the columns.

Anyway you have to consult the execution plans of actual statements.

Jan Received on Fri Aug 31 2007 - 13:58:00 CDT