Index Backbone Join for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
As data volumes grow in relational databases, query optimizers face challenges in returning result sets quickly due to inefficient access plans, especially when dealing with medium selection criteria across multiple tables, leading to slower response times and increased data retrieval costs.
Innovation Solution
The index backbone join technique filters data using index entries before accessing tables, reducing unnecessary data reads by applying query criteria to index entries first, thereby minimizing table access and improving query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the query optimizer uses traditional table scan or index scan methods for medium selection criteria, then it can handle queries with multiple tables, but it retrieves a large absolute number of rows resulting in slower response times
Solution Approach 1:
The patent segments the data retrieval process into two distinct phases: first retrieving only row identifiers (rowids) from indexes, then selectively retrieving full row data. This segmentation allows the system to separate the filtering operation from the data retrieval operation, significantly reducing the amount of data that needs to be processed and transferred.
Solution Approach 2:
The patent applies preliminary filtering actions by executing the query criteria against index entries before accessing the actual table data. The query optimizer first identifies matching rowids through index scans, then uses only those rowids to retrieve specific rows from tables. This preliminary action eliminates the need to retrieve and process entire tables or large portions of them.
2Ease of operation
If the database reads entire data blocks containing multiple rows, then it simplifies the reading mechanism, but it increases the number of rows retrieved including those not matching criteria
Solution Approach 1:
The patent extracts only the essential information (row identifiers) from the index structures, separating this metadata from the full row data stored in tables. By taking out just the rowids needed for filtering, the system avoids reading unnecessary full row data, reducing I/O operations and processing overhead while maintaining the simplicity of block-based reading for the actual table access.
3Device complexity
If the query optimizer cannot combine filter criteria across different tables, then it simplifies the query processing logic, but it results in reading unnecessary data from the first table
Solution Approach 1:
The patent merges the filtering operations across multiple tables by combining the results of index scans from different tables through a hash join operation. The rowids from the first table's index and the second table's index are matched and combined based on the join condition, allowing the system to effectively apply multiple filter criteria simultaneously without increasing processing complexity.
Data Source
AI summary
Techniques described herein perform an index backbone join of data that is contained within two or more tables. Significantly, key data are selected from the indices constructed on the tables, and such data are filtered by the query-indicated criteria, before any data is selected from the tables themselves. Row identifiers of the rows remaining after the index filtering has been performed are then used to select the qualifying rows (only) from the tables. Data selected from the tables is joined to produce query results. Because all of the filtering is performed based on index entries prior to any table access, and because index access is typically much faster than table access, queries whose results require very large quantities of data from multiple tables can be performed much more quickly.