5.8.4 Determining the key length (KEYSZ) of a B-tree index
This subsection explains how to compute the key length (KEYSZ) of a B-tree index.
First, identify the B-tree index type based on the following figure.
|
|
![[Figure]](GRAPHICS/F0000219.GIF)
Next, determine KEYSZ by using the formula for the identified B-tree index type. For the key length of each indexed column, see the following table.
|
No. |
Classification |
Data type |
Key length (bytes) |
|
|---|---|---|---|---|
|
1 |
Numeric data |
INTEGER |
8 |
|
|
2 |
SMALLINT |
4 |
||
|
3 |
DECIMAL(m, n) |
1 ≤ m ≤ 4 |
2 |
|
|
4 |
5 ≤ m ≤ 8 |
4 |
||
|
5 |
9 ≤ m ≤ 16 |
8 |
||
|
6 |
17 ≤ m ≤ 38 |
16 |
||
|
7 |
DOUBLE PRECISION |
8 |
||
|
8 |
Character string data |
CHARACTER(n) |
n |
|
|
9 |
VARCHAR(n) |
d |
||
|
10 |
Datetime data |
DATE |
4 |
|
|
11 |
TIME(p) |
3 + ↑p ÷ 2↑ |
||
|
12 |
TIMESTAMP(p) |
7 + ↑p ÷ 2↑ |
||
|
13 |
Binary data |
BINARY(n) |
n |
|
|
14 |
VARBINARY(n) |
d |
||
- Legend:
-
d: Actual data length
m, n: Positive integers
p: 0, 3, 6, 9 or 12
- Organization of this subsection
(1) Single-column index
- Formula (single-column index) (bytes)
-
KEYSZ = flag_size + key_size
- Explanation of variables
-
-
flag_size
One of the following values (in bytes):
- If the indexed column is not a VARCHAR or VARBINARY column: 0
- If the indexed column is a VARCHAR or VARBINARY column with a defined length of no more than 255 bytes: 1
- If the indexed column is a VARCHAR or VARBINARY column with a defined length of 256 or more bytes: 2
-
key_size
Key length of the indexed column (bytes)
-
(2) Multiple-column index comprised of fixed-length columns only
Formula (multiple-column index comprised of fixed-length columns only) (bytes)
|
|
![[Figure]](GRAPHICS/F0000220.GIF)
- Explanation of variables
-
-
idx_col_num
Number of indexed columns
-
flag_null
Either of the following values (bytes):
• For a key that does not include a null value in the indexed columns: 0
• For a key that includes a null value in the indexed columns: 1
-
key_size(i)
Key length of i-th indexed column (bytes)
-
(3) Multiple-column index containing variable-length columns
Formula (multiple-column index containing variable-length columns) (bytes)
|
|
![[Figure]](GRAPHICS/F0000221.GIF)
- Explanation of variables
-
-
idx_col_num
Number of indexed columns
-
flag_type
Either of the following values (bytes):
• If the size of the columns comprising the B-tree index is 255 bytes or less: 1
• If the size of the columns comprising the B-tree index is 256 bytes or greater: 2
For details about the length of columns in a multiple-column index, see CREATE INDEX (define an index) in Definition SQL in the manual HADB SQL Reference.
-
key_size(i)
Key length of i-th indexed column (bytes)
-