Limiting rows per block in Oracle database table – setting Hakan factor
This is something I learned today by accident
There are times when you need to optimize physical data arrangement in database, in this case in a database table. The reasons for this vary from row migration to concurrency problems.
Here is one way to limit rows inside database table to a set value.
You must understand this is may not be the best way to accomplish this.
Of course this can be done also by setting PCTFREE to a high value, but that is not as exact science as this.
Here goes:
create table test(field number);
insert into test values(1);
commit;
alter table test minimize records_per_block;
–From this point on, number of rows per block will be limited to 1.
I learned this from Mark J. Bobak from Oracle forums.
Here are his words:
“The alter table … minimize records_per_block;
functionality sets the Hakan factor. This limits the number of rows per block to the max of the largest number of rows in any block in the table. This functionality was originally conceived to help optimize builds of bitmap indexes, but it can be used anytime you want to ‘artificially’ limit the number of rows per block.”
Tags: Hakan factor, Oracle, Rows per block