Estimate the size of your base tables and hash and join indexes using the equations provided in the sections below.
These equations assume a constant block size. Because block sizes can vary widely within a table, you can obtain 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:
| This variable … | Specifies … |
|---|---|
| ROUNDUP | the ROUNDUP function. |
| MAX × ¾ | three quarters of the maximum block size in bytes. The upper limit on this value is 255 sectors, or 130,560 bytes. |
| 512 | 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 above). |
| 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). Remember that all row lengths 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, Hash, 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 it is 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