Database Query Cost Estimation via Tree Index Traversal
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems using histograms for cost-based query optimization face efficiency and accuracy issues due to limitations in histogram granularity, leading to incorrect cost estimations and increased query costs.
Innovation Solution
The use of balanced or semi-balanced tree indexes for database queries, which allow for precise cost estimation by traversing the tree structure and maintaining a running estimate of entries less-than and greater-than a specified key, effectively handling both simple and composite queries.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If histograms are used for cost-based query optimization, then query cost estimation can be performed, but accuracy is compromised due to histogram granularity limitations
Solution Approach 1:
The patent replaces the histogram-based statistical estimation mechanism with a tree index traversal mechanism. Instead of using histograms that require granularity tuning and produce approximation errors, the system traverses balanced or semi-balanced tree indexes to compute exact cost estimates by counting actual data entries during traversal, thereby substituting a mechanical approximation system with a precise computational approach
Solution Approach 2:
The patent creates a virtual copy of the tree traversal process for cost estimation purposes. By simulating the query execution path through the tree index structure and counting entries without actually executing the query, the system obtains accurate cost estimates while avoiding the overhead of actual query processing, effectively using a computational copy to predict performance
2Measurement precision
If higher resolution histograms are used, then cost estimation accuracy improves, but computational overhead and efficiency deteriorate
Solution Approach 1:
The patent substitutes the computationally intensive histogram construction and processing mechanism with a lightweight tree traversal approach. The tree index structure already exists in the database system, and traversing it for cost estimation requires minimal additional computation compared to building and processing high-resolution histograms, thereby achieving accuracy without the associated efficiency penalties
Solution Approach 2:
The patent performs cost estimation by pre-traversing the tree index structure before actual query execution. This preliminary action of counting entries during tree traversal provides accurate cost information in advance, allowing the query optimizer to make informed decisions without the computational burden of high-resolution histogram processing during query optimization
3Productivity
If lower resolution histograms are used, then computational overhead decreases, but cost estimation accuracy deteriorates
Solution Approach 1:
The patent replaces the histogram approximation mechanism entirely with direct tree index traversal. This substitution eliminates the fundamental limitation of histogram resolution, as the tree traversal approach computes exact counts of data entries matching query conditions regardless of any resolution parameter, thereby achieving both efficiency and accuracy simultaneously
4Measurement precision
If balanced tree indexes are used for cost estimation, then accuracy improves, but handling duplicate entries becomes complex
Solution Approach 1:
The patent applies local quality by treating duplicate entries at each tree node with special handling logic. When traversing the tree, the system identifies nodes containing duplicate keys and applies localized counting adjustments to ensure each duplicate entry is properly accounted for in the cost estimation, rather than applying a uniform approach throughout the entire tree structure
Solution Approach 2:
The patent incorporates feedback mechanisms in the tree traversal process to handle duplicates. During traversal, the system monitors for duplicate key values and adjusts the cost estimation accordingly by providing feedback about duplicate presence to the counting logic, ensuring accurate cost calculation while maintaining awareness of data distribution characteristics
Data Source
AI summary
Described are systems and methods relating to database query optimization and query cost estimation. The approach is described in the context of searching balanced and semi-balanced tree indexes such as B-trees, B+ trees, and R-B trees. The described approach may be used for both simple and composite queries, and the described approach may be used for relational queries, i.e., where a variable is less than or greater than a certain value and the database is being used to find the set of records that satisfy the relation. Further, the described approach may be used for generalized N-ary tree queries and cost estimations.