Database Partition Validation via Stratified Sampling
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database table partitioning methods are resource-intensive and time-consuming, especially as the number of candidate partition schemes and queries grows, leading to impractical resource costs and potential incorrect recommendations due to exhaustive enumeration.
Innovation Solution
Implementing stratified random sampling to validate candidate partition schemes using a smaller, statistically representative sample of queries, allowing for efficient evaluation of partitioning schemes while maintaining confidence and accuracy.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If exhaustive enumeration of candidate partition schemes is used, then completeness of evaluation is improved, but resource intensity and time consumption worsen
Solution Approach 1:
The patent applies partial action by evaluating only a statistically significant sample of queries rather than the entire workload. By using stratified random sampling to select a representative subset of queries, the system achieves sufficient evaluation completeness without the prohibitive resource costs of exhaustive enumeration, directly resolving the contradiction between completeness and resource intensity
Solution Approach 2:
The patent changes the parameter of evaluation scope from 100% of queries to a statistically determined sample size. By calculating the minimum sample size needed to achieve a confidence interval of at least 90%, the system transforms the exhaustive evaluation approach into a sampled evaluation approach that maintains reliability while dramatically reducing resource intensity
2Reliability
If exhaustive enumeration of candidate partition schemes is used, then completeness of evaluation is improved, but time consumption worsens
Solution Approach 1:
The patent applies partial action by evaluating only a statistically significant sample of queries rather than the entire workload. By using stratified random sampling to select a representative subset of queries, the system achieves sufficient evaluation completeness without the prohibitive time costs of exhaustive enumeration, directly resolving the contradiction between completeness and time consumption
Solution Approach 2:
The patent changes the parameter of evaluation scope from 100% of queries to a statistically determined sample size. By calculating the minimum sample size needed to achieve a confidence interval of at least 90%, the system transforms the exhaustive evaluation approach into a sampled evaluation approach that maintains reliability while dramatically reducing time consumption
3Use of energy by moving object
If a smaller sample of queries is used for validation, then resource intensity is reduced, but measurement precision worsens
Solution Approach 1:
The patent applies segmentation by dividing the query workload into strata based on table access patterns. This stratification ensures that the sample contains proportional representation of different query types, maintaining measurement precision while using fewer queries. The segmentation prevents bias that would occur with simple random sampling and preserves evaluation accuracy
Solution Approach 2:
The patent substitutes mechanical exhaustive evaluation with statistical sampling theory. By applying statistical principles to determine sample size and stratification, the system replaces the mechanical approach of running all queries with a mathematically grounded sampling approach that maintains precision while reducing resource intensity
4Productivity
If the number of queries in workload is reduced, then productivity is improved, but reliability worsens
Solution Approach 1:
The patent applies preliminary action by pre-calculating the minimum sample size needed to achieve the desired confidence interval before performing the evaluation. This preliminary statistical analysis ensures that the reduced query set maintains sufficient reliability for accurate partitioning recommendations, resolving the contradiction between productivity and reliability
Solution Approach 2:
The patent incorporates feedback through confidence interval calculation. By continuously monitoring whether the sample size achieves the minimum required for the desired confidence level, the system adjusts the evaluation process to maintain reliability while maximizing productivity through efficient use of the sampled queries
Data Source
AI summary
A method, system, and computer program product for validating database table partitioning from partition advisors. The method commences by receiving a workload comprising a plurality of queries, then analyzes the queries to determine stratification buckets based on the usage of tables in the queries. Further analysis of the queries results in assigning the queries into one or more of the stratification buckets from which buckets a number n of queries (n being smaller than the total number of queries in the received workload) are drawn from the stratification buckets to form a representative workload having a confidence interval C and a margin of error M. Now, having a representative workload that is smaller, yet statistically representative of the received workload, a computer evaluates each of a plurality of partition schemes using the representative workload to determine an optimal partitioning scheme. The confidence interval C can be increased or decreased.