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.

• Index Database Object: 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.
• index is not free: 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.

One of the first things that novice DBAs learn in school is that creating an index can relieve the requirement to do a full table scan on an Oracle table. By providing the alternative access methods into Oracle tables, queries that used to take hours can be tuned in a matter of seconds. Oracle indexng techniques include

1. bitmapped indexes,
2. star query joins, and
3. hash join techniques

that can also dramatically improve the performance of certain specialized classes of queries. It is important to remember when we go through this module that not all of these items are applicable in all cases. For example, bitmapped indexes are only useful in cases where you have a small number of unique values within a table column. However these advanced tuning techniques are very important for the experienced DBA, and very useful in the tuning of Oracle database systems.

The star join^[1] has its roots firmly planted in data warehouse design. Dr. Ralph Kimball popularized the term star schema to describe a de-normalization process that simulates the structure of a multi-dimensional database. With a star schema, the designer can simulate the functions of a multi-dimensional database without having to purchase third-party software. Ralph Kimball describes the de-normalization process as the pre-joining of tables, such that the run-time SQL application does not have to join the tables at execution time. At the heart of the star schema is a fact table, a long and wide table that is usually composed entirely of key values and raw facts. A fact table is generally very long and wide and may have millions of rows. Surrounding the fact table are a series of dimension tables that serve to add value to the base information in the fact table.

• Star Queries and Dimension Tables:
  One type of data warehouse design is known as a star schema. This typically consists of one or more very large "fact" tables and a number of much smaller "dimension" or reference tables. A star query is one that joins several of the dimension tables, usually by predicates in the query, to one of the fact tables.Oracle cost-based optimization recognizes star queries and generates efficient execution plans for them. You must use cost-based optimization to get efficient star query execution. To enable cost-based optimization, ANALYZE your tables and make sure not to set the OPTIMIZER_MODE parameter to RULE

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

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. The next lesson reviews the limitations of standard Oracle indexes to understand why the new indexing methods were created.

[1]Star Join: A Star Join is where you join a really big fact table (or a really big subset of an enormous table) to a number of smaller tables. A typical use for a Star Join would be the creation of an aggregated fact table from an atomic fact table.