When can secondary indexes be counterproductive on fact tables?
The data in fact tables is stored in chronological order (due to the SYSKEY). Fact tables are usually very large and therefore cannot be loaded completely into the data cache. If a time period is read in a query, it may be useful to create an index using less selective columns that contain time data (for example, an index using columns that contain periods or fiscal years). However, the optimizer program will only choose this type of index if it assumes that a more selective index does not exist. If an index of more selective columns exists for this query but the index has no chronological reference, the optimizer will select this index because it does not know anything about the favorable storage format of the index that contains time-related data. As a result, more pages might have to be loaded into the cache than the number of pages loaded when (from where the optimizer is concerned) the least favorable index is used. You should bear this in mind when creating indexes. If you do not
