Suspending a result set and continuing from a suspended result set

• US 20060059176A1
• Filed: 04/01/2005
• Published: 03/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,613,710 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2438 Embedded query languages
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement

Abstract

A system, method, computer program and system for suspending a result set and continuing from a suspended result set for a cursor is disclosed. The rows from a row source corresponding to the 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 cursor row source or session to be available.

Claims

1. A method of suspending a result set, the method comprising:

determining that a result set is to be suspended;

storing a first set of data of the result set in a storage area; and

repeating the storing until the required data has been suspended, the storage area being available after one or more of the result set, the cursor area, and the session are no longer available.

2. The method of claim 1, wherein the storage area is self describing.

3. The method of claim 2, wherein the self-describing storage area includes data detailing a data packing layout.

4. The method of claim 3, wherein the self describing storage uses versioned data packing layouts and data compression.

5. The method of claim 1, wherein the storage area is not contiguous.

6. The method of claim 1, wherein the storage area is shareable across sessions or nodes.

7. The method of claim 6, wherein a second node has direct access to the suspended result set located in another node.

8. The method of claim 1, wherein the storage area is persistent storage, memory, or a combination of persistent storage and memory.

9. The method of claim 1, wherein the result set is a cursor result set.

10. The method of claim 1, wherein the method is reentrant.

11. The method of claim 1, wherein the required data include all or a subset of the result set.

12. The method of claim 1, further comprising updating a row mapping index.

13. The method of claim 1, further comprising:

checking if constraint values have been exceeded; and

pausing the suspending, if the constraint values have been exceeded.

14. The method of claim 13, wherein the constraint values are based on one cursor or on a session.

15. The method of claim 13, wherein suspending the row is performed during an absolute time period added to an average row fetch time or as a percent delay to an average row fetch time.

16. The method of claim 13, wherein suspending the row comprises storing a set number of rows during a row fetch or storing a portion of a row during a row fetch, the portion set at runtime.

17. The method of claim 1, wherein the storing of the first set of data comprises storing a first row, the row comprising row bytes, the method comprising:

a) caching row bytes into a first storage area;

b) determining if the first storage area is full;

c) flushing the first storage area into a second storage area, if the first storage area is full;

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

e) determining whether the entire row has been cached;

f) repeating a)-e) if the entire row has not been cached; and

g) flushing the first storage area into a second storage area, if any of the set of constraint values have been exceeded and it is determined that the first storage area needed to be flushed.

18. The method of claim 1, further comprising

simultaneously fetching a second set of data of the result set;

and

repeating the fetching until all the requested data has been fetched.

19. A method of continuing a suspended result set, the method comprising:

determining the result set is suspended;

determining the location of a subject row in the suspended result set; and

fetching the subject row from the location.

20. The method of claim 19, wherein suspending the result set and the act of fetching occur contemporaneously.

21. The method of claim 19, wherein the suspending and fetching occur such that any row of the result set can be fetched in any order.

22. The method of claim 19, wherein the suspended result set is self-describing.

23. The method of claim 19, wherein determining the location of a subject row comprises reading a location information of a row mapping index, the location information corresponding to the requested row and including an offset.

24. The method of claim 19, wherein the suspending comprises:

storing a first row of the result set in a storage area; and

repeating the storing until the required rows have been suspended, the storage area being available after the result set is no loner available.

25. A method of continuing from a result set being suspended, the method comprising:

a) determining the result set suspension is enabled;

b) determining if a subject row in the result set has been suspended;

c) determining if the subject row is to be suspended, if the subject row has not been suspended;

d) suspending the subject row if it is determined that the subject row is to be suspended;

e) extracting and returning the subject row, from a suspended result set if the subject row has been suspended, or from the cursor row source if the row is not to be suspended; and

f) repeating a)-f) until all the rows in the result set are processed.

26. The method of claim 25, further comprising:

g) determining if the cursor should be closed if there are no more rows in the result set;

h) closing the cursor if it is determined that the cursor should be closed; and

i) waiting for a client call.

27. The method of claim 25, wherein the subject row is returned from the row source prior to suspending.

28. A computer program product comprising a computer usable medium having executable code to execute a process for suspending a result set, the process comprising:

determining that a result set is to be suspended;

storing a first set of data of the result set in a storage area; and

repeating the storing until the required data has been suspended, the storage area being available after one or more of the result set, the cursor area, and the session are no longer available.

29. The computer program product of claim 28, further comprising continuing from the suspended result set.

30. A computer program product comprising a computer usable medium having executable code to execute a process for continuing from a result set being suspended, the process comprising:

a) determining the result set suspension is enabled;

b) determining if a subject row in the result set has been suspended;

c) determining if the subject row is to be suspended, if the subject row has not been suspended;

d) suspending the subject row if it is determined that the subject row is to be suspended;

e) extracting and returning the subject row, from a suspended result set if the subject row has been suspended, or from the cursor row source if the row is not to be suspended; and

f) repeating a)-f) until all the rows in the result set are processed.

31. The computer program of claim 30, wherein the subject row is returned from the row source prior to the suspending.

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

determining the result set is suspended;

determining the location of a subject row in the suspended result set; and

fetching the subject row from the location.