I agree with David's comments on considering bitmap indices even if the
cardinality is not very low, but bitmap indexes can be disastrous for
performance if there are *any* INSERT, UPDATE, or DELETE operations
expected against the table in question. While bitmap indexes support
insertions, updates, and deletions transparently, they don't do it with
any sense of urgency, so to speak. So, comparison testing should
include samples of modifications as well, to be sure that this is proven
empirically.
And, if you are not on 10g or above, modifications to bitmap indexes
typically cause them to become less compact and less efficient over
time, necessitating periodic rebuilds. If you're on 10g or above,
Oracle took care of this...
David Aldridge wrote:
In the context of memory requirements and disk bandwidth the index size is rather a critical issue though, so there is a correlation between segment size and performance.
Fortunately this issue is very amenable to performance testing of the two alternatives. You might also consider bitmap indexes, even for columns with a relatively high number of distinct values. Throw those into the mix and you have the basis for a very nice little comparison.
-- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l
