We're looking at a large database that will be reorganized to use more Type II storage areas than it currently is using, and the question has come up whether it would be simpler to just define a separate area for each table and index than to decide which tables are good to put in the same area with each other.
Is there any reason to avoid giving each table and index its own area?
Unless your database has a very small number of tables and indexes you would end up with a lot of storage areas and extents. Which would be a bit of a hassle to manage. It would also use a lot more file handles at the kernel level which /could/ be a problem on some operating systems.
It really isn't *that* hard to pick your top X tables and split them out. It's usually pretty obvious if you just spend a few minutes scanning dbanalys output.
Unless your database has a very small number of tables and indexes you would end up with a lot of storage areas and extents. Which would be a bit of a hassle to manage. It would also use a lot more file handles at the kernel level which /could/ be a problem on some operating systems.
It really isn't *that* hard to pick your top X tables and split them out. It's usually pretty obvious if you just spend a few minutes scanning dbanalys output.
In my opinion, it would be "simpler" to have fewer areas, not more. I wouldn't enjoy reading a dbanalys report in a 500-table DB with 1000+ areas. And I second all of Tom's points. Create separate areas only when they're needed for practical purposes.
Since opportunities to restructure large databases tend not to come along very often, it's worth asking about things you can only address via dump and load. Are you using optimal DB block size, area RPBs, and cluster sizes? Are there unresolved application performance issues?
Thanks Tom and Rob for your responses.
You're definitely right that we don't get a chance to restructure very often, which is one reason the question came up. If it's going to be 10 years before we get another chance to do this we want to err on the side of more areas than we truly need rather than fewer.
We haven't had any difficulty finding tables and indexes that we are sure we need to separate. And we do have a number of tables we are sure will never be big enough or active enough to require their own areas.
When we start looking at tables that don't need a separate area now but would if the company using the database had twice as many users as it currently does, that is the main driver of the "can we future-proof our structure better than we did last time we got to dump-and-load" question.
If this restructuring is a once in 10 years event - Mike F (I think) had a situation where a company was down and they to do scans on extents to find the problem. Because the db used large extents it took 3 hours per iteration vs less time it would have taken if the extents were smaller.
Grist for the mill.
In terms of future-proofing, having historical data is a huge boon. Set up a recurring monthly dbanalys, then parse the data and put it in a database. Look at the trends. Then, if you want to be cautious, you can either tablemove/idxmove or D&L selectively to get your fast-growing tables and their indexes into separate areas before they become huge and limit your restructuring options.
Yes, in the short term that will leave a bit of wasted space in the multi-object areas they came from, but (a) disk space is cheap and (b) as those tables and indexes in the multi-object areas grow, you'll use up that space anyway.
Thanks to all for the input. We will use all of that advice.
We do have that historical data to work from, which is how we know that we'll have some tables get large enough to need to be in their own area later. Since some of the customers that use the database in question are replicating to a SQL Server database via a tool that relies on the Progress rowids to coordinate the activity between the two databases, we don't get to do things that change the rowids of existing records very often, so we will probably end with more areas than we need today, but not an area for every table and index.
Thank you very much.