Estimate the size of your base tables and join indexes using the equations provided in the following sections.
These equations assume a constant block size. Because block sizes can vary widely within a table, you can get more finely tuned estimates by computing values over a range of block sizes.
Calculating a Typical Block Size
Depending on how you define your tables and the boundary conditions on their rows, the maximum size in bytes for a typical block can be estimated by the following equation:
Typical block size = ROUNDUP(MAX × ¾, 512)
where:
| Variable | Description |
|---|---|
| ROUNDUP | Specifies the ROUNDUP function. |
| MAX × ¾ | Specifies three quarters of the maximum block size in bytes. The upper limit on this value is 255 sectors, or 130,560 bytes. |
| 512 | Specifies the rounded up sector boundary, which is the number of bytes per sector for the table. |
Sizing a Column-Partitioned Join Index
See Sizing a Column-Partitioned Table for information about sizing a column-partitioned join index. The methodology for column-partitioned join indexes is identical to that used to size a column-partitioned table.
Table Sizing Equations
The following parameter definitions are used with this equation set:
| Parameter | Definition |
|---|---|
| Block Overhead | Block Header + Block Trailer = 72 bytes + 2 bytes = 74 bytes |
| Minimum Table Header Size | 512 bytes |
| Typical Block Size | Number of bytes per block (see Calculating a Typical Block Size). |
| NumAmps | Number of AMPs in the configuration. |
| RowCount | Estimated cardinality for the table. |
| Average RowSize | Physical row size for the table (see Row Size Calculation). Each row length must be an even number of bytes (see Byte Alignment), so be sure to take this into account. |
Rows per block (rounded down) = Typical block size – 38 ÷ Row size
Number of blocks (rounded up) = Row count ÷ Rows per block
Number of table header bytes = (Number of AMPs )(512)
Number of base table bytes (without fallback) = (Number of blocks × Typical block size) + Number of header bytes
Number of base table bytes (with fallback) = 2(Number of blocks)(Typical block size) – Number of header bytes
Example: Table or Join Index on a Packed64 Format System
You have collected the following information.
| Parameter | Value |
|---|---|
| Block size | 5,120 bytes Typical block size used.
|
| Number of AMPs | 20 |
| Row count | 1,000,000 |
| Row size | 100 bytes |
| Rows per block are rounded down. Number of blocks are rounded up. |
|
Calculate the size of this object when defined with fallback.
Number of rows per block = (5,120 - 16) ÷ 100 = 51
Number of blocks = 1,000,000 ÷ 51 = 19,608
Number of table header bytes = 20 × 512 = 10240
Number of bytes in base table with fallback = ((19,608 × 5,120) × 2) + 10,240