Re: bitmap index

"lsllcm" <lsllcm_at_gmail.com> wrote in message news:1132884016.729101.276150_at_g44g2000cwa.googlegroups.com...
>I want to ask do I need to create a bitmap index on STATUS column. And
> create b*tree index on (FNAME,MNAME,LNAME) columns.
>

It is unlikely that any index on status will yield performance benefits, unless the distribution of values are highly skewed and an on-line query exists that wants to see the ones with just a few matching rows.

For most applications, you will want an index on (LNAME, FNAME) only. The index can resolve the matches using these two columns and then further refine the search using the table data in case their happen to more than one row retrieved by the index. Unless there are more than 5 matches on "average" using just the (LNAME, FNAME) index, then you will not see any performance hit.

Probably the two biggest mistakes people make in database design are:

1. Expecting to resolve the entire predicate in the index, and
2. Creating too many indexes that are never used because of low cardinality.

Indexes are not free. There is performance penalty for maintaining indexes during inserts and deletes, or when an indexed column is updated. Received on Thu Nov 24 2005 - 21:04:22 CST