Suspending a result set and continuing from a suspended result set for scrollable cursors

• US 7,743,333 B2
• Filed: 04/01/2005
• Issued: 06/22/2010
• Est. Priority Date: 08/12/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Bastawala, Mehul D., Gollapudi, Sreenivas, Chatterjee, Debashish, Krishnaswamy, Srinath, Kaluskar, Sanjay, Chidambaran, Lakshminarayanan
• Primary Examiner(s): Huynh; Ba
• Assistant Examiner(s): Iturralde; Enrique W
• Application Number:
  US11/097,065
• Publication Number:
  US 20060036616A1
• Time in Patent Office: 1,908 Days
• Field of Search: 707/100, 707/3, 707/1, 707/662, 707/705, 707/713, 707/802, 715/738
• US Class Current: 715/738
• CPC Class Codes: G06F 16/24539 using cached or materialised query results

Abstract

A system, method, computer program and system for suspending a result set and continuing from a suspended result set for a scrollable cursor is disclosed. The rows from a row source corresponding to the scrollable cursor result set are suspended into storage so that when a fetch is requested the data can be sent (continued) from the suspended storage and does not depend on the original scrollable cursor row source or session to be available.

Claims

1. A computer implemented method for suspending a result set for scrollable cursors, comprising:

receiving a request for data in an original result set which is generated in response to the request or to a query, wherein

the original result set in a first computer readable storage medium is maintained by a first scrollable cursor state on a side of a server and a second scrollable cursor state on a side of a client;

suspending, by using a processor, the data from the original result set by copying the original result set to a second computer readable storage medium to allow the client to access, from the result set that is being suspended, the same data or same order of the data of the original result set without re-issuing or re-executing the query or a new query, wherein,

the result set that is being suspended allows the data of the original result set to be accessible by the client without retaining the first scrollable cursor state on the side of the server when the original result set is no longer available to the client, and

the client is allowed to access the same data or the same order of the data of the original result set from the result set that is being suspended without re-issuing or re-executing the query or the new query while ensuring correctness in accessing the data;

fetching the data from the original result set or from the data set that is being suspended; and

displaying a result of the act of fetching the data on a display apparatus or storing the result of the act of fetching the data in a tangible computer usable storage medium, the first computer readable storage medium, the second computer readable medium, or a storage device.

2. The computer implemented method of claim 1, wherein the act of fetching the data comprises:

determining that the result set suspension is enabled, indicating suspending is occurring or has occurred;

determining a location of the suspended data of the result set; and

returning the data from the location.

3. The method of claim 1, wherein the data comprises a row of data.

4. The computer implemented method of claim 3, wherein the act of determining a location of the row comprises:

converting a client request into a row number; and

reading the location and offset from a row mapping index, the location and offset corresponding to the converted row number.

5. The method of claim 3, wherein the row is the last row.

6. The method of claim 5, wherein a subsequent call requests a row that has a lower row number than a first call.

7. The computer implemented method of claim 1, wherein the act of suspending the data from the result set further comprises:

suspending the data into the second computer readable medium;

determining whether any of a set of constraint values have been exceeded;

determining whether there are more data to be suspended if the constraint values have not been exceeded; and

repeating the act of suspending the data, the act of determining whether any of the set of constraint values have been exceeded, and the act of determining whether there are more data to be suspended if there are more data to be suspended.

8. The method of claim 7 wherein the storage location is shareable across sessions or nodes.

9. The computer implemented method of claim 7, wherein the second computer readable storage medium comprises memory, persistent storage, or a combination of the persistent storage and the memory.

10. The computer implemented method of claim 7, wherein the second computer readable storage medium is a self describing storage.

11. The computer implemented method of claim 1, wherein the method is reentrant which is characterized by being capable of being stopped and later invoked to further suspend additional data of the original result set.

12. The computer implemented method of claim 1, wherein the result set that is being suspended is transmitted from a first node to a second node.

13. The method of claim 1, wherein the first scrollable cursor is a cursor for a distributed SQL query.

23. The computer implemented method of claim 1, wherein the result set that is being suspended is available for the life of the first scrollable cursor.

24. The computer implemented method of claim 23, wherein the life of the first scrollable cursor is the life of a client cursor.

14. A computer implemented method for continuing a suspended result set for scrollable cursors, comprising:

receiving a call for data in an original result set which is generated in response to the call or to a query, wherein

the original result set in a first computer readable medium is maintained by a first scrollable cursor state on a side of a server and a second scrollable cursor state on a side of a client;

fetching, by using a processor, the data of the original result set from a suspended result set, wherein

suspension of the original result set by copying the original result set to a second computer readable storage medium allows the client to access, from the result set that is being suspended, same data or same order of the data of the original result set without re-issuing or re-executing the query, and the result set that is being suspended allows the data of the original result set to be accessible by the client without retaining the first scrollable cursor state on the side of the server when the original result set is no longer available to the client, and

the client is allowed to access, from the result set that is being suspended, the same data or the same order of the data of the original result set without re-issuing or re-executing the query or the new query while ensuring correctness in accessing the data; and

displaying a result of the act of fetching the data on a display apparatus or storing the result of the act of fetching the data in a tangible computer usable medium, the first computer readable storage medium, the second computer readable storage medium, or a storage device.

