Although we have covered indexes extensively in the series, there are still some obscure index features that you can use to greatly promote
the performance of your Oracle queries. While all indexes serve to avoid the costly full-table scan, Oracle has developed a wealth of new indexing options that can speed certain types of queries.
This module discusses the benefits of
- bitmapped indexes,
- STAR query operations,
- STAR transformation queries,
- a concatenated index, and
- a function-based index.
By the time you complete this module you should be able to:
Explain the benefits of bitmapped indexes
Describe a STAR query operation
Describe a STAR transformation query
Create a concatenated index
Describe a function-based index
The next lesson reviews the limitations of standard Oracle indexes to understand why the new indexing methods were created.
Bitmap indexes are widely used in data warehousing applications, which have large amounts of data and ad hoc queries but a low level of concurrent transactions.
For such applications, bitmap indexing provides:
- Reduced response time for large classes of ad hoc queries
- A substantial reduction of space usage compared to other indexing techniques
- Dramatic performance gains even on hardware with a relatively small number of CPUs or small amount of memory
- Very efficient maintenance during parallel DML and loads
Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of space because the indexes can be several times larger than the
data in the table. Bitmap indexes are typically only a fraction of the size of the indexed data in the table.