Global Index Repartitioning Operator for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems face inefficiencies when processing queries that filter data based on columns not aligned with the partitioning key, as they must search all partitions, leading to computational inefficiencies, especially in systems lacking support for global indexes.
Innovation Solution
Implementing a global index with a repartitioning operator that generates an asymmetric replica of the source table, partitions it based on the query column, and prunes the table to focus searches on relevant partitions, allowing efficient querying without accessing unnecessary partitions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the database searches all partitions for queries filtering on non-partitioning columns, then complete data coverage is achieved, but computational efficiency deteriorates
Solution Approach 1:
The patent creates a global index table that segments the data by the query column (first column) while maintaining references to the original partitioned table. This segmentation allows the system to divide the search space into relevant partitions based on the query column values, avoiding full-table scans across all partitions while ensuring complete data coverage for the queried column.
Solution Approach 2:
The global index table acts as an intermediary between the query and the partitioned source table. It contains the query column values and corresponding partition identifiers, serving as a mediator that directs queries to only the relevant partitions without requiring access to all partitions, thus maintaining data coverage while improving efficiency.
2Productivity
If a global index is implemented to improve query efficiency on non-partitioning columns, then query execution speed improves, but device complexity increases
Solution Approach 1:
The patent creates a global index table that is a simplified copy or projection of the partitioned table, containing only the query column (first column) and partition identifier columns. This copying approach provides the necessary indexing capability to improve query execution speed while keeping the additional structure relatively simple and manageable.
Solution Approach 2:
The global index table is pre-computed and maintained in advance, containing pre-calculated partition identifiers for each value in the query column. This preliminary action allows queries to directly use the pre-computed index without performing complex runtime analysis, improving execution speed while the index maintenance overhead remains manageable.
Data Source
AI summary
A global index with a repartitioning operator is provided. A method for executing a query includes receiving a query including a request to search a first table based on a first value in a first column. The method may include generating a second table by partitioning the second table based on the first column and including, in the second table, a partition identifier and a reference row identifier. The method may include pruning the second table such that a row corresponding to the first value remains in the second table. The method may include repartitioning the row to a stream based on the partition identifier. The method may include executing the query by at least searching the partition of the first table based on the stream and the row identifier of the row. Related systems and articles of manufacture are provided.