Re: Separate Index or Composite Indice ?

xhoster@xxxxxxxxx wrote:
"userjohn" <userjohn@xxxxxxxxxxxxxx> 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

.