Efficient Identification of Entire Row Uniqueness in Relational Databases

• US 20090319541A1
• Filed: 06/19/2008
• Published: 12/24/2009
• Est. Priority Date: 06/19/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Jaiswal, Peeyush, Narayan, Naveen, Khatri, Vikram S., Vialpando, Burt L.
• Granted Patent: US 8,984,301 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2255 Hash tables
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

A method, system, and computer program product for efficiently comparing multiple columns of a row of a relational database to an incoming record. A computer creates a cryptographic sum for columns of a row of the relational database. The cryptographic sum is stored as a hidden column in the relational database. Logic may compare the cryptographic sum with an incoming cryptographic sum of entries in an incoming record. Logic may then determine if the incoming cryptographic sums differ from the corresponding cryptographic sums of rows of data of the relational database. When the two cryptographic sums are identical, the data of the incoming record is disregarded as an identical record that already exists. An entry of the incoming record may be added to the target table or updated within an existing record of the relational database when the cryptographic sum and the incoming cryptographic sum of that entry differ.

Claims

1. A method for efficiently identifying uniqueness of rows of a relational database, the method comprising:

creating a cryptographic sum of one or more rows of a target table of the relational database;

wherein the cryptographic sum contains a unique value representing the contents of a row of the target table;

receiving an incoming record;

selecting a row of the plurality of rows of the incoming record;

determining if the selected row contains an incoming cryptographic sum, wherein the incoming cryptographic sum contains a unique value representing the contents of a row;

in response to determining that the selected row contains the incoming cryptographic sum, comparing the incoming cryptographic sum of the selected row to the cryptographic sum of a similarly identified row of the target table; and

in response to the incoming cryptographic sum of the selected row being identical to the cryptographic sum of the similarly identified row, disregarding the selected row when updating the target table.

2. The method of claim 1, further comprising:

in response to the incoming cryptographic sum of the selected row not being identical to the cryptographic sum of the similarly identified row, determining if the selected row contains an incoming record ID, wherein the incoming record ID is an identification value of the selected row;

in response to determining that the selected row contains the incoming record ID, identifying the incoming record ID for the selected row;

identifying a record ID of one or more rows of the similarly identified row;

comparing the incoming record ID of the selected row with the record ID of similarly identified row; and

in response to the incoming record ID matching the record ID of the similarly identified row, updating contents of the similarly identified row with contents of the selected row.

3. The method of claim 2, further comprising, in response to the incoming record ID not being identical to the record ID of the similarly identified row, adding the selected row as a new row of the target table.

4. The method of claim 2, further comprising, in response to the selected row not containing the incoming record ID, adding the selected row as a new row of the target table.

5. The method of claim 1, further comprising, appending the cryptographic sum of one or more rows of the target table to a hidden column of the corresponding rows of the target table.

6. The method of claim 1, further comprising, in response to determining that the selected row does not contain the incoming cryptographic sum, calculating the incoming cryptographic sum for the selected row.

7. The method of claim 6, further comprising, storing the incoming cryptographic sum for the selected row in a hidden column of the selected row.

8. A computer comprising:

a processor;

a memory coupled to the processor;

processing logic for;

creating a cryptographic sum of one or more rows of a target table of the relational database;

wherein the cryptographic sum contains a unique value representing the contents of a row of the target table;

receiving an incoming record;

selecting a row of the plurality of rows of the incoming record;

determining if the selected row contains an incoming cryptographic sum, wherein the incoming cryptographic sum contains a unique value representing the contents of a row;

in response to determining that the selected row contains the incoming cryptographic sum, comparing the incoming cryptographic sum of the selected row to the cryptographic sum of a similarly identified row of the target table; and

in response to the incoming cryptographic sum of the selected row being identical to the cryptographic sum of the similarly identified row, disregarding the selected row when updating the target table.

9. The computer of claim 8, the processing logic further comprising logic for:

in response to the incoming cryptographic sum of the selected row not being identical to the cryptographic sum of the similarly identified row, determining if the selected row contains an incoming record ID, wherein the incoming record ID is an identification value of the selected row;

in response to determining that the selected row contains the incoming record ID, identifying the incoming record ID for the selected row;

identifying a record ID of one or more rows of the similarly identified row;

comparing the incoming record ID of the selected row with the record ID of similarly identified row; and

in response to the incoming record ID matching the record ID of the similarly identified row, updating contents of the similarly identified row with contents of the selected row.

10. The computer of claim 9, the processing logic further comprising logic for, in response to the incoming record ID not being identical to the record ID of the similarly identified row, adding the selected row as a new row of the target table.

11. The computer of claim 9, the processing logic further comprising logic for, in response to the selected row not containing the incoming record ID, adding the selected row as a new row of the target table.

12. The computer of claim 8, the processing logic further comprising logic for, appending the cryptographic sum of one or more rows of the target table to a hidden column of the corresponding rows of the target table.

13. The computer of claim 8, the processing logic further comprising logic for, in response to determining that the selected row does not contain the incoming cryptographic sum, calculating the incoming cryptographic sum for the selected row.

14. The computer of claim 13 the processing logic further comprising logic for, storing the incoming cryptographic sum for the selected row in a hidden column of the selected row.

15. A readable medium having a plurality of instructions embodied therein, wherein the plurality of instructions, when executed by a processing device, allows a machine to:

create a cryptographic sum of one or more rows of a target table of the relational database;

wherein the cryptographic sum contains a unique value representing the contents of a row of the target table;

receive an incoming record;

select a row of the plurality of rows of the incoming record;

determine if the selected row contains an incoming cryptographic sum, wherein the incoming cryptographic sum contains a unique value representing the contents of a row;

in response to determining that the selected row contains the incoming cryptographic sum, compare the incoming cryptographic sum of the selected row to the cryptographic sum of a similarly identified row of the target table; and

in response to the incoming cryptographic sum of the selected row being identical to the cryptographic sum of the similarly identified row, disregard the selected row when updating the target table.

16. The computer-readable medium of claim 15, the plurality of instructions further comprising instructions for enabling the machine to, in response to the incoming cryptographic sum of the selected row not being identical to the cryptographic sum of the similarly identified row, determine if the selected row contains an incoming record ID, wherein the incoming record ID is an identification value of the selected row;

in response to determining that the selected row contains the incoming record ID, identify the incoming record ID for the selected row;

identify a record ID of one or more rows of the similarly identified row;

compare the incoming record ID of the selected row with the record ID of similarly identified row; and

in response to the incoming record ID matching the record ID of the similarly identified row, update contents of the similarly identified row with contents of the selected row.

17. The computer-readable medium of claim 16, the plurality of instructions further comprising instructions for enabling the machine to, add the selected row as a new row of the target table.

18. The computer-readable medium of claim 16, the plurality of instructions further comprising instructions for enabling the machine to, in response to the selected row not containing the incoming record ID, add the selected row as a new row of the target table.

19. The computer-readable medium of claim 15, the plurality of instructions further comprising instructions for enabling the machine to, append the cryptographic sum of one or more rows of the target table to a hidden column of the corresponding rows of the target table.

20. The computer-readable medium of claim 15, the plurality of instructions further comprising instructions for enabling the machine to, in response to determining that the selected row does not contain the incoming cryptographic sum, calculate the incoming cryptographic sum for the selected row;

and

store the incoming cryptographic sum for the selected row in a hidden column of the selected row.