Oracle and DB2, Comparison and Compatibility/Storage Model/Indexes/Oracle

Oracle

In Oracle indexes are stored in their own segment separately from the table they index, and they identify table rows by pointing to the rowid in the block header. Indexes are used and maintained by the Optimizer.

From the Physical Storage section earlier on Oracle, there can be indexes on tables, and indexes on clusters. The cluster index is different from a table index in that the index must exist before data is inserted into the table, and clustered data cannot be accessed without the cluster index. Since the advantage of clustering is that a block fetch retrieves a group of logically related (and hence physically adjacent) data, the cluster index points to a block (instead of a row).

Bitmapped Indexes

Bitmapped indexes (introduced in Oracle 7) are particularly useful in data warehouse applications where data is typically retrieved on broad criteria such as “How many orders came from California last year”. Bitmapped indexes can be used alongside standard table indexes, clustered indexes and hash clusters. Bitmap indexes have a significant space and performance advantage over other indexes. Bitmap indexes use bit arrays (bitmaps). While a regular index will typically uniquely identify a row, you can use a bitmap when you want to find all the column index values that are the same. For example, if you created a bitmapped index on ‘California’ you would use logical bitwise comparison against this bitmap to rapidly identify all occurrences of ‘California’ as opposed to a string comparison. Bitmapped indexes are maintained by the DBMS across insertion, deletion and modification.