Re: Separate Index or Composite Indice ?

"userjohn" <userjohn_at_mailinator.com> wrote in message news:13ddrbf3th7fsd8_at_corp.supernews.com...
> 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" ?
>

Can't really answer the question without a bit more info but some things to consider include:

Is this one SQL statement or are we talking about separate / differing SQL statements ?

Could the some_id column also be used as a predicate or will all resultant values of some_id be of interest ?

Is some_id a PK or a FK in this table ?

Are there other columns in the table (or could an index organized table be considered) ? If there are other columns, how frequently are they referenced ?

How large is this table and how large is the table (or tables) that it joins with ?

What's the data distribution in the yes_no column ? Is there a value which constitutes the vast percentage of rows or is yes and no reasonably evenly distributed ?

Note by placing the yes_no column first in a composite column, you might be able to compress the index a tad (depends on actual length of column values). Unlikely the other way around.

Note by placing the yes_no column first in a composite column, you can potentially use a skip-scan access if you happen to not reference yes_no in a predicate. Unlikely the other way around. So I would consider a composite index on yes_no, some_id a more likely combination than the other way around if you go down the composite index path although it does depend on the distribution of data on the yes_no column and how likely the column is referenced.

Some things to consider anyways.

Cheers

Richard Received on Fri Aug 31 2007 - 08:24:45 CDT