Benefits of table partitioning

Save PDF

OpenEdge table partitioning provides the following benefits.

The following table lists each benefit with its description:


Benefit	Description
High availability	Because a table consists of multiple partitions, if one partition is not available, the remaining partitions are still available for users to access. Maintenance tasks can be done on individual partitions. So, downtime is also reduced, adding to higher availability.
Easy administration	You can perform data dump and load, data move, partition management, and index creation and rebuild on a partition more quickly than on a very large table. Such administration tasks also consume fewer system and database resources.
Better performance	OpenEdge RDBMS can process user queries that access data in partitions more quickly than those that access data in a very large table. Because partitions are treated as separate tables, if CRUD operations are being performed simultaneously on multiple partitions, concurrency is improved. Maintenance tasks such as data dump and load, index creation, and rebuild can be done on partitions individually and in parallel, thus enhancing performance. If partitions are stored in different storage areas spread across different disks or physical devices, there is less I/O contention for CRUD and maintenance operations, resulting in better performance.

Features of OpenEdge table partitioning

OpenEdge table partitioning has the following features:

Table partitioning is built into the database layer, making it transparent to the application.
Applications that access non-partitioned tables can access partitioned tables with little or no changes required.
Each row of a partitioned table has the same columns.
- Each row is located in one partition and does not span multiple partitions.
Every partitioned table consists of multiple partitions.
- Each partition contains a subset of rows of the table data.
- Each partition can reside in its own storage area.
- Each partition can be independently modified and managed without affecting the other partitions of the same table.
- You can have up to 32,765 partitions per table.
Indexes associated with partitioned tables can also be partitioned. These are known as local indexes.
- Each partition of a local index can reside in its own storage area.
- Indexes associated with a table partition can be independently rebuilt without affecting the indexes of the other table partitions.
You can query a partitioned table using an index or using TABLE-SCAN.
You can use database utilities and tools to manage partitions.

Note:

You cannot partition multi-tenant tables. You can have multi-tenant tables and partitioned tables in the same database, but you cannot apply both features to the same table.
To implement and administer OpenEdge table partitioning, you must have a Progress OpenEdge Enterprise RDBMS license and a Progress OpenEdge Table Partitioning license.

Here are common reasons you should consider partitioning tables:

Tables contain historical data and you need to periodically archive the historical data as new data is added.
Tables contain data that must be deleted or loaded periodically, and the delete or load operation takes a long time and blocks users from accessing the tables.
Tables contain data that have to be spread across different storage devices.
Tables must undergo periodic table and/or index maintenance operations that can be slow and require significant downtime.
Tables contain data that stops changing over time and you have a way to identify these subsets of data.
Tables have a specific column or columns whose data is frequently queried by the applications that access the database.
Tables contain geographical or organizational groups of data.
Tables contain numeric or timestamp-based groups of data.
Tables have one or more columns that can be used to uniquely identify logical groups of data.
Tables contain data that is frequently queried using TABLE-SCAN instead of an index.
Tables you know will grow very large.