In OceanBase Database, partitioning refers to the process of dividing a table into multiple smaller and more manageable parts based on specific rules. Each partition is an independent object with its own name and optional storage characteristics. This topic introduces the concepts of partitioning and the benefits of using partitions.
For applications accessing the database, there is only one logical table or index to access. However, in reality, this table may consist of several physical partition objects, where each partition is an independent object that can be accessed individually or as part of the table. Partitions are completely transparent to the application and do not affect the business logic.
From the perspective of an application, there is only one schema object. Accessing a partitioned table does not require modifying SQL statements. Partitioning is particularly useful for various types of database applications, especially those that manage a large amount of data.
The benefits of using partitioning are 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 a partition is unavailable.
Easier management of database objects
Partitioned objects have segments that can be managed collectively or individually. DDL statements can operate on partitions instead of the entire table or index. This allows for breaking down resource-intensive tasks such as rebuilding indexes or tables. For example, it is possible to move only one partition at a time. If any issues occur, it is only necessary to redo the partition movement, rather than moving the entire table. Additionally, performing a
TRUNCATEoperation on partitions can prevent the deletion of a large amount of data compared to using theDELETEstatement.Reduced contention for shared resources in OLTP systems
In online transaction processing (OLTP) scenarios, partitioning can reduce contention for shared resources. For example, DML operations can be performed on many partitions instead of a single 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.