Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Query Optimization

Oracle first provided the ability to recognize a star schema in the query optimizer in Oracle7 and has focused on making its cost-based query optimizer smarter in response to decision-support queries.

How does the optimizer handle a query against a star schema? First, it notices that the sales transactions fact table (shown in Figure 9-2) has a lot more entries than the surrounding dimension tables. This is the clue that a star schema exists. As Oracle7 evolved, the optimizer began to produce much smarter plans. The optimizer for a standard relational database typically would have tried to join each of the dimension tables to the fact table, one at a time. Because the fact table is usually very large, involving the fact table in multiple joins takes a lot of time.

Cartesian product joins were added to Oracle7 to first join the dimension tables, with a subsequent single join back to the fact table in the final step. This technique works relatively well when there aren’t too many dimension tables (typically six or fewer, as a rule of thumb, since the Cartesian product can get quite large) and when data is relatively well populated.

In some situations, there are a fairly large number of dimension tables or the data in the fact table is sparse. These data warehouse schemas can be handled better by using Oracle’s bitmap indexes, which were initially described in Chapter 4.

Bitmap indexes were first introduced in Oracle7 to speed up the type of data retrieval and ...