Secondary index and indexed view maintenance for updates to complex types

• US 20060015490A1
• Filed: 07/14/2004
• Published: 01/19/2006
• Est. Priority Date: 07/14/2004
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Stefani, Stefano, Denuit, Bruno H.M.
• Granted Patent: US 7,769,770 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24526 Internal representations for queries
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Pursuant to receipt of a data modification command or statement, such as an Update command, it is determined which fields in a data structure, such as a UDT, computed column, indexed view, or index, for example, will be changed as a result of the command. Only those fields that are affected by the command will be changed. Thus, changes are propagated to only to those dependent data structures whose content is being modified. Tree representations are used to facilitate the determination as to whether a subfield in a hierarchy is changed.

Claims

1. A method of processing a data modification statement, comprising:

receiving a data modification statement;

determining which fields of a data structure are affected by the data modification statement; and

modifying a query plan in response to the fields of the data structure that are affected by the data modification statement.

2. The method of claim 1, wherein the data modification statement comprises an update command.

3. The method of claim 1, wherein the data structure comprises a user-defined type (UDT).

4. The method of claim 1, wherein the data structure comprises one of a computed column, an indexed view, and an index.

5. The method of claim 1, further comprising determining a tree representation for the data modification statement and determining an operator tree representing the expressions of for the fields of the data structure.

6. The method of claim 5, wherein determining which fields of the data structure are affected by the data modification statement comprises comparing the tree representation for the fields of the data structure and the tree representation for the data modification statement.

7. The method of claim 6, wherein comparing the tree representation for the fields of the data structure and the tree representation for the data modification statement comprises, for each relational operator in the tree representation for the fields of the data structure, determining scalar expressions and comparing the scalar expressions to the tree representation for the data modification statement.

8. The method of claim 5, wherein determining which fields of the data structure are affected by the data modification statement comprises walking the tree representation for the data modification statement attempting to match fields of the data structure.

9. The method of claim 8, wherein walking the tree representation for the data modification statement proceeds in a top-down manner and matches the fields of the data structure from bottom to top.

10. The method of claim 1, wherein determining which fields of the structure are affected by the data modification statement comprises matching fields of the data structure with fields in the data modification statement.

11. A system for processing a data modification statement, comprising:

a front end that receives a data modification statement; and

a query optimizer that determines which fields of a data structure are affected by the data modification statement, and modifies a query plan responsive to the fields of the data structure that are affected by the data modification statement.

12. The system of claim 11, wherein the front end comprises a parser and algebrizer that transforms the textual representation of the data modification statement into tree nodes.

13. The system of claim 11, wherein the data modification statement comprises an update command.

14. The system of claim 11, wherein the data structure comprises a user-defined type (UDT).

15. The system of claim 11, wherein the data structure comprises one of a computed column, an indexed view, and an index.

16. The system of claim 11, wherein the front end is adapted to determine a tree representation for the data modification statement and the query optimizer is adapted to determine an operator tree for the fields of the data structure.

17. The system of claim 16, wherein the query optimizer is adapted to determine which fields of the data structure are affected by the data modification statement by comparing the tree representation for the fields of the data structure and the tree representation for the data modification statement.

18. The system of claim 17, wherein the query optimizer is adapted to compare the tree representation for the fields of the data structure and the tree representation for the data modification statement by, for each relational operator in the tree representation for the fields of the data structure, determining scalar expressions and comparing the scalar expressions to the tree representation for the data modification statement.

19. The system of claim 16, wherein the query optimizer is adapted to determine which fields of the data structure are affected by the data modification statement by walking the tree representation for the data modification statement attempting to match fields of the data structure.

20. The system of claim 19, wherein the query optimizer walks the tree representation for the data modification statement in a top-down manner and matches the fields of the data structure from bottom to top.

21. The system of claim 11, wherein the query optimizer is adapted to determine which fields of the structure are affected by the data modification statement by matching fields of the data structure with fields in the data modification statement.

22. A computer-readable medium having stored thereon a data structure, comprising:

a first data field containing a first tree representation of a data modification statement; and

a second data field containing a second tree representation of data subfields.

23. The computer-readable medium of claim 22, wherein the data modification statement comprises an update command.

24. The computer-readable medium of claim 22, wherein the data subfields are subfields of one of a user-defined type (UDT), a computed column, an indexed view, and an index.

25. The computer-readable medium of claim 22, further comprising a third data field identifying the fields of a data structure that are affected by the data modification statement.

26. The computer-readable medium of claim 22, wherein the third data field contains the results of a comparison between the first and second data fields.

27. The computer-readable medium of claim 26, further comprising a fourth data field containing a query plan modified in response to the third data field.