Nonstop Database, HiRDB Version 9 Installation and Design Guide

[Contents][Index][Back][Next]

18.2.2 Size of a work table file used by a utility

If you create an index in batch mode, re-create an index, reorganize an index, or reorganize data using the rebalancing utility, you need the following size of work table file:

Formula
Size of a work table file used by a utility (bytes) = {A + B} [Figure] 2 [Figure] D} [Figure] C

A: Number of rows in the work table required for index creation 1

B: Number of rows in the work table required for index creation 2

C: Number of rows per work table page

D: Page length of work table

Notes
  • If you create multiple indexes in batch mode or re-create multiple indexes using one utility, obtain the size for the index with the longest index key.
  • If you execute batch index creation and re-creation concurrently, obtain the size of work table file for each operation and add the sizes.
  • If you execute multiple utilities concurrently, obtain the total of the sizes of the work table files calculated for each utility.
Organization of this subsection
(1) Obtaining the number of rows in the work table required for index creation 1
(2) Obtaining the number of rows in the work table required for index creation 2
(3) Obtaining the number of rows per work table page
(4) Obtaining the page length of a work table

(1) Obtaining the number of rows in the work table required for index creation 1

To obtain the number of rows in the work table required for index creation 1, use the following formula:

Formula
Number of rows in work table 1
= [Figure]c [Figure] {[Figure] [Figure]a [Figure] (100 - b) [Figure] 0.01[Figure] [Figure] (d + 22)[Figure] }[Figure]

a: Page size of a user RDAREA used to store the index

b: Percentage of unused area specified in the PCTFREE operand of the CREATE INDEX statement

c: Number of data items
For the index for repetition columns, use the sum of the elements of each row per repetition column among the index component columns.

d: Length of index key
For details about the length of the index key, see Table 16-5 List of index key lengths. Because the key lengths stored in the database are based on a 4-byte boundary, it becomes [Figure]key length [Figure] 4[Figure] [Figure] 4.
For multiple indexes, add the key lengths of all component columns on the basis of Table 16-5 List of index key lengths.

(2) Obtaining the number of rows in the work table required for index creation 2

To obtain the number of rows in the work table required for index creation 2, use the following formula:

Formula
Number of rows in work table 2
= [Figure]c [Figure] {[Figure] [Figure]a [Figure] (100 - b) [Figure] 0.01[Figure] [Figure] (d + 14)[Figure] }[Figure]

a: Page size of a user RDAREA used to store the index

b: Percentage of unused area specified in the PCTFREE operand of the CREATE INDEX statement

c: Number of rows in the work table required for index creation 1
Use the value obtained at (1) previously.

d: Length of index key
For details about the length of the index key, see Table 16-5 List of index key lengths. Because the key lengths stored in the database are based on a 4-byte boundary, it becomes [Figure]key length [Figure] 4[Figure] [Figure] 4.
For multiple indexes, add the key lengths of all component columns on the basis of Table 16-5 List of index key lengths.

(3) Obtaining the number of rows per work table page

To obtain the number of rows per work table page, use the following formula:

Formula
Number of rows per work table page = MIN{[Figure](b - 48) [Figure] a[Figure] 255}

a: Length of row in the work table (index key length + 18)
For details about the length of the index key, see Table 16-5 List of index key lengths. The key length is [Figure]key length [Figure] 4[Figure] [Figure] 4.
For multiple indexes, add the key lengths of all component columns on the basis of Table 16-5 List of index key lengths.

b: Page length of the work table
See (4) as follows.

(4) Obtaining the page length of a work table

To obtain the page length of a work table, use the following formula:

Formula
Page length of work table# = MAX{[Figure](Row length of work table + 48) [Figure] 2,048[Figure] [Figure] 2,048, 4,096}

#: The page length of a work table must be no more than 32,768 bytes.

a: Length of row in the work table (index key length + 18)
For details about the length of the index key, see Table 16-5 List of index key lengths. The key length is [Figure]key length [Figure] 4[Figure] [Figure] 4.
For multiple indexes, add the key lengths of all component columns on the basis of Table 16-5 List of index key lengths.

[Contents][Back][Next]

[Trademarks]

All Rights Reserved. Copyright (C) 2012, 2015, Hitachi, Ltd.