Database Sampling Predicate for Query Execution Cost Reduction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Sampled queries in database management systems face challenges in reducing execution costs, especially when dealing with complex queries and joins, as existing methods either fail to effectively sample early in the query process or require excessive computing resources.
Innovation Solution
A method is introduced that generates a subset of entries from a larger table's index based on an input query and applies a sampling predicate to reduce the subset size, proportionally reducing the number of rows retrieved and execution costs while maintaining the ability to examine trends in data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If sampling is applied early in the query process to reduce execution cost, then query performance improves, but sampling does not commute with many query operations and may result in few or no records output
Solution Approach 1:
The patent applies sampling early in the query execution process, before join operations are performed. By sampling the fact table first and then performing joins on the sampled data, the system achieves both early sampling benefits and correct join semantics, resolving the contradiction between early sampling and operational commutativity
Solution Approach 2:
The patent segments the query execution into distinct phases: first sampling the fact table independently, then performing dimension table joins on the sampled result. This segmentation allows sampling to be applied at the optimal point without interfering with join operations, maintaining both productivity and reliability
2Measurement precision
If the complete join result is evaluated and then sampled, then sampling accuracy is maintained, but a large number of computing resources are consumed
Solution Approach 1:
Instead of evaluating the complete join result first and then sampling, the patent performs sampling as a preliminary action on the fact table before the join operation. This approach maintains sampling accuracy while dramatically reducing computing resource consumption by avoiding the generation and processing of the complete join result
Solution Approach 2:
The patent applies partial action by sampling only the necessary portion of the fact table (e.g., 10% sample) rather than evaluating the complete result set. This partial evaluation followed by join operations achieves the desired sampling accuracy with significantly reduced computational resources
3Productivity
If sampling rate is divided and sampling is applied to individual relations, then execution cost is reduced, but few or no records are output
Solution Approach 1:
The patent merges the sampling operation with the join operation by applying the sampling predicate to the fact table first, then performing the join with dimension tables on the sampled result. This combination ensures that sufficient records are preserved through the sampling process while still achieving execution cost reduction, avoiding the problem of few or no output records
Data Source
AI summary
A method, system and computer program product for reducing the execution cost of sampled queries of tables in a database. A subset of entries comprising row identifiers or key values based on an input query of data in the tables of the database is generated. A sampling predicate to reduce the number of entries in the subset is generated. The sampling predicate is applied to the subset to reduce the number of entries in the subset. The data in the database corresponding to the reduced subset is retrieved from the database.