Tablespaces for DW Dimensions

Hello folks,
I had a couple of questions on setting up tablespaces for our DW (single instance 10gR2 EE).

1. We'll have, say, 10 dimension tables (some small, some rather large - maybe into millions of long rows). Is there any performance benefit in creating multiple tablespaces for these dimension tables? In other words, what do we lose if we create all the dimensions on a single, bigfile tablespace? Assume the latest storage with proper RAID and I/O bandwidth. If we should be using multiple tablespaces, what's the recommendation - 1 tablespace per dimension table? Some other ratio?

2. I keep seeing discussions on setting extents for fact table tablespaces to be uniform instead of auto. What's the benefit there? I'm wondering if it's that big of a performance gain (can't see one via my tests so far).

3. OK, not a tablespace question, but ... what do most people do with indexes when loading dimension tables? Our loads happen throughout the day (hourly) and obviously so do user queries - doesn't seem to make sense for us to drop indexes during loads. Is it best to just rebuild all bitmap indexes each day (during the night) to clean them up?

Thanks.

I assume you are going to use ASM, aren't you?

1- For the large DIM tables one tablespace for each one of them. All small DIM tables in a single generic tablespace.

2- Auto works just fine in our shop.

3- Since your business requirements call for running ETL during business hours you just can't drop your indexes(*)
What you can and should do is setup a process at the end of your ETL checking how large your bitmap indexes got, when they reach some threshold like twice the normal size just rebuild them, only the offending ones, much better if you can do this in a partition by partition basis.
As per rebuilding all of them during the night... depending on DW size and number of bitmap indexes you might find the night is not long enough to rebuild all of them.

(*) Just as a side note, if you are planing to resort to bitmap-join indexes you have to drop/create them

Just my two cents.

Thanks - good suggestions on the tablespaces.

Yes, we're using ASM.

for 3, how do you know what "the normal size" is? I guess we won't until we have enough data flowing in ... just wondering if there was a "magical" calculation ... it's a smart idea vs doing all of them at night.

About the size of your bitmap indexes... try and error, you will have to fine tune it over time.

I would start by creating the indexes and look how big they are, this is your initial "normal size". We actually have a table keeping that data for us, a simple query populates it when the index or index-partition gets created.

PAVB, seems u have good practical experience with that. Can u please tell us what is the benefit of having each large dimension in separate TS? Just it's not obvious for me

Thanks

Background Information
- Ora9i
- Raw devices based storage
- Some DIM tables are large, in excess of 250 million rows
- A couple of DIM tables are very large, in excess of one billion rows
- All large and very large DIM tables are range partitioned
- Each large DIM table is sitting in its own private tablespace
- Each very large DIM table is sitting in its own private tablespace/s
- Small DIM tables are grouped in a couple of generic DIM tablespaces

Benefits of Having Private Tablespaces for Large and Very Large DIM Tables
- Allows for more control over physical I/O planning, monitoring & remediation
- Allows for physical vs. logical I/O crosscheck monitoring
- Allows for more control on backup/restore strategy
- Allows for specific blocksize leading to private/semiprivate buffer caching
- It looks cleaner, nicer, more elegant :)

Just my experience and what works for me

10x PAVB,
sounds reasonable, but it's good to have that here, since u understand that in many environments these characteristics will not be considered as an advantage

Regards

Is it a question or a statement?

I'll love to see a Data Warehouse environment where these characteristics will not be considered as an advantage.

Quote:

---

Originally Posted by pbarmak

1. We'll have, say, 10 dimension tables (some small, some rather large - maybe into millions of long rows). Is there any performance benefit in creating multiple tablespaces for these dimension tables? In other words, what do we lose if we create all the dimensions on a single, bigfile tablespace? Assume the latest storage with proper RAID and I/O bandwidth. If we should be using multiple tablespaces, what's the recommendation - 1 tablespace per dimension table? Some other ratio?

---

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.

In a DW the use of tablespaces is much more dynamic than in OLTP systems, and we tend to create and drop them as part of regular ETL work, not through some big DBA-led design process.

Quote:

---

Originally Posted by pbarmak

2. I keep seeing discussions on setting extents for fact table tablespaces to be uniform instead of auto. What's the benefit there? I'm wondering if it's that big of a performance gain (can't see one via my tests so far).

---

There's a marginal performance disadvantage from having some extents smaller than the multiblock read size, but it really is marginal. I like my multiblock reads to all be the same size if possible as you don't end up chasing down the odd strange number in a wait event analysis.

Quote:

---

3. OK, not a tablespace question, but ... what do most people do with indexes when loading dimension tables? Our loads happen throughout the day (hourly) and obviously so do user queries - doesn't seem to make sense for us to drop indexes during loads. Is it best to just rebuild all bitmap indexes each day (during the night) to clean them up?

---

If your large dim table is hash partitioned to allow partition-wise joins with a composite range-hash partitioned fact table then you can load your dimension through partition exchanges, having loaded the new data to table with bitmap indexes disabled. Even if the dim table is not partitioned you can still load to a table with a single partition and use partition exchange to swap the data segment with the "live" nonpartitioned dimension table. There's some caveats on that for materialized views, of course, and you have to jump through some FK hoops.

PAVB,
1st I said that sounds good, so I do not say it's wrong or anything, just if u have a storage array, u do not really have a control over where the files are placed and u delegated that to the storage
2nd, about the backup strategy, it depends on the strategy if u will get some advantage on that or not.
So, again, I like this approach, just I I think it's better to say we do so, because we have that and that and that.
In the real world, the thinks rarely are just black and white

Fair enough

Quote:

---

Originally Posted by slimdave

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.

Quote:

---

Originally Posted by slimdave

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.

Quote:

---

Originally Posted by paulb

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.