How to Choose a Storage Area for a Table

Where should you put that new table and its associated indexes and LOBs? Let's figure it out together!Before we start, let's make sure you are only using type 2 storage areas (SAII) in your database!

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. Therefore, 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 but often 4K on Windows). The block header consumes about 150 block 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 bytes – 150-byte header) / 100 bytes mean record size ~= 80 records fit per block.

Using an RPB of 64 would leave empty, unused space in the block: 64 records X 100 bytes = 6400 bytes out of the roughly 8000 bytes available. That’s 20% empty space in the storage area!

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. Since I have not yet encountered a database with 11 quadrillion records in one storage area (at 100 bytes per record, the storage area would be 1 Exabyte, or 1 million trillion bytes, in size), I think that it is safe to waste these ROWIDs.

Note: the RPB setting only applies to table objects. 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 that is stored in an 8K block size database in an SAII with BPC=512, the DBMS will format 512 X 8K = 4 MB of space per cluster.

Generally speaking, a database object whose total size is greater than the max cluster size (512 X DB block size) should be assigned to an SAII with BPC=512.

Selecting a Type II Storage Area

First things first: a type II storage area should only contain one type of object: either table, index or LOB. Why? That's an entire blog post in itself! For now, trust me please.

Tables

SAIIs for tables are separated 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 in the RPB section above.

2. Shared areas, by RPB: All other tables with data should go into one or more “misc###_dat” storage areas, where the “###” 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 records X 125 bytes = 8000 bytes, filling the 8K block.

3. Unused tables: Tables with no data should be placed in an “unused_dat” storage area. If later they begin to accumulate data, use the “proutil db ‐C tablemove” command 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 is handy when 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, while “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.

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.

In case of Error

If, for whatever reason, you determine that a table or index is no longer in the correct storage area, the object can be moved using either the proutil tablemove/indexmove command or a dump&load. The former is only used for smallish tables as it is a record‐level operation. Make sure to test before running in production.

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

Conclusion

Done right, a database with objects placed in the appropriate storage areas will be faster, more efficient and easier to manage than a misconfigured database. 

Do you configure SAII's differently?  Let me know in the comments.