Transferring records between tables using a change transaction log

• US 8,682,859 B2
• Filed: 10/19/2007
• Issued: 03/25/2014
• Est. Priority Date: 10/19/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gutlapalli, Hari K., Kothari, Shirish K., Mehta, Suhas R., Pak, Wai
• Primary Examiner(s): Btit, Jacob F
• Assistant Examiner(s): Corbo, Griselle
• Application Number:
  US11/875,542
• Publication Number:
  US 20090106196A1
• Time in Patent Office: 2,349 Days
• Field of Search: 707/705, 707/667, 707/706
• US Class Current: 707/667
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27

Abstract

A method and apparatus for transferring records between database tables using a transaction log. In one embodiment of the method, a first application generates a transaction for deleting a record from a first table in a database. In response to the generation of the first transaction, a copy of the record is stored in an entry of a transaction log before the record is deleted from the first table. A second application generates an instruction for copying the record from the transaction log to a second table, and the record is copied from the entry to the second table.

Claims

1. A method comprising:

generating a first transaction for deleting a record from a first table in a database;

deleting the record from the first table;

in response to the generation of the first transaction, storing a copy of the record in an entry of a transaction log before the record is deleted from the first table;

after the record is deleted from the first table, generating a second instruction for copying the record from the transaction log to a second table; and

copying the record from the entry to the second table;

generating a first message in response to generating the first transaction, wherein

the first message comprises a table name of the first table and an identifier of the record in the first table, and

the first message comprises information that indicates that the record was or will be deleted; and

transmitting the first message to a computer system that implements a search engine;

generating a second message in response to generating the second instruction, wherein

the second message comprises the table name of the second table and the identifier of the record in the second table, and

the second message comprises information that indicates that the record has been inserted into the second table; and

transmitting the second message to the computer system that implements the search engine;

generating a second index identifier of the record using the table name of the second table and the identifier of the record in the second table.

2. The method of claim 1 wherein

the generating the first transaction is performed by a first application,

the generating the second instruction is performed by a second application, and

the first and second applications are distinct.

3. The method of claim 1 further comprising:

generating a first index identifier of the record using the table name of the first table and the identifier of the record in the first table, wherein

the generating the first index identifier is performed by the search engine.

4. The method of claim 3 further comprising:

removing all instances of the first index identifier from a search index.

5. The method of claim 1

wherein

the generating the second index identifier of the record is performed by the search engine.

6. The method of claim 5 further comprising:

adding at least one instance of the second index identifier to a search index.

7. A non-transitory computer readable medium (CRM) storing instructions, wherein a method is implemented in response to executing the instructions, the method comprising:

receiving a first instruction for deleting a record from a first table in a database;

deleting the record from the first table;

storing a copy of the record in an entry of a transaction log before the record is deleted from the first table;

receiving a second instruction for copying the record from the transaction log to a second table, after the record is deleted from the first table; and

copying the record from the entry to the second table;

generating a first message in response to receiving the first instruction, wherein

the first message comprises a table name of the first table and an identifier of the record in the first table, and

the first message comprises information that indicates that the record was or will be deleted; and

transmitting the first message to a computer system that implements a search engine;

generating a second message in response to receiving the second instruction,

wherein

the second message comprises the table name of the second table and the identifier of the record in the second table, and

the second message comprises information that indicates that the record has been inserted into the second table; and

transmitting the second message to the computer system that implements the search engine;

generating a second index identifier of the record using the table name of the second table and the identifier of the record in the second table.

8. The non-transitory CRM of claim 7 wherein

the first instruction is received from a first application,

the second instruction is received from a second application, and

the first and second applications are distinct.

9. The non-transitory CRM of claim 7 wherein the method further comprises generating a first index identifier of the record using the table name of the first table and the identifier of the record in the first table.

10. The non-transitory CRM of claim 9 wherein the method further comprises removing all instances of the first index identifier from a search index.

11. The non-transitory CRM of claim 7 wherein the method further comprises adding at least one instance of the second index identifier to a search index.