Patent Number: 7,272,598

Title: Structured indexes on results of function applications over data

Abstract: Indexed views or materialized views are used as a secondary index on a base table with multi-valued attributes. This provides for using the index to search in the nested data. Moreover, indexing is provided on the result of an unnest operation. Indexing a view on the result of an unnesting operation provides the ability to index the contents of a nested collection. One such unnesting operation is "cross apply unnest". This provides additional options for a query execution plan, leading to a more optimized query. A back-join is provided from the indexed view to the base table to allow fields from the base table that are not present in the indexed view to be included in a result of a query on the table which is processed using the indexed view as an access path. This provides a means of including columns in the query result that are not in the indexed view but are in the base table. The back-join is supported from a single-table indexed view to the base table via a unique clustering key which acts as a logical row locator. Thus, the system can back-join to the base table from an indexed view via the unique clustering key. These features allow the use of indexed views to index a table on the contents of multi-set or multi-valued attributes.

Inventors: Cunningham; Conor J. (Redmond, WA), Hanson; Eric N. (Bellevue, WA), Joshi; Milind M. (Redmond, WA), Galindo-Legaria; Cesar A. (Redmond, WA), Waas; Florian M. (Seattle, WA)

Assignee: Microsoft Corporation

International Classification: G06F 17/30 (20060101)

Expiration Date: 2019-09-18 0:00:00