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.