Method, system, and program for accessing rows in one or more tables satisfying a search criteria

• US 6,856,996 B2
• Filed: 03/30/2001
• Issued: 02/15/2005
• Est. Priority Date: 03/30/2001
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Chow, Jyh-Herng, Leung, Ting Yu, Pham, Thanh Tan
• Primary Examiner(s): LE, UYEN T
• Application Number:
  US09/822,899
• Publication Number:
  US 20020143751A1
• Time in Patent Office: 1,418 Days
• Field of Search: 707/101, 707/100, 707/102, 707/3, 707/2
• 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
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Provided is a method, system, and program for accessing rows of data from a base table satisfying a search criteria. An initial request to access rows in the base table is received, including the search criteria specifying predicates for one or more columns in the base table. A data structure is generated to include information on qualifying base table rows that satisfy the search criteria. Notification is provided that the qualifying base table rows are accessible before information on all the qualifying base table rows is included in the data structure. A request is then received for one qualifying base table row. A determination is made as to whether the data structure includes information on the requested qualifying base table row. If so, the information in the data structure is used to provide access to the requested qualifying base table row.

Claims

1. A method for accessing rows of data from a base table satisfying a search criteria, comprising:

receiving an initial request to access rows in the base table including the search criteria specifying predicates for one or more columns in the base table;

generating a data structure to include information on qualifying base table rows that satisfy the search criteria in response to the initial request;

providing notification that the qualifying base table rows are accessible before information on all the qualifying base table rows is included in the data structure;

receiving a second request for one qualifying base table row after the notification is provided;

determining whether the data structure includes information on the requested qualifying base table row in the base table; and

using the information in the data structure to access the requested qualifying base table row from the base table, wherein the requested qualifying row is returned from the base table to the second request.

2. The method of claim 1, further comprising:

scanning the base table to locate a next qualifying base table row after determining that the data structure does not include information on the requested qualifying base table row;

locating the next qualifying base table row; and

adding information on the located qualifying base table row to the data structure.

3. The method of claim 1, wherein the information on the requested qualifying base table row maintained in the data structure comprises a unique row identifier of the row in the base table, wherein the row identifier in the data structure is used to access the qualifying base table row identified by the row identifier.

4. The method of claim 2, wherein the data structure maintains one row for each base table row for which information is maintained, further comprising:

positioning a first pointer to point to a position in the data structure including information on the last requested qualifying base table row; and

positioning a second pointer to address a most recently located qualifying base table row in the base table for which information was added to the data structure, wherein the data structure includes information on all the qualifying base table rows after a last row in the base table is evaluated with the search criteria.

5. The method of claim 4, wherein the initial request to access data in the base table comprises a request to initialize a database cursor.

6. The method of claim 5, wherein the second request for the qualifying base table row comprises a FETCH request with an offset, wherein the requested qualifying base table row is at the offset from the last requested qualifying base table row, and wherein determining whether the data structure includes information on the requested qualifying base table row comprises determining whether the data structure includes one row at the offset from the row addressed by the first pointer.

7. The method of claim 6, wherein the FETCH request is capable of comprising one of a:

FETCH NEXT request;

FETCH PRIOR request;

FETCH LAST request;

FETCH FIRST request;

FETCH RELATIVE request;

or FETCH ABSOLUTE request.

8. The method of claim 6, wherein the FETCH request comprises a FETCH NEXT request to FETCH the next qualifying base table row following the base table row identified by the information in the data structure addressed by the first pointer.

9. The method of claim 6, wherein the FETCH request comprises a request for the qualifying base table row at the offset from the base table row identified by the information in the row in the data structure row addressed by the first pointer, wherein after determining that the data structure does not include information on the requested qualifying base table row and if the offset number of rows from the row in the data structure addressed by the pointer is more than one row beyond a last row in the data structure, further performing:

locating multiple qualifying base table rows following the second pointer;

adding information on the located multiple qualifying base table rows to corresponding added rows in the data structure; and

positioning the first pointer to the last row in the data structure after row are added to the data structure to include information on the multiple located base table rows, wherein the last row in the data structure includes information on the requested qualifying base table row.

10. The method of claim 9, wherein if the offset specifies one qualifying base table row preceding the qualifying base table row for which information is maintained in the row in the data structure addressed by the first pointer, further performing:

positioning the first pointer to the row in the data structure at the offset from the row addressed by the first pointer.

11. The method of claim 6, wherein the FETCH request comprises a FETCH ABSOLUTE request to fetch one qualifying base table row at the offset from a first or last qualifying row in the base table, further comprising:

scanning the entire base table to locate further qualifying base table rows if the FETCH ABSOLUTE request specifies one base table row at the offset from the last qualifying base table row;

adding information on all located qualifying base table rows to rows in the data structure; and

positioning the first pointer to one row in the data structure preceding a last row in the data structure by the specified offset.

