Techniques for query statistics inheritance
AssigneeTeradata US, Inc.
InventorsRama Krishna Korlapati, Haiyan Chen
Life of this patent
Estimating the term…
Abstract
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.
Claims6
A method implemented and programmed within a non-transitory computer-readable storage medium and processed by a processor, the processor configured to execute the method, comprising:
- receiving, via the processor, a query having two tables joined on a column with skewed values;
- acquiring, via the processor, a set of statistics available for the join;
- and
- resolving a priority order for selecting particular portions of the set of statistics after grouping histograms associated with the two tables joined on the column and using the grouped histograms to resolve the priority order, and wherein resolving further includes generating the priority order as 1) firstly identifying a histogram for a database object having a smallest row count;
- 2) secondly identifying a histogram having a highest sample percentage;
- 3) thirdly identifying a histogram having a latest version;
- and 4) fourthly a histogram collected most recently, wherein generating further includes checking each histogram in the priority order to ensure a value range survives after the join, and wherein checking further includes ensuring range conditions on a joined column for the join and row filtering conditions of the database object in the selected histogram.
A processor-implemented system, comprising:
- a memory programmed with a statistics resolver for execution on a processor;
- and
- the memory also programmed with a statistics selector for execution on the processor;
- the statistics selector is configured to permit resolved statistics for a database object to be inherited to a base table within a query, and the statistics selector is configured to resolve a priority order for selecting particular portions of a set of statistics after grouping histograms associated with two tables being joined on a column with skewed values within the query and using the grouped histograms to resolve the priority order, and wherein resolving further includes generating the priority order as 1) firstly identifying a histogram for a database object having a smallest row count;
- 2) secondly identifying a histogram having a highest sample percentage;
- 3) thirdly identifying a histogram having a latest version;
- and 4) fourthly a histogram collected most recently, wherein generating further includes checking each histogram in the priority order to ensure a value range survives after the join, and wherein checking further includes ensuring range conditions on a joined column for the join and row filtering conditions of the database object in the selected histogram.
Dependencies are inferred from the claim text.