A Plethora of Design Options

One of the first things to do before designing your fact tables is to fully understand your entire range of table and index implementation options (shown in Figure 8-1). If nothing else, this plethora of design options should very clearly explain why DBAs are necessary. Far too many developers "want to be" DBAs and don't really know all the possible options for implementing a table. These types are often shocked by Figure 8-1.

Figure 8-1. Plethora of Oracle Table and Index Implementation Options

The key point (objects shaded in gray in Figure 8-1) is to follow the right-hand side of this tree of options. Namely, facts should be simple, heap-organized tables that are partitioned. And their indexes should be locally partitioned (i.e., each table partition has a matching index partition). The fact's unique index should be a local, prefixed b-tree, while all the remaining indexes should be bitmaps. Assuming that you partition by time, the time dimension-based bitmap will most likely be a local, prefixed bitmap index, while the others will be local, non-prefixed bitmaps.

And that brings us to picking a stated design direction and then implementing it.