Re: Importance of Column Order in Composite Index

The distribution of values looks like the sort of situation where bitmap indexes are recommended (going by the docs), especially if the queries use lots of AND conditions testing for the precise values. I wonder if there is particular benefit in this case to make the other index(es) bitmap ones?

John

On Wed, 31 Oct 2001 09:15:58 +1100, "Howard J. Rogers" <howardjr_at_www.com> wrote:

>Indexing as X, Y, Z might actually be preferable, because with the low
>selectivity columns first, you can use index key compression, and get much
>more compact indexes (and hence much lower I/O) than you might otherwise
>manage. That's assuming you have at least 8i, of course.
>
>The old myth about the most selective column needing to be first is just
>that -a myth. Hasn't been true for ages. Whatever the order, the space in
>the leaf nodes is going to be the same, and the only benefit of having Z
>first would be a potential saving in branch nodes because of the possible
>trimming of branch entries that can take place -but that's probably not even
>going to be noticeable.
>
>9i has skip scanning of indexes which means searches for non-leading keys
>can be done efficiently, so that shouldn't be a major issues there.
>
>And if you select for X, and X isn't in the index at all, I doubt the Z,Y
>index will be touched.
>
>Just my first thoughts. I may have seconds, though.
>
>Regards
>HJR

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/