Here find some considerations, insights into and benefits of using Type II storage areas for indexes in an OpenEdge database.
Adam:
"There are fewer benefits for indexes than for records, but they should still be in type II.
The ability to delete the index with a single update is one of the most notable.
When doing an index rebuild, the index can be deleted by updating the first block to put it on the free chain, and all the others will follow without the need to visit each block.
Indexes do not tend to be very large, so higher blocks per cluster values would waste space on small indexes but using blocks per cluster of 8 fits for the vast majority of indexes without wasting loads of blocks on very small indexes.”
Tom:
"Rows per block = 1 for index areas is a matter of opinion. Some people dislike it because it means that you will very likely waste a lot of disk space if you mistakenly assign a table to an index area. Other people see that as a feature ;)
Blocks per cluster = 1 = type 1 area. For indexes that doesn’t matter nearly so much as it does for data because indexes never share a block. So for small indexes you would not “waste” blocks between 1 block and 8 (8 is the minimum cluster for a type 2 area). If you have a small database with lots of small indexes that could add up - although, these days even if it adds up it still isn’t much.
Performance impact would mostly occur when an index is allocating new blocks (i.e. during a “block split”). That would occur less often with a type 2 area and would be most significant for large indexes. It would be most likely to happen at elevated levels shortly after a D&L or after an IDXCOMPACT.
During normal OLTP operations it shouldn’t matter at all. But lack of harm isn’t a good reason to do it ;)
Having said all of that - all user objects should be in a type 2 area."
Rob:
"As Tom points out, there is currently no such thing as an index area, in terms of a platform guarantee that an area can only contain objects of a given type, e.g. only tables, or only indexes. So the price of having an index-only area is eternal vigilance: if you have an area with RPB 1 and it only contains indexes, that doesn’t affect the content of those index blocks. But if you mistakenly put a table in there, it’s a whole different (and bad) ballgame.
I think it’s very important in such discussions to provide context. Area type (I vs. II) matters and OE release matters. You can’t make blanket statements about RPB 1 being good or bad without that context. RPB doesn’t affect index block layout but it does significantly affect maximum area size when the area’s address size is 32 bits. In the worst case (RPB 256) a 32-bit area can only have up to 2^23 blocks. With RPB 1, it can have up to 2^31 blocks.
RPB has no effect on maximum area size when the address size is 64 bits.
64-bit area:
Cluster size 8 or higher (i.e. Type II), and
DB was opened with OE 10.1B or later
32-bit area:
Any area that does not satisfy both conditions for 64-bit
Obviously RPB choice can only be made for new areas, not for existing ones. My thoughts for choosing RPB for a new index-only area are:
If you are using OE 10.1B or later, make the area Type II and set a high RPB, e.g. 128 or 256. This RPB choice is just for the scenario where someone accidentally assigns a table to the area. You wouldn’t want to have RPB 1 in this case.
If you are on v9 and can’t create a Type II area, use RPB 1. This maximizes the number of block-addressing bits, and therefore the maximum number of available dbkeys and addressable blocks (i.e. maximum area size).
If you are on 10.0A – 10.1A, you can create Type II areas but you still have 32-bit addresses. In this case, for index areas, I would use RPB 1, unless an upgrade to a later OE release is coming soon."