5.8.6 Determining the number of segments for storing each range index
Use the following formula to determine the number of segments needed to store each range index (RS(i) variable).
- Formula (segments)
-
RS(i) = SGRI + SGSAM
- Explanation of variables
-
SGRI: See (1) Determining the SGRI variable.
SGSAM: See (2) Determining the SGSAM variable.
- Organization of this subsection
(1) Determining the SGRI variable
Use the following formula to determine the SGRI variable.
Formula (segments)
|
|
![[Figure]](GRAPHICS/F0000227.GIF)
Explanation of variables
- SEGSIZE
-
Segment size in the DB area in which a range index is defined (pages)
Use the following formula to determine this value:
Formula
![[Figure]](GRAPHICS/F0000228.GIF)
- page_size
-
Page size of the DB area that stores range indexes (bytes)
- PGGRPRI
-
Use the following formula to determine this value.
Formula (page groups)
![[Figure]](GRAPHICS/F0000229.GIF)
- DBASGM
-
Use the following formula to determine this value.
Formula (segments)
![[Figure]](GRAPHICS/F0000230.GIF)
- SUBAREANUM
-
Use the following formula to determine this value.
Formula (segments)
![[Figure]](GRAPHICS/F0000231.GIF)
- cmd_num
-
The total number of times the following commands were used to initialize or add a DB area that stores the tables for which range indexes are defined
-
adbinit command
-
adbmodarea command
-
- tbl_dbareafile_num(i)
-
The number of DB area files created when the i-th use of the following commands were used to initialize or add a DB area that stores the tables for which range indexes are defined (files)
-
adbinit command
-
adbmodarea command
-
- tbl_dbarea_initsize(i)
-
The size specified for the initial allocation size option of the i-th use of the following commands to initialize or add the DB area that stores the tables for which range indexes are defined (gigabytes)
-
adbinit command
-
adbmodarea command
-
- SGTBL
-
The number of segments that will store the table for which a range index has been defined and the tables stored in the same data DB area as for the former (segments)
See the following subsections in (2) Explanation of variables under 5.8.1 Determining the total number of pages in the data DB area. Note that the subsection to see differs depending on the type of the table.
-
Single-chunk table that is a row store table
(a) Determining the variable SGROWTBL (for a single-chunk table)
-
Single-chunk table that is a column store table
(b) Determining the variable SGCOLUMNTBL (for a single-chunk table)
-
Multi-chunk table that is a row store table
(f) Determining the variable SGROWTBL (for a multi-chunk table)
-
Multi-chunk table that is a column store table
(g) Determining the variable SGCOLUMNTBL (for a multi-chunk table)
-
- SGIDX
-
Number of segments in the B-tree index stored in the same data DB area that stores the tables for which range indexes are defined (segments)
See the following subsections in (2) Explanation of variables under 5.8.1 Determining the total number of pages in the data DB area. Note that the subsection to see differs depending on the type of the table.
-
Single-chunk table
(c) Determining the variable SGIDX (for a single-chunk table)
-
Multi-chunk table
(h) Determining the variable SGIDX (for a multi-chunk table)
-
- SGTIX
-
Number of segments in the text index stored in the same data DB area that stores the tables for which range indexes are defined (segments)
See the following subsections in (2) Explanation of variables under 5.8.1 Determining the total number of pages in the data DB area. Note that the subsection to see differs depending on the type of the table.
-
Single-chunk table
(e) Determining the variable SGTIX (for a single-chunk table)
-
Multi-chunk table
(j) Determining the variable SGTIX (for a multi-chunk table)
-
- rng_num_in_table_dbarea
-
Number of range indexes in the data DB area that stores the tables for which range indexes are defined (indexes)
- ASGRI(i)
-
Number of segments in the range index stored in the same data DB area that stores the tables for which range indexes are defined (segments)
Formula (segments)
![[Figure]](GRAPHICS/F0000232.GIF)
- SGSAM
-
For details, see (2) Determining the SGSAM variable.
- SGTBL
-
The number of segments in the table for which the i-th range index is defined, and which is stored in the same data DB area as the tables for which range indexes are defined (files)
See the following subsections in (2) Explanation of variables under 5.8.1 Determining the total number of pages in the data DB area. Note that the subsection to see differs depending on the type of the table.
-
Single-chunk table that is a row store table
(a) Determining the variable SGROWTBL (for a single-chunk table)
-
Single-chunk table that is a column store table
(b) Determining the variable SGCOLUMNTBL (for a single-chunk table)
-
Multi-chunk table that is a row store table
(f) Determining the variable SGROWTBL (for a multi-chunk table)
-
Multi-chunk table that is a column store table
(g) Determining the variable SGCOLUMNTBL (for a multi-chunk table)
-
- col_size
-
Data length of the columns for which range indexes are defined (bytes)
To determine the data length of each column, see Table 5‒15: Data length of each data type in (1) Determining the number of pages for base rows (variable BP(i)) under 5.8.2 Determining the number of pages for storing each type of row.
- PGGRPSGM
-
Use the following formula to determine this value.
Formula (page groups)
![[Figure]](GRAPHICS/F0000233.GIF)
- PGGRPSIZE
-
The variable PGGRPSIZE is the smallest power of 2 that is equal to or greater than the variable TMPPGGRPSIZE, which is determined using the formula below.
However, if the targeted range index was defined before version 02-02 and the adbidxrebuild command has never been executed on it, substitute the value of the variable SEGSIZE for the variable PGGRPSIZE.
Formula
![[Figure]](GRAPHICS/F0000234.GIF)
(2) Determining the SGSAM variable
Use the following formula to determine the SGSAM variable.
Formula (segments)
|
|
![[Figure]](GRAPHICS/F0000235.GIF)
Explanation of variables
- SUBAREAPTRNUM
-
Use the following formula to determine this value.
Formula (segments)
![[Figure]](GRAPHICS/F0000236.GIF)
- cmd_num
-
The total number of times the following commands were used to initialize or add a DB area that stores the tables for which range indexes are defined
-
adbinit command
-
adbmodarea command
-
- tbl_dbareafile_num(i)
-
The number of DB area files created when the i-th iteration of the following commands were used to initialize or add a DB area that stores the tables for which range indexes are defined (files)
-
adbinit command
-
adbmodarea command
-
- tbl_dbarea_max_initsize
-
The largest size specified for the initial allocation size option among all uses of the following commands to initialize or add the DB area that stores the tables for which range indexes are defined (gigabytes)
-
adbinit command
-
adbmodarea command
-
- col_size
-
Data length of a column for which a range index is defined (bytes)
To determine the data length of each column, see Table 5‒15: Data length of each data type in (1) Determining the number of pages for base rows (variable BP(i)) under 5.8.2 Determining the number of pages for storing each type of row.
If the targeted range index is a range index that cannot skip chunks, substitute 0 for the variable col_size. To check whether the targeted range index can skip chunks, see 11.3.6 Checking a range index (whether it can skip chunks).
- page_size
-
Page size of the DB area that stores range indexes (bytes)
- SEGSIZE
-
Segment size (pages)
Use the following formula to determine this value:
Formula
![[Figure]](GRAPHICS/F0000237.GIF)