Index Clustered Fact Tables in DW
- From: "Stahlke, Mark" <mstahlke@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 3 May 2005 11:18:27 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
