Data Table Partitioning for OLTP and OLAP Workloads
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face challenges in ensuring real-time performance for online transaction processing (OLTP) and analysis efficiency and effectiveness for online analytical processing (OLAP, particularly due to high data network transmission overheads and resource consumption during table joining operations.
Innovation Solution
A data table partitioning management method that applies different partitioning techniques based on the type and dimension of data tables, performing one-dimensional or multidimensional partitioning on row and column replica spaces using join keys to optimize data processing and reduce resource consumption.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If data is stored in multiple physical disks with separate column and row storage replicas for OLTP and OLAP, then read and write performance is improved, but computing amount and data network transmission overheads increase during table joining operations
Solution Approach 1:
The patent segments the data storage system into multiple physical disks with separate column storage replicas and row storage replicas, allowing OLTP operations to access row storage while OLAP operations access column storage. This segmentation enables simultaneous optimization for both transaction processing and analytical processing without interference, reducing network transmission overheads by allowing local data access rather than requiring data transfer between separate subsystems
Solution Approach 2:
The patent introduces a new dimension of storage organization by implementing both column-oriented and row-oriented storage replicas within the same physical disk structure. This dimensional approach allows the system to serve different query patterns (OLTP and OLAP) through different data access dimensions, improving performance while reducing the need for complex data transfers during joining operations
2Adaptability or versatility
If the database system is divided into two independent subsystems for non-column and column storage, then different query services can be routed to appropriate subsystems, but hardware configuration and system maintenance require high overheads
Solution Approach 1:
The patent merges the column storage and row storage functions into a unified database system structure where both storage types coexist within the same system framework. This merging eliminates the need for completely separate subsystems while maintaining the ability to route different query services to appropriate storage mechanisms, thereby reducing hardware configuration and maintenance overheads
Solution Approach 2:
The patent implements a universal storage system that can handle both OLTP and OLAP workloads through multi-functional storage replicas. The same physical disk structure supports both column-oriented and row-oriented storage, allowing a single system to serve multiple query types without requiring separate dedicated subsystems for each function
Data Source
AI summary
A data table partitioning management method and apparatus are disclosed. The method includes: determining a type and a join key of each data table in a table group, where the type of the data table includes a one-dimensional table, a multidimensional table, or a fact table; and performing one-dimensional partitioning on row replica space of each data table in the table group, and performing one-dimensional or multidimensional partitioning on column replica space of the data table according to the type of the data table and based on the join key of the data table. Different partitioning management methods are applied to data tables of different types and different dimensions, so that data processing mechanisms of OLTP and OLAP are efficiently implemented in a system, and resource consumption is reduced.