Cost-Based Table Partitioning for Parallel Query Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database query optimization methods fail to effectively utilize multi-core CPUs for parallel execution of queries, leading to suboptimal performance with large data sets, as they do not accurately determine the computational costs and resource utilization across multiple tables.
Innovation Solution
A cost-based piece partitioning (CBPP) optimizer that calculates an accumulated processing cost for each table based on the number of records, operator costs, and selectivity values, allowing for the identification of the table with the highest processing cost and subsequent partitioning to maximize CPU resource utilization, enabling parallel execution across multiple cores.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional query optimization methods are used, then implementation simplicity is maintained, but CPU parallelism utilization is insufficient leading to suboptimal query performance
Solution Approach 1:
The patent segments the database tables into multiple partitions based on cost-based piece partitioning (CBPP). Each partition is independently processed by different CPU cores, enabling parallel query execution. The segmentation is achieved by dividing tables into pieces that can be distributed across multiple processing units, thereby improving CPU parallelism utilization and query execution speed without requiring complex distributed system architecture.
Solution Approach 2:
The patent performs preliminary cost analysis and partitioning before query execution. The CBPP optimizer pre-calculates the accumulated processing cost for each table and determines the optimal partitioning strategy in advance. This preliminary action allows the system to prepare partitioned table structures beforehand, enabling efficient parallel execution when queries are submitted, thus improving productivity without adding complexity to the query processing logic itself.
2Productivity
If table partitioning is implemented without cost-based selection, then parallel execution can be achieved, but CPU resource utilization is suboptimal due to partitioning inappropriate tables
Solution Approach 1:
The patent introduces cost-based parameters (accumulated processing cost, operator costs, table sizes, selectivity values) to determine which tables should be partitioned. The CBPP optimizer calculates these parameters dynamically based on the specific query and database state, then selects tables for partitioning based on cost thresholds. This parameter-driven approach ensures that only tables benefiting from parallel processing are partitioned, maximizing CPU resource utilization while avoiding the overhead of partitioning inappropriate tables, thus reducing query execution time.
3Productivity
If more tables are partitioned for parallel processing, then CPU parallelism increases, but the complexity of execution plan computation increases
Solution Approach 1:
The patent applies partitioning selectively to specific tables based on local cost analysis rather than uniformly across all tables. The CBPP optimizer evaluates each table's accumulated processing cost, operator costs, and selectivity values to determine local partitioning needs. This local quality approach allows the system to achieve high parallel execution efficiency by partitioning only the necessary tables, while keeping the execution plan computation manageable by avoiding unnecessary partitioning complexity.
Data Source
AI summary
A method that comprises receiving a logical execution plan for a database query corresponding to a plurality of tables of the database, wherein the logical execution plan comprises one or more operators, receiving an operator cost for each of the operators in the logical execution plan, computing a first accumulated processing cost for a first of the tables based on the logical execution plan, operator selectivity, and operator costs corresponding to the first table, computing a second accumulated processing cost for a second of the tables based on the logical execution plan, operator selectivity, and operator costs corresponding to the second table, comparing the first accumulated processing cost and the second accumulated processing cost to determine a table with the highest accumulated processing cost, and responsive to comparing the accumulated processing costs, computing a physical execution plan that requires partitioning the table with the highest accumulated processing cost.