At 12:01 PM 9/30/2002 -0400, Thomas Miller wrote:

> >
> >Never was. When you create multi-segment indexes, you should put the
> >least selective column(s) first, and the most selective last. That
> >will help compression, thus reduce the size of the index.
> >
>Nothing like getting bad information. Thanks for getting me straight on
>this.

Actually the case you presented is interesting. InterBase can
use multiple indexes on a single query, so yes, it does "cobble
together" several indexes - actually it does an AND of bitmaps,
so it's pretty efficient. So, if you have Columns 1, 2, & 3 and
you create indexes A, B, & C one on each of those columns, there's
no need for an index D composed of 1, 2, & 3. Or an index E
composed of 3, 2, & 1...

If, however, you generally ask for a combination of 1, 2, & 3,
sometimes for 1 alone, and sometimes for 1 & 2, but never 2
without 1 or 3 without the others, then a compound index is
fine. Just don't build duplicate indexes.

> >
>Oracle does not create the index for
>you so you have complete control over making sure the index runs
>properly. If you forget
>the index, it still works, it just runs dog slow.

Right, I'm with you there. The existence of those indexes is
built into Firebird pretty thoroughly, so the change is not
simple.

> From what I understand, I shouldn't and don't need to create the index
>first, just alter the table.

Right.


Regards,

Ann
www.ibphoenix.com
We have answers.