Structured indexes on results of function applications over data
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.
1. A method of indexing the contents of a nested collection of data organized into a plurality of base tables, comprising:
performing an unnesting operation on the base tables of the nested collection of data;
generating an indexed view on the result of the unnesting operation, the indexed view having a plurality of entries, at least some of which are nested;
providing a back-join from the indexed view to at least one of the base tables via a clustering key, the clustering key having a separate sub-entry for each entry in the indexed view that is nested, the provided back-join and clustering key establishing a link to at least one field from the base table that is not present in the indexed view;
receiving a query on at least one of the base tables;
processing the received query using the generated indexed view and the provided back-join and clustering key as an access path to the at least one of the base tables.
2. The method of claim 1 , wherein the unnesting operation is a cross apply unnest operation.
3. A computer system comprising:
a data store comprising data organized into a plurality of base tables and an indexed view generated from the result of an unnesting operation performed on the base tables, the indexed view having a plurality of entries, at least some of which are nested;
a software interface providing a back-join from the indexed view to at least one of the base tables via a clustering key, the clustering key having a separate sub-entry for each entry in the indexed view that is nested, the provided back-join and clustering key establishing a link to at least one field from the base table that is not present in the indexed view;
a query engine receiving a query on at least one of the base tables and processing the received query using the generated indexed view and the provided back-join and clustering key as an access path to the at least one of the base tables.
4. The computer system of claim 3 , wherein the data comprises nested data, and the software interface determines a query result by performing an unnesting operation on the nested data.
5. The computer system of claim 4 , wherein the software interface indexes a view on the result of the unnesting operation.
6. The computer system of claim 3 , wherein the data comprises XML valued data fields.
7. The computer system of claim 3 , wherein the data comprises multiple columns or multiple fields of a complex structure.