Parallel Partition-Wise Insert Sub-Select for Distributed Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional distributed database systems face high transaction latency and resource consumption due to the need to collect and transfer data from multiple nodes when executing INSERT sub-SELECT queries across partitioned tables stored on different nodes.
Innovation Solution
The solution involves generating partition-wise select statements and shipping operators to each node to perform on-node insertions, minimizing off-node data transfers by copying rows from a source table partition to a target table partition located on the same node, thereby reducing inter-node data shipping.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If all source table rows are collected at a master node and then sent to target table nodes, then the query can be executed correctly, but transaction latency increases and resource consumption increases
Solution Approach 1:
The patent segments the INSERT sub-SELECT query into multiple partition-wise select statements, one for each partition. Each partition's rows are copied directly from the source partition to the target partition on the same node, eliminating the need to collect all rows at a master node. This segmentation resolves the contradiction by maintaining query correctness through structured partition processing while reducing transaction latency through parallel local operations.
Solution Approach 2:
The patent changes the execution dimension from a centralized master-node approach to a distributed per-node approach. Instead of all nodes communicating through a single master node, each node independently processes its local partitions. This dimensional shift in query execution architecture eliminates inter-node data transfer overhead while ensuring correctness through partition-wise processing.
2Reliability
If all source table rows are collected at a master node and then sent to target table nodes, then the query can be executed correctly, but resource consumption increases
Solution Approach 1:
The patent segments the INSERT sub-SELECT query into multiple partition-wise select statements, one for each partition. Each partition's rows are copied directly from the source partition to the target partition on the same node, eliminating the need to collect all rows at a master node. This segmentation resolves the contradiction by maintaining query correctness through structured partition processing while reducing resource consumption through parallel local operations.
Solution Approach 2:
Each database node independently processes its own local partitions without requiring data to be shipped from other nodes. The node performs self-service by copying rows from source partitions to target partitions locally, eliminating the need for master node coordination and inter-node data transfer, thus reducing resource consumption while ensuring correctness.
3Productivity
If data is transferred among nodes for INSERT sub-SELECT queries, then source rows can be copied to target table, but inter-node data shipping increases transaction latency
Solution Approach 1:
The patent segments the INSERT sub-SELECT query into multiple partition-wise select statements, one for each partition. Each partition's rows are copied directly from the source partition to the target partition on the same node, eliminating the need to collect all rows at a master node. This segmentation resolves the contradiction by maintaining query correctness through structured partition processing while reducing transaction latency through parallel local operations.
Solution Approach 2:
The patent changes the execution dimension from a centralized master-node approach to a distributed per-node approach. Instead of all nodes communicating through a single master node, each node independently processes its local partitions. This dimensional shift in query execution architecture eliminates inter-node data transfer overhead while ensuring correctness through partition-wise processing.
4Productivity
If data is transferred among nodes for INSERT sub-SELECT queries, then source rows can be copied to target table, but resource consumption increases
Solution Approach 1:
The patent segments the INSERT sub-SELECT query into multiple partition-wise select statements, one for each partition. Each partition's rows are copied directly from the source partition to the target partition on the same node, eliminating the need to collect all rows at a master node. This segmentation resolves the contradiction by maintaining query correctness through structured partition processing while reducing resource consumption through parallel local operations.
Solution Approach 2:
Each database node independently processes its own local partitions without requiring data to be shipped from other nodes. The node performs self-service by copying rows from source partitions to target partitions locally, eliminating the need for master node coordination and inter-node data transfer, thus reducing resource consumption while ensuring correctness.
Data Source
AI summary
A system includes reception of a database query, the query comprising a first query statement to insert rows of a source table into a target table, generation a second query statement and a third query statement based on the first query statement, the second query statement to insert rows of a first partition of the source table into the target table and the third query statement to insert rows of a second partition of the source table into the target table, generation of a first query operator executable to perform the second query statement, generation of a second query operator executable to perform the third query statement, transmission of the first executable query operator to a second database server node storing the first partition of the source table, and transmission of the second executable query operator to a third database server node storing the second partition of the source table.