Question: I have some read-only tables and I
want to tightly pack the rows as dense as possible onto the data
blocks in an optimal way. There will be no updates, deletes or
inserts. How to you minimize space wastage and ensure fast
retrieval via an index?
Answer: There are several factors that
govern free space within a table and a tablespace:
- PCTFREE: This is the percentage of
space to leave free default (20%) for subsequent inserts or
updates. To pack the data tightly, set
PCTFREE to a
small value (e.g. 1).
- NEXT extent size: A smaller NEXT extent
size will minimize wastage.
To pack space tight, use these techniques:
STEP 1 - Load the table, adjusting PCTFREE and
NEXT to minimize wastage.
STEP 2 - Reorganize the table (using
CTAS with order by) into a fresh tablespace.
Re-sequencing the rows into the same order as the
most-frequently-used index. This reduces run-time I/O by
fetching related rows in a single block read
The reorg table will have these features:
- Use
insert append in the CTAS to always use a fresh empty block.
- Use table compression
- Make sure that there are no
chained rows
- Re-set INITIAL to fit the whole table into one
extent
- Set PCTFREE to a small value, to un-link the
page only when it is very full.
To verify the dense row space packing you can run
tablespace
mapping scripts to see the free space on each block.
