Abstract: A method of accumulating and aggregating statistics concerning the use of database table indexes includes using a plurality of counters to register index use. The plurality of counters allow multiple instances of index usage to be recorded simultaneously. A missing index may be detected using an optimal index in a query plan. The optimal index is compared against the available indexes to determine if an index is missing. Statistics may be gathered against the used and missing indexes.

Abstract: A system and method for creation, modification and/or use of plan guide(s) is provided. Plan guide(s) can be employed to optimize the performance of query(ies) when it is not possible and/or desirable to change the text of the query directly. Plan guides can be useful, for example, when a small subset of queries in a database application deployed from a third-party vendor is not performing as expected. Plan guides influence optimization of queries by attaching query hints to them. In accordance with an aspect of the subject invention, a query plan execution system is provided. The system includes an execution component, a query optimizer, and, a plan cache. Plan guides and other associated metadata are stored in a plan guide metadata store.

Abstract: A system and method for facilitating secure access to database(s) is provided. The system relates to authorizing discriminatory access to relational database data. More particularly, the invention provides for an innovative technique of defining secured access to rows in relational database tables in a way that cannot be spoofed while preserving various optimization techniques. The invention affords a persistent scheme via providing for a security architecture whereby discriminatory access policies on persistent entities can be defined and enforced while preserving set based associative query capabilities. A particular aspect of the invention relates to the specification of such policies and the technique by which those policies are enforced. With respect to one particular implementation of the invention, creation, modification and deletion of access control lists called security descriptors is provided.

Abstract: Efficient hierarchical searching is based on object type. By pre-computing additional information and storing it in a fast-lookup structure, it is possible to quickly identify objects that satisfy an object retrieval request. Furthermore, it is also possible to use this technique to avoid object hydration for operations in the store. Moreover, it is possible to leverage database statistical structures such as histograms to estimate the number of qualifying objects without having to examine each object.

Abstract: Efficient hierarchical searching is based on object type. By pre-computing additional information and storing it in a fast-lookup structure, it is possible to quickly identify objects that satisfy an object retrieval request. Furthermore, it is also possible to use this technique to avoid object hydration for operations in the store. Moreover, it is possible to leverage database statistical structures such as histograms to estimate the number of qualifying objects without having to examine each object.

Abstract: A suggested query execution plan is used as a constraint on a guided optimization process such that all execution plans that do not conform to the suggested execution plan are eliminated as possible alternatives. If the guided optimization process successfully generates a resulting execution plan, then the result will conform to the suggested execution plan. If the guided optimization process fails to generate a resulting execution plan, then it may be determined that the suggested execution plan is invalid.

Abstract: Efficient hierarchical searching is based on object type. By pre-computing additional information and storing it in a fast-lookup structure, it is possible to quickly identify objects that satisfy an object retrieval request. Furthermore, it is also possible to use this technique to avoid object hydration for operations in the store. Moreover, it is possible to leverage database statistical structures such as histograms to estimate the number of qualifying objects without having to examine each object.

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.

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.

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.

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.

Abstract: A system and method for facilitating secure access to database(s) is provided. The system relates to authorizing discriminatory access to relational database data. More particularly, the invention provides for an innovative technique of defining secured access to rows in relational database tables in a way that cannot be spoofed while preserving various optimization techniques. The invention affords a persistent scheme via providing for a security architecture whereby discriminatory access policies on persistent entities can be defined and enforced while preserving set based associative query capabilities. A particular aspect of the invention relates to the specification of such policies and the technique by which those policies are enforced. With respect to one particular implementation of the invention, creation, modification and deletion of access control lists called security descriptors is provided.