PCTFREE vs. minimize_records_per_block
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
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
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.