Intelligent compilation of materialized view maintenance for query processing systems
First Claim
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.
1 Assignment
0 Petitions
Accused Products
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.
76 Citations
36 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. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
(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. - View Dependent Claims (14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24)
(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. - View Dependent Claims (26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36)
(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.
-
Specification