The procedure used to calculate the number of pages required to store an index is explained in (1) as follows. The variables used in the formulas are explained in (2), and examples of calculating the number of pages needed to store an index are presented in (3).
If cluster key is specified with CREATE TABLE, the number of cluster key storage pages should be obtained in the same manner as for the number of index storage pages.
If an index is row-partitioned, the number of pages for the index in each storage RDAREA must be obtained.
The following formula is used to calculate the number of pages needed to store an index.
n |
The recursive formula shown in Formula 1 as follows is used to obtain Pi. Pi + 1 must be calculated until Pn = 1, then the sum of the results must be obtained.
The value of Pd must be obtained if the number of duplicated key values exceeds 200. The formula for obtaining the value of Pd is shown in Formula 2 as follows:
![]() |
![]() |
Number of duplicated elements = ![]() ![]() ![]() ![]() ![]() ![]() |
Table 17-5 List of index key lengths
Classifi- cation | Data type | Data length (bytes) | |||||
---|---|---|---|---|---|---|---|
Key length less than 256 | Key length 256 or greater | ||||||
Single- column index | Multicolumn index | Single- column index | Multicolumn index | ||||
Fixed length1 | Variable length2 | Fixed length1 | Variable length2 | ||||
Numeric data | INTEGER | 4 | 5 | 6 | N | 5 | 7 |
SMALLINT | 2 | 3 | 4 | N | 3 | 5 | |
LARGE DECIMAL(m,n)3 | ![]() ![]() ![]() | ![]() ![]() ![]() | ![]() ![]() ![]() | N | ![]() ![]() ![]() | ![]() ![]() ![]() | |
FLOAT or DOUBLE PRECISION | 8 | E | E | ![]() | E | E | |
SMALLFLT or REAL | 4 | E | E | ![]() | E | E | |
Character data | CHARACTER(n) | n | n + 1 | n + 2 | n | n + 1 | n + 3 |
VARCHAR(n) | a + 1 | N | a + 2 | a + 2 | N | a + 3 | |
National character data | NCHAR(n) or NATIONAL CHARACTER(n) | 2 ![]() | 2 ![]() | 2 ![]() | 2 ![]() | 2 ![]() | 2 ![]() |
NVARCHAR(n) | 2 ![]() | N | 2 ![]() | 2 ![]() | N | 2 ![]() | |
Mixed character data | MCHAR(n) | n | n + 1 | n + 2 | n | n + 1 | n + 3 |
MVARCHAR(n) | a + 1 | N | a + 2 | a + 2 | N | a + 3 | |
Date data | DATE | 4 | 5 | 6 | ![]() | 5 | 7 |
Time data | TIME | 3 | 4 | 5 | ![]() | 4 | 6 |
Date interval data | INTERVAL YEAR TO DAY | 5 | 6 | 7 | ![]() | 6 | 8 |
Time interval data | INTERVAL HOUR TO SECOND | 4 | 5 | 6 | ![]() | 5 | 7 |
Timestamp data | TIMESTAMP(p) | 7 + (p![]() | 8 + (p![]() | 9 + (p![]() | ![]() | 8 + (p![]() | 10 + (p![]() |
Obtain the number of index storage pages for a unique index (no duplicated key values) of the PCODE column for the following STOCK table:
PNO | PNAME | STANDARD | PRICE | QUANTITY | COST |
---|---|---|---|---|---|
20180 | CLEANER | C20 | 20000 | 26 | 15000 |
20190 | CLEANER | C77 | 28000 | 105 | 23000 |
20130 | REFRIGERATOR | P10 | 30000 | 70 | 25000 |
20220 | TV | K18 | 35000 | 12 | 30000 |
20200 | CLEANER | C89 | 35000 | 30 | 30000 |
20140 | REFRIGERATOR | P23 | 35000 | 60 | 30000 |
20280 | AMPLIFIER | L10 | 38000 | 200 | 33000 |
20150 | REFRIGERATOR | P32 | 48000 | 50 | 43000 |
20290 | AMPLIFIER | L50 | 49800 | 260 | 45000 |
20230 | TV | K20 | 50000 | 15 | 45000 |
20160 | REFRIGERATOR | P35 | 55800 | 120 | 50000 |
Obtain the number of index storage pages for the STOCK table shown in Example 1 when the PNAME column is used as the index (with duplicated key values).
Obtain the number of index storage pages for the following MEMBERSHIP_TABLE using the SEX and YEAR_JOINED columns as a multicolumn index:
MNO | NAME | AGE | SEX | YEAR_JOINED |
---|---|---|---|---|
0001 0002 0003 0004 ![]() ![]() ![]() 1000 | Lisa Roberts John Anderson Jane Wood Mark Wood ![]() ![]() ![]() Joe Young | 18 25 24 25 ![]() ![]() ![]() 30 | F M F M ![]() ![]() ![]() M | 1983 1967 1987 1964 ![]() ![]() ![]() 1995 |
Column | Data type |
---|---|
MNO | CHARACTER(5) |
NAME | NCHAR(4) |
AGE | INTEGER |
SEX | CHARACTER(4) |
YEAR_JOINED | INTEGER |