Highly selective invalidation of database cursors

Patent metadata

Field	Value
Publication number	US-10360209-B2
Application number	US-201615267118-A
Country	US
Kind code	B2
Filing date	Sep 15, 2016
Priority date	Oct 21, 2015
Publication date	Jul 23, 2019
Grant date	Jul 23, 2019

Abstract

A method commences upon receiving a database language statement pertaining to a database comprising database objects that are accessed by computing processes. The method analyzes the database language statement to form or identify a cursor object that has dependencies on one or more referenced data objects. The cursor object is associated with a set of cursor attributes that are stored in a cursor metadata data structure. By analyzing a subsequently-received database language statement, the method determines one or more operations to operate over at least one of the data objects. Rules are applied to the cursor attributes and/or to the operations and/or to objects referenced in the cursor so as to determine whether or not to invalidate the cursor object. The cursor may be considered at a later time for possible later invalidation or revalidation based on processing of later received database language statements and/or based on a later event.

First claim

What is claimed is:

1. A computer-implemented method, comprising:
receiving a plurality of database language statements pertaining to a first database-object in a database, wherein the plurality of database language statements comprises a first database statement and a second database statement, the first database statement performs a first data manipulation language (DML) operation and the second database statement performs a second DML operation against the first database object;
determining a first database cursor corresponding to the first database statement;
determining a second database cursor corresponding to the second database statement;
determining a respective set of cursor attributes that is associated with each of the first and second database cursors;
receiving and executing a data definition language (DDL) statement on the first database object or a second database object related to the first database object; and
determining whether or not to perform cursor invalidation based at least in part on the DDL statement and at least one cursor attribute of the set of cursor attributes, at least by:
invalidating the first database cursor corresponding to the first database language statement based at least in part upon the DDL statement and at least a first cursor attribute pertaining to the first database cursor; and
maintaining the second database cursor corresponding to the second database language statement without invalidation based at least in part upon the DDL statement and at least a second cursor attribute pertaining to the second database database cursor.

2. The computer-implemented method of claim 1 , further comprising analyzing the second database language statement to identify a portion of the database cursor that is not invalidated based in whole or in part on the set of cursor attributes.

3. The computer-implemented method of claim 2 , wherein the DDL statement comprises a database language clause pertaining to at least one of an index to be dropped or a dropped index.

4. The computer-implemented method of claim 1 , further comprising analyzing the DDL statement to identify a portion of the first database cursor for which invalidation is deferred to a future processing time.

5. The computer-implemented method of claim 1 , further comprising:
associating the first or second database cursor with cursor objects comprising one or more of the set of cursor attributes that correspond to the first or second database cursor; and
performing a rolling invalidation on a third database cursor corresponding to a third database language statement based at least in part upon the DDL statement and at least a third cursor attribute pertaining to the third database cursor.

6. The computer-implemented method of claim 5 , wherein the set of cursor attributes comprise at least one of one or more dependency attributes, or one or more segment attributes, or one or more partitioning attributes, or one or more statistics attributes, or one or more cursor rules attributes, or any combination thereof.

7. The computer-implemented method of claim 6 , further comprising registering a set of cursor invalidation instructions as metadata corresponding to one or more updated database objects that are updated by an execution of the DDL statement.

8. The computer-implemented method of claim 7 , wherein the set of cursor invalidation instructions serves to update a cursor status of the first or second database cursor based in whole or in part on one or more of the set of cursor attributes.

9. The computer-implemented method of claim 7 , wherein the set of cursor invalidation instructions serves to invalidate fewer than all of the cursor objects that correspond to the first or second database cursor.

10. The computer-implemented method of claim 7 , wherein the set of cursor invalidation instructions serves to schedule a rolling invalidation of at least one of the cursor objects, and the rolling invalidation comprises recompiling the at least one of the cursor objects at a future processing time.

11. The computer-implemented method of claim 1 , wherein the act of determining whether or not to perform the cursor invalidation is further based at least in part upon one or more invalidation rules that comprise one or more operators pertaining to one or more database statement types and one or more types of database operations.

12. A non-transitory computer readable medium having stored thereon a sequence of instructions which, when stored in memory and executed by one or more processors causes the one or more processors to perform a set of acts the acts comprising:
receiving a plurality of database language statements pertaining to a first database object in a database, wherein the plurality of database language statements comprises a first database statement and a second database statement, the first database statement performs a first data manipulation language (DML) operation and the second database statement performs a second DML operation against the first database object;
determining a first database cursor corresponding to the first database statement;
determining a second database cursor corresponding to the second database statement;
determining a respective set of cursor attributes that is associated with each of the first and second database cursors;
receiving and executing a data definition language (DDL) statement on the first database object or a second database object related to the first database object; and
determining whether or not to perform cursor invalidation based at least in part on the DDL statement and at least one cursor attribute of the set of cursor attributes, at least by:
invalidating the first database cursor corresponding to the first database language statement based at least in part upon the DDL statement and at least a first cursor attribute pertaining to the first database cursor; and
maintaining the second database cursor corresponding to the second database language statement without invalidation based at least in part upon the DDL statement and at least a second cursor attribute pertaining to the second DB cursor.

13. The non-transitory computer readable medium of claim 12 , further comprising instructions which, when stored in the memory and executed by the one or more processors, cause the one or more processors to perform acts of analyzing the second database language statement to identify a portion of the database cursor that is not invalidated based at least in part on the set of cursor attributes.

14. The non-transitory computer readable medium of claim 13 , wherein the DDL statement comprises a database language clause pertaining to at least one of an index to be dropped or a dropped index.

15. The non-transitory computer readable medium of claim 12 , further comprising instructions which, when stored in the memory and executed by the one or more processors, cause the one or more processors to perform acts of analyzing the DDL statement to identify a portion of the first database cursor for which invalidation is deferred to a future processing time.

16. The non-transitory computer readable medium of claim 12 , further comprising instructions which, when stored in the memory and executed by the one or more processors, cause the one or more processors to perform acts of associating the first or second database cursor with cursor objects comprising one or more of the set of cursor attributes that correspond to the first or second database cursor.

17. The non-transitory computer readable medium of claim 16 , wherein the set of cursor attribute

Assignees

• Oracle Int Corp

Inventors

• Eadon George
• Raghavan Ananth
• Kumar Ramesh

Classifications

• G06F16/24524 Primary

  Access plan code generation and invalidation; Reuse of access plans · CPC title

• G06F16/2453

  Query optimisation · CPC title

• G06F16/2454

  Optimisation of common expressions · CPC title