15. The method of claim 14, wherein the data comprises a row of data.

16. The method of claim 15, wherein the row is the last row.

17. The method of claim 15, wherein a subsequent call requests a row that has a lower row number than a first call.

18. The computer implemented method of claim 14, wherein the act of fetching the data comprises:

determining that the result set suspension is enabled, indicating suspending is occurring or has occurred;

determining a location of the data of the result set; and

returning the data from the location.

19. The computer implemented method of claim 18 wherein the data comprises a row, and the act of determining the location of the subject row comprises:

converting a client request into a row number; and

reading the location and offset from a row mapping index, the location and offset corresponding to the converted row number.

20. The computer implemented method of claim 14, wherein the result set that is being suspended is available after a server session of the cursor is closed.

21. The computer implemented method of claim 14 wherein the result set that is being suspended is shareable across sessions or nodes.

22. The computer implemented method of claim 14, wherein the result set that is being suspended comprises memory, persistent storage, or a combination of the persistent storage and the memory.

25. The computer implemented method of claim 14, wherein the result set that is being suspended includes an entire cursor row source.

26. The computer implemented method of claim 14, wherein the result set that is being suspended includes a subset of a cursor row source.

27. The computer implemented method of claim 14, wherein the first scrollable cursor is a cursor for a distributed SQL query.

28. A system for continuing a suspended result set for scrollable cursors, comprising:

means for receiving a call for data in an original result set which is generated in response to a query, wherein

the original result set in a first computer readable medium is maintained by a first scrollable cursor state on a side of a server and a second scrollable cursor state on a side of a client; and

a processor for fetching the data of the original result set from a suspended result set, wherein

suspension of the original result set by copying the original result set to a second computer readable storage medium allows the client to access, from the result set that being suspended, same data or same order of the data of the original result set without re-issuing or re-executing the query or a new query, and the result set that is being suspended allows the data of the original result set to be accessible by the client without retaining the first scrollable cursor state on the side of the server when the original result set is no longer available to the client, and

the client is allowed to access, from the result set that is being suspended, the same data or the same order of the data of the original result set from the result set that is being suspended without re-issuing or re-executing the query or the new query while ensuring correctness in accessing the data; and

a display apparatus configured for displaying a result of the act of fetching the data or a tangible computer usable medium, the first computer readable storage medium, the second computer readable storage medium, or a storage device configured for storing the result.

31. The system of claim 28, wherein the result set that is being suspended is available after a server session of the first scrollable cursor is closed.

32. The system of claim 28, wherein the result set that is being suspended is shareable across sessions or nodes.

29. A computer program product comprising a volatile or non-volatile computer usable medium having executable code which, when executed by a processor, causes the processor to execute a process for continuing a suspended result set for scrollable cursors, the process comprising:

receiving a call for data in an original result set which is generated in response to the call or to a query, wherein

the original result set in a first computer readable medium is maintained by a first scrollable cursor state on a side of a server and a second scrollable cursor state on a side of a client; and

fetching, by using the processor, the data of the original result set from the suspended result set, wherein

suspension of the original result set by copying the original result set to a second computer readable storage medium allows the client to access, from the result set that is being suspended, same data or same order of the data of the original result set without re-issuing or re-executing the query or a new query, and the result set that is being suspended allows the data of the original result set to be accessible by the client without retaining the first scrollable cursor state on the side of the server when the original result set is no longer available to the client, and

the client is allowed to access, from the result set that is being suspended, the same data or the same order of the data of the original result set from the result set that is being suspended without re-issuing or re-executing the query or the new query while ensuring correctness in accessing the data; and

displaying a result of the act of fetching the data on a display apparatus or storing the result of the act of fetching the data in a tangible computer usable medium, the first computer readable storage medium, the second computer readable storage medium, or a storage device.

33. The computer program product of claim 29, wherein the result set that is being suspended is available after a server session of the first scrollable cursor is closed.

34. The computer program product of claim 29, wherein the result set that is being suspended is shareable across sessions or nodes.

30. A system for suspending a result set for scrollable cursors, comprising:

means for receiving a request for data in an original result set which is generated in response to the request or to a query, wherein

the original result set in a first computer readable storage medium is maintained by a first scrollable cursor state on a side of a server and a second scrollable cursor state on a side of a client;

a processor for suspending the data from the original result set by copying the original result set to a second computer readable storage medium to allow the client to access, from the result set that is being suspended, the same data or same order of the data of the original result set without re-issuing or re-executing the query or a new query, wherein

the result set that is being suspended allows the data of the original result set to be accessible by the client without retaining the first scrollable cursor state on the side of the server when the original result set is no longer available to the client, and

the client is allowed to access same data or same order of the data of the original result set from the result set that is being suspended without re-issuing or re-executing the query or a new query while ensuring correctness in accessing the data; and

means for fetching the data from the original result set or from the data set that is being suspended; and

a display apparatus displaying a result of the act of fetching the data or a tangible computer usable medium, the first computer readable storage medium, the second computer readable storage medium, or a storage device storing the result of the act of fetching the data.

35. The system of claim 30, wherein the result set that is being suspended is available after a server session of the first scrollable cursor is closed.

36. The system of claim 30, wherein the result set that is being suspended is shareable across sessions or nodes.