Distributed Join Index for Log-Structured Database Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing techniques for building and maintaining join indices in databases are costly and inefficient, especially in log-structured database architectures, due to high maintenance costs and the inability to reuse join indices across different join conditions, and they do not effectively address the consistency of record identifiers across tables.
Innovation Solution
A method for building, maintaining, and querying join indices in a distributed, shared-nothing, log-structured database architecture that involves repartitioning data tables across multiple nodes, replicating data segments, and using a dictionary of join key values to facilitate efficient join operations while preserving record identifier consistency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If a join index is created for a specific join condition, then the join operation performance is improved, but the storage cost and maintenance cost increase because another join index must be created for each different join condition
Solution Approach 1:
The patent creates a universal join index structure that can serve multiple join conditions. Instead of creating separate join indexes for each join condition (e.g., NationKey=Supplier.NationKey and NationKey=Customer.NationKey), the system builds a single join index that can be reused across different join operations. This is achieved by organizing the index to accommodate multiple foreign key relationships, thereby reducing storage requirements while maintaining query performance.
Solution Approach 2:
The patent merges multiple join index requirements into a single unified index structure. By combining the indexing capabilities for different join conditions into one structure, the system eliminates redundant storage. The merged index maintains the ability to support various join operations simultaneously, reducing the total quantity of indexed data while preserving access efficiency.
2Speed
If a join index is created for a specific join condition, then the join operation performance is improved, but the maintenance cost increases because every modified row must be verified and join operations must be recomputed
Solution Approach 1:
The universal join index structure reduces maintenance overhead by eliminating redundant verification steps. When a row is modified, the system only needs to update the single unified index rather than verifying and recomputing multiple separate join indexes. This multi-functional index design ensures that maintenance operations are performed once rather than repeatedly across multiple index structures.
Solution Approach 2:
By merging multiple join index maintenance requirements into a single maintenance operation, the patent significantly reduces the time loss associated with index updates. The combined index structure allows the system to perform one verification and recomputation cycle instead of multiple separate cycles, thereby reducing maintenance time while preserving join operation performance.
3Reliability
If record identifiers are carefully defined to avoid collision in shared-nothing distributed architecture, then consistency between join index and original tables is maintained, but the complexity of identifier management increases
Solution Approach 1:
The patent introduces an intermediary mechanism for identifier management that mediates between the distributed nodes and the join index. This intermediary layer handles the complexity of identifier assignment and collision avoidance, providing a standardized interface that simplifies identifier management across the distributed system. The mediator ensures consistency without requiring complex identifier management logic at each node.
Solution Approach 2:
The patent segments the identifier space across distributed nodes in a systematic way that prevents collisions while simplifying management. By dividing the identifier namespace into manageable segments assigned to different nodes, the system maintains consistency without requiring complex global identifier management. Each node manages its own segment independently, reducing overall system complexity while ensuring global uniqueness.
Data Source
Figure 1~2
Figure 3
Figure 4~5
AI summary
A method and computer program for optimizing computation and data retrieval of join operations from a multi-node log-structure database system. The database system has one or more data tables each having a plurality of columns. A join index is created for one or more join key columns, with optionally one or more secondary columns, of one of the data tables. The join index is partitioned across at least one computing node, and each join index partition reproduces the log structure storage of the data table. The join index is maintained in a series of incremental operations by replicating the creation or deletion of data segments from the log structure storage of the data table to the join index partitions.