Sampled Tables with Hash Indexes for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems face inefficiencies in query optimization due to the lack of indexing for less common query filters, leading to increased computational burden and stale data issues with pre-queries and on-the-fly sampling methods.
Innovation Solution
Implementing sampled tables with a hash function to continuously maintain a random sample of primary keys, using a primary key index and an ordered hash index to optimize queries without inducing latency and ensuring data accuracy.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If table indices are created for common query columns, then query optimization is improved, but columns with less common filters cannot be indexed, leading to increased computational load
Solution Approach 1:
The system performs preliminary sampling of database records and pre-computes indices on the sampled data before queries are executed. This allows the query optimizer to have pre-prepared statistical information about less common columns, enabling optimization without the computational burden of indexing every column in the full dataset.
Solution Approach 2:
Instead of creating indices on the entire database table, the system creates a copied sampled version of the data with pre-computed indices. This sampled copy contains representative data that provides optimization information for less common query columns without requiring full-table indexing, thus reducing computational load while maintaining query optimization capability.
2Productivity
If pre-queries are used to collect data for query optimization, then optimization information is obtained, but the data becomes stale and does not reflect current database state
Solution Approach 1:
The system continuously maintains sampled tables and updates them as new records are inserted into the database. Rather than performing periodic pre-queries that result in stale data, the sampling process runs continuously, ensuring the optimization data always reflects the current database state without interruption to query processing.
Solution Approach 2:
The sampled tables automatically update themselves through triggers that fire on database insert operations. When new records are added to the main database tables, corresponding samples are automatically added to the sampled tables, maintaining data freshness without requiring external intervention or periodic refresh operations.
3Adaptability or versatility
If on-the-fly sampling is performed during query execution, then query optimization information is obtained, but latency is introduced to query processing
Solution Approach 1:
The system pre-computes and stores sampling information in dedicated sampled tables before queries are executed. This eliminates the need to perform sampling operations during query execution, as all the statistical information is already prepared and available in the sampled tables, thus avoiding latency while maintaining adaptability to different query types.
Solution Approach 2:
The system uses lightweight sampled tables that contain only essential sampling information needed for query optimization. These sampled tables are inexpensive to maintain and update, providing quick access to optimization data without the overhead of complex on-the-fly sampling computations during query execution.
Data Source
AI summary
In accordance with disclosed embodiments, there are provided systems and methods for implementing and maintaining sampled tables in a database system. According to a particular embodiment such a system includes a processor and a memory to execute instructions at the system; a database to store database records; a query interface to submit queries to the database and receive results from the database; triggers to initiate a hash function on database insert transactions, the hash function to hash a primary key of each database record to be inserted into the database to determine whether a first hash value of the primary key of the database record to be inserted satisfies a sampling condition; a sample table to store a sample record corresponding to any database record to be inserted into the database which satisfies the sampling condition based on the hash function; a primary key index providing a first index of the primary keys of the sample records stored in the sample table; and an ordered hash index providing a second index on a second hash value corresponding to a second hash of the primary key of the sample records stored in the sample table ordered numerically by the second hash value. Other related embodiments are disclosed.