Server integrated system and methods for processing precomputed views

• US 6,594,653 B2
• Filed: 03/25/1999
• Issued: 07/15/2003
• Est. Priority Date: 03/27/1998
• Status: Expired due to Term

• Current Assignee: Workday Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Cole, Richard L., Johnson, Galt, Colby, Latha S., Wilhite, David G. Jr., Haslam, Edward P., Jazayeri, Nasi, McKenna, William J.
• Primary Examiner(s): HOMERE, JEAN RAYMOND
• Application Number:
  US09/277,040
• Publication Number:
  US 20020077997A1
• Time in Patent Office: 1,573 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/10, 707/102, 345/348, 709/203
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• 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/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

Server-integrated methods and apparatus for processing precomputed views for answering user queries on a database are described. In accordance with one embodiment, a system for answering user queries on a database stored on a server includes a query processing system integrated into the server and configured to respond to user queries, and a query processing system integrated into the server and configured to process precomputed results of user queries on the database. In another aspect, a method of answering user queries on a database stored on a server includes responding to user queries received at the server, and processing, at the server, precomputed results of user queries on the database.

Claims

1. A system for answering queries sent to a database server, comprising:

a query processing system integrated into the database server and configured to respond to queries; and

a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy by providing advice on which potential views should be precomputed and which existing views should be dropped and wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server.

2. The system of claim 1, wherein the advisor is configured to analyze the effectiveness of a precomputation strategy.

3. The system of claim 1, wherein the advisor is configured to determine a precomputation strategy.

4. The system of claim 3, wherein the precomputation strategy is determined based on query history.

5. The system of claim 1, wherein the advisor is configured to generate a report on a precomputation strategy.

6. The system of claim 5, wherein the report is generated in the form of a relational table.

7. The system of claim 5, wherein the report is generated in response to a query.

8. The system of claims 7, wherein the query is expressed in SQL.

9. The system of claim 5, wherein the generated report includes precomputed view utilization.

10. The system of claim 1, wherein the advisor is configured to analyze the effectiveness of potential precomputed views.

11. The system of claim 1, wherein the advisor is configured to analyze the effectiveness of existing precomputed views.

12. The system of claim 1, wherein the precomputed view processing system includes a query rewriter configured to rewrite queries submitted against the database using precomputed views.

13. The system of claim 12, wherein the advisor utilizes the query rewriter.

14. The system of claim 1, wherein the query processing system utilizes meta data.

15. The system of claim 14, wherein the meta data include functional dependencies.

16. The system of claim 1, wherein the advisor is configured to define an analysis space consisting of a subset of all possible precomputed views for the database.

17. The system of claim 16, wherein the analysis space is based on query history.

18. The system of claim 1, wherein the advisor is configured to generate a candidate view.

19. The system of claim 18, wherein the candidate view is generated in response to a query that was not rewritten to use a precomputed view.

20. The system of claim 1, wherein the queries contain aggregation.

21. The system of claim 1, wherein the advisor is configured to log queries that are rewritten to access data in precomputed views, candidate views, and information about correlated subqueries.

22. The system of claim 1, wherein the one or more capabilities of the query processing system include a SQL interface.

23. The system of claim 1, wherein integration of the precomputed view processing system with the query processing system comprises storing precomputed view metadata in system catalogs.

24. A system for answering queries sent to a database server, comprising:

a query processing system integrated into the database server and configured to respond to queries; and

a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy, wherein the advisor is configured to generate a report on a precomputation strategy, and wherein the generated report includes a measure of the benefit of an existing or potential precomputed view relative to other existing or potential precomputed views.

25. A system for answering queries sent to a database server, comprising:

a query processing system integrated into the database server and configured to respond to queries; and

a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy, wherein the advisor is configured to generate a report on a precomputation strategy, and wherein the generated report includes a measure of costs associated with existing or potential precomputed views.

26. A system for answering queries sent to a database server, comprising:

a query processing system integrated into the database server and configured to respond to queries; and

a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy, wherein the advisor generates a candidate view, and wherein the candidate view is generated in response to a query that was rewritten to use a first precomputed view and wherein the generated candidate view is different from the first precomputed view.

27. A system for answering queries sent to a database server, comprising:

a query processing system integrated into the database server and configured to respond to queries; and

a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy, wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server, and wherein the one or more capabilities of the query processing system include estimating the size of a potential precomputed view.

28. A system for answering queries sent to a database server, comprising:

a query processing system integrated into the database server and configured to respond to queries; and

a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes a rewriter configured to rewrite queries using one or more precomputed views utilizing functional dependencies and wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server.

29. The system of claim 28, wherein the rewriter is configured to use a cost-based analysis to choose among potential rewrites.

