CA2322603C - Optimizing updatable scrollable cursors in database systems - Google Patents
Optimizing updatable scrollable cursors in database systems Download PDFInfo
- Publication number
- CA2322603C CA2322603C CA002322603A CA2322603A CA2322603C CA 2322603 C CA2322603 C CA 2322603C CA 002322603 A CA002322603 A CA 002322603A CA 2322603 A CA2322603 A CA 2322603A CA 2322603 C CA2322603 C CA 2322603C
- Authority
- CA
- Canada
- Prior art keywords
- data record
- data
- sequence number
- record
- temporary
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Expired - Fee Related
Links
- 238000000034 method Methods 0.000 claims description 10
- 230000004048 modification Effects 0.000 claims description 10
- 238000012986 modification Methods 0.000 claims description 10
- 230000004044 response Effects 0.000 claims description 9
- 238000004590 computer program Methods 0.000 claims description 4
- 241000269627 Amphiuma means Species 0.000 claims 1
- 238000005457 optimization Methods 0.000 abstract description 9
- 238000010586 diagram Methods 0.000 description 3
- 230000008859 change Effects 0.000 description 1
- 238000012790 confirmation Methods 0.000 description 1
- 230000006872 improvement Effects 0.000 description 1
- 238000012545 processing Methods 0.000 description 1
- 238000012797 qualification Methods 0.000 description 1
- 230000009467 reduction Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2308—Concurrency control
- G06F16/2315—Optimistic concurrency control
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2308—Concurrency control
- G06F16/2315—Optimistic concurrency control
- G06F16/2322—Optimistic concurrency control using timestamps
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2308—Concurrency control
- G06F16/2315—Optimistic concurrency control
- G06F16/2329—Optimistic concurrency control using versioning
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99941—Database schema or data structure
- Y10S707/99943—Generating database or data structure, e.g. via user interface
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99951—File or database maintenance
- Y10S707/99952—Coherency, e.g. same view to multiple users
- Y10S707/99954—Version management
Abstract
An optimization for updatable scrollable cursors in database systems. To ensure that a temporary copy of a data record matches the data record maintained in the database, the temporary copy is associated with a timestamp included on each page storing the data records of the database. The timestamp on a page in the database is updated whenever any record stored on the page is updated.
The optimization compares the timestamp stored with the temporary record with the current timestamp associated with the page on which the data record is stored in the database. If the two timestamps are matching then the temporary record is identical to the database table record and there is no need to carry out a comparison of the attribute values of the temporary record with the record in the database table.
The optimization compares the timestamp stored with the temporary record with the current timestamp associated with the page on which the data record is stored in the database. If the two timestamps are matching then the temporary record is identical to the database table record and there is no need to carry out a comparison of the attribute values of the temporary record with the record in the database table.
Description
OPTIMIZING UPDATABLE SCROLLABLE CURSORS
IN DATABASE SYSTEMS
FIELD OF THE INVENTION
The present invention is directed to an improvement in computing systems and in particular to optimizing command execution in computer database systems that provide for updatable scrollable cursors.
to BACKGROUND OF THE INVENTION
The Open Database Connectivity (ODBC) specification supports updatable scrollable cursors for Relational Database Management Systems (RDBMSs). This standard provides that multiple cursors may be defined for tables in relational databases and that positioned UPDATES
and DELETEs may be performed on the tables based on the scrollable cursor's location. The ODBC
standard also provides for an attribute in the database to define an optimistic concurrency scheme. In the ODBC
standard this attribute is referred to as SQL CONCUR VALUES. Use of the SQL CONCUR VALUES attribute provides that a positioned UPDATE or DELETE
succeeds only if the record data to be modified has not been changed since it was last fetched by the user.
In certain relational database systems such as the DB2 UDB (trade-mark) RDBMS, for each scrollable cursor a temporary copy of record data is made when the data is fetched by the user.
Where the SQL CONCUR VALUES attribute is applied, and a positioned UPDATE or DELETE
is to be carned out, the temporary copy of record data must be compared to the current record data in the database to ensure that the record data has not changed since the time that it was copied to the temporary location. A comparison of the record in the temporary copy with the record in the current table may result in significant overhead cost for the UPDATE or DELETE where the records to compare are extensive.
It is therefore desirable to have a relational database system that will support the ODBC updatable scrollable cursors and the SQL CONCUR VALUES attribute in which it is possible to optimize the steps to carry out the positioned UPDATE or DELETE commands.
SUMMARY OF THE INVENTION
According to one aspect of the present invention, there is provided an improved system for optimizing updatable scrollable cursors in database systems.
According to another aspect of the present invention, there is provided a method for optimizing command execution in a database system, the database system storing data records on data pages, 1o a log sequence number being maintained in association with each data page, the log sequence number including a time stamp indicating the time of the last modification of data on the data page, the database system supporting the selective copying of a source data record from a specified data page into a temporary data record in a temporary data structure, the method including the steps of storing a reference log sequence number in association with a temporary data record on the selective copying of a source data record to the temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, and determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record by comparing the reference log sequence 2o number for the temporary data record with the current log sequence number of the specified data page.
According to another aspect of the present invention, there is provided a method for optimizing positioned UPDATE and DELETE command execution in a relational database system supporting scrollable cursors and optimistic concurrency, the database system storing data records on data 2s pages, a log sequence number being maintained in association with each data page, the log sequence number including a time stamp indicating the time of the last modification of data on the data page, the database system fetching data in response to a user request by selectively copying a source data record from a specified data page into a temporary data record in a temporary data structure, the method including the steps of:
in conjunction with the selective copying of data into a temporary data record, the step of storing a reference log sequence number in association with the temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, in response to a request for execution of a positioned UPDATE or DELETE
command, 1 o determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record by comparing the reference log sequence number for the temporary data record with the current log sequence number of the specified data page, and where the source data record remains unmodified, carrying out the step of executing the UPDATE or DELETE command without comparing values of the attributes in the temporary data record and the source data record.
According to another aspect of the present invention, there is provided a computer program product 2o for a database management system, the computer program product including a computer usable medium having computer readable code means embodied in said medium, including computer readable program code means for carrying out the above methods.
According to another aspect of the present invention, there is provided a relational database management system including, data records stored on data pages, each data page including a log sequence number, the log sequence number for a page being updated on a modification being made to a data record stored on the said page, means for copying a specified data record from the data page containing the data record to a temporary data record in a temporary table, means for associating the value of the log sequence number of the data page containing the data record with the temporary data record, at the time of copying the data record into the temporary data record, means for comparing the associated log sequence number of the temporary data record with a current log sequence number of the data page containing the data record to determine if a comparison of attribute values of the data record and of the temporary data record is required to verify that the records match each other.
According to another aspect of the present invention, there is provided a relational database system supporting positioned UPDATE and DELETE command execution, scrollable cursors and optimistic concurrency, the relational database system storing data records on data pages, a log sequence number being maintained in association with each data page, the log sequence number including a l0 time stamp indicating the time of the last modification of data on the data page, the relational database system fetching data in response to a user request by selectively copying a source data record from a specified data page into a temporary data record in a temporary data structure, the relational database system including means for storing a reference log sequence number in association with the temporary data record in conjunction with the selective copying of data into a 15 temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, means for determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record, in response to a request for execution of a positioned UPDATE or DELETE command, by comparing the reference log sequence number for 20 the temporary data record with the current log sequence number of the specified data page, and means for carrying out the step of executing the UPDATE or DELETE command without comparing values of the attributes in the temporary data record and the source data record., where the source data record is determined to have remained unmodified.
25 Advantages of the present invention include a reduction in processing time of database UPDATE
or DELETE operations based on scrollable cursors where there is support for optimistic concurrency.
BRIEF DESCRIPTION OF THE DRAWINGS
The preferred embodiment of the invention is shown in the drawings, wherein:
Figure 1 is a block diagram representing example tables in a database subject to the optimization of the preferred embodiment.
In the drawings, the preferred embodiment of the invention is illustrated by way of example. It is to be expressly understood that the description and drawings are only for the purpose of illustration and as an aid to understanding, and are not intended as a definition of the limits of the invention.
DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT
Figure 1 illustrates, in a block diagram, data which is subject to the optimization of the preferred to embodiment. Figure 1 shows a portion of a relational database containing rows m and n, shown as records 12, 14 in the figure. Figure 1 also shows a temp table 16 in which data corresponding to rows m and n are shown as records 18, 20.
In the preferred embodiment, records in the relational database are stored on pages. Each page has a log sequence number (LSN) associated with the page. In Figure l, data page 10 is shown with associated LSN 22. The LSN for the data page includes information which effectively provides a time stamp of the last modification made to any table data (records) on that page. With reference to the example of Figure 1, LSN 22 is updated when either record 12 or record 14, or any other record stored on data page 10, is modified in any way.
As is indicated in the diagram of Figure 1, according to the preferred embodiment, a copy of the data page LSN is maintained in association with a record when that record is written to temp table 16.
Temp table 16 is used to copy row values when an updatable scrollable cursor is used to retrieve table record values for a user. In the example of Figure 1, a cursor has been used to access rows m and n and therefore the row m value in record 12 in data page 10 is copied to record 18 in temp table 16. Similarly the row n value in record 14 is copied to temp table 16 record 20 when a cursor in table is used to fetch row n for a user.
In the preferred embodiment, when record 18 having the value of row m is stored in temp tablel6, a copy of LSN 22 is made and stored in the temp table in association with record 18. This is shown in Figure 1 as LSN 24. Similarly, a copy of LSN 22 is made in association with record 20 when the value of row n is copied to temp table 16. This associated LSN value is shown as LSN 26 in Figure 1.
Due to the concurrency available in the ODBC standard, it is possible for row m to be copied to temp table 16 from data page 10 with the then current value of a LSN 22 being copied to LSN 24 and to then have a subsequent modification to data page 10 before the cursor reaches row n (in the table record 14). As a result, row n values may be copied into record 20 in temp table 16 with LSN value 26 that differs from LSN 24. This is due to a change to the value of LSN 22 when the data page 10 values are modified prior to copying the value of row n into temp table 16.
In the preferred embodiment, where SQL CONCUR VALUES attribute is associated with the table containing data page 10, and an UPDATE or DELETE operation is specified for, for example, row m or row n, based on the position of a cursor, it is necessary to ensure that the value of rows m and n as stored in temp table 16 are the same as the values as stored in data page 10. Although this may be carried out by a direct comparison of the attribute values in the respective rows in temp table 16 and data page 10, the preferred embodiment is able to potentially avoid such a direct comparison by comparing the LSN values of the records. Where, for example, row m is subject to a positioned UPDATE or DELETE based on a scrollable cursor, the value of LSN 24 is compared with the value of LSN 22. Where these values match, it is necessarily the case that record 12 has not been modified since a copy of the value of row m was copied into record 18 in temp table 16.
If record 12 had been modified, the value of LSN 22 would have changed and there would therefore not be a match between the value of LSN 22 and the value of LSN 24. In this manner, it is possible to avoid comparing all attribute values of record 12 and of record 18 by carrying out the simple comparison of the value of LSN 24 and the value of LSN 22. Where these values are different, it will be necessary to carry out the direct comparison of record 12 and record 18.
However, this step may be avoided where the values of LSN 24 and LSN 22 are found to match. Where this is the case, the user will be able to directly update the value of record 12 and rely on the record 18 value as corresponding to record 12.
In the implementation of the preferred embodiment, the retrieval and comparison of LSN values is carried out using an internal command referred to as FETCH SENSITIVE NO DATA.
The operations carried out by the FETCH SENSITIVE NO DATA internal command result in the LSN
for the data page and the LSN for the record in the temp table being retrieved and compared, as is described above. In this way, the RDBMS of the preferred embodiment is able to execute the FETCH SENSITIVE NO DATA command as a part of the steps taken by the RDBMS in optimizing execution of UPDATE or DELETE commands in response to a user request.
to The preferred embodiment is described above with respect to the UPDATE and DELETE commands where a RDBMS supports optimistic concurrency for a scrollable cursor. The optimization of the preferred embodiment may also be used in implementing other commands in an RDBMS which include a temporary table copy of a record, and require a confirmation that the temp table copy is 15 equivalent to the database copy.
An example of such an implementation of the optimization of the preferred embodiment is with respect to the FETCH SENSITIVE command in the DB2 UDB RDBMS. The FETCH
SENSITIVE
command is available to users (in contrast to the FETCH SENSITIVE NO DATA
command referred 20 to above which is used internally in the RDBMS, only). Execution of the command, without any optimization, results in the fetch of a record from the database table and the qualification of that record (its attribute values are compared with the SQL predicates associated with the command).
Where the record qualifies, the temp table is updated and the record is returned to the user. The optimization of the preferred embodiment makes it possible to use the value of the LSN stored in 25 the temp table to avoid steps in carrying out the command. Where the row m, for example, has been previously fetched and is in temp table 16, and the value of LSN 24 is equivalent to data page 10 LSN 22, a FETCH SENSITIVE command carried out on row m may be implemented by positioning the cursor at the appropriate record and returning a flag to the user to indicate that the previously fetched values remain current. If the two LSN values are not equal, then the non-optimized steps to carryout the FETCH SENSITIVE command are followed. Using the comparison of the LSN
value associated with the temp table record and the LSN value of the data page, the copying of attribute values to the temp table may be avoided. In this manner, the optimization of the preferred embodiment may be used to increase efficiency in carrying out command execution in a database with updatable scrollable cursors.
Although a preferred embodiment of the present invention has been described here in detail, it will be appreciated by those skilled in the art, that variations may be made thereto, without departing from the spirit of the invention or the scope of the appended claims.
CA9-2000-0033 g
IN DATABASE SYSTEMS
FIELD OF THE INVENTION
The present invention is directed to an improvement in computing systems and in particular to optimizing command execution in computer database systems that provide for updatable scrollable cursors.
to BACKGROUND OF THE INVENTION
The Open Database Connectivity (ODBC) specification supports updatable scrollable cursors for Relational Database Management Systems (RDBMSs). This standard provides that multiple cursors may be defined for tables in relational databases and that positioned UPDATES
and DELETEs may be performed on the tables based on the scrollable cursor's location. The ODBC
standard also provides for an attribute in the database to define an optimistic concurrency scheme. In the ODBC
standard this attribute is referred to as SQL CONCUR VALUES. Use of the SQL CONCUR VALUES attribute provides that a positioned UPDATE or DELETE
succeeds only if the record data to be modified has not been changed since it was last fetched by the user.
In certain relational database systems such as the DB2 UDB (trade-mark) RDBMS, for each scrollable cursor a temporary copy of record data is made when the data is fetched by the user.
Where the SQL CONCUR VALUES attribute is applied, and a positioned UPDATE or DELETE
is to be carned out, the temporary copy of record data must be compared to the current record data in the database to ensure that the record data has not changed since the time that it was copied to the temporary location. A comparison of the record in the temporary copy with the record in the current table may result in significant overhead cost for the UPDATE or DELETE where the records to compare are extensive.
It is therefore desirable to have a relational database system that will support the ODBC updatable scrollable cursors and the SQL CONCUR VALUES attribute in which it is possible to optimize the steps to carry out the positioned UPDATE or DELETE commands.
SUMMARY OF THE INVENTION
According to one aspect of the present invention, there is provided an improved system for optimizing updatable scrollable cursors in database systems.
According to another aspect of the present invention, there is provided a method for optimizing command execution in a database system, the database system storing data records on data pages, 1o a log sequence number being maintained in association with each data page, the log sequence number including a time stamp indicating the time of the last modification of data on the data page, the database system supporting the selective copying of a source data record from a specified data page into a temporary data record in a temporary data structure, the method including the steps of storing a reference log sequence number in association with a temporary data record on the selective copying of a source data record to the temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, and determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record by comparing the reference log sequence 2o number for the temporary data record with the current log sequence number of the specified data page.
According to another aspect of the present invention, there is provided a method for optimizing positioned UPDATE and DELETE command execution in a relational database system supporting scrollable cursors and optimistic concurrency, the database system storing data records on data 2s pages, a log sequence number being maintained in association with each data page, the log sequence number including a time stamp indicating the time of the last modification of data on the data page, the database system fetching data in response to a user request by selectively copying a source data record from a specified data page into a temporary data record in a temporary data structure, the method including the steps of:
in conjunction with the selective copying of data into a temporary data record, the step of storing a reference log sequence number in association with the temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, in response to a request for execution of a positioned UPDATE or DELETE
command, 1 o determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record by comparing the reference log sequence number for the temporary data record with the current log sequence number of the specified data page, and where the source data record remains unmodified, carrying out the step of executing the UPDATE or DELETE command without comparing values of the attributes in the temporary data record and the source data record.
According to another aspect of the present invention, there is provided a computer program product 2o for a database management system, the computer program product including a computer usable medium having computer readable code means embodied in said medium, including computer readable program code means for carrying out the above methods.
According to another aspect of the present invention, there is provided a relational database management system including, data records stored on data pages, each data page including a log sequence number, the log sequence number for a page being updated on a modification being made to a data record stored on the said page, means for copying a specified data record from the data page containing the data record to a temporary data record in a temporary table, means for associating the value of the log sequence number of the data page containing the data record with the temporary data record, at the time of copying the data record into the temporary data record, means for comparing the associated log sequence number of the temporary data record with a current log sequence number of the data page containing the data record to determine if a comparison of attribute values of the data record and of the temporary data record is required to verify that the records match each other.
According to another aspect of the present invention, there is provided a relational database system supporting positioned UPDATE and DELETE command execution, scrollable cursors and optimistic concurrency, the relational database system storing data records on data pages, a log sequence number being maintained in association with each data page, the log sequence number including a l0 time stamp indicating the time of the last modification of data on the data page, the relational database system fetching data in response to a user request by selectively copying a source data record from a specified data page into a temporary data record in a temporary data structure, the relational database system including means for storing a reference log sequence number in association with the temporary data record in conjunction with the selective copying of data into a 15 temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, means for determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record, in response to a request for execution of a positioned UPDATE or DELETE command, by comparing the reference log sequence number for 20 the temporary data record with the current log sequence number of the specified data page, and means for carrying out the step of executing the UPDATE or DELETE command without comparing values of the attributes in the temporary data record and the source data record., where the source data record is determined to have remained unmodified.
25 Advantages of the present invention include a reduction in processing time of database UPDATE
or DELETE operations based on scrollable cursors where there is support for optimistic concurrency.
BRIEF DESCRIPTION OF THE DRAWINGS
The preferred embodiment of the invention is shown in the drawings, wherein:
Figure 1 is a block diagram representing example tables in a database subject to the optimization of the preferred embodiment.
In the drawings, the preferred embodiment of the invention is illustrated by way of example. It is to be expressly understood that the description and drawings are only for the purpose of illustration and as an aid to understanding, and are not intended as a definition of the limits of the invention.
DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT
Figure 1 illustrates, in a block diagram, data which is subject to the optimization of the preferred to embodiment. Figure 1 shows a portion of a relational database containing rows m and n, shown as records 12, 14 in the figure. Figure 1 also shows a temp table 16 in which data corresponding to rows m and n are shown as records 18, 20.
In the preferred embodiment, records in the relational database are stored on pages. Each page has a log sequence number (LSN) associated with the page. In Figure l, data page 10 is shown with associated LSN 22. The LSN for the data page includes information which effectively provides a time stamp of the last modification made to any table data (records) on that page. With reference to the example of Figure 1, LSN 22 is updated when either record 12 or record 14, or any other record stored on data page 10, is modified in any way.
As is indicated in the diagram of Figure 1, according to the preferred embodiment, a copy of the data page LSN is maintained in association with a record when that record is written to temp table 16.
Temp table 16 is used to copy row values when an updatable scrollable cursor is used to retrieve table record values for a user. In the example of Figure 1, a cursor has been used to access rows m and n and therefore the row m value in record 12 in data page 10 is copied to record 18 in temp table 16. Similarly the row n value in record 14 is copied to temp table 16 record 20 when a cursor in table is used to fetch row n for a user.
In the preferred embodiment, when record 18 having the value of row m is stored in temp tablel6, a copy of LSN 22 is made and stored in the temp table in association with record 18. This is shown in Figure 1 as LSN 24. Similarly, a copy of LSN 22 is made in association with record 20 when the value of row n is copied to temp table 16. This associated LSN value is shown as LSN 26 in Figure 1.
Due to the concurrency available in the ODBC standard, it is possible for row m to be copied to temp table 16 from data page 10 with the then current value of a LSN 22 being copied to LSN 24 and to then have a subsequent modification to data page 10 before the cursor reaches row n (in the table record 14). As a result, row n values may be copied into record 20 in temp table 16 with LSN value 26 that differs from LSN 24. This is due to a change to the value of LSN 22 when the data page 10 values are modified prior to copying the value of row n into temp table 16.
In the preferred embodiment, where SQL CONCUR VALUES attribute is associated with the table containing data page 10, and an UPDATE or DELETE operation is specified for, for example, row m or row n, based on the position of a cursor, it is necessary to ensure that the value of rows m and n as stored in temp table 16 are the same as the values as stored in data page 10. Although this may be carried out by a direct comparison of the attribute values in the respective rows in temp table 16 and data page 10, the preferred embodiment is able to potentially avoid such a direct comparison by comparing the LSN values of the records. Where, for example, row m is subject to a positioned UPDATE or DELETE based on a scrollable cursor, the value of LSN 24 is compared with the value of LSN 22. Where these values match, it is necessarily the case that record 12 has not been modified since a copy of the value of row m was copied into record 18 in temp table 16.
If record 12 had been modified, the value of LSN 22 would have changed and there would therefore not be a match between the value of LSN 22 and the value of LSN 24. In this manner, it is possible to avoid comparing all attribute values of record 12 and of record 18 by carrying out the simple comparison of the value of LSN 24 and the value of LSN 22. Where these values are different, it will be necessary to carry out the direct comparison of record 12 and record 18.
However, this step may be avoided where the values of LSN 24 and LSN 22 are found to match. Where this is the case, the user will be able to directly update the value of record 12 and rely on the record 18 value as corresponding to record 12.
In the implementation of the preferred embodiment, the retrieval and comparison of LSN values is carried out using an internal command referred to as FETCH SENSITIVE NO DATA.
The operations carried out by the FETCH SENSITIVE NO DATA internal command result in the LSN
for the data page and the LSN for the record in the temp table being retrieved and compared, as is described above. In this way, the RDBMS of the preferred embodiment is able to execute the FETCH SENSITIVE NO DATA command as a part of the steps taken by the RDBMS in optimizing execution of UPDATE or DELETE commands in response to a user request.
to The preferred embodiment is described above with respect to the UPDATE and DELETE commands where a RDBMS supports optimistic concurrency for a scrollable cursor. The optimization of the preferred embodiment may also be used in implementing other commands in an RDBMS which include a temporary table copy of a record, and require a confirmation that the temp table copy is 15 equivalent to the database copy.
An example of such an implementation of the optimization of the preferred embodiment is with respect to the FETCH SENSITIVE command in the DB2 UDB RDBMS. The FETCH
SENSITIVE
command is available to users (in contrast to the FETCH SENSITIVE NO DATA
command referred 20 to above which is used internally in the RDBMS, only). Execution of the command, without any optimization, results in the fetch of a record from the database table and the qualification of that record (its attribute values are compared with the SQL predicates associated with the command).
Where the record qualifies, the temp table is updated and the record is returned to the user. The optimization of the preferred embodiment makes it possible to use the value of the LSN stored in 25 the temp table to avoid steps in carrying out the command. Where the row m, for example, has been previously fetched and is in temp table 16, and the value of LSN 24 is equivalent to data page 10 LSN 22, a FETCH SENSITIVE command carried out on row m may be implemented by positioning the cursor at the appropriate record and returning a flag to the user to indicate that the previously fetched values remain current. If the two LSN values are not equal, then the non-optimized steps to carryout the FETCH SENSITIVE command are followed. Using the comparison of the LSN
value associated with the temp table record and the LSN value of the data page, the copying of attribute values to the temp table may be avoided. In this manner, the optimization of the preferred embodiment may be used to increase efficiency in carrying out command execution in a database with updatable scrollable cursors.
Although a preferred embodiment of the present invention has been described here in detail, it will be appreciated by those skilled in the art, that variations may be made thereto, without departing from the spirit of the invention or the scope of the appended claims.
CA9-2000-0033 g
Claims (5)
1. A method for optimizing command execution in a database system, the database system storing data records on data pages, a log sequence number being maintained in association with each data page, the log sequence number comprising a time stamp indicating the time of the last modification of data on the data page, the database system supporting the selective copying of a source data record from a specified data page into a temporary data record in a temporary data structure, the method comprising the steps of:
a. storing a reference log sequence number in association with a temporary data record on the selective copying of a source data record to the temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, and b. determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record by comparing the reference log sequence number for the temporary data record with the current log sequence number of the specified data page.
a. storing a reference log sequence number in association with a temporary data record on the selective copying of a source data record to the temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, and b. determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record by comparing the reference log sequence number for the temporary data record with the current log sequence number of the specified data page.
2. A method for optimizing positioned UPDATE and DELETE command execution in a relational database system supporting scrollable cursors and optimistic concurrency, the database system storing data records on data pages, a log sequence number being maintained in association with each data page, the log sequence number comprising a time stamp indicating the time of the last modification of data on the data page, the database system fetching data in response to a user request by selectively copying a source data record from a specified data page into a temporary data record in a temporary data structure, the method comprising the steps of:
a. in conjunction with the selective copying of data into a temporary data record, the step of storing a reference log sequence number in association with the temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, b. in response to a request for execution of a positioned UPDATE or DELETE
command, determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record by comparing the reference log sequence number for the temporary data record with the current log sequence number of the specified data page, and c. where the source data record remains unmodified, carrying out the step of executing the UPDATE or DELETE command without comparing values of the attributes in the temporary data record and the source data record.
a. in conjunction with the selective copying of data into a temporary data record, the step of storing a reference log sequence number in association with the temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, b. in response to a request for execution of a positioned UPDATE or DELETE
command, determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record by comparing the reference log sequence number for the temporary data record with the current log sequence number of the specified data page, and c. where the source data record remains unmodified, carrying out the step of executing the UPDATE or DELETE command without comparing values of the attributes in the temporary data record and the source data record.
3. A computer program product for a database management system, the computer program product comprising a computer usable medium having computer readable code means embodied in said medium, comprising computer readable program code means for carrying out the method of claim 1 or claim 2.
4. A relational database management system comprising:
data records stored on data pages, each data page comprising a log sequence number, the log sequence number for a page being updated on a modification being made to a data record stored on the said page, means for copying a specified data record from the data page containing the data record to a temporary data record in a temporary table, means for associating the value of the log sequence number of the data page containing the data record with the temporary data record, at the time of copying the data record into the temporary data record, means for comparing the associated log sequence number of the temporary data record with a current log sequence number of the data page containing the data record to determine if a comparison of attribute values of the data record and of the temporary data record is required to verify that the records match each other.
data records stored on data pages, each data page comprising a log sequence number, the log sequence number for a page being updated on a modification being made to a data record stored on the said page, means for copying a specified data record from the data page containing the data record to a temporary data record in a temporary table, means for associating the value of the log sequence number of the data page containing the data record with the temporary data record, at the time of copying the data record into the temporary data record, means for comparing the associated log sequence number of the temporary data record with a current log sequence number of the data page containing the data record to determine if a comparison of attribute values of the data record and of the temporary data record is required to verify that the records match each other.
5. A relational database system supporting positioned UPDATE and DELETE
command execution, scrollable cursors and optimistic concurrency, the relational database system storing data records on data pages, a log sequence number being maintained in association with each data page, the log sequence number comprising a time stamp indicating the time of the last modification of data on the data page, the relational database system fetching data in response to a user request by selectively copying a source data record from a specified data page into a temporary data record in a temporary data structure, the relational database system comprising a. means for storing a reference log sequence number in association with the temporary data record in conjunction with the selective copying of data into a temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, b. means for determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record, in response to a request for execution of a positioned UPDATE or DELETE command, by comparing the reference log sequence number for the temporary data record with the current log sequence number of the specified data page, and c. means for carrying out the step of executing the UPDATE or DELETE command without comparing values of the attributes in the temporary data record and the source data record., where the source data record is determined to have remained unmodified.
command execution, scrollable cursors and optimistic concurrency, the relational database system storing data records on data pages, a log sequence number being maintained in association with each data page, the log sequence number comprising a time stamp indicating the time of the last modification of data on the data page, the relational database system fetching data in response to a user request by selectively copying a source data record from a specified data page into a temporary data record in a temporary data structure, the relational database system comprising a. means for storing a reference log sequence number in association with the temporary data record in conjunction with the selective copying of data into a temporary data record, the reference log sequence number representing the log sequence number of the specified data page at the time the source data record is copied to the temporary data record, b. means for determining that the source data record remains unmodified since the time that the source data record is copied to the temporary data record, in response to a request for execution of a positioned UPDATE or DELETE command, by comparing the reference log sequence number for the temporary data record with the current log sequence number of the specified data page, and c. means for carrying out the step of executing the UPDATE or DELETE command without comparing values of the attributes in the temporary data record and the source data record., where the source data record is determined to have remained unmodified.
Priority Applications (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA002322603A CA2322603C (en) | 2000-10-06 | 2000-10-06 | Optimizing updatable scrollable cursors in database systems |
| US09/872,827 US7010552B2 (en) | 2000-10-06 | 2001-05-31 | Optimizing command execution in database systems that provide support for updatable scrollable cursors |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA002322603A CA2322603C (en) | 2000-10-06 | 2000-10-06 | Optimizing updatable scrollable cursors in database systems |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CA2322603A1 CA2322603A1 (en) | 2002-04-06 |
| CA2322603C true CA2322603C (en) | 2005-04-12 |
Family
ID=4167339
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CA002322603A Expired - Fee Related CA2322603C (en) | 2000-10-06 | 2000-10-06 | Optimizing updatable scrollable cursors in database systems |
Country Status (2)
| Country | Link |
|---|---|
| US (1) | US7010552B2 (en) |
| CA (1) | CA2322603C (en) |
Families Citing this family (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7689607B2 (en) * | 2005-04-15 | 2010-03-30 | Microsoft Corporation | Database page mirroring |
| US8818960B2 (en) | 2011-03-18 | 2014-08-26 | Microsoft Corporation | Tracking redo completion at a page level |
| US10282349B2 (en) | 2015-08-26 | 2019-05-07 | International Business Machines Corporation | Method for storing data elements in a database |
Family Cites Families (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US4536837A (en) | 1982-05-25 | 1985-08-20 | Elxsi | Improved disk file allocation and mapping system utilizing cylinder control blocks and file map having unbalanced tree structure |
| US5333303A (en) * | 1991-03-28 | 1994-07-26 | International Business Machines Corporation | Method for providing data availability in a transaction-oriented system during restart after a failure |
| US5592664A (en) * | 1991-07-29 | 1997-01-07 | Borland International Inc. | Database server system with methods for alerting clients of occurrence of database server events of interest to the clients |
| US5440727A (en) * | 1991-12-18 | 1995-08-08 | International Business Machines Corporation | Asynchronous replica management in shared nothing architectures |
| US5517641A (en) | 1992-05-27 | 1996-05-14 | Cdb Software, Inc. | Restartable method to reorganize DB2 tablespace records by determining new physical positions for the records prior to moving using a non sorting technic |
| US5455946A (en) * | 1993-05-21 | 1995-10-03 | International Business Machines Corporation | Method and means for archiving modifiable pages in a log based transaction management system |
| US5546579A (en) * | 1994-05-02 | 1996-08-13 | International Business Machines Corporation | Page refreshing procedure using two locking granularities to ensure cache coherency in a multisystem database processing environment having a high-speed shared electronic store |
| US5574902A (en) * | 1994-05-02 | 1996-11-12 | International Business Machines Corporation | Efficient destaging of updated local cache pages for a transaction in a multisystem and multiprocess database management system with a high-speed shared electronic store |
| US5822749A (en) | 1994-07-12 | 1998-10-13 | Sybase, Inc. | Database system with methods for improving query performance with cache optimization strategies |
| JPH0962558A (en) * | 1995-08-29 | 1997-03-07 | Fuji Xerox Co Ltd | Method and system for database management |
| US5907848A (en) * | 1997-03-14 | 1999-05-25 | Lakeview Technology, Inc. | Method and system for defining transactions from a database log |
| US6161109A (en) * | 1998-04-16 | 2000-12-12 | International Business Machines Corporation | Accumulating changes in a database management system by copying the data object to the image copy if the data object identifier of the data object is greater than the image identifier of the image copy |
| US6591269B1 (en) * | 1999-05-19 | 2003-07-08 | Sybase, Inc. | Database system with methodology for online index rebuild |
-
2000
- 2000-10-06 CA CA002322603A patent/CA2322603C/en not_active Expired - Fee Related
-
2001
- 2001-05-31 US US09/872,827 patent/US7010552B2/en not_active Expired - Lifetime
Also Published As
| Publication number | Publication date |
|---|---|
| CA2322603A1 (en) | 2002-04-06 |
| US7010552B2 (en) | 2006-03-07 |
| US20020042788A1 (en) | 2002-04-11 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US6714943B1 (en) | Method and mechanism for tracking dependencies for referential integrity constrained tables | |
| US6728719B1 (en) | Method and mechanism for dependency tracking for unique constraints | |
| US9830109B2 (en) | Materializing data from an in-memory array to an on-disk page structure | |
| US9411866B2 (en) | Replication mechanisms for database environments | |
| US6161109A (en) | Accumulating changes in a database management system by copying the data object to the image copy if the data object identifier of the data object is greater than the image identifier of the image copy | |
| US6119128A (en) | Recovering different types of objects with one pass of the log | |
| US6098075A (en) | Deferred referential integrity checking based on determining whether row at-a-time referential integrity checking would yield the same results as deferred integrity checking | |
| US7672926B2 (en) | Method and system for updating value correlation optimizations | |
| US6480848B1 (en) | Extension of data definition language (DDL) capabilities for relational databases for applications issuing DML and DDL statements | |
| US6438538B1 (en) | Data replication in data warehousing scenarios | |
| US7890466B2 (en) | Techniques for increasing the usefulness of transaction logs | |
| US9639542B2 (en) | Dynamic mapping of extensible datasets to relational database schemas | |
| US6366902B1 (en) | Using an epoch number to optimize access with rowid columns and direct row access | |
| US6567816B1 (en) | Method, system, and program for extracting data from database records using dynamic code | |
| US9418094B2 (en) | Method and apparatus for performing multi-stage table updates | |
| US8280907B2 (en) | System and method for managing access to data in a database | |
| US20160147804A1 (en) | Forced ordering of a dictionary storing row identifier values | |
| JPS61170842A (en) | Updating of snap shot table for related database system | |
| EP1480132B1 (en) | System and method for identifying and storing changes made to a table | |
| CA2652115A1 (en) | Apparatus and method for read consistency in a log mining system | |
| US20030177117A1 (en) | Metadata system for managing data mining environments | |
| US9390111B2 (en) | Database insert with deferred materialization | |
| US6820080B2 (en) | Dependent object processing for triggers | |
| US7136861B1 (en) | Method and system for multiple function database indexing | |
| US6275832B1 (en) | Providing transaction undo without logging |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| EEER | Examination request | ||
| MKLA | Lapsed | ||
| MKLA | Lapsed |
Effective date: 20121009 |