When a large amount of table data is deleted by a batch job or some other operation, some of the pages in which that table data is being stored (data pages) may become used free pages. Similarly, when an index is defined, some of the pages in which index key values are being stored (index pages) also become used free pages. By executing the free page release utility (pdreclaim), you can convert used free pages to unused pages, and reuse them. This is called releasing used free pages. Figure 7-15 provides an overview of releasing used free pages.

Figure 7-15 Releasing used free pages

Hint

• You cannot release used free pages of data stored in LOB RDAREAs.
• You cannot release used free pages of plug-in indexes.

For details about releasing used free pages, see the HiRDB Version 8 System Operation Guide.

(a) Benefits gained from releasing used free pages of tables

Table 7-4 lists and describes the benefits gained from releasing used free pages of a table.

Table 7-4 Benefits gained from releasing used free pages of a table

Benefit	Description	Degree of benefit
Ability to increase the table reorganization cycle	The ability to reuse used free pages improves data storage efficiency. In turn, this can increase (improve) the table reorganization time cycle.	Good
Improvement in performance when searching large data sets	Because they are a type of used page, used free pages are searched. However, searching is not performed on unused pages (they are skipped by the search). Thus, converting to unused pages improves the search performance in direct proportion to the ratio converted. The benefits are particularly evident when large data sets are searched.	Varies
Improvement in performance when INSERT and UPDATE are used.	If sufficient contiguous free space cannot be allocated when an attempt is made to save data to a used page, HiRDB performs a process called page compaction. Page compaction refers to a process whereby data in the affected page is repacked to secure sufficient contiguous free space in which to store the new data. When free used pages are released, page compaction is also performed at the same time. This eliminates the need to perform page compaction which prolongs INSERT and DELETE processing and improves performance by the corresponding amount. The pages on which page compaction is performed are used pages other than full pages and used free pages.	Varies
Potential reduction in errors when INSERT and UPDATE are performed on branch rows.	If you execute INSERT or UPDATE on a branch row when there are no unused pages, an error occurs (KFPA11756-E message). The increase in unused pages due to the release of used free pages tends to reduce the frequency of this error.	Varies

Legend:

Good: Always beneficial.

Varies: The degree of benefit varies depending on conditions.

(b) Benefits gained from releasing used free pages of indexes

Table 7-5 lists and describes the benefits gained from releasing used free space of an index.

Table 7-5 Benefits gained from releasing used free space of an index

Benefit	Description	Degree of benefit
Potential reduction of insufficient capacity in RDAREAs storing indexes	If capacity runs out even though free pages (used free pages) exist, release the used free pages. Note that this tends to reduce the occurrence of insufficient capacity in RDAREAs that store indexes with key values that are frequently updated or deleted.	Great
Ability to increase the index reorganization cycle	The ability to reuse used free pages improves data storage efficiency. In turn, this can increase (improve) the index reorganization time cycle.	Good
Improvement in performance when searching large data sets that use indexes	Because they are a type of used page, used free pages are searched. However, searching is not performed on unused pages (they are skipped by the search). Thus, converting to unused pages improves the search performance in direct proportion to the ratio converted. The benefits are particularly evident when large data sets are searched.	Varies

Legend:

Great: Particularly beneficial.

Good: Always beneficial.

Varies: The degree of benefit varies depending on conditions.

Using this utility is particularly beneficial when deleted key values are not re-registered. Because used free pages are reused when the same key value is added or deleted repeatedly, large numbers of used free pages do not appear. However, for indexes that are defined for rows that increase or decrease by a fixed number (such as date, sequence number, and so on), and if past data is deleted in order with the increases in data, a large number of used free pages that are not reused appear in the first half of the index pages. Figure 7-16 shows the processing of a used free page being created for index pages.

Figure 7-16 Process of used free pages being created for index pages

Note that, after the used free pages are released, the key values are stored in the released pages, which improves data storage efficiency.

From the standpoint of performance and data storage efficiency, reorganizing tables or indexes is superior to releasing used free pages. However, while releasing used free pages, you can still access the tables or indexes on which the utility is operating. With reorganization, you cannot access the tables or indexes on which the utility is operating. This means that you do not need to interrupt normal operations when releasing free pages.

Use the execution results of the database condition analysis utility to evaluate whether to reorganize tables or indexes, or to release used free pages. The following lists the evaluation criteria:

• If there is a large number of used free pages, release the used free pages.
• If the page utilization ratio of the used pages differs significantly from the segment free page ratio (value of the PCTFREE operand of CREATE TABLE), perform reorganization.