About Indexes

Indexes are created to provide direct access to rows. An index is a tree structure. Indexes can be classified on their logic design or their physical implementation. Logical classification is based on application perspective, whereas physical classification is based on how the indexes are stored. Indexes can be partitioned or nonpartitioned. Large tables use partitioned indexes, which spreads an index to multiple table spaces, thus decreasing contention for index look up and increasing manageability. An index may consist of a single column or multiple columns; it may be unique or nonunique. Some of these indexes are outlined below.

• Function - based indexes precompute the value of a function or expression of one or more columns and store it in an index. It can be created as a B - tree or as a bit map. It can improve the performance of queries performed on tables that rarely change.

• Domain indexes are application specifi c and are created and managed by the user or applications. Single - column indexes can be built on text, spatial, scalar, object, or LOB data types.

• B - tree indexes store a list of row IDs for each key. Structure of a B - tree index is similar to the ones in the SQL Server described above. The leaf nodes contain indexes that point to rows in a table. The leaf blocks allow the scanning of the index in either ascending or descending order. Oracle server maintains all indexes when insert, update, or delete operations are performed on a table.

• Bitmap indexes are useful when columns have low cardinality and a large number of rows. For example, a column may contain few distinct values, like Y/N for marital status, or M/F for gender. A bitmap is organized like a B - tree where the leaf nodes store a bitmap instead of row IDs. When changes are made to the key columns, bit maps must be modified.