PCTFREE vs. minimize_records_per_block Don Burleson

Many Oracle systems store rows as small and then expand them with SQL UPDATE statements causing the rows to fragment into multiple blocks.

To prevent the inevitable row migration that results from extreme row size expansion, I set PCTFREE to a high value whenever rows are stored small, and later expanded.

For example, I had a lab system that stored row "stubs" at 40-bytes and then updated a VARCHAR2 column, causing the avg_row_len to be over 500 bytes and the value of chain_cnt to become huge. During the table reorganization, I re-set PCTFREE to un-link the block from the freelist after only 200 bytes was stored. That way, the rows could expand without migrating to other blocks.

But there is another method, using a "minimize records_per_block" feature.  With this approach you do these steps:

1 - Find-out the number of rows you want to have in a block at full expansion, say 5 rows/block.

2 - You insert that number of records in that table (e.g 5)

3 - Then issue "alter table xxx minimize_records_per_block;"

4 - Then, delete those 5 rows and then load your table
 

You will then only get that many rows in a block and your rows can then grow without migrating.  This approach is useful if you know exactly how big your rows are going to be.

However, the Oracle documentation does not talk about minimize_records_per_block usage for this purpose::

Use SQL statements with the ALTER TABLE syntax to optimize the mapping of bitmaps to rowids. The MINIMIZE RECORDS_PER_BLOCK clause enables this optimization and the NOMINIMIZE RECORDS_PER_BLOCK clause disables it.

When enabled, Oracle scans the table and determines the maximum number of records in any block and restricts this table to this maximum number. This enables bitmap indexes to allocate fewer bits per block and results in smaller bitmap indexes. The block and record allocation restrictions this statement places on the table are only beneficial to bitmap indexes.

Therefore, Oracle does not recommend using this mapping on tables that are not heavily indexed with bitmap indexes.