System and method for real-time materialized view maintenance

Patent metadata

Field	Value
Publication number	US-9984119-B2
Application number	US-6116208-A
Country	US
Kind code	B2
Filing date	Apr 2, 2008
Priority date	Mar 22, 2006
Publication date	May 29, 2018
Grant date	May 29, 2018

Abstract

There are provided a method, a computer program product, and a system for maintaining a materialized view defined on a relation of a relational database. The method includes the step of performing content-based filtering on the relation to identify an update to the relation as being irrelevant with respect to the materialized view.

First claim

What is claimed is:

1. A method for maintaining a materialized view defined on a base relation of a relational database, the method comprising:
providing a materialized view maintenance system implemented in a computing device having at least a processor and a memory device;
storing at least the base relation in the memory device;
determining whether an update to the base relation is irrelevant with respect to the materialized view, comprising:
determining whether the update is irrelevant based on a “where” clause condition;
if the update is not determined to be irrelevant based on the “where” clause condition, performing content-based filtering on the base relation using the materialized view maintenance system to determine whether the update is irrelevant by determining that no join result tuple is produced in a join result set such that the join result set includes no members from a join operation applied to the updated base relation and at least one filtering relation associated with another base relation of the materialized view; and
if the update is not determined to be irrelevant based on content-based filtering, checking the base relation to determine whether the update is irrelevant;

generating an estimate of at least one of an importance and an effect of the update to the base relation;
at least one of performing a load shedding operation on the relational database based upon the estimate and quantifying the effect of the update being omitted from the materialized view based on the estimate; and
displaying the maintained materialized view so as to exclude determined irrelevant updates.

2. The method of claim 1 , wherein said performing content-based filtering utilizes at least one filtering relation data structure to perform the content-based filtering.

3. The method of claim 2 , wherein the at least one filtering relation data structure is shared among multiple materialized views.

4. The method of claim 1 , further comprising localizing an effect of the update on the materialized view.

5. The method of claim 1 , further comprising collapsing multiple updates to the base relation to improve filtering efficiency.

6. The method of claim 1 , wherein performing content-based filtering comprises filtering based on a threshold-based filtering ratio requirement, the threshold-based filtering ratio requirement for selectively pre-specifying a percentage of irrelevant updates to be filtered, a value of the threshold-based filtering ratio requirement being capable of being pre-specified to be equal to less than one hundred percent.

7. The method of claim 1 , wherein performing content-based filtering comprises filtering based on an association requirement for capturing a relationship among multiple join attributes of the base relation, wherein the base relation involves multiple matching tuples with respect to an input tuple, and further comprises finding only a first matching one of the multiple tuples while ignoring other ones of the multiple matching tuples when providing an output based on the association requirement.

8. The method of claim 1 , further comprising projecting out all unnecessary attributes with respect to and during the join operation in order to reduce an overhead of the join operation.

9. The method of claim 1 , wherein the materialized view maintenance system stops joining the updated base relation with at least one filtering relation upon finding a first join result tuple.

10. A non-transitory computer program product comprising a computer usable medium having computer usable program code for maintaining a materialized view defined on a base relation of a relational database, said computer program product comprising:
computer usable program code for determining whether an update to a base relationship is irrelevant with respect to the materialized view by:
determining whether the update is irrelevant based on a “where” clause condition;
if the update is not determined to be irrelevant based on the “where” clause condition, performing content-based filtering on the base relation to determine whether the update to the base relation is irrelevant by determining that no join result tuple is produced in a join result set such that the join result set includes no members from a join operation applied to the updated base relation and at least one filtering relation associated with another base relation of the materialized view; and
if the update is not determined to be irrelevant based on content-based filtering, checking the base relation to determine whether the update is irrelevant;

generating an estimate of at least one of an importance and an effect of the update to the base relation; and
at least one of performing a load shedding operation on the relational database based upon the estimate and quantifying the effect of the update being omitted from the materialized view based upon the estimate.

11. The computer program product of claim 10 , wherein performing the content-based filtering utilizes at least one filtering relation data structure to perform the content-based filtering, and wherein the at least one filtering relation data structure is capable of being shared among multiple materialized views.

12. The computer program product of claim 10 , further comprising computer usable program code for localizing an effect of the update on the materialized view.

13. The computer program product of claim 10 , further comprising computer usable program code for collapsing multiple updates to the base relation to improve filtering efficiency.

14. A system for maintaining a materialized view defined on a base relation of a relational database, the system comprising:
a memory device configured to store the base relation therein; and
a materialized view manager configured to determine whether an update to the base relation is irrelevant with respect to the materialized view, by:
determining whether the update is irrelevant based on a “where” clause condition;
if the update is not determined to be irrelevant based on the “where” clause condition, performing content-based filtering on the base relation to determine whether the update to the base relation is irrelevant by determining that no join result tuple is produced in a join result set such that the join result set includes no members from a join operation applied to the updated base relation and at least one filtering relation associated with another base relation of the materialized view; and
if the update is not determined to be irrelevant based on content-based filtering, checking the base relation to determine whether the update is irrelevant,

said materialized view manager being further configured to generate an estimate of at least one of an importance and an effect of the update to the base relation and to perform at least one of a load shedding operation on the relational database based upon the estimate and quantifying the effect of the update being omitted from the materialized view based upon the estimate.

15. The system of claim 14 , wherein said materialized view manager utilizes at least one filtering relation data structure to perform the content-based filtering.

16. The system of claim 15 , wherein the at least one filtering relation data structure is capable of being shared among multiple materialized views.

17. The system of claim 14 , wherein said materialized view manager localizes an effect of the update on the materialized view.

18. The system of claim 14 , wherein said materialized view manager collapses multiple updates to the base relation to impr

Assignees

• Luo Gang
• Yu Philip Shi Lung
• IBM

Inventors

• Luo Gang
• Yu Philip Shi-Lung

Classifications

• G06F17/30457

  Physics · mapped topic

• G06F17/30442

  Physics · mapped topic

• G06F17/30451 Primary

  Physics · mapped topic

• G06F16/24539

  using cached or materialised query results · CPC title

• G06F16/2453

  Query optimisation · CPC title