Database system with improved methods for updating records

• US 5,842,196 A
• Filed: 04/03/1996
• Issued: 11/24/1998
• Est. Priority Date: 04/03/1996
• Status: Expired due to Term

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Krishnaswamy, Vasudha, Agarwal, Sunil
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Corrielus, Jean M.
• Application Number:
  US08/626,093
• Time in Patent Office: 965 Days
• Field of Search: 395/602, 1/1, 707/2, 707/10, 707/4, 707/102, 707/100, 707/1, 364/283.4, 364/282.4, 364/282.1, 364/283.1
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2246 Trees, e.g. B+trees
• CPC Class Codes: G06F 16/23 Updating
• CPC Class Codes: G06F 16/24557 Efficient disk access during query execution
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Database system and methods are described for updating records, such as are commonly used in a relational database environment. Updates are carried out in a manner which allows a substantial portion of the work to be performed in direct mode (when possible), thereby avoiding the inefficiency of re-reading records. In this fashion, a scenario which requires deferred updating can, in accordance with the present invention, be treated mostly as a direct update, with minimal deferred updating. Given a query involving an "unsafe" unique index (i.e., query includes update to key field), for instance, the present invention provides a method whereby each data row is updated in direct mode. In conjunction with this, the corresponding index row is deleted in direct mode. The system will not insert an index row at this point, however. Instead, updating of the index is deferred. The index is inserted later during the deferred or default mode. By updating data rows in direct mode (when possible), system performance is enhanced.

Claims

1. In a database system having a database storing a database table comprising a plurality of data pages, each data page capable of storing a plurality of data records, said data records storing information organized into particular database fields, said database table having an index for logically ordering said plurality of data records, said index comprising a plurality of index entries wherein each data record is referenced by a corresponding index entry, said database system providing a direct mode, and a deferred mode for updating the database table and the index, said direct mode for performing actual updates to the database table or the index as each data record is read during a first pass, and said deferred mode for updating the database table or the index by first marking data records or index entries during the first pass for actual updating during a second pass, an improved method for updating said data records, the method comprising:

receiving a query which specifies a change in value for a database filed whose values form a unique index for the database table; and

performing said update by;

in a first pass, scanning said data pages for said data records and changing any field value which is specified to change, so that said data records are updated in the direct mode,

in conjunction with said scanning step occurring during the first pass, deleting any corresponding index entry which references a data record changed during said scanning step, so that any corresponding index entries are deleted in the direct mode,

marking any index entry so deleted in the direct mode for inserting back into the index during a second pass, and

after completing said scanning step of the first pass, inserting in the second pass all such corresponding index entries back into the index which were marked during the first pass for insertion during the second pass, so that all such corresponding index entries are inserted back into the index in the deferred mode.

2. The method of claim 1, wherein said index is employed by the system for enforcing entity integrity for the database table.

3. The method of claim 1, wherein said database field whose values are changed during scanning serves as a primary key for said database table.

4. The method of claim 1, wherein said index is a non-clustered index.

5. The method of claim 1, wherein said query specifies incrementing a value stored at the database field for each record, such that a primary key violation occurs during processing of the query but not upon completion of the query.

6. The method of claim 1, wherein said index comprises a B-Tree index.

7. The method of claim 6, wherein said B-Tree index includes a plurality of leaf-level nodes, each node storing one key value together with a record identifier for a corresponding data record which stores that key value.

8. The method of claim 1, wherein said scanning step is performed in a single pass, so that each data record is read only once for updating.

9. The method of claim 1, wherein said scanning step is performed in direct mode, such that no data records are updated in deferred mode.

10. The method of claim 1, wherein said query specifies an update which leads to a primary key violation for the database table and wherein said inserting an index entry step includes detecting said primary key violation.

11. In a database system having a database storing a database table comprising a plurality of data pages, each capable of storing a plurality of data records, said data records storing information organized into particular database fields, said database table having an index for logically ordering said plurality of data records, said index comprising a plurality of index entries wherein each data record is referenced by a corresponding index entry, said database system providing a direct mode and a deferred mode for updating the database table and the index, said direct mode for performing actual updates to the database table or the index as each data record is read during a first pass, and said deferred mode for updating be database table or the index by first marking data records or index entries during the first pass for actual updating during a second pass, an improved method for updating said data records, the method comprising:

