In OceanBase Database, partitioning allows you to decompose a table into multiple smaller and more manageable parts called partitions based on specific rules. Each partition is an independent object with its own name and may have its own storage features. This topic describes partitions and the benefits of partitioning.
An application that accesses a database logically accesses only one table or one index. However, the table may consist of dozens of physical partitions. Each partition is an independent object and can be independently accessed or accessed as part of the table. The partitions are completely transparent to the application and do not affect the business logic of the application.
From the perspective of an application, only one schema object exists. No modification to SQL statements is required to access partitioned tables. Partitioning is useful for many types of database applications, especially those that manage large amounts of data.
Benefits of partitioning are described as follows:
Higher availability
The unavailability of a partition does not necessarily mean that the entire table is unavailable. The query optimizer automatically removes unreferenced partitions from the query plan. Therefore, queries are not affected when the partitions are unavailable.
Easier management of database objects
A partitioned object has pieces that can be managed collectively or separately. DDL statements can manipulate partitions rather than entire tables or indexes. Therefore, you can decompose resource-intensive tasks such as the recreation of an index or table. For example, you can move only one partition at a time. If an issue occurs, you need only to redo the partition move rather than the table move. In addition, you can execute a
TRUNCATEstatement on a partition to avoid unnecessary deletion of a large amount data.Reduced contention for shared resources in OLTP systems
In online transaction processing (OLTP) scenarios, partitioning can reduce contention for shared resources. For example, a DML operation is performed on many partitions rather than one table.
Enhanced query performance in data warehouses
In analytical processing (AP) scenarios, partitioning can speed up the processing of ad hoc queries. Partitioning keys can implement filtering. For example, when sales data is partitioned by sales time and you want to query the sales data for a quarter, you need to query only one or several partitions rather than the entire table.
Better load balancing results
OceanBase Database stores data and implements load balancing by partition. Different partitions can be stored on different OBServer nodes in an OceanBase cluster. Therefore, different partitions of a partitioned table can be distributed on different OBServer nodes so that the data of a table can be evenly distributed across the entire OceanBase cluster.