Using Indexes with an Oracle Database - Bitmap indexes
(Page 2 of 4 )
In a standard B*-tree index, the ROWIDs are stored in the leaf blocks of the index. In a bitmap index, each bit in the index represents a ROWID. If a particular row contains a particular value, the bit for that row is “turned on” in the bitmap for that value. A mapping function converts the bit into its corresponding ROWID. Unlike other index types, bitmap indexes include entries for NULL values.
You can store a bitmap index in much less space than a standard B*-tree index if there aren’t many values in the index. Figure 4-2 shows an illustration of how a bit map index is stored. Figure 10-3 in Chapter 10 shows how a bitmap index is used in a selection condition.
Figure 4-2. Bitmap index
The functionality provided by bitmap indexes is especially important in data warehousing applications in which each dimension of the warehouse contains many repeating values, and queries typically require the interaction of several different dimensions. For more about data warehousing, see Chapter 10.
Function-based indexes
Function-based indexes were introduced in Oracle8i. A function-based index is just like a standard B*-tree or bitmap index, except that you can base the index on the result of a SQL function, rather than just on the value of a column or columns.
Prior to Oracle8i, if you wanted to select on the result of a function, Oracle retrieved every row in the database, executed the function, and then accepted or rejected each row. With function-based indexes you can simply use the index for selection, without having to execute the function on every row, every time.
For example, without a function-based index, if you wanted to perform a case-insensitive selection of data you would have to use the UPPER function in the WHERE clause, which would retrieve every candidate row and execute the function. With a function-based index based on the UPPER function, you can select directly from the index.
As of Oracle Database 10g, you can perform case- or accent-insensitive queries; these queries provide another way to solve this problem.
This capability becomes even more valuable when you consider that you can create your own functions in an Oracle database. You can create a very sophisticated function and then create an index based on the function, which can dramatically affect the performance of queries that require the function.
Invisible indexes
Oracle Database 11g introduces a new option for all of the index types we’ve discussed in previous sections—the invisible index. Normally, all indexes are used by the optimizer, which is described later in this chapter. You can eliminate an index from optimizer consideration by taking the index offline or by deleting the index. But with both of these methods you will have to take some actions to bring the index up to date when you bring it back into the database environment.
But what if you want to just remove the index from optimizer consideration for a limited time, such as when you are testing performance? With the invisible option, an index is not considered as a possible step in an access path, but updates and deletes to the underlying data are still applied to the index.
Next: Partitioning >>
More Database Articles Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of the book Oracle Essentials, Fourth Edition Oracle Database 11g, written by Rick Greenwald, Robert Stackowiak, and Jonathan Stern (O'Reilly, 2007; ISBN: 0596514549). Check it out today at your favorite bookstore. Buy this book now.
|
|
