Use Cases for Table Partitions and Storage Groups
Table partitioning helps to improve SQL execution plans using the optimizer to process queries against table partitions, i.e. a technique called "partition pruning". For example, if a table is split horizontally into partitions
p i by ranges on
col1 such that data where
col1 < 3 is stored in
p1, data where
col1 < 7 is stored in
p2, and data where
col1 < 10 is stored in
p3. In this case, the query, “
select * from t1 where col1 < 2” can be optimized to access only partition
Data aging is the process of logically grouping, and then physically moving, historical data within a database or between databases to improve the response time of queries to recent data or conserve disk space on lower-capacity, higher-performance storage media. Table partitioning and storage groups improve support for data aging within a NuoDB database by enabling logical grouping by partitioning key, and the storage of historical data in storage groups served by higher-capacity media.
Partitioning data into multiple storage groups enables the storage of partitions on different SMs. This can effectively increase the I/O bandwidth available when the database is I/O bound and database reads or writes are striped across multiple storage groups. For example, a write-intensive application (such as data ingest) that loads data into multiple partitions may benefit from storing those partitions in storage groups served by different SMs to take advantage of multiplexed I/O bandwidth while committing data load transactions.