System and method for determining and generating candidate views for a database

• US 6,480,836 B1
• Filed: 03/25/1999
• Issued: 11/12/2002
• 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., McKenna, William J., Johnson, Galt, Colby, Latha S., Wilhite, David G. Jr., Haslam, Edward P., Jazayeri, Nasi
• Primary Examiner(s): HOMERE, JEAN RAYMOND
• Application Number:
  US09/277,041
• Time in Patent Office: 1,328 Days
• Field of Search: 707/2, 707/3, 707/102, 707/201
• US Class Current: 707/717
• 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

The invention features methods and apparatus for processing precomputed views for answering user queries on a database. In accordance with one aspect, a user query on the database is analyzed, and a candidate view based upon the user query is generated.

Claims

1. A method for determining and generating a candidate view for, use in answering subsequent queries on a database, comprising:

analyzing a query on the database that may or may not reference any views; and

based upon the analyzed query, determining and generating the candidate view that can be used to create a precomputed view containing precomputed results of the generated candidate view, wherein the precomputed view can be used to answer a set of queries that are subsequently submitted to the database and wherein the set of queries that can be answered using the precomputed view includes the analyzed query and wherein the analyzed query and the candidate view can be different.

2. The method of claim 1, further comprising generating a log record for the generated candidate view.

3. The method of claim 2, wherein the precomputed view is a first precomputed view and wherein generating the log record for the generated candidate view comprises determining whether the analyzed query was rewritten to use a second precomputed view.

4. The method of claim 2, wherein generating the log record comprises determining whether the rewritten query includes aggregation.

5. The method of claim 1, wherein the candidate view is generated in SQL.

6. The method of claim 1, wherein the candidate view is generated based upon functional dependencies between columns of tables in the database.

7. The method of claim 1, wherein the candidate view is generated based upon primary key information.

8. The method of claim 1, wherein the candidate view is generated based upon foreign key information relating two or more tables in the database.

9. The method of claim 1, wherein the candidate view is generated based upon user-specified hierarchies.

10. The method of claim 1, wherein the candidate view is generated based upon capabilities of the query processing system.

11. The method of claim 10, wherein the precomputed view based on the generated candidate view would be usable by a query rewriting facility to rewrite the analyzed query.

12. The method of claim 1, wherein the precomputed view is a first precomputed view and wherein the candidate view is generated for the analyzed query that has been rewritten to use a second precomputed view.

13. The method of claim 12, wherein the candidate view is generated when additional aggregation is present in the rewritten query.

14. The method of claim 12, wherein the candidate view is generated when additional aggregation is not present in the rewritten query.

15. The method of claim 1, further comprising generating one or more additional candidate views based upon the analyzed query.

16. The method of claim 1, further comprising characterizing the generated candidate view.

17. The method of claim 16, wherein characterizing the generated candidate view comprises generating a report that reflects current aggregation strategy effectiveness.

18. The method of claim 16, wherein characterizing the generated candidate view comprises generating a report that includes information about the generated candidate view.

19. The method of claim 16, wherein characterizing the generated candidate view comprises generating a measure of the benefit of the generated candidate view relative to other views.

20. The method of claim 1, further comprising:

generating the precomputed view based on the generated candidate view.

21. The method of claim 20, further comprising:

answering at least one of the subsequent queries on the database using the precomputed view.

22. An apparatus for determining and generating a candidate view for use in answering subsequent queries on a database, comprising:

a query processor; and

a precomputed view processor configured to analyze a query on the database, and based upon the analyzed query, to determine and generate the candidate view that can be used to create precomputed view containing precomputed results of the generated candidate view, wherein the precomputed view can be used to answer a set of queries that are subsequently submitted to the database and wherein the set of queries that can be answered using the precomputed view includes the analyzed query and wherein the analyzed query and the candidate view can be different.

23. The apparatus of claim 22, wherein the precomputed view processor is configured to generate a log record for the generated candidate view.

24. The apparatus of claim 23, wherein the precomputed view is a first precomputed view and wherein the generation of the log record includes determining whether the analyzed query was rewritten to use a second precomputed view.

25. The apparatus of claim 23, wherein the generation of the log record includes determining whether the rewritten query includes aggregation.

26. The apparatus of claim 22, wherein the candidate view is generated in SQL.

27. The apparatus of claim 22, wherein the candidate view is generated based upon functional dependencies between columns of tables in the database.

28. The apparatus of claim 22, wherein the candidate view is generated based upon primary key information.

29. The apparatus of claim 22, wherein the candidate view is generated based upon foreign key information relating two or more tables in the database.

30. The apparatus of claim 22, wherein the candidate view is generated based upon user-specified hierarchies.

31. The apparatus of claim 22, wherein the candidate view is generated based upon capabilities of the query processing system.

32. The apparatus of claim 31, wherein the precomputed view based on the generated candidate view would be usable by a query rewriting facility to rewrite the analyzed query.

33. The apparatus of claim 22, wherein the precomputed view is a first precomputed view and wherein the candidate view is generated for the analyzed query that has been rewritten to use a second precomputed view.

34. The apparatus of claim 33, wherein the candidate view is generated when additional aggregation is present in the rewritten query.

35. The apparatus of claim 33, wherein the candidate view is generated when additional aggregation is not present in the rewritten query.

36. The apparatus of claim 22, wherein the precomputed view processor is configured to generate one or more additional candidate views based upon the analyzed query.

37. The apparatus of claim 22, wherein the precomputed view processor is configured to characterize the generated candidate view.

38. The apparatus of claim 37, wherein candidate view characterization includes generating a report that reflects current aggregation strategy effectiveness.

39. The apparatus of claim 37, wherein candidate view characterization includes generating a report that includes information about the generated candidate view.

40. The apparatus of claim 37, wherein candidate view characterization includes generating a measure of the benefit of the generated candidate view relative to other views.

41. The apparatus of claim 22, wherein the precomputed view processor is further configured to:

generate the precomputed view based on the generated candidate view.

42. The apparatus of claim 41, wherein the precomputed view processor is further configured to:

answer at least one of the subsequent queries on the database using the precomputed view.