Abstract: In the course of an optimization, a query can be broken down into its sub-expressions. Materialized views may be substituted for the sub-expressions during query optimization. Encoded sub-expressions are generated and used in the comparison with stored materialized views. Instead of discarding the encoding of a query's sub-expression if no matching materialization is found, the encoding is stored. If subsequently submitted queries contain the same sub-expression, a view matching mechanism will find the previously stored encoding. Because no materialization is associated with this expression, the view is not substituted. However, usage statistics counters are updated; e.g., a counter is incremented indicating that the sub-expression was found in another query. Because view matching is applied to the sub-expression that are candidates for being materialized, the statistics accurately reflect which are the most frequently occurring candidate sub-expressions in a workload.

Abstract: Validation of large numbers of alternative execution plans for a database query, either an exhaustive enumeration of the complete space of alternatives, or else an unbiased random sample, is performed by efficiently constructing execution trees from a data structure having groups alternative operators that are ranked in a directory. Each global rank of a plan identifies that plan uniquely among all the alternative plans. The operators are unranked from the directory according to a specification that characterizes the desired plans.

Abstract: A partial pre-aggregation database operation improves processing efficiency of database queries by reducing the number of records input into a subsequent database operation, provided the query includes a final aggregation. A query optimizer is provided to determine when it is economical to partially pre-aggregate data records and when it is not. The partial pre-aggregation creates a record store in memory as input records are received. The record store is then used by another database operator, which saves the other database operator from having to re-create the record store.

Abstract: A partial pre-aggregation database operation improves processing efficiency of database queries by reducing the number of records input into a subsequent database operation, provided the query includes a final aggregation. A query optimizer is provided to determine when it is economical to partially pre-aggregate data records and when it is not. The partial pre-aggregation creates a record store in memory as input records are received. The record store is then used by another database operator, which saves the other database operator from having to re-create the record store.

Abstract: Improved query optimizer using implied predicates. The system facilitates allowing a query optimizing component to introduce into a query, extra predicates that facilitate the following: render the same results as the original query; are used as dictated by rules passed to the optimizing component—these rules specify whether the new predicate is an equivalence (that will substitute the old predicate) or an implication (in which the old predicate is preserved); are considered as cost-based alternatives, and discarded if not useful; are tied to index utilization; and can consider both standard and multi-valued indices.

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 database system and method allows a user to write program code in a high-level programming language that implements a class that defines the structure of a user-defined aggregate and methods that can be invoked on instances of the user-defined aggregate. The class is then registered with the database system, which enforces a specific contract for user-defined aggregates against the class. The contract comprises the following requirements. First, the class must specify one of a plurality of different formats for persisting instances of the user-defined aggregate in a database store.

Abstract: Validation of large numbers of alternative execution plans for a database query, either an exhaustive enumeration of the complete space of alternatives, or else an unbiased random sample, is performed by efficiently constructing execution trees from a data structure having groups alternative operators that are ranked in a directory. Each global rank of a plan identifies that plan uniquely among all the alternative plans. The operators are unranked from the directory according to a specification that characterizes the desired plans.

Abstract: A method of satsfying a database query includes evaluating certain joins on a per-segment basis. An expression tree is produced for the query, and the expression tree is evaluated to identify joins whose operands are two instances of the same relation and whose join predicate conjunctively includes an equality comparison between two instances of the same column. When such a join is identified, it may be evaluated by segmenting the operand relation according to the columns that are compared for equality in the predicate. The join is then evaluated by performing the join operation separately on each segment. Segments may be spooled separately, thereby exploiting the efficiencies obtained by spooling even where the entire relation is too large to fit in the spool. Execution iterators are provided for spooling successive segments and for applying the join to the spooled segment.

Abstract: A query optimizer produces a preferred execution plan by utilizing statistics on views. These provide the optimizer with statistical information beyond that available on tables, for example, with information on the result of scalar or relational expressions. Pre-derived accurate statistics are thus available which are used to improve the accuracy of estimation and thus increase the quality and reliability of the query processor. Transformations of expressions and sub-expressions of candidate plans produce equivalent sub-expressions. Where those sub-expressions include references to views, an estimation of the cardinality and other execution statistics for the sub-expression can be made using the view statistics. This can be used to estimate the cardinality for all equivalent sub-expressions, leading to higher accuracy for the estimate.

Abstract: Systems and methods are provided to increase the scalability of subscriptions in an electronic database environment. In an illustrative implementation, a computing application comprises at least one instruction set to cooperate with a data environment to optimize the processing of subscriptions by the data environment when communicating with cooperating services and/or applications. In operation, a subscription is identified. A subscription template is created for the subscription and the subscription template is parameterized to create a parameter table containing parameters (e.g. subscription constants). A join is then performed between the parameters of the parameter table and the parameterized subscription templates to generate application and/or service data required by the cooperating services and/or applications. The data is then processed by the applications and/or services.

Abstract: A client submits a subscription to a database specifying selected data within a data set. A subscription plan is generated based on the subscription. A query is submitted to the database changing selected data within the data set. A query plan is generated based on the query. The subscription is matched to the query based on the data set, and the query plan is supplemented with the subscription plan to generate a notification of the change to the client.

Abstract: During query execution, statistics associated with expressions are observed. Such observed statistics preferably include the cardinality of each expression. The observed statistics are submitted to an expression manager as feedback from the executed query. The statistics are preferably stored for use in estimating the cardinality of execution plans for future queries.

Abstract: A method of satisfying a database query includes evaluating certain joins on a per-segment basis. An expression tree is produced for the query, and the expression tree is evaluated to identify joins whose operands are two instances of the same relation and whose join predicate conjunctively includes an equality comparison between two instances of the same column. When such a join is identified, it may be evaluated by segmenting the operand relation according to the columns that are compared for equality in the predicate. The join is then evaluated by performing the join operation separately on each segment. Segments may be spooled separately, thereby exploiting the efficiencies obtained by spooling even where the entire relation is too large to fit in the spool. Execution iterators are provided for spooling successive segments and for applying the join to the spooled segment.

Abstract: Validation of large numbers of alternative execution plans for a database query, either an exhaustive enumeration of the complete space of alternatives, or else an unbiased random sample, is performed by efficiently constructing execution trees from a data structure having groups alternative operators that are ranked in a directory. Each global rank of a plan identifies that plan uniquely among all the alternative plans. The operators are unranked from the directory according to a specification that characterizes the desired plans.

Abstract: An index is used for a query that contains an original condition having a comparison based on a function. An implied condition is first identified and applied to values in a column having multiple rows. An index is used to identify rows having values meeting the implied condition. Finally, the query is executed over the index using the original condition. Flags may be used to identify how to handle exceptions during run time. A table may be used to provide bounds for multiple different functions.

Abstract: A method of satisfying a database query includes evaluating certain joins on a per-segment basis. An expression tree is produced for the query, and the expression tree is evaluated to identify joins whose operands are two instances of the same relation and whose join predicate conjunctively includes an equality comparison between two instances of the same column. When such a join is identified, it may be evaluated by segmenting the operand relation according to the columns that are compared for equality in the predicate. The join is then evaluated by performing the join operation separately on each segment. Segments may be spooled separately, thereby exploiting the efficiencies obtained by spooling even where the entire relation is too large to fit in the spool. Execution iterators are provided for spooling successive segments and for applying the join to the spooled segment.