receiving a query which specifies a change in value for a database field such that a particular data record, once updated, overflows a corresponding data page where the record is stored; and

performing said update by;

in a first pass, deleting the particular data record which overflows the data page when said database page is first read, so that said particular data record is deleted in the direct mode,

in conjunction with said deleting step occurring during the first pass, marking said particular data record for inserting back into the database table during a second pass, which is to be perform after all data pages required for processing the query have been scanned, and

after completing scanning of all data pages, inserting in the second pass the marked particular data record into the database table at a particular data page, wherein the marked particular data record is inserted in the deferred mode such that only the particular data page need be read after completion of said scanning.

12. The method of claim 11, wherein said particular data record is a variable-length data record and wherein said query increases the size of said particular data record such that it exceeds storage space available on the data page where the record is stored.

13. The method of claim 11, further comprising:

in conjunction with said deleting step, deleting a corresponding index record for said particular data record.

14. The method of claim 13, further comprising:

after completing said insertion of the particular data record which overflowed the data page, inserting the corresponding index row for said particular data record.

15. The method of claim 11, further comprising:

updating other data records which do not overflow said corresponding data page when said corresponding data is first read.

16. The method of claim 11, further comprising:

updating in direct mode other data records which do not overflow said corresponding data page, so that said other data records can be updated without having to re-read said corresponding data page.

17. The method of claim 11, wherein said index is a non-clustered index.

18. The method of claim 11, wherein said index is a clustered index.

19. The method of claim 11, wherein said corresponding data page includes a directory of data records on that page and wherein said directory is preserved for other data records on the page by deleting any data record which, once updated, overflows the data page.

20. The method of claim 11, wherein said index comprises a B-Tree index.

21. In a database system having a database storing a database table comprising a plurality of data pages for storing data records, said data records storing information organized into particular database fields, said database table having an index for logically ordering said plurality of data records, said index comprising a plurality of index records wherein each data record is referenced by a corresponding index record, said database system providing a direct mode and a deferred mode for updating the database table and the index, said direct mode for performing actual updates to the database table or the index as each data record is read during a first pass, and said deferred mode for updating the database table or the index by first marking data records or index records during the first pass for actual updating during a second pass, an improved method for updating said data records, the method comprising:

receiving a query specifying updating of data records which are indexed, each data record being associated with an index record storing a key value for the data record together with an identifier for that data record; and

updating said data records by;

updating the data records in the direct mode, so that all data records which can be updated are updated in a first pass,

for any data record updated in the direct mode, deleting its associated index record also in the direct mode and marking said associated index record so deleted for insertion in a second pass, and

inserting in the second pass each index record which has been marked during the first pass, so that each such index record is inserted in the deferred mode.

22. The method of claim 21, wherein said query specifies an update which causes a temporary primary key violation when said data records are updated one by one.

23. The method of claim 21, wherein said index is a unique index for said database table, and wherein said query modifies values which contribute to the index having a uniqueness constraint, and wherein said index cannot be updated in the direct mode without violating the uniqueness constraint.

24. The method of claim 21, wherein said index is a B-Tree index.

25. The method of claim 21, wherein a data record is updated in direct mode by deleting an existing copy of the data record and inserting a new copy of that data record.

26. A client/server database system having a database storing a database table comprising a plurality of data pages for storing data records, said data records storing information organized into particular database fields, said database table having an index for logically ordering said plurality of data records, said index comprising a plurality of index records-wherein each data record is referenced by a corresponding index record, the system including means for updating sad data records and said index, the system comprising:

a server connected to a client;

means for transmitting from the client to the server a query specifying updating of data records which are indexed, each data record being associated with an index record storing a key value for the data record together with an identifier for that data record; and

means for updating said data records at the server by;

providing a direct mode and a deferred mode for updating the database table and the index, sad direct mode for performing actual updates to the database table or the index as each record is read during a first pass, and said deferred mode for updating the database table or the index by first marking data records or index records during the first pass for actual updating during a second pass;

receiving a query specifying updating of data records which are indexed, each data record being associated with an index record storing a key value for the data record together with an identifier for that data record; and

updating said data records by;

updating the data records in the direct mode, so that all data records which can be updated are updated in a first pass,

for any data record updated in the direct mode, deleting its associated index record also in direct mode and marking said associated index record so deleted for insertion in a second pass, and

inserting in the second pass each index record which has been marked during the first pass, so that each such index record is inserted in the deferred mode.