Temporal Join Elimination in Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Temporal join operations in databases are complex due to the lack of effective referential integrity constraints, making it difficult to simplify joins involving temporal tables, unlike non-temporal tables where referential integrity simplifies joins.
Innovation Solution
A computer-implemented method that determines if a query involving a SEQUENCED join between a temporal parent table and a temporal child table satisfies specific temporal join elimination criteria, allowing the elimination of the join to produce an optimized query, using mechanisms like coalescing rows with the same values and applying a NORMALIZE ON MEETS clause.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If temporal join operations are performed between temporal parent and child tables, then query results can be obtained with temporal accuracy, but query complexity and computational load increase significantly
Solution Approach 1:
The patent extracts and eliminates the temporal parent table from the query when referential integrity conditions are met, removing the need to perform complex temporal join operations. The optimizer detects that the foreign key column in the child table already provides the necessary temporal linkage, allowing the parent table to be taken out of the query execution plan entirely.
Solution Approach 2:
The patent establishes referential integrity constraints and temporal uniqueness conditions in advance (before query execution) to enable join elimination. By pre-defining that foreign key columns must be temporal-unique and that parent table columns referenced in join conditions must be temporal-unique, the system prepares the structural conditions needed to simplify future temporal queries without performing actual join operations.
2Reliability
If temporal join operations are performed between temporal parent and child tables, then accurate temporal relationships are maintained, but computational load and execution time increase
Solution Approach 1:
The patent removes the temporal parent table from the query execution plan when referential integrity conditions are satisfied, eliminating the computational overhead of temporal join operations while preserving temporal relationship accuracy through pre-established foreign key constraints and temporal uniqueness guarantees.
Solution Approach 2:
The foreign key column in the child table serves dual purposes: it maintains referential integrity relationships and simultaneously provides temporal linkage information. This self-service mechanism allows the foreign key to fulfill both relational and temporal functions without requiring additional join operations with the parent table.
3Device complexity
If referential integrity constraints are enforced in temporal tables, then join simplification becomes possible, but data insertion and update operations become more restrictive
Solution Approach 1:
The patent changes the uniqueness parameter requirement from standard uniqueness to temporal uniqueness for foreign key columns. Instead of requiring foreign key values to be unique across all time, the system requires them to be unique at any given temporal moment, allowing historical changes while maintaining the ability to eliminate joins when conditions are met.
4Productivity
If the optimizer eliminates joins from temporal queries, then query execution efficiency improves, but the requirements for join elimination become more stringent
Solution Approach 1:
The patent modifies the uniqueness parameter requirement to temporal uniqueness, which enables join elimination in temporal contexts where standard uniqueness would fail. This parameter change allows the optimizer to eliminate joins more frequently while maintaining correctness, as temporal uniqueness accommodates the temporal nature of data changes.
Data Source
AI summary
A computer receives a query for optimization. The query includes a SEQUENCED join between a temporal parent table and a temporal child table. The parent table has one or more rows. The child table has one or more rows. The parent table has a Primary Key (“PK”) column having a value for each row. The parent table has a temporal dimension having a value for each row. The child table has a Foreign Key (“FK”) column having a value for each row. The child table has a temporal dimension. The child table's temporal dimension has a value for each row. The computer determines that the query, the parent table, the child table, and the join satisfy a set of CURRENT temporal join elimination criteria. The computer determines that the query, the parent table, the child table, and the join satisfy a set of SEQUENCED temporal join elimination criteria. In response, the computer eliminates the join from the query to produce an optimized form of the query. In further response, the computer executes the optimized form of the query to produce a result. The result has one or more rows. The result has one or more projected columns. The projected columns have values. The result has a temporal dimension. The result's temporal dimension has a value. In further response, the computer stores the result in a memory.