Rollup Aggregation Tuning

Hi,

I'm working on a data warehouse solution for fact data aggregation. Short description:

there's base level fact table:

TIME_ID, GEO_ID, PROD_ID, AMOUNT1, AMOUNT2.... Fact data is 3-dimensional with, say ~500 nodes in each of the hierarchies lowest level (input data).
Rows are distinct. So there's 500^3 rows, with low cardinality for each separate dimension column.

I also have a flattened time hierarchy with 3 levels. Flattened with this procedure:
When graph is (text-mode compliant :)...

           X (year)
          / \
         /   \
        P     Q (halfs)
       / \     \
      /   \     \
     A     B     C (lowest level)

...HIER table rows look like this:

Lvl1, Lvl2, Lvl3

X     P     A
X     P     B
X     Q     C

FACT table is joined with HIER (WHERE TIME_ID = Lvl3) Then ROLLUP(Lvl1, Lvl2, Lvl3) is used for full aggregation on time dimension.
[The lowest level (Lvl3) might be skipped and gotten from FACT table - it's written here for clarity.]

I'd like to know your opinions about the best way to tune the performance of this query - or even rewrite it to leverage other Oracle features (e.g. Dimension objects).
Fact table needs to be FULLy ACCESSed, but when GROUPing, there's sorting needed as far as I know, so what about bitmap indexes? Or concatenated bitmap index?
Then PARALLEL - will Oracle use it's advantages here? Would partitioning help?

Thanks,
Pawel Received on Sun Oct 16 2005 - 21:37:40 CDT