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 O’Reilly online learning.

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