12. The method of claim 11, wherein the offset specifies a row from the first qualifying base table row for which information is not provided in the data structure, further comprising:

locating one or more qualifying base table rows following the second pointer;

adding information on the located multiple qualifying base table rows to corresponding added rows in the data structure; and

positioning the first pointer to the row in the data structure following the first row in the data structure by the specified offset.

13. The method of claim 1, wherein the method for accessing data is implemented in a hand held computing device.

14. The method of claim 1, wherein the notification indicates that a result set including all qualifying base table rows is complete.

15. The method of claim 1, wherein the notification is provided in response to adding information on a first qualifying base table row to the data structure.

16. A system for accessing rows of data from a base table satisfying a search criteria, comprising:

means for receiving an initial request to access rows in the base table including the search criteria specifying predicates for one or more columns in the base table;

means for generating a data structure to include information on qualifying base table rows that satisfy the search criteria from the base table;

means for providing notification that the qualifying base table rows are accessible before information on all the qualifying base table rows is included in the data structure; and

means for receiving a second request for one qualifying base table row after the notification is provided;

means for determining whether the data structure includes information on the requested qualifying base table row in the base table; and

means for using the information in the data structure to access the requested qualifying base table row from the base table, wherein the requested qualifying row is returned from the base table to the second request.

17. The system of claim 16, further comprising:

means for scanning the base table to locate a next qualifying base table row after determining that the data structure does not include information on the requested qualifying base table row;

means for locating the next qualifying base table row; and

means for adding information on the located qualifying base table row to the data structure.

18. The system of claim 16, wherein the information on the requested qualifying base table row maintained in the data structure comprises a unique row identifier of the row in the base table, wherein the row identifier in the data structure is used to access the qualifying base table row identified by the row identifier.

19. The system of claim 17, wherein the data structure maintains one row for each base table row for which information is maintained, further comprising:

means for positioning a first pointer to point to a position in the data structure including information on the last requested qualifying base table row; and

means for positioning a second pointer to address a most recently located qualifying base table row in the base table for which information was added to the data structure, wherein the data structure includes information on all the qualifying base table rows after a last row in the base table is evaluated with the search criteria.

20. The system of claim 19, wherein the initial request to access data in the base table comprises a request to initialize a database cursor.

21. The system of claim 20, wherein the second request for the qualifying base table row comprises a FETCH request with an offset, wherein the requested qualifying base table row is at the offset from the last requested qualifying base table row, and wherein determining whether the data structure includes information on the requested qualifying base table row comprises determining whether the data structure includes one row at the offset from the row addressed by the first pointer.

22. The system of claim 21, wherein the FETCH request is capable of comprising one of a:

FETCH NEXT request;

FETCH PRIOR request;

FETCH LAST request;

FETCH FIRST request;

FETCH RELATIVE request;

or FETCH ABSOLUTE request.

23. The system of claim 21, wherein the FETCH request comprises a FETCH NEXT request to FETCH the next qualifying base table row following the base table row identified by the information in the data structure addressed by the first pointer.

24. The system of claim 21, wherein the FETCH request comprises a request for the qualifying base table row at the offset from the base table row identified by the information in the row in the data structure row addressed by the first pointer, further comprising:

means for locating multiple qualifying base table rows following the second pointer after determining that the data structure does not include information on the requested qualifying base table row and if the offset number of rows from the row in the data structure addressed by the pointer is more than one row beyond a last row in the data structure;

means for adding information on the located multiple qualifying base table rows to corresponding added rows in the data structure; and

means for positioning on the first pointer to the last row in the data structure after rows are added to the data structure to include information on the multiple located base table rows, wherein the last row in the data structure includes information on the requested qualifying base table row.

25. The system of claim 24, further comprising:

means for positioning the first pointer to the row in the data structure at the offset from the row addressed by the first pointer if the offset specifies one qualifying base table row preceding the qualifying base table row for which information is maintained in the row data structure addressed by the first pointer.

26. The system of claim 21, wherein the FETCH request comprises a FETCH ABSOLUTE request to fetch one qualifying base table row at the offset from a first or last qualifying row in the base table, further comprising:

means for scanning the entire base table to locate further qualifying base table rows if the FETCH ABSOLUTE request specifies one base table row at the offset from the last qualifying base table row;

means for adding information on all located qualifying base table rows to rows in the data structure; and

means for positioning the first pointer to one row in the data structure preceding a last row in the data structure by the specified offset.

27. The system of claim 26, wherein the offset specifies a row from the first row qualifying base table row for which information is not provided in the data structure, further comprising:

means for locating one or more qualifying base table rows following the second pointer;

means for adding information on the located multiple qualifying base table rows to corresponding added rows in the data structure; and

means for positioning the first pointer to the row in the data structure following the first row in the data structure by the specified offset.

28. The system of claim 16, wherein the method for accessing data is implemented in a hand held computing device.

