Re: Separate Index or Composite Indice ?

Jan Krueger <jk_at_stud.uni-hannover.de> wrote:
> 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.

Maybe. But if you look only for some_id, then a regular range scan on (some_id,yes_no) would be better than the skip scan on (yes_no,some_id), wouldn't it?

And if you specify just yes_no, the cluster factor would probably dictate against using the index at all, no matter which way around it is.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB