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 p1.

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.

The aggregate SM storage and memory capacity can be effectively increased if SMs serve disjoint parts of the database.