29. The system of claim 16, wherein the notification indicates that a result set including all qualifying base table rows is complete.

30. The system of claim 16, wherein the notification is provided in response to adding information on a first qualifying base table row the data structure.

31. A program for accessing rows of data from a base table satisfying a search criteria, wherein the program is comprised of code implemented in a computer readable medium that is capable of causing a processor to perform:

receiving an initial request to access rows in the base table including the search criteria specifying predicates for one or more columns in the base table;

generating a data structure to include information on qualifying base table rows that satisfy the search criteria in response to the initial request;

providing notification that the qualifying base table rows are accessible before information on all the qualifying base table rows is included in the data structure; and

receiving a second request for one qualifying base table row after the notification is provided;

determining whether the data structure includes information on the requested qualifying base table row in the base table; and

using the information in the data structure to access the requested qualifying base table row from the base table, wherein the requested qualifying row is returned from the base table to the second request.

32. The program of claim 31, wherein the program code is further capable of causing the processor to perform:

scanning the base table to locate a next qualifying base table row after determining that the data structure does not include information on the requested qualifying base table row;

locating the next qualifying base table row; and

adding information on the located qualifying base table row to the data structure.

33. The program of claim 31, wherein the information on the requested qualifying base table row maintained in the data structure comprises a unique row identifier of the row in the base table, wherein the row identifier in the data structure is used to access the qualifying base table row identified by the row identifier.

34. The program of claim 33, wherein the data structure maintains one row for each base table row for which information is maintained, and wherein the program code is further capable of causing the processor to perform:

positioning a first pointer to point to a position in the data structure including information on the last requested qualifying base table row; and

positioning a second pointer to address a most recently located qualifying base table row in the base table for which information was added to the data structure, wherein the data structure includes information on all the qualifying base table rows after a last row in the base table is evaluated with the search criteria.

35. The program of claim 34, wherein the initial request to access data in the base table comprises a request to initialize a database tumor.

36. The program of claim 35, wherein the second request for the qualifying base table row comprises a FETCH request with an offset, wherein the requested qualifying base table row is at the offset from the last requested qualifying base table row, and wherein determining whether the data structure includes information on the requested qualifying base table row comprises determining whether the data structure includes one row at the offset from the row addressed by the first pointer.

37. The program of claim 36, wherein the FETCH request is capable of comprising one of a:

FETCH NEXT request;

FETCH PRIOR request;

FETCH LAST request;

FETCH FIRST request;

FETCH RELATIVE request;

or FETCH ABSOLUTE request.

38. The program of claim 36, wherein the FETCH request comprises a FETCH NEXT request to FETCH the next qualifying base table row following the base table row identified by the information in the data structure addressed by the first pointer.

39. The program of claim 36, wherein the FETCH request comprises a request for the qualifying base table row at the offset from the base table row identified by the information in the row in the data structure row addressed by the first pointer, wherein after determining that the data structure does not include information on the requested qualifying base table row and if the offset number of rows from the row in the data structure addressed by the pointer is more than one row beyond a last row in the data structure, and wherein the program code is further capable of causing the processor to perform:

locating multiple qualifying base table rows following the second pointer;

adding information on the located multiple qualifying base table rows to corresponding added rows in the data structure; and

positioning the first pointer to the last row in the data structure after row are added to the data structure to include information on the multiple located base table rows, wherein the last row in the data structure includes information on the requested qualifying base table row.

40. The program of claim 39, wherein if the offset specifies one qualifying base table row preceding the qualifying base table row for which information is maintained in the row in the data structure addressed by the first pointer, and wherein the program code is further capable of causing the processor to perform:

positioning the first pointer to the row in the data structure at the offset from the row addressed by the first pointer.

41. The program of claim 36, wherein the FETCH request comprises a FETCH ABSOLUTE request to fetch one qualifying base table row at the offset from a first or last qualifying row in the base table, and wherein the program code is further capable of causing the processor to perform:

scanning the entire base table to locate further qualifying base table rows if the FETCH ABSOLUTE request specifies one base table row at the offset from the last qualifying base table row;

adding information on all located qualifying base table rows to rows in the data structure; and

positioning the first pointer to one row in the data structure preceding a last row in the data structure by the specified offset.

42. The program of claim 41, wherein the offset specifies a row from the first row qualifying base table row for which information is not provided in the data structure, and wherein the program code is further capable of causing the processor to perform:

locating one or more qualifying base table rows following the second pointer;

adding information on the located multiple qualifying base table rows to corresponding added rows in the data structure; and

positioning the first pointer to the row in the data structure following the first row in the data structure by the specified offset.

43. The program of claim 31, wherein the method for accessing data is implemented in a hand held computing device.

44. The article of manufacture of claim 31, wherein the notification indicates that a result set including all qualifying base table rows is complete.

45. The article of manufacture of claim 31, wherein the notification is provided in response to adding information on a first qualifying base table row to the data structure.