30. A system for answering queries sent to a database server, comprising:

a query processing system integrated into the database server and configured to respond to queries; and

a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system enables creating and dropping precomputed views, enables rewriting of queries using precomputed views, and provides advice on a precomputation strategy by providing advice on which potential views should be precomputed and which existing views should be dropped and wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server.

31. A method for answering queries sent to a database server, comprising:

responding to queries using a query processing system integrated into the database server; and

processing precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy by providing advice on which potential views should be precomputed and which existing views should be dropped and wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server.

32. The method of claim 31, wherein the advisor analyzes the effectiveness of a precomputation strategy.

33. The method of claim 31, wherein the advisor determines a precomputation strategy.

34. The method of claim 33, wherein the precomputation strategy is determined based on query history.

35. The method of claim 31, wherein the advisor generates a report on a precomputation strategy.

36. The method of claim 35, wherein the report is generated in the form of a relational table.

37. The method of claim 35, wherein the report is generated in response to a query.

38. The method of claim 37, wherein the query is expressed in SQL.

39. The method of claim 35, wherein the generated report includes precomputed view utilization.

40. The method of claim 31, wherein the advisor analyzes the effectiveness of potential precomputed views.

41. The method of claim 31, wherein the advisor analyzes the effectiveness of existing precomputed views.

42. The method of claim 31, wherein the precomputed view processing system includes a query rewriter that rewrites queries submitted against the database using precomputed views.

43. The method of claim 42, wherein the advisor utilizes the query rewriter.

44. The method of claim 31, wherein the query processing system utilizes meta data.

45. The method of claim 44, wherein the meta data include functional dependencies.

46. The method of claim 31, wherein the advisor defines an analysis space consisting of a subset of all possible precomputed views for the database.

47. The method of claim 46, wherein the analysis space is based on query history.

48. The method of claim 31, wherein the advisor generates a candidate view.

49. The method of claim 48, wherein the candidate view is generated in response to a query that was not rewritten to use a precomputed view.

50. The method of claim 31, wherein the queries contain aggregation.

51. The method of claim 31, wherein the advisor logs queries that are rewritten to access data in precomputed views, candidate views, and information about correlated subqueries.

52. The method of claim 31, wherein the one or more capabilities of the query processing system include a SQL interface.

53. The method of claim 31, wherein integration of the precomputed view processing system with the query processing system comprises storing precomputed view metadata in system catalogs.

54. A method for answering queries sent to a database server, comprising:

responding to queries using a query processing system integrated into the database server; and

processing precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy, wherein the advisor generates a report on a precomputation strategy, and wherein the generated report includes a measure of the benefit of an existing or potential precomputed view relative to other existing or potential precomputed views.

55. A method for answering queries sent to a database server, comprising:

responding to queries using a query processing system integrated into the database server; and

processing precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy, wherein the advisor generates a report on a precomputation strategy, and wherein the generated report includes a measure of costs associated with existing or potential precomputed views.

56. A method for answering queries sent to a database server, comprising:

responding to queries using a query processing system integrated into the database server; and

processing precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy, wherein the advisor generates a candidate view, and wherein the candidate view is generated in response to a query that was rewritten to use a first precomputed view and wherein the generated candidate view is different from the first precomputed view.

57. A method for answering queries sent to a database server, comprising:

responding to queries using a query processing system integrated into the database server; and

processing precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy, wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server, and wherein the one or more capabilities of the query processing system include estimating the size of a potential precomputed view.

58. A method for answering queries sent to a database server, comprising:

responding to queries using a query processing system integrated into the database server; and

processing precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes a rewriter configured to rewrite queries using one or more precomputed views utilizing functional dependencies and wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server.

59. The method of claim 58, wherein the rewriter uses a cost-based analysis to choose among potential rewrites.

60. A method for answering queries sent to a database server, comprising:

responding to the queries using a query processing system integrated into the database server; and

processing precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system enables creating and dropping precomputed views, enables rewriting of queries using precomputed views, and provides advice on a precomputation strategy by providing advice on which potential views should be precomputed and which existing views should be dropped and wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server.

61. A computer program product, tangibly stored on a computer-readable medium, for answering queries sent to a database server, comprising instructions operable to cause a programmable processor to:

respond to the queries using a query processing system integrated into the database server to respond to queries; and

process precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy by providing advice on which potential views should be precomputed and which existing views should be dropped and wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server.

62. The computer program product of claim 61, further comprising instructions at the advisor operable to cause a programmable processor to analyze the effectiveness of a precomputation strategy.

63. The computer program product of claim 61, further comprising instructions at the advisor operable to cause a programmable processor to determine a precomputation strategy.

