Nearest Match Index Traversal for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems are inefficient when retrieving a single row with a value nearest to a target value, as they often require full table scans or multiple queries, which are cumbersome and performance-intensive, especially when the data distribution is unknown.
Innovation Solution
A new operator, 'NEAREST TO,' is defined to find rows in a table whose values are nearest to a target value by traversing an index once, allowing for efficient retrieval of rows or a set of rows with key values closest to the target value, even if the search column is not an index key, thereby reducing the need for full table scans and multiple queries.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If a full table scan is performed to find the nearest match, then all rows are examined ensuring the correct result is found, but the data retrieval time and computational resources increase significantly
Solution Approach 1:
The patent segments the search space by utilizing existing database indexes. Instead of scanning the entire table, the query is divided into multiple index range scans that cover different portions of the data. Each index range scan operates on a segmented portion of the table defined by index key ranges, allowing the system to find nearest matches efficiently without examining all rows.
2Measurement precision
If multiple separate queries are executed to find nearest matches above and below a target value, then the nearest match can be found, but the query complexity and number of index accesses increase
Solution Approach 1:
The patent merges multiple search operations into a single unified index range scan. Instead of executing separate queries to find values above and below the target, the system combines these operations by performing one index range scan that retrieves candidate rows from both directions. The ordering by distance metric then combines the results, eliminating the need for multiple separate index accesses and reducing query complexity.
Solution Approach 2:
The patent creates a universal query mechanism that handles both finding values greater than and less than the target within a single operation. The index range scan is designed to be multi-functional, capable of retrieving candidates from both sides of the target value simultaneously, and the distance-based ordering provides a universal method for identifying the nearest match regardless of direction.
3Productivity
If an index is created on the search column to speed up retrieval, then query performance improves, but the index maintenance overhead and storage requirements increase
Solution Approach 1:
The patent applies partial action by utilizing existing indexes that may already exist on the table for other purposes. Instead of always creating new indexes on search columns, the system leverages available indexes when possible, performing index range scans on existing index structures. This approach provides speedup without incurring the full overhead of creating and maintaining new indexes, reducing storage requirements while still improving retrieval performance.
Data Source
AI summary
A technique for finding the nearest match in a computer storage system is provided. A query statement includes a new operator that indicates that a user desires to access a set of rows that contain a value nearest to a target value. An index is accessed that is based at least in part on a column reference included in the statement. The index comprises a plurality of leaf nodes, where each leaf node comprises one or more entries, where each entry contains a key value, corresponding to the column reference, and a reference to a row in a table. Because leaf nodes in an index are ordered and linked to one another, a portion of the index need only be scanned once. The set of rows from the table are returned that are referenced by one or more entries whose column values are nearest to the target value.