Colocation Partitioning for Distributed Database Association Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Distributed databases face performance deterioration during OLAP operations due to the need to obtain large amounts of data from multiple nodes and global matching of dimension tables, leading to excessive system delays.
Innovation Solution
A method for partitioning association tables in a distributed database by determining a first data table with non-foreign keys, generating a colocation partition (CP) table set, and partitioning each CP table with the same partition range as the first data table, ensuring associated CP tables are partitioned uniformly, thereby reducing the need to access entire fact tables during OLAP operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If batch data is obtained from multiple nodes during OLAP operation, then data completeness is improved, but system delay increases excessively
Solution Approach 1:
The patent segments the association table into multiple partitions based on the partition key, allowing data to be distributed across multiple nodes. Each partition can be independently queried, enabling the system to retrieve only the necessary data from specific nodes rather than requiring batch data from all nodes, thus reducing system delay while maintaining data completeness.
2Measurement precision
If global matching of dimension table is performed during multiple-table Join operation, then query accuracy is improved, but performance of entire system deteriorates
Solution Approach 1:
The patent applies local quality by enabling selective partition matching where only the necessary partitions of the dimension table are matched locally at each node rather than performing global matching across all nodes. This allows query accuracy to be maintained for the relevant data while significantly improving system performance by avoiding unnecessary global matching operations.
3Stability of the object's composition
If partition key is selected as foreign key, then table relationship is maintained, but querying efficiency of OLAP deteriorates
Solution Approach 1:
The patent applies dynamics by allowing the partition key selection to be flexible and adaptive. The system can dynamically choose partition keys that are not foreign keys to optimize querying efficiency for OLAP operations, while still maintaining table relationships through the association table structure. This dynamic selection enables the system to balance between relationship maintenance and querying efficiency based on the specific operational requirements.
Data Source
AI summary
A method for partitioning an association table in a distributed database, where a manager determines a first data table in data tables requiring partition and generates a colocation partition (CP) table set of the first data table, the CP table set of the first data table includes the first data table and at least one CP table of the first data table, and a CP table of the first data table includes a data table whose partition key includes a subset of a partition key of the first data table. The manager partitions the first data table according to the partition key and partitions each CP table in the CP table set, a partition range of a partition key of each CP table is the same as a partition range of a corresponding partition key in the first data table.