Partition Elimination Lists for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database systems face performance issues when executing complex queries on large volumes of data due to inefficient data organization across physical storage facilities, leading to increased read and write operations.
Innovation Solution
A partition-sensitive index-assisted method that creates partition elimination lists based on query conditions, allowing for targeted access to specific partitions, reducing unnecessary data searches and reorganization, and estimating the cost of access using an adjustment factor based on non-eliminated partitions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If data is stored in multiple physical partitions across storage facilities, then data organization flexibility and storage capacity are improved, but query execution time increases due to needing to search across all partitions
Solution Approach 1:
The patent divides the database into multiple partitions stored across different physical storage facilities, with each partition containing a subset of the data. This segmentation allows the system to scale storage capacity while enabling targeted queries that can access only specific partitions rather than scanning all data, thus resolving the contradiction between storage capacity and query execution time.
Solution Approach 2:
The patent introduces an intermediary mechanism (partition elimination logic and indexing structures) that mediates between the query request and the physical storage partitions. This intermediary determines which partitions are relevant to the query and eliminates unnecessary partition accesses, preventing the time loss that would otherwise occur from searching all partitions.
2Speed
If data is copied and restructured into a useful organization, then query performance is improved, but write operations become slower due to additional data manipulation
Solution Approach 1:
The patent performs preliminary organization of data into partitions during the data loading and insertion phase, rather than restructuring during queries. Data is pre-grouped into meaningful partitions based on the partitioning scheme, so that when queries execute, they can directly access relevant partitions without requiring additional data copying or restructuring operations.
Solution Approach 2:
The patent implements a dynamic partitioning strategy where the system can adaptively determine the optimal partitioning approach based on query patterns and data characteristics. This allows the system to optimize for query performance when needed while minimizing write overhead by selecting appropriate partitioning strategies that balance read and write requirements.
3Reliability
If the entire data set is searched to satisfy a request, then query completeness is ensured, but execution time increases unnecessarily when data is partitioned
Solution Approach 1:
The patent implements feedback mechanisms through indexing structures that provide information about which partitions contain data relevant to query conditions. The query optimizer uses this feedback to make informed decisions about partition elimination, ensuring that only necessary partitions are accessed while maintaining query completeness. This resolves the contradiction by providing the feedback needed to balance reliability and efficiency.
Solution Approach 2:
The patent replaces the mechanical approach of sequentially searching all partitions with an intelligent selection mechanism that uses indexes and partition elimination logic to identify and access only the relevant partitions. This substitution maintains query completeness by ensuring all relevant data is found, while dramatically improving execution efficiency by avoiding unnecessary partition scans.
Data Source
AI summary
A method, computer program, and database system for performing a request on a table associated with an index are disclosed. A database contains a table, an index, and a partition elimination list and accepts a query. A table has one or more rows stored in one or more partitions. The index contains index entries, each of which references a row in a partition, where the row is in the table. The query has a first and a second condition. The partition elimination list is populated with partitions based on the first condition of the query. Only index entries that are identified by the request because of the second condition and that reference partitions that are not eliminated by the partition elimination list are read.