Table 4-2 lists and describes the statuses that are assigned to segments.

Table 4-2 Segment statuses

Segment status	Description
Used segment*	A segment in which table or index data is stored. A used segment that is completely filled with data such that no more can be added is called a full segment, and a used segment from which data has been deleted so that only free pages remain (used free pages or unused pages) is called a used free segment.
Unused segment	A segment that is not being used. This segment can be used by any table or index in the RDAREA.
Free segment	A segment in which no data is stored. Both used free segments and unused segments are called free segments.

^* A used segment can only be used by a table or an index that has stored data in that segment. Other tables or indexes cannot use the segment.

Segments must be designed with care, because the segment size (number of pages per segment) affects I/O times and disk space requirements. The recommended segment size for most purposes is 10-20 pages. For details about segment design, see the HiRDB Version 8 Installation and Design Guide.

When you define a table, you can specify the ratio of free pages in a segment. This concept is illustrated in Figure 4-2.

Figure 4-2 Ratio of free pages in a segment

Explanation

• In this example, the segment free page ratio is set to 30%. Therefore, three out of every 10 pages are free pages.
• The segment free page ratio is specified with the PCTFREE option of the CREATE TABLE. This ratio can be specified in the 0-50% range; the default is 10%.
• When data is loaded (including when reloading and reorganizing), it is not stored in the free pages specified here.

The data storage efficiency improves as the segment free page ratio becomes smaller.

Increasing the segment free page ratio sometimes improves performance. For example, when data is added to a table for which a cluster key is defined and a segment free page ratio has been set, you can cause data to be stored in positions that are near a cluster key value, which minimizes the number of data I/O operations that will be needed.

For details about determining the segment free page ratio, see the HiRDB Version 8 Installation and Design Guide.

Segments are not allocated when the table is defined. Instead, segments are allocated as needed when data is saved to the table. Once a segment is allocated (once it becomes a used segment), no other table or index can use that segment until it is released. Consequently, the RDAREA may run out of free space if data is added and deleted repeatedly, even though the amount of data has not increased. To prevent this from occurring, periodically perform the following operations to release segments:

• Reorganize tables or indexes using the database reorganization utility (pdrorg command).
• Release used free segments using the free page release utility (pdreclaim command).

For details about reorganizing tables and indexes, and about releasing used free segments, see the HiRDB Version 8 System Operation Guide. In addition to these operations, the following operations also release segments:

• Executing the PURGE TABLE statement
• Reinitializing the RDAREA
• Deleting the table definition
• Deleting the index definition
• Loading data in the creation mode (-d option specification)

The free space reuse facility enables you to efficiently utilize free space made available by deleting data.

(a) How searching is performed when data is saved

When data is saved to a table, there are two page search modes that are used to search for a storage area:

• New page allocate mode
  In this mode, when the last page in a used segment becomes full, a new unused segment is allocated. If the RDAREA runs out of unused pages, a search is then conducted to find free space in a used page, starting at the beginning of the first used segment. Once free space is found, the data is saved to that free space.
  
• Free page reuse mode
  In this mode, when the last page in a used segment becomes full, a search is conducted to find free space in used pages starting in the first used segment. If no free space is found, an unused segment is allocated. The next search start position is also remembered, so that, the next time, searching for free space begins at this position.
  

(b) Overview of the free space reuse facility

The free space reuse facility is designed to enable the free space in used pages to be utilized by activating the free page reuse mode. This occurs when the number of used segments in a table reaches the number of segments specified by the user, and the last segment becomes full. Once the free space in every segment of the specified number of segments runs out, the free space reuse facility activates the new page allocate mode, and a new unused segment is allocated. If the number of segments is not specified, free space is not reused until there are no more unused pages in the RDAREA. If the free space reuse facility is not used, the system searches for free space from the beginning of the used segment every time a search is performed. If the free space reuse facility is used, the free page reuse mode is activated, the next search start position is remembered, and the next search is performed from there. As such, use of this facility enables searches to be performed more efficiently. Figure 4-3 provides an overview of the free space reuse facility.

Figure 4-3 Overview of the free space reuse facility

Explanation

• If the free space reuse facility is not used
  Every time data is to be inserted after the RDAREA has run out of unused pages, the system searches for free space in a used page, starting at the beginning of the first used segment. Once the space is found, the data is saved to that free space.
  
• If the free space utility is used (with the number of segments specified)
  When an attempt is made to insert data into a table after the specified number of segments is reached, this facility searches for free space in a used page, starting at the beginning of the first used segment. Once the space is found, it saves the data to that free space. In addition, the facility remembers that location as the next search start position and, the next time, begins searching for free space from this position.
  
• If the free space utility is used (without the number of segments specified)
  When an attempt is made to insert data after the RDAREA has run out of unused pages, this facility searches for free space in a used page, starting at the beginning of the first used segment. Once the space is found, it saves the data to that free space. In addition, the facility remembers that location as the next search start position and, the next time, begins searching for free space from this position.
  

For details about the free space reuse utility, see the HiRDB Version 8 System Operation Guide.

(c) Application criteria

For operations requiring frequent reorganization, use the free space reuse facility to absolutely minimize the number of reorganizations you need to perform, especially when repeated deletions and insertions of data are using up large numbers of segments.

(d) Environment settings

The following environment settings are used for the free space reuse facility. For details, see the HiRDB Version 8 Installation and Design Guide.

1. In the pd_assurance_table_no operand, specify the number of tables that can use the free space reuse facility.
2. Specify the number of segments in the SEGMENT REUSE option of the definition SQL CREATE TABLE statement. For tables that have already been created, specify this value in the SEGMENT REUSE option of the ALTER TABLE statement.

(e) Notes

The free space reuse facility does not operate in the following cases:

• When data is being stored with the hash facility for hash row partitioning
• When a data dictionary table is being stored
• When data is being stored using the data load or database reorganization utility (pdrorg)