Hash-Based Histogram Generation for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems face challenges in generating histograms for query optimization due to the memory and disk-intensive nature of sorting data, which can interfere with other database operations and is not feasible during peak usage hours.
Innovation Solution
A hash-based approach is used to generate histograms, where unique descriptors are stored in a hash table, allowing for efficient analysis of data distribution without the need for sorting, and utilizing multi-threading to process data in parallel, reducing the load on memory and disk resources.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If sorting is performed to generate histograms, then data distribution analysis accuracy is improved, but memory and disk resource consumption increases and database operations are interfered with
Solution Approach 1:
The patent extracts the sorting operation from the histogram generation process and replaces it with a hash-based approach. Instead of sorting all column data to obtain cell boundaries, the system uses hash functions to directly compute histogram cell boundaries from unsorted data, eliminating the resource-intensive sorting step while maintaining histogram accuracy for query optimization
Solution Approach 2:
The patent replaces the mechanical sorting process with a computational hash-based approach. Rather than physically sorting data in memory and spilling to disk, the system uses hash functions to map data values to histogram cells, substituting a computationally efficient operation for a resource-intensive mechanical process
2Productivity
If sorting is performed during off-hours to avoid interference, then database operation efficiency is maintained, but histogram generation timeliness deteriorates
Solution Approach 1:
The patent enables histogram generation to be performed as a preliminary action during peak hours rather than waiting for off-hours. The hash-based approach allows the system to pre-compute histogram statistics on-demand during active database operations, eliminating the delay caused by waiting for off-hour windows while maintaining database performance
3Speed
If memory cache is used to store frequently accessed data, then data access speed is improved, but storage cost increases and memory resources are consumed
Solution Approach 1:
The patent applies local quality by storing only the essential histogram statistics in memory rather than caching entire data pages. The hash-based histogram computation stores compact summaries (cell boundaries, frequencies) that fit efficiently in memory, providing fast access for query optimization without consuming excessive memory resources compared to full page caching
Data Source
AI summary
Disclosed herein are system, method, and computer program product embodiments for generating a histogram used to optimize a query plan. An embodiment operates by initializing a first thread and a second thread, such that the first thread processes a first section of a column and the second thread processes a second section of the column, concurrently with the first thread. The first thread generates a first hash table and the second thread generates a second hash table. The first and second hash tables represent data distribution stored in the respective first and second sections of the column. The first and second tables are merged into a histogram that represents data distribution in the column.