Intelligent compilation of materialized view maintenance for query processing systems

• US 6,581,205 B1
• Filed: 12/16/1999
• Issued: 06/17/2003
• Est. Priority Date: 12/17/1998
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Mohan, Chandrasekaran, Pirahesh, Mir Hamid, Leung, Ting Yu, Cochrane, Roberta Jo, Sidle, Richard Sefton
• Primary Examiner(s): Khatri, Anil
• Application Number:
  US09/464,556
• Time in Patent Office: 1,279 Days
• Field of Search: 717/106, 717/115, 717/140, 717/150, 717/100, 717/101, 717/127, 706/45, 706/46, 706/61, 706/50, 707/3, 707/4, 707/5, 707/101, 707/200
• US Class Current: 717/140
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A method, apparatus, and article of manufacture for maintaining a materialized view derived from at least one base table in a database stored on a computer. An update is performed to the base table in a transaction. Thereafter, the materialized view is accessed to identify affected records therein using at least one mechanism, such as an isolation level or update- and exclusive-locks, to guarantee consistency while avoiding deadlocks with other transactions that modify at least one base table of the materialized view and to improve concurrency with other transactions that are accessing the materialized view when the modification to the base table is applied to the materialized view.

Claims

1. A computer-implemented method of maintaining a materialized view derived from at least one base table in a database stored on the computer, the method comprising:

(a) performing a modification to the base table in a transaction; and

(b) accessing the materialized view to identify affected records therein using at least one mechanism to guarantee consistency while avoiding deadlocks with other transactions that modify at least one base table of the materialized view and to improve concurrency with other transactions that are accessing the materialized view when the modification to the base table is applied to the materialized view.

2. The method of claim 1 above, wherein the modification is selected from a group comprising an update, delete and insert.

3. The method of claim 1 above, wherein the accessing step comprises performing an outerjoin between the materialized view and the modification to the base table to identify the affected records.

4. The method of claim 3 above, further comprising modifying the identified affected records in the materialized view incrementally using the modification to the base table.

5. The method of claim 1 above, wherein the mechanism to guarantee consistency is selected from a group comprising an isolation level and at least one lock.

6. The method of claim 5 above, wherein the isolation level comprises repeatable read isolation.

7. The method of claim 5 above, wherein the lock comprises an exclusive lock when the materialized view is accessed.

8. The method of claim 5 above, wherein the lock comprises an update lock when the materialized view is accessed.

9. The method of claim 8 above, wherein the lock comprises an exclusive lock when the materialized view is modified.

10. The method of claim 9 above, further comprising:

(1) examining and using next-key locking in the materialized view while determining that the modification to the base table results in a new record being inserted to the materialized view; and

(2) when the modification to the base table results in the new record being inserted into the materialized view, creating the new record using the modification to the base table, escalating the update lock to the exclusive lock, and inserting the new record into the materialized view, wherein the exclusive lock is released upon completion of the insert.

11. The method of claim 9 above, further comprising:

(1) examining and locking the materialized view to determine whether the modification to the base table results in an existing record being updated in the materialized view; and

(2) when the modification to the base table results in the existing record being updated in the materialized view, creating new values for the existing record using the modification to the base table, escalating the update lock to the exclusive lock for the existing record, and updating the existing record in the materialized view, wherein the exclusive lock is released upon completion of the update.

12. The method of claim 9 above, further comprising:

(1) examining and locking the materialized view to determine whether the modification to the base table results in an existing record being deleted from the materialized view; and

(2) when the modification to the base table results in the existing record being deleted from the materialized view, escalating the update lock to the exclusive lock, and deleting the existing record from the materialized view, wherein the exclusive lock is released upon completion of the deletion.

13. An apparatus for maintaining a materialized view, comprising:

(a) a computer system having a data storage device coupled thereto for storing a database, wherein the materialized view is derived from at least one base table stored in the database;

(b) logic, performed by the computer system, for;

(1) performing a modification to the base table in a transaction; and

(2) accessing the materialized view to identify affected records therein using at least one mechanism to guarantee consistency while avoiding deadlocks with other transactions that modify at least one base table of the materialized view and to improve concurrency with other transactions that are accessing the materialized view when the modification to the base table is applied to the materialized view.

14. The apparatus of claim 13 above, wherein the modification is selected from a group comprising an update, delete and insert.

15. The apparatus of claim 13 above, wherein the logic for accessing comprises logic for performing an outerjoin between the materialized view and the modification to the base table to identify the affected records.

