SQL language extensions for modifying collection-valued and scalar valued columns in a single statement

• US 20050091256A1
• Filed: 10/24/2003
• Published: 04/28/2005
• Est. Priority Date: 10/24/2003
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Seeliger, Oliver Nicholas, Stefani, Stefano, Surna, Aleksandras, Blakeley, Jose A., Rathakrishnan, Balaji
• Granted Patent: US 7,433,886 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2291 User-Defined Types; Storage management thereof
• CPC Class Codes: G06F 16/235 Update request formulation
• CPC Class Codes: G06F 16/289 Object oriented databases
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A technique for updating collection-valued and other complex structured columns in a nested table using a nested extension of an UPDATE statement that uses syntax and semantics to modify collection-valued columns in a way that is analogous to the syntax and semantics of the UPDATE statement that is used to modify scalar-valued columns of the table (called the outer UPDATE). Using the same syntactic and semantic constructs as the table at the outer level allows an existing implementation that processes modifications to relational tables to reuse its implementation techniques for processing outer updates to modify collection-valued columns as well. The UPDATE extensions enable the specification of updates to nested collections embedded at arbitrary levels of depth in the object model. The new syntax is embedded inside the outer UPDATE statement in a way that parallels the structure of the data itself and thus maps more directly to the user'"'"'s conceptual model of the data. The method for implementing the UPDATE extensions uses a change descriptor, which is a data structure that aggregates substantially all changes, both scalar and collection-valued into a single value that can be applied to the changed collection-valued column. This technique can also be used for modifications to other kinds of complex-structured columns such as objects or xml. The change descriptor includes hierarchical information for the cell, thereby enabling efficient application of multiple updates at various granularity levels in a single operation and enabling the implementation of efficient index maintenance algorithms by updating only the indexes affected by the UPDATE operation and updating only those index rows that were affected by the UPDATE operation.

Claims

1. A method of updating values in a complex structured type column in a relational database system, comprising the steps of:

representing modifications to values in the complex structured type column using a data structure that aggregates changes to the values at any level of a hierarchy of the complex structured column; and

computing the data structure in response to a data modification statement on the database to determine which values within the complex structured type column to update with the aggregated changes.

2. A method as in claim 1, comprising the further step of simultaneously updating multiple scalar values at different levels within the hierarchy of the complex structured type column.

3. A method as in claim 1, comprising the further step of simultaneously updating a scalar value in a table along with a complex structured type value in a complex structured type column of said relational database system.

4. A method as in claim 1, comprising the further step of embedding an INSERT/UPDATE/DELETE statement inside a SET clause of an UPDATE statement.

5. A method as in claim 4, comprising the further step of embedding a plurality of nested SET clauses inside an outer-most UPDATE statement corresponding to each layer within the hierarchy of the complex structured type column.

6. A method as in claim 4, wherein the computing step comprises the steps of updating only indexes affected by specific scalar fields modified at various nesting levels by the SET clause in the UPDATE statement and updating only those rows of the index that correspond to the actual values that are modified by the UPDATE statement.

7. A method as in claim 1, comprising the further step of applying the aggregated changes to the complex structured type column, wherein the applying step is separate from the computing step so as to provide Halloween Protection.

8. A relational database system responsive to database modification statements to store and update values in at least one complex structured type column, comprising:

a parser that parses a database modification statement and produces a description of changes to the database proposed by the database modification statement;

a query optimizer that produces an execution algorithm to implement the database modification statement; and

a query execution engine that uses the execution algorithm to compute a data structure of the database modification statement to determine which values within a complex structured type column are to be updated, wherein the data structure represents values in the complex structured type column as an aggregation of changes to the values at any level of hierarchy of the complex structured type column, and said query execution engine applies the changes to the values in the complex structured type column that are to be updated.

9. A system as in claim 8, wherein the query execution engine simultaneously updates multiple scalar values at different levels within the hierarchy of the complex structured type column.

10. A system as in claim 8, wherein the query execution engine simultaneously updates a scalar value in a table along with a complex structured type value in a complex structured type column of said relational database system.

11. A system as in claim 8, wherein the parser parses a SET clause of a database UPDATE statement.

12. A system as in claim 11, wherein the parser parses the UPDATE statement in a plurality of nested SET clauses inside an outermost UPDATE statement corresponding to each level within the hierarchy of the complex structured type column.

13. A system as in claim 11, wherein the query execution engine updates only indexes affected by specific scalar fields modified at various nesting levels by the SET clause in the UPDATE statement and updates only those rows of the index that correspond to the actual values that are modified by the UPDATE statement.

14. A system as in claim 8, wherein the query execution engine applies the aggregated changes to the complex structured type column separate from the computation of the data structure so as to provide Halloween Protection.

15. A method of updating values in a collection-valued column in a relational database system, comprising the steps of:

representing modifications to values in the collection-valued column using a data structure that aggregates changes to the values inside the collection-valued column; and

computing the data structure in response to a data modification statement on the database to determine which values within the collection-valued column to update with the aggregated changes.

16. A method as in claim 15, comprising the further step of simultaneously updating multiple scalar values at different levels within the collection-valued column.

17. A method as in claim 15, comprising the further step of simultaneously updating a scalar value in a table along with a value in a collection-valued column of said relational database system.

18. A relational database system responsive to database modification statements to store and update values in at least one collection-valued column, comprising:

a parser that parses a database modification statement and produces a description of changes to the database proposed by the database modification statement;

a query optimizer that produces an execution algorithm to implement the database modification statement; and

a query execution engine that uses the execution algorithm to compute a data structure of the database modification statement to determine which values within a collection-valued column are to be updated, wherein the data structure represents values in the collection-valued column as an aggregation of changes to the values inside the collection-valued column, and said query execution engine applies the changes to the values in the collection-valued column that are to be updated.

19. A system as in claim 18, wherein the parser parses a SET clause of a database UPDATE statement.

20. A system as in claim 19, wherein the parser parses the UPDATE statement in a plurality of nested SET clauses inside an outermost UPDATE statement corresponding to each level within the collection-valued column.

21. A system as in claim 18, wherein the query execution engine updates only indexes affected by specific scalar fields modified at various nesting levels by the SET clause in the UPDATE statement and updates only those rows of the index that correspond to the actual values that are modified by the UPDATE statement.

22. A system as in claim 18, wherein the query execution engine applies the aggregated changes to the collection-valued column separate from the computation of the data structure so as to provide Halloween Protection.