64. The computer program product of claim 63, wherein the precomputation strategy is determined based on query history.

65. The computer program product of claim 61, further comprising instructions at the advisor operable to cause a programmable processor to generate a report on a precomputation strategy.

66. The computer program product of claim 65, wherein the report is generated in the form of a relational table.

67. The computer program product of claim 65, wherein the report is generated in response to a query.

68. The computer program product of claim 67, wherein the query is expressed in SQL.

69. The computer program product of claim 65, wherein the generated report includes precomputed view utilization.

70. The computer program product of claim 61, further comprising instructions at the advisor operable to cause a programmable processor to analyze the effectiveness of potential precomputed views.

71. The computer program product of claim 61, further comprising instructions at the advisor operable to cause a programmable processor to analyze the effectiveness of existing precomputed views.

72. The computer program product of claim 61, wherein the precomputed view processing system includes a query rewriter for rewriting queries submitted against the database using precomputed views.

73. The computer program product of claim 72, further comprising instructions at the advisor operable to cause a programmable processor to utilize the query rewriter.

74. The computer program product of claim 61, wherein the query processing system utilizes meta data.

75. The computer program product of claim 74, wherein the meta data include functional dependencies.

76. The computer program product of claim 61, further comprising instructions at the advisor operable to cause a programmable processor to define an analysis space consisting of a subset of all possible precomputed views for the database.

77. The computer program product of claim 76, wherein the analysis space is based on query history.

78. The computer program product of claim 61, further comprising instructions at the advisor operable to cause a programmable processor to generate a candidate view.

79. The computer program product of claim 78, wherein the candidate view is generated in response to a query that was not rewritten to use a precomputed view.

80. The computer program product of claim 61, wherein the queries contain aggregation.

81. The computer program product of claim 61, further comprising instructions at the advisor operable to cause a programmable processor to log queries that are rewritten to access data in precomputed views, candidate views, and information about correlated subqueries.

82. The computer program product of claim 61, wherein the one or more capabilities of the query processing system include a SQL interface.

83. The computer program product of claim 61, wherein integration of the precomputed view processing system with the query processing system comprises storing precomputed view metadata in system catalogs.

84. A computer program product, tangibly stored on a computer-readable medium, for answering queries sent to a database server, comprising instructions operable to cause a programmable processor to:

respond to the queries using a query processing system integrated into the database server to respond to queries; and

process precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes a rewriter configured to rewrite queries using one or more precomputed views utilizing functional dependencies and wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server.

85. The computer program product of claim 84, further comprising instructions at the rewriter operable to cause a programmable processor to use a cost-based analysis to choose among potential rewrites.

86. A computer program product, tangibly stored on a computer-readable medium, for answering queries sent to a database server, comprising instructions operable to cause a programmable processor to:

respond to the queries using a query processing system integrated into the database server to respond to queries; and

process precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy;

generate a report on a precomputation strategy, wherein the generated report includes a measure of the benefit of an existing or potential precomputed view relative to other existing or potential precomputed views.

87. A computer program product, tangibly stored on a computer-readable medium, for answering queries sent to a database server, comprising instructions operable to cause a programmable processor to:

respond to the queries using a query processing system integrated into the database server to respond to queries; and

process precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy;

generate a report on a precomputation strategy, wherein the generated report includes a measure of costs associated with existing or potential precomputed views.

88. A computer program product, tangibly stored on a computer-readable medium, for answering queries sent to a database server, comprising instructions operable to cause a programmable processor to:

respond to the queries using a query processing system integrated into the database server to respond to queries;

process precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy; and

generate a candidate view, wherein the candidate view is generated in response to a query that was rewritten to use a first precomputed view and wherein the generated candidate view is different from the first precomputed view.

89. A computer program product, tangibly stored on a computer-readable medium, for answering queries sent to a database server, comprising instructions operable to cause a programmable processor to:

respond to the queries using a query processing system integrated into the database server to respond to queries; and

process precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system includes an advisor that provides advice on a precomputation strategy, wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server, and wherein the one or more capabilities of the query processing system include estimating the size of a potential precomputed view.

90. A computer program product, tangibly stored on a computer-readable medium, for answering queries sent to a database server, comprising instructions operable to cause a programmable processor to:

respond to the queries using a query processing system integrated into the database server to respond to queries; and

process precomputed views using a precomputed view processing system integrated with the query processing system, wherein the precomputed view processing system enables creating and dropping precomputed views, enables rewriting of queries using precomputed views, and provides advice on a precomputation strategy by providing advice on which potential views should be precomputed and which existing views should be dropped and wherein integration of the precomputed view processing system with the query processing system integrated into the database server comprises utilizing one or more capabilities of the query processing system located at the database server.