Partially materialized views

• US 20060047696A1
• Filed: 10/21/2004
• Published: 03/02/2006
• Est. Priority Date: 08/24/2004
• Status: Abandoned Application

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Larson, Per-Ake, Goldstein, Jonathan, Zhou, Jingren
• Application Number:
  US10/971,600
• Publication Number:
  US 20060047696A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24552 Database cache management

Abstract

Partially materialized views in the field of database systems are described herein. A method of partially materializing a view computed from one or more source tables comprises generating a view definition including a query expression and at least one control predicate referencing at least one control table. The query expression specifies the maximal content of the view, that is, the complete set of records that could be materialized. One or more control predicates and the contents of one or more control tables restrict what records are actually materialized and stored in the view. This allows the content of the partially materialized view to be adjusted simply by updating one or more control tables.

Claims

1. A method of partially materializing a view of database records, comprising:

a) defining a view of records in one or more source tables;

b) identifying a subset of records of the view that are to be materialized; and

c) storing the subset of records to partially materialize the view.

2. The method of claim 1, wherein identifying the subset of records that are materialized includes a control predicate referencing one or more control tables.

3. The method of claim 2 further comprising changing one or more control tables, and in response to the changed one or more control tables, updating the subset of records.

4. The method of claim 3, wherein changing the one or more control tables includes changing a value of at least one of a column and a field of the control table.

5. The method of claim 3, wherein changing the one or more control tables includes adding a data record to at least one control table.

6. The method of claim 3, wherein changing the one or more control tables includes removing a data record from at least one control table.

7. The method of claim 1 further comprising updating the at least one record of the one or more source tables, and in response, automatically updating the stored subset of records.

8. The method of claim 7, wherein automatically updating includes utilizing view maintenance algorithms.

9. The method of claim 2 further comprising before changing one or more control tables, receiving a query and in response compiling a query plan, and after changing one or more control tables, executing the query using the compiled query plan.

10. The method of claim 1 further comprising determining at the execution time of the query whether a query can utilize the partially materialized view as part of a query execution plan.

11. The method of claim 10, wherein determining whether a query can utilize the partially materialized view includes determining if a runtime constant of the query is contained in at least one control table record.

12. The method of claim 11, wherein determining if a plurality of runtime constants of the query are contained in multiple control table records.

13. A data structure, comprising:

a view defining expression including a query expression and at least one control predicate, the query expression referencing one or more source tables, the control predicate referencing one or more control tables, the view defining expression identifying a subset of records in a result of the query expression to be included in a partially materialized view.

14. The data structure of claim 13 further comprising a partially materialized view constructed and adapted to store the subset of records.

15. The data structure of claim 13 further comprising the one or more control tables referenced by the control predicate.

16. The data structure of claim 13, wherein the control table includes a plurality of control tables which identify the subset of records.

17. The data structure of claim 13, wherein the control table includes a table stored in a relational data store.

18. The data structure of claim 13, wherein an expression of the control predicate includes a user defined function.

19. The data structure of claim 13, wherein the control predicate references a second partially materialized view.

20. The data structure of claim 13, wherein the control predicate specifies at least one equivalency constraint.

21. The data structure of claim 13, wherein the control predicate specifies at least one of an upper bound and a lower bound.

22. The data structure of claim 13, wherein the control predicate specifies at least one of an upper and a lower bound that is used to identify the subset of records that are materialized.

23. The data structure of claim 13, wherein the control predicate specifies an indicator of records that are to be excluded from the partially materialized view.

24. One or more computer-readable media containing executable instructions that, when executed, implement a method comprising:

a) storing at least one record to partially materialize a view of a query result based on a view defining expression and at least one source table;

b) receiving a query including a query predicate;

c) determining whether the partially materialized view contains all records required by the query predicate;

d) computing the query from the partially materialized view if the partially materialized view contains all records required by the query predicate.

25. The one or more computer readable media as recited in claim 24, wherein the method further comprises computing at least a portion of the query on at least one source table if the partially materialized view does not contain all records required by the query predicate.

26. The one or more computer readable media as recited in claim 24, wherein determining whether the partially materialized view contains all records required by the query predicate includes evaluating a compile time test and evaluating an execution time test of the query.

27. The one or more computer readable media as recited in claim 26, wherein the compile time test includes forming a fully materialized view expression by removing a control predicate from the view defining expression, and determining if a fully materialized view resulting from the fully materialized view expression would contain all records required by the query predicate.

28. The one or more computer readable media as recited in claim 27, wherein determining whether the partially materialized view contains all records required by the query predicate includes creating a guard predicate including at least one condition that references an execution time constant of the query predicate and at least a portion of at least one control table.

29. The one or more computer readable media as recited in claim 28, wherein creating the guard predicate includes generating a query predicate evaluating an equivalency between a run time constant and a parameter value of the at least one control table.

30. The one or more computer readable media as recited in claim 28, wherein creating the guard predicate includes generating a query predicate comparing a run time constant and a range parameter value of the at least one control table.

31. The one or more computer readable media as recited in claim 28, further comprising generating a dynamic query plan including the guard predicate, a primary plan fragment referencing the partially materialized view, and a fallback plan fragment referencing one or more source tables.

32. The one or more computer readable media as recited in claim 28, wherein evaluating the execution time test includes evaluating the guard predicate.

33. The one or more computer readable media as recited in claim 32, further comprising computing the query based on the primary plan fragment if the guard evaluates to true.

34. The one or more computer readable media as recited in claim 32, further comprising computing the query based on the fallback plan fragment if the guard predicate evaluates to false.

35. The one or more computer readable media as recited in claim 26, wherein the execution time test is evaluated at execution time of the query.

36. One or more computer-readable media containing executable components comprising:

a) means for storing a partially materialized view, a control table, and one or more source tables;

b) means for receiving a query;

c) means for compiling the query including means for determining whether the partially materialized view would contain all records required by the query if fully materialized, means for creating a guard predicate, and means for creating a dynamic query plan including the guard predicate; and

d) means for executing the query based on the dynamic query plan and an evaluation of the guard predicate at execution time of the query.