How to Choose a Storage Area for a Table

Choosing the right storage area for a new table isn’t just a “nice‑to‑have” DBA detail—it’s one of the fastest ways to quietly waste space, fragment extents, and sabotage performance. Get it wrong and your database will be slower, harder to manage, and more fragile than it needs to be. In this post, I’ll walk you through exactly how to pick the right type 2 storage area (SAII), RPB, and BPC for your tables, indexes, and LOBs—so you can stop guessing and start tuning with confidence.

First: you must be using type 2 storage areas

If you’re still using anything other than type 2 storage areas (SAII), stop and fix that first. Everything that follows assumes SAII.

Type 2 storage areas differ from their predecessors because each physical database block contains data from only one object. To do this efficiently, two parameters must be specified when the SAII is created: Records per Block (RPB) and Blocks per Cluster (BPC).

RPB: Records per Block

A record’s ROWID is the physical address of the record within the database. When a storage area is created, each block is assigned a set number of ROWIDs based on the RPB parameter. Valid values are 1, 2, 4, 8, 16, 32, 64, 128, and 256.

Each database block is a fixed size (typically 8K, often 4K on Windows). The block header consumes about 150 bytes, leaving the rest of the space to store the database objects. Using a table’s mean size from the table analysis output (proutil db -C tabanalys), you can calculate how many records of a given table could fit in a DB block.

For example, if the DB block size is 8192 bytes and the mean record size is 100 bytes:

(8192−150)/100≈80 records per block.(8192−150)/100≈80 records per block.

An RPB of 64 would leave 20% of your block wasted:

64×100=6400 bytes out of roughly 8000 available.64×100=6400 bytes out of roughly 8000 available.

An RPB of 128 would effectively “waste” 48 ROWIDs per block. In this example, the maximum number of ROWIDs per SAII is 17.5 quadrillion—that’s a million billion—limiting this storage area to 11 quadrillion records. In practice, that’s safe to waste; I’ve yet to see a database with 11 quadrillion records in one storage area (at 100 bytes per record, that storage area would be 1 exabyte, or 1 million trillion bytes, in size).

Rule 1: tune RPB to your record size, not to some arbitrary “nice number.”
If your block is mostly empty, you’re wasting space. If your RPB is way too high, you’re wasting ROWIDs. Neither helps performance. 

Rule 2: RPB only matters for tables.
The setting is ignored for index and LOB objects. 

BPC: Blocks per Cluster

When a new object must be inserted into a block and no space is available, the DBMS must add space and assign it to the object. The number of blocks added and/or assigned to the object is based on the value of BPC. Valid values are 1 (type 1 storage area), 8, 64, and 512.

For example, if the DBMS must assign space to an object stored in an 8K block‑size database in an SAII with BPC=512, it will format:

512×8K=4 MB of space per cluster.512×8K=4 MB of space per cluster.

Rule 3: if an object’s total size exceeds the max cluster size, give it BPC=512.
That means any database object whose size is greater than 512×DB block size512×DB block size should be assigned to an SAII with BPC=512. 

Selecting a Type II Storage Area

Rule 4: one object type per SAII.
A data area holds only tables, an index area only indexes, a LOB area only LOBs. Mixing them is a recipe for confusion and bad performance. 

Tables

SAIIs for tables fall into three categories:

1. Dedicated to one table:
   If a table represents a significant portion of the database in size or activity, it should be placed in its own storage area. Often, this is the top 2–5 tables in the database.
   For these SAIIs, set BPC=512 and RPB based on the calculation above.

2. Shared areas, by RPB:
   All other tables with data should go into one or more “misc###_dat” storage areas, where “###” represents the calculated RPB.

   For example, a database with an 8K block size can fit about 125 records with a mean size of 64 bytes into an 8K block. Any table with a mean size of around 64 bytes or more should be assigned to “Data Area 128” (i.e., 128 ROWIDs are allocated per block). If the mean record size is about 125 bytes or more, put it in “Data Area 64” since:

   64×125=8000 bytes, filling the 8K block.64×125=8000 bytes, filling the 8K block.

3. Unused tables:
   Tables with no data should be placed in an “unused_dat” storage area. If they later begin to accumulate data, use:

   bash

   proutil db -C tablemove <table> <new_area>


   to move them to the appropriate “misc###_dat” storage area.

Indexes

Each table SAII should have its corresponding index SAII. All index SAIIs should have RPB=64 (this value is ignored, but it’s handy if a DBA accidentally drops a table into an index SAII). BPC should be set based on the size of the indexes:

• Most dedicated‑to‑one‑table index SAIIs will use BPC=512.

• “misc###_idx” index areas should use BPC=64.

• The area for indexes of unused tables (“unused_idx”) should use the smallest type 2 BPC of 8.

Rule 5: keep indexes and tables on separate SAIIs, and size the index BPC according to index size, not table size.

LOBs

All LOBs should be placed in a separate SAII. Depending on the size of the LOB data, you can choose to put all LOBs in the same SAII or multiple SAIIs. Similar to indexes, set RPB=64 and BPC=512.

Rule 6: LOBs belong in their own storage area.
Don’t let them pollute your table or index SAIIs. 

In case of error

If you realize a table or index is in the wrong storage area, don’t panic. You can move it using either:

• proutil tablemove/indexmove (only for smallish tables; it’s a record‑level operation), or

• a dump & load.

Rule 7: always test before running in production.
A storage‑area change that looks harmless in a sandbox can be catastrophic in a live database. 

ProTip: The commercial version of ProTop includes a df generator that applies this math to the data found in your most recent dbanalys. See the article on protop.help.com about Dump & Load Scripts (^d) - PAID for more information.

Final Word

Done right, a database with objects placed in the right storage areas is faster, more efficient, and far easier to manage. Done wrong, it’s just a slow, fragmented mess waiting to bite you.

How do you configure your SAIIs? If your approach looks nothing like this, I want to hear about it—let’s debate it in the comments.