US 5,842,196 AGranted

Database system with improved methods for updating records

AssigneeSybase, Inc.

InventorsSunil Agarwal, Vasudha Krishnaswamy

Life of this patent

Estimating the term…

Loading patent…

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.

Claims26

Dependent claims

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.

4 more elements

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.

3 more elements

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.

2 more elements

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.

8 more elements

Dependencies are inferred from the claim text.