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

• US 20060036616A1
• Filed: 04/01/2005
• Published: 02/16/2006
• Est. Priority Date: 08/12/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gollapudi, Sreenivas, Chatterjee, Debashish, Krishnaswamy, Srinath, Kaluskar, Sanjay, Chidambaran, Lakshminarayanan, Bastawala, Mehul D.
• Granted Patent: US 7,743,333 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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 method for suspending a result set for scrollable cursors, comprising:

a) receiving a request from a scrollable cursor for data in a cursor result set;

b) fetching the data of the result set; and

c) suspending the data from the result set.

2. The method of claim 1, wherein fetching the row comprises:

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

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

f) returning the data from the location.

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

4. The method of claim 3, wherein determining the location of a row comprises,

converting the 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 method of claim 1, wherein suspending the data from the result set further comprises:

g) suspending the data into a storage location;

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

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

j) repeating g)-i) 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 method of claim 7, wherein the storage area comprises memory, persistent storage, or a combination of persistent storage and memory.

10. The method of claim 7, wherein the storage area is a self describing storage.

11. The method of claim 1;

wherein the method is reentrant.

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

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

23. The method of claim 1, wherein the suspended result set is available for the life of the scrollable cursor.

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

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

a) receiving a call from a scrollable cursor for data in the cursor result set; and

b) fetching the data of the result set from the suspended result set.

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 method of claim 14, wherein fetching the data comprises:

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

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

f) returning the data from the location.

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

converting the 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 method of claim 14, wherein the suspended result set is available after a server session of the cursor is closed.

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

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

25. The method of claim 14, wherein the suspended result set includes an entire cursor row source.

26. The method of claim 14, wherein the suspended result set include a subset of a cursor row source.

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

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

a) logic for receiving a call from a scrollable cursor for data in the cursor result set; and

b) logic for fetching the data of the result set from the suspended result set.

29. A computer program product comprising a computer usable medium having executable code to execute a process for continuing a suspended result set for scrollable cursors, the process comprising:

a) receiving a call from a scrollable cursor for data in the cursor result set; and

b) fetching the data of the result set from the suspended result set.

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

a) logic for receiving a request from a scrollable cursor for data in a cursor result set;

b) logic for fetching the data of the result set; and

c) logic for suspending the data from the result set.