SQL language extensions for modifying collection-valued and scalar valued columns in a single statement
First Claim
1. A computer implemented method of updating values in a complex structured type column in a relational database system, the method comprising:
- representing modifications to a subset of a plurality of values in the complex structured type column using a data structure, wherein the plurality of values in the complex structured type column are embedded in a hierarchy having a plurality of levels within at least one row of the complex structured type column, wherein the subset of the plurality of values are embedded at arbitrary levels within the hierarchy;
aggregating changes to the subset of the plurality values at the arbitrary levels in the complex structured type column;
computing the data structure in response to a data modification statement on the database to determine the subset of the plurality values within the complex structured type column to update with the aggregated changes; and
applying the aggregated changes to the subset of the plurality of values in the complex structured type column.
3 Assignments
0 Petitions
Accused Products
Abstract
A system and method for updating collection-valued and other complex structured columns in a nested table using a nested extension of an UPDATE statement 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.
23 Citations
22 Claims
-
1. A computer implemented method of updating values in a complex structured type column in a relational database system, the method comprising:
-
representing modifications to a subset of a plurality of values in the complex structured type column using a data structure, wherein the plurality of values in the complex structured type column are embedded in a hierarchy having a plurality of levels within at least one row of the complex structured type column, wherein the subset of the plurality of values are embedded at arbitrary levels within the hierarchy; aggregating changes to the subset of the plurality values at the arbitrary levels in the complex structured type column; computing the data structure in response to a data modification statement on the database to determine the subset of the plurality values within the complex structured type column to update with the aggregated changes; and applying the aggregated changes to the subset of the plurality of values in the complex structured type column. - View Dependent Claims (2, 3, 4, 5, 6, 7)
-
-
8. A relational database system responsive to database modification statements to store and update values in at least one complex structured type column, the relational database system comprising:
-
a parser that parses a database modification statement and produces a description of changes to a subset of a plurality of values in a complex structured type column proposed by the database modification statement, wherein the plurality of values in the complex structured type column are embedded in a hierarchy having a plurality of levels within at least one row of the complex structured type column, and wherein the subset of the plurality of values are embedded at arbitrary levels within the hierarchy; 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 the subset of the plurality of values within the complex structured type column to be updated, wherein the data structure represents the subset of the plurality of values in the complex structured type column as an aggregation of changes to the subset of the plurality of values at the arbitrary levels of the hierarchy of the complex structured type column, and wherein the query execution engine applies the changes to the subset of the plurality of values in the complex structured type column that are to be updated. - View Dependent Claims (9, 10, 11, 12, 13, 14)
-
-
15. A computer implemented method of updating values in a collection-valued column in a relational database system, the method comprising:
-
representing modifications to a subset of a plurality of values in the collection-valued column using a data structure, wherein the plurality of values in the collection-valued column are embedded in a hierarchy having a plurality of levels within at least one row of the collection-valued column, wherein the subset of the plurality of values are embedded at arbitrary levels within the hierarchy, and wherein the data structure aggregates changes to the subset of the plurality of values at the arbitrary levels inside the collection-valued column; computing the data structure in response to a data modification statement on the database to determine the subset of the plurality of values within the collection-valued column to update with the aggregated changes; and applying the aggregated changes to the subset of the plurality of values in the collection-valued column. - View Dependent Claims (16, 17)
-
-
18. A relational database system responsive to database modification statements to store and update values in at least one collection-valued column, the relational database system comprising:
-
a parser that parses a database modification statement and produces a description of changes to a subset of a plurality of values in a collection-valued column proposed by the database modification statement, wherein the plurality of values in the collection-valued column are embedded in a hierarchy having a plurality of levels within at least one row of the collection-valued column, and wherein the subset of the plurality of values are embedded at arbitrary levels within the hierarchy; 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 the subset of the plurality of values within the collection-valued column to be updated, wherein the data structure represents the subset of the plurality of values in the collection-valued column as an aggregation of changes to the subset of the plurality of values at the arbitrary levels inside the collection-valued column, and wherein the query execution engine applies the changes to the subset of the plurality of values in the collection-valued column that are to be updated. - View Dependent Claims (19, 20, 21, 22)
-
Specification