Lesson 1

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

1. bitmapped indexes,
2. STAR query operations,
3. STAR transformation queries,
4. a concatenated index, and
5. a function-based index.

An index is a database object used primarily to improve the performance of SQL queries. The function of a database index is similar to an index in the back of a book. A book index associates a topic with a page number. When you are locating information in a book, it is usually much faster to inspect the index first, find the topic of interest, and identify associated page numbers. With this information, you can navigate directly to specific page numbers in the book. In this situation, the number of pages you need to inspect is minimal. If there were no index, you would have to inspect every page of the book to find information. This results in a great deal of page turning, especially with large books. This is similar to an Oracle query that does not use an index and therefore has to scan every used block within a table. For large tables, this results in a great deal of I/O.
Creating an index on the topic of "bitmap indexes" would be effective because there are only a few pages within the book that are applicable to this feature.

Keep in mind that the index is not free. An index in a back of a book consumes space in the back of the book, and if the material in the book is ever updated (like a second edition), every modification (insert, update, delete) potentially requires a corresponding change to the index. It is important to keep in mind that indexes consume space and require resources when updates occur. In addition, the person who creates the index for the book must consider which topics will be frequently looked up. Topics that are selective and frequently accessed should be included in the book index. If an index in the back of the book is never looked up by a reader, then it unnecessarily wastes space.

By the time you complete this module you should be able to:

1. Explain the benefits of bitmapped indexes
2. Describe a STAR query operation
3. Describe a STAR transformation query
4. Create a concatenated index
5. 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:

1. Reduced response time for large classes of ad hoc queries
2. A substantial reduction of space usage compared to other indexing techniques
3. Dramatic performance gains even on hardware with a relatively small number of CPUs or small amount of memory
4. 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.