XML Table Cardinality Estimation via Path Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Query optimizers face challenges in accurately estimating the cost of query execution plans for queries involving path-based table functions in XML databases, as they often rely on default cardinality values, which can lead to sub-optimal plan selection due to the lack of accurate cardinality information for tables generated by these functions.
Innovation Solution
The solution involves using path statistics to estimate the cardinality of result sets produced by path-based table functions, by collecting and storing unique path expressions and their corresponding node counts for master base tables, allowing the query optimizer to determine the actual cardinality of virtual tables and improve the estimation of query execution costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If default cardinality values are used for path-based table functions, then the query optimizer can execute plans without additional statistics collection, but the cardinality estimates are inaccurate leading to sub-optimal plan selection
Solution Approach 1:
The system performs preliminary action by collecting path statistics (node counts for various path expressions) from the XML data during a pre-processing phase, storing them in a statistics table before query execution. This allows the query optimizer to access accurate pre-computed cardinality estimates without performing complex calculations at query time, resolving the contradiction between accuracy and complexity.
Solution Approach 2:
The invention introduces an intermediary statistics collection mechanism that acts as a bridge between the XML data and the query optimizer. The statistics table serves as an intermediary structure that pre-computes and stores path-based cardinality information, allowing the optimizer to make accurate decisions without directly analyzing the complex XML data structure during query execution.
2Measurement precision
If accurate path statistics are collected and stored for all path expressions, then cardinality estimates improve, but the storage requirements and processing overhead increase
Solution Approach 1:
The system applies local quality by collecting and storing statistics only for the specific path expressions that are actually used in queries, rather than computing statistics for all possible paths in the XML data. The statistics table stores path expression-specific node counts, allowing accurate estimation for queried paths while avoiding unnecessary storage overhead for unused paths.
Data Source
AI summary
Techniques are provided for estimating the cardinality of a virtual result table that is produced by executing path-based table functions within a query, such as the XMLTABLE function. Some path-based table functions apply a path expression to input from a base table of XML documents to select rows to produce the result table. Path statistics are collected for the path expressions for the base table. The path statistics are used to estimate the cardinalities of the result table. The estimated cardinality of the result table is useful for estimating costs of query execution plans that are generated for the query.