Parallel Partitioning Index Scan for Database Join Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database management systems face performance degradation due to expensive repartition operations when joining database tables that are partitioned sub-optimally or have mismatched partitioning schemes, leading to inefficient query execution.
Innovation Solution
The implementation of a method that performs an index scan on a global index of a first database table to determine rows that can be joined with corresponding partitions in a second table, allowing for optimized repartitioning and efficient join operations without re-partitioning the entire table.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional query optimizer performs repartition operation on database tables with mismatched partitioning, then join operation can be executed, but system performance decreases due to expensive repartition operations
Solution Approach 1:
The patent divides the database tables into multiple partitions based on partitioning schemes. Instead of repartitioning entire tables, the system segments data into manageable partitions that can be processed independently, allowing efficient joining of corresponding partitions without full table repartitioning operations
Solution Approach 2:
The system performs preliminary partitioning of database tables before join operations are executed. By pre-organizing data into partitions according to partitioning schemes, the query optimizer can directly access and join corresponding partitions without performing expensive repartition operations at query execution time
2Quantity of substance
If database tables are partitioned sub-optimally on attributes not in equi-join predicates, then storage efficiency is improved, but join operation performance deteriorates due to required repartitioning
Solution Approach 1:
The patent applies different partitioning strategies to different parts of the database system. Tables are partitioned according to their specific characteristics and access patterns, with partitioning schemes optimized for local storage efficiency while the join operation handles attribute mismatches by matching corresponding partitions based on partitioning schemes rather than requiring all attributes to be in equi-join predicates
3Adaptability or versatility
If repartition operation is performed to match partitioning between tables, then join compatibility is achieved, but device complexity increases due to repartition processing requirements
Solution Approach 1:
Instead of inverting the conventional approach of repartitioning tables to match join requirements, the patent inverts the logic by having the join operation adapt to existing partitioning schemes. The system matches corresponding partitions between tables based on their partitioning schemes without requiring repartitioning, thus achieving partition compatibility while avoiding the complexity of repartition processing
Data Source
AI summary
System, methods and articles of manufacture for joining data in the database tables comprising, performing an index scan on a global index of a first database table, determining rows in the first database table that may be joined with a second database table based on a needed partitioning, wherein the needed partitioning is determined using an index scan, determining a number of partitions in the second database table, and joining each of the corresponding partitions in the first database table with a corresponding partitions in the second database table.