Structured indexes on results of function applications over data

• US 20050198016A1
• Filed: 02/04/2005
• Published: 09/08/2005
• Est. Priority Date: 03/08/2004
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Galindo-Legaria, Cesar A., Waas, Florian M., Cunningham, Conor J., Joshi, Milind M., Hanson, Eric N.
• Granted Patent: US 7,349,897 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

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.

Claims

1-14. -14. (canceled)

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

a first data field containing an object; and

a second data field containing data associated with the object, the data being derived from a functional invocation over the data, the functional invocation comprising a table valued function.

16. The computer-readable medium of claim 15, wherein the data is derived from an unnesting operation, and the first data field and the second data field comprise a clustering key.

17. The computer-readable medium of claim 16, wherein the clustering key is capable of back-joining an indexed view to a base table.

18. The computer-readable medium of claim 15, wherein the data comprises XML valued data fields.

19. The computer-readable medium claim 15, wherein the data comprises multiple columns or multiple fields of a complex structure.

20-33. -33. (canceled)