When you perform these calculations, remember the following:
- Implement your measurements over a large number of rows to avoid distorting the figures with table overhead issues.
- An INTEGER column uses 4 bytes.
A SUM(INTEGER) column uses 8 bytes because the system casts its data type to FLOAT.
- If you have not explicitly specified a COUNT column in your aggregate join index definition, add 4 bytes to the definition to account for the required COUNT column.
Example Measurement 1
Table A and Table B both have 60 million 100-byte rows. An aggregate join index with two INTEGER columns and one SUM(INTEGER) column.
From these figures, the computed size of the aggregate join index is as follows:
Row size = (2 × 4 INTEGER bytes) + 4 COUNT bytes + 8 SUM bytes + 14 overhead bytes = 34 bytes
| Type of Join Index | Join Index Row |
|---|---|
| Non-aggregate | For every matching row in the base table. |
| Aggregate | Per group of rows in the join of the base tables. |
The permanent space specification is as follows:
| Current Permanent Space (Bytes) | Peak Permanent Space (Bytes) | Maximum Permanent Space (Bytes) |
|---|---|---|
| 2,041,595,904 | 2,041,595,904 | 0 |
Using these figures, you can compute a measured row size:
Row size = 2,041,595,904 ÷ 60,000,000 = 34.03 bytes
Because the measured and computed disk spaces are identical within a narrow confidence interval, you can conclude that the disk space formula is accurate for an aggregate join index.
Example Measurement 2
This example examines the size of a simple join index created for the same tables used in Example Measurement 1. There is a savings of 8 bytes because there is no COUNT column and no SUM(INTEGER) column.
The computed row size is as follows:
Row size = 34 bytes – (4 INTEGER bytes + 4 SUM(INTEGER) bytes) = 26 bytes
The permanent space specification is as follows.
| Current Permanent Space (Bytes) | Peak Permanent Space (Bytes) | Maximum Permanent Space (Bytes) |
|---|---|---|
| 1,561,216,000 | 1,561,216,000 | 0 |
Using these figures, you can compute a measured row size.
Row size = 1,561,216,000 ÷ 60,000,000 = 26.02
As with Example Measurement 1, the measured and computed disk spaces are identical, and you can conclude that the disk space formula is accurate for a simple join index.