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

Get Oracle® DBA Guide to Data Warehousing and Star Schemas now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.