Structured indexes on results of function applications over data

• US 7,349,897 B2
• Filed: 02/04/2005
• Issued: 03/25/2008
• Est. Priority Date: 03/08/2004
• Status: Expired due to Fees

• 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.
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): DANG, THANH HA T
• Application Number:
  US11/051,447
• Publication Number:
  US 20050198016A1
• Time in Patent Office: 1,145 Days
• Field of Search: 707/102, 707/100, 707/2, 707/3, 707/4, 707/5
• 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. A computer-readable storage medium having stored thereon a data structure, wherein the data structure is generated by a method for querying data, 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, wherein the method for querying data comprises;

receiving an indexed view for the functional invocation,

determining if the query is satisfied by the index view by determining if the query matches the index view;

applying the indexed view as an index to the functional invocation over the data if the query is satisfied by the index view;

introducing a back-join to the indexed view;

applying the indexed view as an index to the functional invocation over the data if the query cannot be satisfied by the index view; and

determining and storing a query result using the index to the functional invocation over the data, wherein determining the query result comprises performing an unnesting operation on nested data, further wherein the unnesting operation is a cross apply unnest operation, and further wherein the indexed view is a generalization of a plurality of partial indexes.

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

3. The computer-readable storage medium of claim 2, wherein the clustering key back-joins an indexed view to a base table.

4. The computer-readable storage medium of claim 1, wherein the data comprises XML valued data fields.

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

6. A computer-readable storage medium having stored thereon a data structure, wherein the data structure is generated by a method for querying data, 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, wherein the method for querying data comprises;

receiving an indexed view for the functional invocation,

determining if the query is satisfied by the index view by determining if the query matches the index view;

applying the indexed view as an index to the functional invocation over the data if the query is satisfied by the index view;

introducing a back-join to the indexed view;

applying the indexed view as an index to the functional invocation over the data if the query cannot be satisfied by the index view; and

determining and storing a query result using the index to the functional invocation over the data, wherein determining the query result comprises performing an unnesting operation on nested data, wherein the unnesting operation is a cross apply unnest operation, and wherein performing the unnesting operation provides an unnested result, and further comprises indexing a view on the unnested result.

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

8. The computer-readable storage medium of claim 7, wherein the clustering key back-joins an indexed view to a base table.

9. The computer-readable storage medium of claim 6, wherein the data comprises XML valued data fields.

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