Index Clustered Fact Tables in DW

Greetings,
The answer to my question is Oracle Partitioning. Alas, I'm not licensed for it. Yet.

We are seeing some severe performance issues with our shiny new data warehouse. It's 9.2.0.4 on Red Hat AS3.0. The hardware is a bit under configured. It's a dual 3.2GHz Xeon box that just had its memory upgraded from 4GB to 8GB yesterday. The top wait events are usually scattered reads or sequential reads.

It is quite common for our reports to select data based on a range of issue dates (it's advertising data) so, of course, we have an index on the issue date column of the fact table. This column would be the ideal partitioning key, but...

A consulting developer has been looking at the index clustering factor of the issue date index. It's 7.3M vs 11M rows in the table. He says this could cause index range scans to be inefficient because the data is scattered through out the blocks that store the table. He created a new version of the fact table in question using CTAS and ordering it by issue date and claims this table perofrms better because it takes fewer IOs to read any given range of issue dates. This seems logical to me but that doesn't make it true. Unfortunately the data will not stay nicely ordered over the course of time.

I'm considering recreating this fact table as an index clustered table clustered by issue date. If my understanding is correct (always a risky bet), this will cause Oracle to store the clustered data in the same data blocks and reduce the number of IOs for a range scan type of operation. Sort of like the very, very, very poor man's partitioning.

Has anyone used index clustered fact tables in a DW environment? What kind of performance issues might I see on the ETL side? Is this worth trying?
Am I insane?

Thanks,
Mark Stahlke
Oracle D'ohBA
Denver Newspaper Agency

--
http://www.freelists.org/webpage/oracle-l