Tablespaces for DW Dimensions

[PAVB]

Fair enough

[paulb]

You might go further than PAVB's suggestion and use multiple tablespaces per fact table, based on placing one or more partitions into each TS. This allows you to move partitions of a table between databases using "transportable tablespaces" (once you've exchanged the partitions out to a different table blah blah blah). You'd do well to mirror that on the index side, or place the index partitions in the same TS as their table partitions, to make things easier.

Dave, yes, our fact table is daily partitioned, sitting on separate "monthly" tablespaces (we archive data monthly, so this strategy made sense). Indexes are in the same TS as the partition. Definitely makes life easier.

There's some caveats on that for materialized views, of course, and you have to jump through some FK hoops.

That brings up an interesting side point. We currently set up our materialized views to be aggregations of just the fact table (grouping on the FK fields without joining), still needing to join to dimensions at query/reporting time. We've been designing this way in order to save on storage, but we have not yet tested query rewrite (still in design) ... do you see any issues with this - you think we'll still get query rewrite and good performance? I'm guessing so but just haven't tested yet. I wonder if the performance benefit in pre-joining justifies the higher cost in storage (not a lot of budget here).

Thanks.

[slimdave]

That brings up an interesting side point. We currently set up our materialized views to be aggregations of just the fact table (grouping on the FK fields without joining), still needing to join to dimensions at query/reporting time. We've been designing this way in order to save on storage, but we have not yet tested query rewrite (still in design) ... do you see any issues with this - you think we'll still get query rewrite and good performance?

That's also what I've been doing There's obviously a performance trade-off but the real issue I think is the work to be done in aggregating the data, not in joining it to the dim table (hash joins being pretty zippy). Make sure you have all the constraints and DIMENSION declarations in place and you should be good to go.