One new feature in both the Enterprise and Workgroup editions of
Oracle7 Server for NT is the ability to use traditional key (B-tree)-based
indexes and bitmapped indexes. Here's a quick look at both types of indexing.
Relational database management systems (RDBMSs) include catalogs or system
tables that store definitions of database components such as tables, columns,
and indexes. Subject tables consist of rows about entities such as employees or
products. Link tables store information that lets an RDBMS join separate pieces
of information (such as a supplier's name, how much product is in inventory, and
sales) about a real-world item.
Well-designed relational database tables have a primary key field and an
index based on that key; you can usually define as many other indexes as you
want. For example, you can index a product table both on the product ID (the
primary key) and the manufacturer ID (the foreign key). In this example, the
foreign key is relating--or linking--data about a specific product to
its manufacturer. An index is basically a lookup table containing actual key
values (the product IDs, for example) and pointers to the complete record.
All RDBMSs support both sequential and indexed access to records. Sequential
access reads data sequentially until it finds the row or rows you want. This
brute-force approach works well for small tables in which all rows are
physically clustered or in applications that must perform an action on every
row.
Most RDBMSs use B-trees or some variation of them as the primary indexing
method. B-trees store index data in a hierarchy (or tree) of pages. Each index
page typically contains many index entries and pointers to the next page of
entries. The advantage of B-tree indexes over sequential access is that, instead
of scanning pages that contain raw data, the RDBMS has to look at only a few
index pages until it finds pointers to the requested rows.
By reducing the number of read operations to data, bitmapped indexes offer
better response time than traditional indexing methods such as B-tree indexes.
The idea behind a bitmapped index is that one bit associates a specific value
for an attribute with a row. For example, each distinct value in a column can
have a bitmapped index consisting of 5 million bitsone for each record in
the database. When a bit is on (1), the value occurs in the record; when the bit
is off (0), the value doesn't occur. The index can identify records through
their bitmap position, so bitmaps don't need pointers.
Bitmapped indexes offer several advantages, such as reduced storage
overhead and the capability to do compression operations on indexes. Bitmapped
indexes are best for data with few unique values (not, for example, a product ID
column in which each record has a unique value). Bitmapped indexes are also best
for nonvolatile data, so these indexes are often an important feature of data
warehouses. Finally, bitmapped indexes are extremely useful if you use Oracle7
for decision support or for fine-tuning performance. However, because modifying
bitmapped indexes can impose a tremendous amount of overhead, they aren't a good
choice for OLTP (i.e., volatile-data) systems.