16. The apparatus of claim 15 above, further comprising logic for modifying the identified affected records in the materialized view incrementally using the modification to the base table.

17. The apparatus of claim 13 above, wherein the mechanism to guarantee consistency is selected from a group comprising an isolation level and at least one lock.

18. The apparatus of claim 17 above, wherein the isolation level comprises repeatable read isolation.

19. The apparatus of claim 17 above, wherein the lock comprises an exclusive lock when the materialized view is accessed.

20. The apparatus of claim 17 above, wherein the lock comprises an update lock when the materialized view is accessed.

21. The apparatus of claim 20 above, wherein the lock comprises an exclusive lock when the materialized view is modified.

22. The apparatus of claim 21 above, further comprising logic for:

(1) examining and using next-key locking in the materialized view while determining that the modification to the base table results in a new record being inserted to the materialized view; and

(2) when the modification to the base table results in the new record being inserted into the materialized view, creating the new record using the modification to the base table, escalating the update lock to the exclusive lock, and inserting the new record into the materialized view, wherein the exclusive lock is released upon completion of the insert.

23. The apparatus of claim 21 above, further comprising logic for:

(1) examining and locking the materialized view to determine whether the modification to the base table results in an existing record being updated in the materialized view; and

(2) when the modification to the base table results in the existing record being updated in the materialized view, creating new values for the existing record using the modification to the base table, escalating the update lock to the exclusive lock for the existing record, and updating the existing record in the materialized view, wherein the exclusive lock is released upon completion of the update.

24. The apparatus of claim 21 above, further comprising logic for:

(1) examining and locking the materialized view to determine whether the modification to the base table results in an existing record being deleted from the materialized view; and

(2) when the modification to the base table results in the existing record being deleted from the materialized view, escalating the update lock to the exclusive lock, and deleting the existing record from the materialized view, wherein the exclusive lock is released upon completion of the deletion.

25. An article of manufacture embodying logic for performing a method of maintaining a materialized view derived from at least one base table in a database stored on the computer, the method comprising:

(a) performing a modification to the base table in a transaction; and

(b) accessing the materialized view to identify affected records therein using at least one mechanism to guarantee consistency while avoiding deadlocks with other transactions that modify at least one base table of the materialized view and to improve concurrency with other transactions that are accessing the materialized view when the modification to the base table is applied to the materialized view.

26. The method of claim 25 above, wherein the modification is selected from a group comprising an update, delete and insert.

27. The method of claim 25 above, wherein the accessing step comprises performing an outerjoin between the materialized view and the modification to the base table to identify the affected records.

28. The method of claim 27 above, further comprising modifying the identified affected records in the materialized view incrementally using the modification to the base table.

29. The method of claim 25 above, wherein the mechanism to guarantee consistency is selected from a group comprising an isolation level and at least one lock.

30. The method of claim 29 above, wherein the isolation level comprises repeatable read isolation.

31. The method of claim 29 above, wherein the lock comprises an exclusive lock when the materialized view is accessed.

32. The method of claim 29 above, wherein the lock comprises an update lock when the materialized view is accessed.

33. The method of claim 32 above, wherein the lock comprises an exclusive lock when the materialized view is modified.

34. The method of claim 33 above, further comprising:

(1) examining and using next-key locking in the materialized view while determining that the modification to the base table results in a new record being inserted to the materialized view; and

(2) when the modification to the base table results in the new record being inserted into the materialized view, creating the new record using the modification to the base table, escalating the update lock to the exclusive lock, and inserting the new record into the materialized view, wherein the exclusive lock is released upon completion of the insert.

35. The method of claim 33 above, further comprising:

(1) examining and locking the materialized view to determine whether the modification to the base table results in an existing record being updated in the materialized view; and

(2) when the modification to the base table results in the existing record being updated in the materialized view, creating new values for the existing record using the modification to the base table, escalating the update lock to the exclusive lock for the existing record, and updating the existing record in the materialized view, wherein the exclusive lock is released upon completion of the update.

36. The method of claim 33 above, further comprising:

(1) examining and locking the materialized view to determine whether the modification to the base table results in an existing record being deleted from the materialized view; and

(2) when the modification to the base table results in the existing record being deleted from the materialized view, escalating the update lock to the exclusive lock, and deleting the existing record from the materialized view, wherein the exclusive lock is released upon completion of the deletion.