Traditional (B*-tree) indexes are most useful on columns with a significant amount of variety in their data. For instance, a column that indicates whether a company is a current client with a Y or N value would be a poor choice for a traditional index and could actually slow down a query; a bitmap index would be a much better choice for that example. A telephone number column would be a good candidate for a B*-tree index. An area code column would be marginal, depending on the distribution of unique area code values in the table.
In a multi-column index, put the column likely to be accessed most often first. You can use the optimizer’s skip-scan feature to use multicolumn indexes even if the leading column is not mentioned in the query.
Small tables may be better left unindexed, except to enforce uniqueness in the primary key. A small table is one that takes so few database blocks that Oracle can read all its data with one physical read; beyond that, indexing will nearly always be productive. The number of blocks read during a physical read is set via the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.
Bitmap indexes present a viable indexing alternative for columns that have very few distinct values. Bitmap indexes are commonly used for “flag” columns that are restricted to values such as Y and N. Bitmap indexes are particularly effective when multiple bitmap indexes are used in a query; the optimizer can quickly evaluate the bitmaps and determine which rows meet all the criteria for the bitmap indexes that are available.
Leave a Reply