Function-Based Indexes
Generally, the fastest way to access Oracle data is with an index. Oracle's indexing schemes are designed to provide complementary performance functionality. While standard B-tree indexes are most effective for columns containing a high number of different values (good selectivity), bitmapped indexes are most appropriate for columns with a limited number (poor selectivity) of possible values.
Administrators supporting large data stores use partitioned indexes to decompose large index structures into smaller, more manageable pieces called index partitions. Oracle8i places index data in the separate index partitions based on the index's partitioning key. The partitioning key of an index must include one or more of the columns that define the index.
Oracle's latest release solves an indexing problem that has been affecting database performance for close to a decade. Before Oracle8i, any SQL statement that contained a function or expression on the columns being searched on in the WHERE clause could not use an index. For example, the statement
SELECT emp_lname FROM employee WHERE salary + commission > 100000;
would not use an index. A full table scan would be required to retrieve the desired result set. We now know that we are able to use B-tree and bitmap indexes to speed query performance.
In Oracle8i, we are able to build both bitmap and B-tree indexes on columns containing the aforementioned functions or expressions. A function-based index precomputes the value of the function or expression and stores it in the index. The following index could be used to increase performance of the query:
CREATE INDEX sal_comm on emp (salary + commission);
B-Tree Indexes
A traditional B-Tree (balanced tree) index stores the key values and pointers in an inverted tree structure. The key to good B-Tree index performance is to build the index on columns having a lot of different values. Oracle describes this as "good selectivity" Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity.
Bitmap Indexes
Conversely, bitmapped indexes perform better when the selectivity of an index is poor. The fewer different values a bitmapped index contains, the better it will perform.
Bitmap indexes, in certain situations, can provide impressive performance benefits. Bitmapped indexes are most appropriate for complex and ad-hoc queries that contain lengthy WHERE clauses on columns that have a limited number of different values (poor selectivity).
Optimizer and Bitmapped Indexes
The optimizer can be stubborn at times. It can be particularly stubborn when you want it to choose a bitmapped index for an access path. A single bitmap index may not be chosen at all. The optimizer will be more inclined to choose bitmapped indexes as an access path if it can use multiple bitmapped indexes simultaneously. If you want Oracle to choose bitmapped indexes as an access path, build a lot of them or use hints.
Bitmapped Index Concurrency
Anyone accustomed to database programming understands the potential for concurrency problems. When one application program tries to update data that is in the process of being changed by another, the DBMS must forbid access until the modification is complete in order to ensure data integrity.
Each entry in a B-Tree index entry contains a single ROWID. When the index entry is locked during an update, a single row is affected. A bitmapped index entry can potentially contain a range of ROWIDs. When Oracle locks the bitmapped index entry, the entire range of ROWIDs is also locked. The number of ROWIDs contained in the range affects the level of concurrency. As ROWIDs increase in the bitmap segment, the level of concurrency decreases.
When to Use Bitmapped Indexes
Locking issues affect data manipulation operations in Oracle. As a result, bitmapped indexes are not appropriate for OLTP applications that have a high level of concurrent insert, update and delete operations. Concurrency is usually not an issue in a data warehousing environment where the data is maintained by bulk loads, inserts and updates.
In addition, bitmapped index maintenance is deferred until the end of the bulk DML operation. If 100 rows are inserted into a table, the inserted rows are placed into a sort buffer and the updates of all 100 index entries are applied as a group. As a result, bitmapped indexes are appropriate for most decision support applications (even those that have bulk updates applied on a regular basis).
Mass updates, inserts and delete will run faster if you drop the bitmapped indexes, execute the DML and recreate the bitmapped indexes when the DML completes. Run timings using the straight DML and compare it to the total time consumed by the drop bitmapped index/execute DML/recreate bitmapped index process.
DBAs must also take column cardinality into consideration when determining the potential performance benefits of bitmapped indexes. The higher the index's cardinality, the larger the index becomes. If a table containing the gender (two different values) and hair_color (four different values) columns contains 1 million rows, the bitmapped index on gender would require 2 million entries and the bitmapped index on hair_color would require 4 million entries.
Bitmap Versus B-Tree
We just learned that standard B-tree indexes are most effective for columns containing a high number of different values (good selectivity) and bitmapped indexes are most appropriate for columns with a limited number (poor selectivity) of possible values.
Where is the selectivity cutoff point? What is that magic number of different values when a bitmapped or a B-Tree index is no longer considered to be a viable indexing option? There are many variables (number of rows, column cardinality, query composition, update frequency) that must be considered when determining the potential performance benefits of bitmapped and B-Tree indexes.
The only definitive method of determining the potential performance benefits of bitmapped and B-Tree indexes is to create them and monitor the application's performance. I've seen B-Tree indexes bring back 25% of the rows faster than table scans and have seen bitmapped indexes with a couple of dozen different values perform flawlessly.
Remember that "rules of thumb" are just general recommendations. The only definitive way to determine index performance is to create the index and test query performance.
Indexing
Some key points on indexing:
If you don't have enough information to make an educated guess on these parameters, don't make an uneducated one. Select the default values and monitor for chained rows by using the LIST CHAINED ROWS option in the ANALYZE statement.
Exit to : Oracle Hints and Tips