Star Schema Challenges

The more experience you have as an Oracle DBA, the harder you're going find this book's techniques to initially trust. Data warehousing is truly a strange new world for anyone , but especially accomplished DBAs. In particular, star schemas require that you:

• Throw out your OLTP experience baggage.

• Be a good dog willing to learn some new tricks.

• Forget all your Oracle design and tuning Golden Rules.

This is no small challenge. It took me over a year to feel comfortable that what I was doing was right (remember, I had no book such as this to soothe my concerns). All my OLTP DBA counterparts thought I was a "crackpot." But as I explained to them, my database's temporary and rollback tablespaces were each double the size of their entire databases, so I had to use radically different techniques.

Here are some basic star schema data warehouse design guidelines that will sound utterly stupid until you've finished reading this book:

• Do not normalize the database design.

• Do not enable primary or foreign key constraints.

• Create bitmap indexes on every column of every table.

• Use bitmap indexes on columns with lengthy character data.

• Use bitmap indexes on columns with millions of different values.

Unless you can let go of your DBA experience and try these techniques, you're never going to have truly ad-hoc queries that reference billions of rows of data and run in your lifetime. The results will speak for themselves . But, you have to be comfortable that the results are more important than the theories you currently hold to be true. It's not easy.

And to make things even worse , not only do you need to let go of your experience, you must also let go of any SQL and database tuning tools you currently rely on. Most of these tuning tools currently embody two decades of OLTP-based expertise. As such, most of them will give advice or recommendations contrary to the design tenets we'll be following. For kicks, you can use these tools to pseudo-gauge your success ”for the more things these tuning tools advise you're doing wrong, the more likely it is that you're doing them right.

A final and equally severe challenge is that you'll have substantially fewer DBAs with whom you can network (i.e., people with whom to discuss your ideas). For example, at EDS, of the dozen or so DBAs for the 7-Eleven account, only two were star schema DBAs. I'd estimate the overall percentage of star schema DBAs to be more like 5%, or 1 in 20 DBAs. So you have notably fewer qualified people with whom you can share and develop ideas. Trust me, it's no fun always being the guy in the know, because then you're not learning anything new from other people you network with.