Re: Separate Index or Composite Indice ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 2 Sep 2007 15:48:40 +0100
Message-ID: <J9-dnTcgxaKzUEfbnZ2dnUVZ8tKsnZ2d@bt.com>

"Richard Foote" <richard.foote_at_nospam.bigpond.com> wrote in message news:xEUBi.29001$4A1.27811_at_news-server.bigpond.net.au...

> 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
>

Just a little warning - last time I checked the skip-scan was not considered as an option for the second table in a nested loop join with index.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Sun Sep 02 2007 - 09:48:40 CDT