Database Row Comparison Using Hash Indicia for Order-Independent Matching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for comparing databases with order-sequenced files/tables face challenges due to issues like differing primary keys, partial record comparisons, and changes in physical or logical positions, which can lead to inconsistencies and errors in data replication.
Innovation Solution
An automated method is introduced that generates indicia for each row in the source and target database tables, compares these indicia without regard to row order, and identifies mismatches to produce a table of differences. This method can optionally repair the target database by inserting, updating, or deleting records based on the identified mismatches.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional row-by-row comparison methods are used to compare database tables, then the comparison process is simple to implement, but it produces incorrect results when rows are in different orders or have different primary keys
Solution Approach 1:
The patent introduces an intermediary hashing mechanism that converts database rows into hash values. Instead of directly comparing rows, the system generates hashes for each row in both tables, then compares these hash values. This intermediary step allows accurate comparison without being affected by row order or primary key differences, resolving the contradiction between accuracy and complexity.
Solution Approach 2:
The patent transforms the comparison problem by changing the parameter being compared from raw row data to hash values of row data. By applying a hashing function to convert rows into comparable hash values, the system can accurately determine table equality without being influenced by row ordering or primary key variations, thus improving measurement precision while managing complexity through parameter transformation.
2Reliability
If all rows in database tables are compared to ensure data consistency, then data accuracy is improved, but the time and computational resources required increase significantly
Solution Approach 1:
The patent segments the database tables into individual rows and converts each row into a separate hash value. This segmentation allows the comparison process to work with discrete, manageable units rather than entire tables, enabling efficient processing while maintaining comprehensive data consistency verification across all rows.
Solution Approach 2:
The patent creates hash copies of the actual row data. Instead of directly comparing the original row data which can be large and complex, the system generates compact hash value copies that represent each row. Comparing these hash copies is computationally much more efficient while still providing reliable data consistency verification, thus reducing comparison time while maintaining reliability.
3Adaptability or versatility
If database tables are compared without considering row order, then the comparison is more robust to replication variations, but it becomes difficult to identify specific mismatched rows
Solution Approach 1:
The patent uses hash values as distinctive identifiers for each row, analogous to giving each row a unique 'color' or label. When comparing tables, mismatched rows are identified by their hash values, making them easily detectable despite the absence of row order consideration. This approach maintains comparison robustness while solving the identification problem through distinctive hashing markers.
Data Source
AI summary
A method and system are provided for comparing a first database table and a second database table with each other to produce a table of differences therebetween. The first database table and the second database table include one or more subsets of data that are intended to match each other, and the first database table and the second database table each include multiple rows. Indicia is generated for each of the multiple rows of a subset of the first database table, and each of the multiple rows of a subset of the second database table. The generated indicia for each of the multiple rows of the subset of the first database table are compared with the generated indicia for the subset of each of the multiple rows of the subset of the second database table, on a row-by-row basis, wherein the comparing occurs without regard to an original order of the rows in either the first database table or the second database table. Any generated indicia for each of the multiple rows of the subset of the first database table that are not present in the generated indicia for each of the multiple rows of the subset of the second database table are identified as a first set of mismatched indicia. Any generated indicia for each of the multiple rows of the subset of the second database table that are not present in the generated indicia for each of the multiple rows of subset of the first database table are identified as a second set of mismatched indicia. The table of differences is populated with the generated indicia. The second database table may be repaired by using at least one of the first set of mismatched indicia and the second set of mismatched indicia.