Database Query Statistics Inheritance via Join Index Copying
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In large commercial database systems, inefficient or inaccurate query statistics can adversely impact performance, especially when dealing with extremely large base tables, and existing methods for statistics collection and inheritance are either costly or lack flexibility, particularly when dealing with superset or subset query conditions.
Innovation Solution
A method for database query statistics inheritance, where statistics are acquired for a database object with a subset of rows and inherited by the base table, using a statistics resolver to combine selectivity estimates and determine optimal query plans, filtering out irrelevant objects and using histograms to adjust for row filtering conditions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If statistics are collected for extremely large base tables, then accurate query planning is achieved, but the cost of collecting statistics becomes prohibitively expensive
Solution Approach 1:
The patent creates a copy of statistics from a join index (which has a manageable size) and applies it to the base table. Instead of collecting statistics directly from the extremely large base table, the system collects statistics from the smaller join index and reuses them, avoiding the prohibitively expensive statistics collection process while still providing accurate statistics for query optimization.
2Ease of operation
If general statistics are used for large base tables, then query processing is simplified, but the costs become too high and query plan optimization is inadequate
Solution Approach 1:
The join index serves as an intermediary between the base table and the query optimizer. It contains a manageable subset of rows with associated statistics that can be easily collected and used. The system uses this intermediary structure to provide accurate cost estimates without directly processing the entire base table, thus maintaining both simplicity and accuracy.
3Measurement precision
If statistics are inherited from join index to base table, then accurate selectivity estimation is achieved, but handling superset or subset row conditions becomes complex
Solution Approach 1:
The patent applies local quality by treating different row condition scenarios (superset, subset, equal) with specific tailored logic. When the join index rows are a subset of base table rows, the system adjusts selectivity estimates by considering the filtering conditions. When they are equal, direct inheritance is used. This localized approach to different scenarios manages complexity while maintaining accuracy.
Data Source
AI summary
Techniques for query statistics inheritance are provided. Statistics for a database are used to determine selectivity estimates for sparse joins and tables being joined together within a given query. These statistics are inherited up to the given query along with the selectivity estimates and provided to a database optimizer to use when developing query plans and selecting an optimal query plan for the given query.