Database data block sizes and alignments are variable. This permits a designer the flexibility of designing tables without having to spend time balancing data types, row sizes, and block sizes to optimize their storage.
Critical Sizing Variables
- Row size
- Estimated cardinality
- Presence of fallback
Table headers are a fixed variable, but must be considered when projecting the sizes of your tables. Take multivalue compression into account when sizing table headers (see Compression Types Supported by Vantage).
Sizing a Column-Partitioned Table
Column-partitioned tables have similar space usage as a table with row partitioning except for the impact of compression.
- The number of physical rows in populated combined partitions is small.
- The number of column partitions with ROW format is large and the subrows are narrow.
- There number of rows per unique value of the primary index is small (for a unique primary index, there is only one row per unique value).
A container can only hold values of rows with the same internal partition number and hash bucket value (for PI or NoPI tables and join indexes) or hash value (for PI tables and join indexes). The size may increase because of the increased number of physical rows and their row headers. A column-partitioned table is likely to be significantly smaller than a table that is not column-partitioned if COLUMN format is used for narrow column partitions and the table is not over-partitioned because of the reduced number of physical rows and autocompression.
- Estimate the size of the table without column partitioning using the standard methods for calculating the size of a table.
- Adjust your estimate based on the expected impact of autocompression, row header compression, and row header expansion.
You can estimate the necessary adjustment by measuring the impact of your proposed column partitioning scheme on sample data.
LOB and XML Sizing Variables
Each chunk of a BLOB, CLOB, or XML string stores an 8-byte length followed by as much as a 64 KB data fragment. LOBs are stored outside its base table row in a subtable. See Sizing a LOB or XML Subtable for more information.