Optimizing Star Queries

You should consider the following when using star queries:

• Tuning Star Queries
• Using Star Transformation

Tuning Star Queries

To get the best possible performance for star queries, it is important to follow some basic guidelines:

• A bitmap index should be built on each of the foreign key columns of the fact table or tables.
• The initialization parameter STAR_TRANSFORMATION_ENABLED should be set to true. This enables an important optimizer feature for star-queries. It is set to false by default for backward-compatibility.
• The cost-based optimizer should be used. This does not apply solely to star schemas: all data warehouses should always use the cost-based optimizer.

When a data warehouse satisfies these conditions, the majority of the star queries running in the data warehouse will use a query execution strategy known as the star transformation. The star transformation provides very efficient query performance for star queries.

Using Star Transformation

The star transformation is a powerful optimization technique that relies upon implicitly rewriting (or transforming) the SQL of the original star query. The end user never needs to know any of the details about the star transformation. Oracle's cost-based optimizer automatically chooses the star transformation where appropriate.

The star transformation is a cost-based query transformation aimed at executing star queries efficiently. Oracle processes a star query using two basic phases. The first phase retrieves exactly the necessary rows from the fact table (the result set). Because this retrieval utilizes bitmap indexes, it is very efficient. The second phase joins this result set to the dimension tables. An example of an end user query is: "What were the sales and profits for the grocery department of stores in the west and southwest sales districts over the last three quarters?" This is a simple star query.

Reference: http://docs.oracle.com/cd/B10501_01/server.920/a96520/schemas.htm#11292