Chapter 4. Star Schema Universe

Let me start this chapter by restating this book's mission statement:

To serve as the DBA's definitive and detailed reference regarding the successful design, construction, tuning, and maintenance of star schema data warehouses in Oracle 8i and 9i.

Note that "star schema" is much more than a term ; it's a mindset. In fact, it's as big a leap in theory and technique as between hierarchical and relational databases. This mindset applies first and foremost to database design and tuning techniques. But more importantly, it directly controls which Oracle optimizer features can best be leveraged and the resulting query explain plans that can possibly be achieved.

If you're the DBA for a star schema data warehouse, then this book is for you. The techniques within are proven and not to be found elsewhere. Moreover, none of these techniques are so obvious as to be considered mainstream. To date, I've found that less than 5% of the people attending my data warehousing presentations have ever thought along the lines espoused throughout this book. Furthermore, few people seem to have read the Oracle white paper titled "Star Queries in Oracle8," published back in June of 1997 and the fore-father for many of my design and tuning techniques.

Conversely, if you are the DBA for a data warehouse that is not doing star schemas, then very little in this book will be of use. While I could get back up on my soapbox from Chapter 1 regarding "What Is a Data Warehouse?" and ask how you expect to successfully build multi-terabyte data warehouses without star schemas, it's not my intention to question or belittle your data warehouse. But I do want to set expectations properly. So once again, if you are not doing star schemas, then this book is not for you.

So let's see where star schemas came from, special challenges they pose (particularly for more experienced DBAs), what they look like, and how to successfully design them.