Jeff Maresh on “Supercharging Star Transformations”

Supercharging Star Transformations : "A star transformation is a methodology used to join tables to greatly improve performance of queries on dimensional data warehouse schemas.  Prior to star transformations, the primary join methods available were nested loops, merge, and hash joins.  Like many other of the more complex Oracle RDBMS features, star transformations much be properly configured and tuned to optimize their performance.  .  This paper explains how to take full advantage of this powerful data warehouse feature.  Specifically, the author will use a specific dimensional data model to introduce star transformations, illustrate the necessity of bitmap indexes, and show how to configure the database instance to minimize star transformation response time.  It covers versions 8.1.7 through 9.2 of the Oracle RDBMS.  All of the results in this paper were produced on version 9.2.0.4."

Star Transformations are the "secret sauce" in Oracle data warehousing and are one of the main reasons why you should design your data warehouse around a star schema. I had planned to do a write up on star transformations after reading Bert Scalzo's "Oracle DBA Guide To Data Warehousing and Star Schemas" but I've just come across Jeff Maresh's excellent paper and you couldn't do much more of a better job than he's done. If you're wondering what all the fuss is around star transformations take a look at Jeff's paper and chapter 5 of Bert Scalzo's book, and once you get what's going on you'll know why Oracle Warehouse Builder gets you to build your warehouse using facts and dimensions.