THE PHYSICAL DESIGN PROCESS - Database Design and Development: An Essential Guide for IT Professionals - page 356

parameter are the same. Let us examine these parameters and understand how
they work.
Block Percent Free This parameter sets the limits on how much space is reserved
in each block for expansion of records during updates. When a record has variable
length fields, it is likely to expand when transactions update such fields. Also, a
record is likely to expand when null values of attributes are replaced with full data
values.
Let us take a specific example of this block usage parameter: PCTFREE 20
This means that 20% of each block is left empty for the records in the block to
expand into when updates to the records take place. Assume a file with records
having many variable length fields subject to many frequent updates. Then you
should allocate enough empty space in each block for updated records to occupy.
The Block Percent Free parameter, in such a case, must be set at a high level to
allow a larger percentage of each block to be left empty. However, if you set the
parameter at too high a level, large chunks of blocks may be reserved unnecessar-
ily and wasted.
Block Percent Used Block management by the DBMS involves keeping track
of blocks ready and available for insertion of new records. DBMS can do this effi-
ciently only if it marks a block as available for new records only when the block
is fairly empty. This reduces the overhead for managing too many “free” blocks.
The Block Percent Used parameter sets an indicator when a block is ready and
available for new records.
Take a particular example of this block usage parameter: PCTUSED 40
What is the meaning of this parameter? It is like a watermark below which the
usage of a block must dip before new records are allowed to be inserted in the
block. Only when space used in a block falls below 40%, can new records be
accepted in the block. Obviously, deletion of records keeps changing the actual per-
centage of the block being used at a given time. If, at a given time, the percentage
of the block used is 41%, leaving 59% free, new records are still not allowed to be
inserted into the block. If a record deletion causes the percent block used to go
down to 39%, new records are allowed to be inserted into the block. If a file is fairly
volatile, with many deletions and insertions of records, then set this parameter as
low as possible.
File Organization
Go back to Figure 12-5 showing how a physical file contains data records. A file is
an arrangement of records, and these records are spread over the storage blocks
allocated to that file. Data records reside in the database as records arranged in files.
Before proceeding further about how records may be arranged in a file, let us review
the major operations on records stored in a database.
Here are the main types of operations:
Insert. Add a record to the database. Identify the file and the block within the file,
and insert a record in the block.