Key Tuning Requirements

In a nutshell, these are the requirements for lightning-fast ad-hoc star schema queries:

  • Oracle 8i or 9i

  • Star transformation explain plan on queries

  • Correct INIT.ORA settings

  • Bitmap indexes (and lots of them)

  • Cost-based optimizer (i.e., statistics)

Of all these, the star transformation explain plan is our ultimate goal. In fact, everything else is merely a prerequisite for the star transformation explain plan. In other words, you cannot get the star transformation explain plan without the proper INIT.ORA settings, bitmap indexes, and cost-based optimization.

As I've said several times now, you must fully meet all these requirements to succeed. Failure to implement any portion of the recommended advice will definitely not achieve the desired results; in fact, it may be worse than any other configuration. So, you have to adopt a "take it or leave it" approach.

If you re-examine Chapter 2's section on Oracle version options, I made a very clear case why you must be on Oracle 8i or 9i to succeed. To recap, only these latest versions of Oracle offer:

  • Reliable and efficient partitioning

  • Reliable and efficient bitmap indexes

  • Star transformation explain plan support

  • Reliable and efficient statistics for cost-based optimization

  • Reliable and efficient histograms for cost-based optimization

  • Reliable, efficient, and easy-to-use parallel query and DML

While Oracle 8.0 offers many or most of these key features, each was either too new or as yet unperfected. You may succeed with Oracle 8.0, but the recommendation is 8i or 9i all the way. Again, it does not matter if your source OLTP systems are in different versions of Oracle than your data warehouse. With data warehouses, you'll generally be far better off riding the bleeding edge of Oracle technology.