15.3.1 Determining the page length

Organization of this subsection
(1) Considerations in determining the page length
(2) Specification
(3) Notes on determining the page length

(1) Considerations in determining the page length

The considerations that should be taken into account in determining the page length are discussed as follows.

  1. A large page size should be used for an RDAREA when a table or index satisfying the following conditions is to be stored by an application that retrieves or updates all entries or a large amount of data:
    • RDAREA stores tables that do not have indexes
    • RDAREA stores tables with a cluster key specified and their indexes
    • RDAREA stores indexes used for range condition retrieval or updating of a large amount of data
  2. The page length should be set on the basis of the row length of the tables stored in the RDAREA so that invalid space can be eliminated as much as possible:
    Page length - length of control information section [Figure] number of rows that can be stored per page.
  3. The following formula should be used as a guideline to setting the percentage of unused space in a page:
    (Page length [Figure] percentage of unused space in a page) [Figure] 100 - row length [Figure] number of rows that can be stored in unused space in a page.
    A meaningless value that does not allow even one row to be stored in the unused space in a page should not be specified.
  4. For a page used to store an index, approximately 4,096 to 8,192 bytes is an appropriate size in terms of input/output efficiency.
  5. If a column's data type is VARCHAR, NVARCHAR, or MVARCHAR and its definition length is at least 256 bytes, its data will be branched onto another page. If there is variable-length character string data with a length of at least 256 bytes, the page length should be set to the smallest value that is at least the average length of the data.
  6. In the case of a column whose data type is VARCHAR, NVARCHAR, or MVARCHAR, if a row of null values is inserted by the INSERT statement, the column's data may be branched onto another page depending on the length of the updated data when the UPDATE statement is used subsequently to update the null-value data to real data. If character string data is often set initially to the null value and then updated later to real data, the page length should be determined taking into account the length of the updated rows.
  7. HiRDB allows locking control in units of pages or rows. If row-level locking control is to be used, the page length should be set on the basis of the row length so that as many rows as possible can be stored per page. The following should be taken into account in this case:
    • Minimize the percentage of unused space in a page.
    • Define the page length to minimize the global buffer lock-release waits count for page input/output requests. In the case of a frequently updated table, small pages should be used; otherwise, the lock-release waits count may increase.
    • Define the page length to lower the page input/output waits count becomes low for the number of page input/output requests. If the application uses mainly random accesses, the page length should be small; otherwise, the actual input/output units become too large for the row length, which is the access unit, resulting in unneeded data transfers.
    If the UPDATE statement is used frequently to update data in a column whose data type is VARCHAR, NVARCHAR, or MVARCHAR and this updating results in a change in the row length, the percentage of unused space in a page should be set to a slightly higher value when the table is defined. For details about how to set the percentage of unused space in a page, see 15.3.2 Setting the percentage of unused space in a page.

(2) Specification

To specify a page length, use the create rdarea statement of the database initialization utility (pdinit) or database structure modification utility (pdmod).

(3) Notes on determining the page length

An error results when a row is added to a table and as a result the actual row length exceeds the page length (except in the case of columns whose data type is VARCHAR, NVARCHAR, or MVARCHAR). The actual row length is obtained using the formula for required disk space that is provided in 17. Determining RDAREA Size. If the obtained row length is greater than the page length of the user RDAREA to be used, the user RDAREA must be reinitialized and then the page size must be redefined. The database structure modification utility (pdmod) is used to reinitialize RDAREAs. For details about how to reinitialize RDAREAs, see the manual HiRDB Version 8 System Operation Guide.