Tuning with Oracle Indexes

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 indexing 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.

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

[1] 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.