1. Technical Field
This invention relates to database. More specifically, the invention relates to maintaining version information in a database to support efficient query processing using an index.
2. Description of the Prior Art
A database is a collection of information organized to enable a computer program to quickly select desired data. Traditional databases are organized by fields, records, and files. A field is a single piece of information. A record is one complete set of fields. A file is a collection of records. To access information from a database, a collection of programs are used to enable entering, organizing, and selecting data in a database. A list of keys, or keywords, may be provided wherein each key or keyword identifies a set of records. The list of keys or keywords is known as a database index, hereinafter referred to as an index. Database indices make it faster to find specific records and to sort records by the field used to identify the records.
Multi-version databases support multiple users reading and/or writing data at any one time. In a multi-version database system, each write on a data item produces a new version of that data item. A point-in-time version of the database system enables programs reading the database, also known as readers, to see the current committed state of the database as of some point in time in the past, for example the start of their transaction. A Multi-Version database system may maintain version information for table data or for table as well as index data. If the index version is not maintained, a scan of the index can still be done, but getting the correct version of the index keys will require a read of the associated data records.
One prior art solution for versioning index data involves adding new fields to each entry in the index, henceforth referred to as an index item. The added fields are used to chain together all versions of an individual index item. There are drawbacks associated with this approach. The added fields increase the overall size of the index and may increase the number of levels in the associated tree structure. Due to the added versioning information, more index pages will need to be read from the disk to access the same amount of index information. This increases the cost of retrieving index data for all readers.
Another prior art solution is to maintain a transaction list within the page header for each index page. The list contains an entry for each transaction that has made a modification to one or more index items in the page. Each entry in the list contains a pointer to obtain older versions of all index items on the page that were modified by the transaction. Individual index items on the page have a field added to them, to identify the entry in the transaction list for the transaction that modified that item. However, there are limitations associated with use of the transaction list. One limitation is that the transaction list is located within the page header and has a limited amount of space. Once the transaction list is filled, transactions making new modifications may need to be failed or suspended until space can be reclaimed in the transaction list. Another limitation with use of the transaction list is that multiple items in a page may share the same entry in the list. This leads to inefficiency in versioned reads as a reader may have to read older versions of more data than he is interested in.
Therefore, there is a need to apply a technique to an index of a multi-version database that overcomes the limitations associated with the prior art solutions. Such a solution should remove the modification data from the page header and from each index item so as to remove the obstacles associated therewith.
This invention comprises a method and system for maintaining version information for a database index.
In one aspect of the invention, a method is provided for maintaining a database index version. An index item is stored in a leaf node of a B-tree. Version information of the index item is stored in a secondary node remote from the B-tree. The leaf node and the secondary node are associated.
In another aspect of the invention, a database is provided with an index item adapted to be stored in a leaf node of a B-tree. Version data of the index item is provided and stored in a secondary node remote from the B-tree. A pointer is provided to connect the leaf node with the secondary node.
In yet another aspect of the invention, an article is provided with a computer useable medium embodying computer usable program code to maintain a database index. The computer program code includes instructions to store an index item in a leaf node of a B-tree. Instructions are also provided to store version information of the index item in a secondary node remote from the B-tree, and to associate the leaf node with the secondary node.
Other features and advantages of this invention will become apparent from the following detailed description of the presently preferred embodiment of the invention, taken in conjunction with the accompanying drawings.
A database index is represented as a B-tree with each leaf node of the tree representing a page of the database index. A secondary node is dynamically created for each associated leaf node as index items for the respective node are inserted or removed therefrom. A pointer is stored in the header section of each leaf node referencing an associated secondary node. Access to the secondary node is restricted to the associated leaf node. Unique identifiers are provided for each transaction and stored in a secondary node associated with the leaf node that has an amended index item. Similarly, as each transaction is committed, the unique identifier associated with the transaction is stored in a status list maintained in memory on a server. Comparison of identifiers in the secondary node and the status list ensure that a scan of the index produces accurate results.
As shown in
Once a secondary node for a leaf node has been established, there are limited circumstances in which the removal of the secondary node will occur. Examples of when the secondary node may be removed include, but are not limited to, when the database management system is shut down or when the version information is no longer needed. Whether or not version information is needed is determined by comparing the timestamp in the page header indicating the time of last modification of the page, with the timestamp of the start of oldest active transaction currently executing in the engine. On systems running with low virtual memory, secondary nodes may be released to conserve memory, even when the version information is needed. In such a case, a negative value is stored in the pointer in the page header, indicating that index versions are not available for a time period and table data needs to be read for determining the right version of a key, as discussed above in the prior paragraph. In this state, on every new modification to the associated leaf node, a check is made to determine if the secondary node can be reallocated. The check consists of two conditions. First, memory should be available. Second, the timestamp in the leaf page header should be less than the timestamp at the start of the oldest active transaction in the system. This guarantees that the secondary nodes, when they exist, store all version information required for the associated leaf node. They never store partial version information. If both checks pass, the secondary node is reallocated, and a pointer to it is stored in the leaf page header.
Index items are only inserted or deleted. As such, version information for each index item only needs to reflect one of these two actions. Version information for each index item consists of two transaction identifiers. One identifier is for the transaction that created the index item, and the second identifier is for the transaction that deleted the index item. Each secondary node includes two fields for each index item in the associated leaf node, to store the transaction identifiers. A first field of the secondary node stores the transaction identifier for a transaction inserting a new index item, also known as transaction insert identifier, TI. Each time an item is inserted into a leaf node, the TI of the transaction inserting the new index item is written into the first field of the associated secondary node. A second field of the secondary node stores a transaction deletion identifier, TD. Each time an item is removed from a leaf node, the identifier of the deleting transaction is written into the TD field of the associated secondary node. In addition, when an index item is removed, an indicator is provided to communicate that an item has been marked for removal from the leaf node, i.e. index page. In one embodiment, the indicator may be in the form of a flag which is part of the index item.
The value of the TI and TD identifiers reflect changes to index items. In one embodiment, a counter is maintained on a server in communication with the database management system. At the start of each transaction, the counter is incremented and the value of the incremented counter is assigned to the transaction identifier associated with the transaction. For example, TI may be the value of the global counter at the start of the insertion transaction. Similarly, TD may be the global value of the counter at the time the transaction is removed from an index item. In one embodiment, the counter is a 64 bit monotonically increasing global variable stored in memory and periodically copied into persistent storage of the server. In case of an abnormal shutdown of the database server, the counter can be reconstructed during the recovery process using database log files. Accordingly, a global counter functions in conjunction with the transaction identifier to ensure that each transaction is assigned a unique identifier.
In addition to the transaction identifiers of the secondary node, a status list is maintained on the server.
As an index page is scanned in response to a query, it must be determined whether a returned keyword, as reflected in an associated leaf node, has been subject to a change in data. If the delete flag is set, then it must be determined whether the delete transaction was committed before the index scan was initiated. If the delete flag is not set, then it must be determined if the insert transaction was committed before the scan transaction began. A scan of the database index in conjunction with a consult of the status list will determine whether the returned keyword is valid.
Following an acceptance of the returned index item at step (114), a subsequent test is conducted to determine if the index data can satisfy the query (118). More specifically, the test at step (118) determines if the query requires a look-up of a database record associated with the index item returned from the index scan. A negative response to the test at step (118) will result in a lookup of the associated database record and/or table using a row identifier associated with the transaction identifier (120) followed by proceeding to the next item in the scan (126). The transaction identifier enables the row in the record that has a change to be identified without having to review each item in the index page associated with the leaf node. A positive response to the test at step (118) will follow with a read of the data returned from the index scan (122), followed by proceeding to the next item in the scan (126). Following step (126), the process returns to step (106) to determine the status of the item returned from the index scan. Accordingly, as each returned item in the index scan is returned it is reviewed to determine if this item has been committed to the index.
As shown in
The method for maintaining index version information and scanning index pages may be invoked in the form of a tool utilized by a client machine in communication with a database management system.
In one embodiment, the database query tool (212), the manager (214), and the database management system (232) may be software components stored on a computer-readable medium as it contains data in a machine readable format. For the purposes of this description, a computer-useable, computer-readable, and machine readable medium or format can be any apparatus that can contain, store, communicate, propagate, or transport the program for use by or in connection with the instruction execution system, apparatus, or device. Accordingly, the database query tool and database management system component may all be in the form of hardware elements in the computer system or software elements in a computer-readable format or a combination of software and hardware.
Version information for index items of a database is maintained external to the index pages. More specifically, version information is stored in a secondary node with access to each secondary node being limited to an associated leaf node. By storing the additional data in the secondary nodes the size of the index remains unchanged as the version information is external to the index. This mitigates performance overhead in traversing the index for all queries. In addition, version information is kept for each modified index item, and is not shared between items. Maintaining version information at a finer granularity allows a query to only access older versions of exactly those items that it is interested in. Furthermore, the basic structure of the index and index items is not altered, allowing easier migration of a non versioned database to be used by a multi-versioning database system.
It will be appreciated that, although specific embodiments of the invention have been described herein for purposes of illustration, various modifications may be made without departing from the spirit and scope of the invention. In particular, the design of the secondary node can be implementation specific and should not be limited to the design shown herein. Accordingly, the scope of protection of this invention is limited only by the following claims and their equivalents.