System for maintaining precomputed views

• US 6,374,263 B1
• Filed: 07/19/1999
• Issued: 04/16/2002
• Est. Priority Date: 07/19/1999
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Johnson, Galt, Mulagund, Gopal B., Cole, Richard L., McKenna, William J., Colby, Latha S., Wilhite, David G. Jr., Bunger, Craig J.
• Primary Examiner(s): HOMERE, JEAN RAYMOND
• Application Number:
  US09/356,486
• Time in Patent Office: 1,002 Days
• Field of Search: 707/8, 707/9, 707/10, 707/3, 707/2, 707/201, 707/203, 709/203, 709/219
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• 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/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A system and a method maintain a precomputed view corresponding to detail data in a database server by: determining a maintenance plan to refresh the precomputed view when detail data is changed; integrating the maintenance plan with a query execution plan in the database server; and executing the query execution plan.

Claims

1. A method for maintaining a precomputed view corresponding to detail data in a database server, comprising:

determining a maintenance plan to refresh the precomputed view when the detail data is altered;

integrating the maintenance plan with a query execution plan in the database server;

executing the query execution plan;

generating a delta table for each update operation;

sending the delta table information to the server;

acquiring locks and transaction handles associated with the update operation;

updating the precomputed view; and

dropping the delta table.

2. The method of claim 1, wherein the views are maintained incrementally.

3. The method of claim 1, wherein the views are maintained by recomputing one or more detail tables.

4. The method of claim 1, wherein dependency information is used to determine a maintenance strategy.

5. The method of claim 4, wherein a query rewriter generates dependency information among views.

6. The method of claim 1, further comprising maintaining views sequentially.

7. The method of claim 1, further comprising maintaining views in parallel.

8. The method of claim 1, further comprising using one of foreign-key/primary-key relationships, hierarchy information, nullability of columns and predetermined metadata to produce a maintenance plan.

9. The method of claim 1, further comprising integrating data communication between a loader and a server to leverage their mutual capabilities in maintaining the views.

10. Computer software for maintaining a precomputed view corresponding to detail data in a database server, the computer software residing on a computer-readable medium and comprising instructions for causing a computer to perform the following operations:

determine a maintenance plan to refresh the precomputed view when the detail data is altered;

integrate the maintenance plan with a query execution plan in the database server;

execute the query execution plan;

generate a delta table for each update operation;

send the delta table information to the server;

acquire locks and transaction handles associated with the update operation;

update the precomputed view; and

drop the delta table.

11. The computer software of claim 10, wherein the views are maintained incrementally.

12. The computer software of claim 10, wherein the views are maintained by recomputing one or more detail tables.

13. The computer software of claim 10, wherein dependency information is used to determine a maintenance strategy.

14. The computer software of claim 13, wherein a query rewriter generates dependency information among views.

15. The computer software of claim 10, further comprising code to maintain views sequentially.

16. The computer software of claim 10, further comprising code to maintain views in parallel.

17. The computer software of claim 10, further comprising using one of foreign-key/primary-key relationships, hierarchy information, nullability of columns and predetermined metadata to produce a maintenance plan.

18. The computer software of claim 10, further comprising integrating data communication between a loader and a server to leverage their mutual capabilities in maintaining the views.