MINIMIZE RECORD_PER_BLOCK
The basic idea of this article is to store a single row into a single block.
This could be useful to avoid the hot block contention for the small look up tables, which access heavily.
The MINIMIZE RECORDS_PER_BLOCK features comes from Oracle 8i, which gives the
facility to almost store a single record into a single block.
Test case:
Normal Heap Table (without MINIMIZE RECORDS_PER_BLOCK)
17:26:39 jaffar@PRIMEDB> create table nomin1 as select * from user_objects where rownum = 1;
Table created.
Elapsed: 00:00:00.01
17:27:18 jaffar@PRIMEDB> analyze table nomin1 compute statistics;
Table analyzed.
Elapsed: 00:00:00.00
17:27:33 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'NOMIN1';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
NOMIN1 1 4
17:28:22 jaffar@PRIMEDB> insert into nomin1 select * from user_objects;
242 rows created.
Elapsed: 00:00:00.00
17:28:34 jaffar@PRIMEDB> commit;
Commit complete.
Elapsed: 00:00:00.00
17:28:35 jaffar@PRIMEDB> analyze table nomin1 compute statistics;
Table analyzed.
Elapsed: 00:00:00.00
17:28:39 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'NOM
IN1';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
NOMIN1 243 8
Note : Total rows comes in 8 blocks.
Test case with MINIMIZE RECORD_PER_BLOCK
17:28:47 jaffar@PRIMEDB> create table min01 as select * from user_objects where rownum = 1;
Table created.
Elapsed: 00:00:00.00
17:29:12 jaffar@PRIMEDB> analyze table min01 compute statistics;
Table analyzed.
Elapsed: 00:00:00.00
17:29:22 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'MIN01';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
MIN01 1 4
17:32:26 jaffar@PRIMEDB> alter table min01 minimize records_per_block;
Table altered.
17:32:53 jaffar@PRIMEDB> insert into min01 select * from user_objects;
243 rows created.
Elapsed: 00:00:00.09
17:33:12 jaffar@PRIMEDB> commit;
Commit complete.
Elapsed: 00:00:00.00
17:33:12 jaffar@PRIMEDB> analyze table min01 compute statistics;
Table analyzed.
Elapsed: 00:00:00.00
17:33:14 jaffar@PRIMEDB>
17:33:14 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'MIN0
1';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
MIN01 244 180
Note : Total rows 243 comes in 180 blocks.
References:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76962/ch2.htm#103385
Sources Used in the test cases:
Orcle Version : 9.2.0.7
db_block_size : 8194
Tablespace : LMT + Auto Extent Size.
