The Rationale for Stars

Over the past decade , an interesting computer phenomenon has occurred: Hardware technology has grown much faster than software technology. We see this all the time in the PC market with hard drives , memory, CPUs, and video cards getting more powerful and cheaper year after year. But, the same is also true in the database server world as well. Mainstream UNIX servers and their disk storage systems have grown in similar leaps and bounds. When's the last time you managed a production Oracle database on a single-processor server? Even test and development database servers are predominately multi-processor given today's low hardware costs.

But alas, the poor Oracle database and optimizer have grown at a somewhat slower pace with regard to handling data warehouses. In fact, Oracle 8i and 9i are the first versions to truly begin tackling the monumental performance challenges posed by today's enormous data warehouses. And as I stated back in Chapter 2, "Software Architecture," you cannot successfully do multi-terabyte data warehouses in Oracle prior to Oracle 8i.

Back in the early 1990s, Ralph Kimball began proposing new relational database design techniques to make data warehouses both understandable and fast. His technique, known as dimensional modeling, makes data warehouses faster by limiting the number of join operations that the database optimizer has to handle. Since join operations are generally quite expensive and since the major database vendors have only recently significantly improved their query optimizers for such large joins, Ralph's database design techniques have become a true staple for data warehousing.

Of course, it is possible to build successful very large databases, not data warehouses, without using star schemas. But you'll find that both Oracle 8i and 9i support star schemas via database initialization parameters, object partitioning, indexing options, explain plans, and materialized views. For example, the same query ran as follows :

• Over 12 hours on Oracle 7.3

• Under 12 minutes on Oracle 8.0

• Under 8 minutes on Oracle 8i

• Under 4 minutes on Oracle 9i

The tables, indexes, and rows of data were exactly the same; only the database initialization parameters ( chosen for star schema explain plan support) and the level of statistics gathered were different. Oracle has clearly recognized the validity of this database design approach. As a DBA, you too should try to employ Ralph Kimball's dimensional modeling techniques.