Oracle® DBA Guide to Data Warehousing and Star Schemas

Star Optimization Evolution

There are several ways to optimize a query against a star schema design. But, the results are radically different in terms of runtime performance. Let's examine the evolution of these star schema query optimization techniques, their basic explain plan formats, and their performance results. We'll use the same example query from Chapter 4: How much beer and coffee did we sell in our Dallas stores during December 1998? The SQL code is as follows:

 SELECT prod.category_name, sum (fact.sales_unit) Units, sum (fact.sales_retail) Retail FROM pos_day fact, period per, location loc, product prod WHERE fact.period_id = per.period_id AND fact.location_id = loc.location_id AND fact.product_id = prod.product_id AND per.levelx ...