Database Query Optimization Using Dictionary Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in handling aggregate queries, as they require additional objects like materialized views and in-memory columnar storage, which incur overhead and limitations due to memory constraints, and existing methods do not effectively utilize statistics from data dictionaries for query optimization.
Innovation Solution
The technique involves rewriting aggregate queries to directly target statistics maintained in data dictionary tables, allowing for the derivation of aggregate results without creating additional objects or loading data into volatile memory, by using pre-computed statistics for accurate or approximate answers, and maintaining accuracy indicators to determine reliable statistics for query execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If materialized views are used to answer aggregate queries, then query performance is improved, but device complexity and maintenance overhead increase
Solution Approach 1:
The patent extracts the aggregate query answering capability from the base table and relocates it to the data dictionary by using pre-computed statistics. Instead of creating materialized views that require maintenance, the system uses existing statistics objects to provide aggregate information, thereby eliminating the complexity of materialized view management while maintaining query performance benefits.
Solution Approach 2:
The patent creates a copy of aggregate information by pre-computing and storing statistics in the data dictionary. These statistics copies allow the system to answer aggregate queries without accessing the original base table data, enabling fast query response while avoiding the need for complex materialized view structures.
2Speed
If in-memory columnar storage is used for aggregate queries, then query processing speed improves, but memory requirements and device complexity increase
Solution Approach 1:
The patent uses lightweight statistics objects stored in the data dictionary instead of heavy in-memory columnar storage structures. These statistics are pre-computed and stored as compact representations that can be quickly accessed during query processing, providing fast aggregate query performance without requiring large amounts of volatile memory.
Solution Approach 2:
The patent performs preliminary computation of aggregate statistics and stores them in the data dictionary before queries are executed. This pre-computation allows the system to answer aggregate queries by simply retrieving pre-computed statistics rather than performing complex aggregations in memory during query execution, significantly reducing memory requirements.
3Productivity
If statistics are maintained in data dictionaries, then query optimization improves, but measurement precision and reliability of aggregate results may be compromised
Solution Approach 1:
The patent incorporates feedback mechanisms through the accuracy indicator that monitors the freshness and reliability of statistics. When statistics become outdated or inaccurate due to data changes, the system can detect this through the accuracy indicator and adjust its behavior accordingly, ensuring that only reliable statistics are used for query optimization and aggregate result computation.
Solution Approach 2:
The patent makes the statistics system dynamic by introducing accuracy indicators that can change over time as data is modified. The accuracy indicator allows the system to adapt to changing data conditions and dynamically determine when statistics are still valid and when they need to be updated or discarded, maintaining measurement precision despite data changes.
Data Source
AI summary
Techniques are described for responding to aggregate queries using optimizer statistics already available in the data dictionary of the database in which the database object targeting by the aggregate query resides, without the user creating any additional objects (e.g. materialized views) and without requiring the objects to be loaded into volatile memory in a columnar fashion. The user query is rewritten to produce a transformed query that targets the dictionary tables to form the aggregate result without scanning the user tables. “Accuracy indicators” may be maintained to indicate whether those statistics are accurate. Only accurate statistics are used to answer queries that require accurate answers. The accuracy check can be made during runtime, allowing the query plan of the transformed query to be used regardless of the accuracy of the statistics. For queries that request approximations, inaccurate statistics may be used so long as the statistics are “accurate enough”.