Abstract: Described is a query optimizer comprising a query tuner that performs actual execution of query fragments to obtain actual results during compilation time, and uses those actual results to select a query plan. The actual results may be combined with estimates for fragments that were not executed. The tree may be traversed in a top-down traversal, processing every node. Alternatively, the tree may be traversed in a bottom-up traversal, re-deriving data for higher nodes as each lower level is completed. A limit, such as a time limit or level limit, may be used to control how much time is taken to determine the execution plan.

Abstract: Computer systems, devices, and associated methods of optimizing the execution of instructions of a database statement by a database server are disclosed herein. In one embodiment, a method includes identifying a potential execution plan for executing instructions of the database statement and estimating a cost for executing the execution plan. The cost can comprise an encrypted data processing cost associated with a operation in the execution plan of executing an operation on encrypted data in a protected computing environment. The method can include estimating the encrypted data processing cost in the protected computing environment based on statistics generated in the protected computing environment about a database table. In response to estimating the cost for executing the execution plan, comparing the cost to estimated costs of alternative execution plans, selecting the lowest-cost plan for execution, and executing the lowest-cost execution plan.

Abstract: Computer systems, devices, and associated methods of optimizing the execution of instructions of a database statement by a database server are disclosed herein. In one embodiment, a method includes identifying a potential execution plan for executing instructions of the database statement and estimating a cost for executing the execution plan. The cost can comprise an encrypted data processing cost associated with a operation in the execution plan of executing an operation on encrypted data in a protected computing environment. The method can include estimating the encrypted data processing cost in the protected computing environment based on statistics generated in the protected computing environment about a database table. In response to estimating the cost for executing the execution plan, comparing the cost to estimated costs of alternative execution plans, selecting the lowest-cost plan for execution, and executing the lowest-cost execution plan.

Abstract: Systems and methods are described that generate an execution plan for a query in a relational database system. The systems and methods generate the execution plan by generating one or more initial logical representations of the query, performing an exploration process around each of the one or more initial logical representations of the query, the performing of the exploration process around a particular initial logical representation of the query comprising applying transformation rules to generate one or more additional logical representations of the query that are logically equivalent to the particular initial logical representation of the query and that are within a maximum allowable transformation distance of the particular initial logical representation of the query, generating one or more execution plans for each initial logical representation of the query and each additional logical representation of the query, and selecting an execution plan from among the generated execution plans.

Abstract: A query is received. The query comprises a plural number of efficiency expressions. A key is generated for the query indicating a first selectivity region in multi-dimensional selectivity space comprising an array of selectivity regions arranged according to a grid with a number of dimensions equal to the plural number. The generated key is used to look up a stored query plan among a plurality of stored query plans. A stored query plan is executed for the query that is associated with a stored key determined to match the generated key.

Abstract: A query is received. The query comprises a plural number of efficiency expressions. A key is generated for the query indicating a first selectivity region in multi-dimensional selectivity space comprising an array of selectivity regions arranged according to a grid with a number of dimensions equal to the plural number. The generated key is used to look up a stored query plan among a plurality of stored query plans. A stored query plan is executed for the query that is associated with a stored key determined to match the generated key.

Abstract: A query configured according to a query template is received. The query includes values for a plurality of parameters. A key is generated for the query that indicates a selectivity region in a selectivity space determined for the included parameter values. Whether the generated key matches any key of a plurality of keys is determined. Each key of the plurality of keys is associated with a corresponding query plan. If the generated key is determined to not match any of the keys, the query is compiled to generate a new query plan, and the generated query plan is executed with respect to the query. If the key is determined to match any key of the plurality of keys, a query plan associated with the matched key is selected, and the query plan associated with the matched key is executed with respect to the query.

Abstract: Systems and methods are described that generate an execution plan for a query in a relational database system. The systems and methods generate the execution plan by generating one or more initial logical representations of the query, performing an exploration process around each of the one or more initial logical representations of the query, the performing of the exploration process around a particular initial logical representation of the query comprising applying transformation rules to generate one or more additional logical representations of the query that are logically equivalent to the particular initial logical representation of the query and that are within a maximum allowable transformation distance of the particular initial logical representation of the query, generating one or more execution plans for each initial logical representation of the query and each additional logical representation of the query, and selecting an execution plan from among the generated execution plans.

Abstract: Technologies for forming logical indexes and utilizing such indexes so as to abstract many of the complexities resulting from referencing partitioned database tables. Included are technologies for making use of order-preserving properties of table metadata, for adding a partition equality predicate to an explicit predicate in merge-join processing on partitioned tables, and for selecting execution of a logical skip scan on a partitioned table when a query predicate does not reference a specific partition. Such technologies generally abstract from the query writer and processing systems explicit referencing of table partitions.

Abstract: A query configured according to a query template is received. The query includes values for a plurality of parameters. A key is generated for the query that indicates a selectivity region in a selectivity space determined for the included parameter values. Whether the generated key matches any key of a plurality of keys is determined. Each key of the plurality of keys is associated with a corresponding query plan. If the generated key is determined to not match any of the keys, the query is compiled to generate a new query plan, and the generated query plan is executed with respect to the query. If the key is determined to match any key of the plurality of keys, a query plan associated with the matched key is selected, and the query plan associated with the matched key is executed with respect to the query.

Abstract: Systems and methods that generate specialized plans for compiling SQL queries. A plan generator component scans the query representation for parameter sensitive predicates and evaluates each predicate individually based on the parameter values. Accordingly, queries can be identified not only based on their structures, but also based on their parameter conditions. The specialized plans are more efficient for particular values, wherein queries that employ such values are optimally executed.

Abstract: When two or more scalar subtrees in an execution plan are directed towards the same data set, but request different data from the data set, the two subtrees are replaced in the execution plan with a new subtree which queries the data set only once. This new query to the data set covers all data required by the different subtrees. A pivoting step then is used to separate the data which would have been sought by each subtree. The pivoting step also yields data in the same form as would have resulted from the original subtrees.

Abstract: Technologies for forming logical indexes and utilizing such indexes so as to abstract many of the complexities resulting from referencing partitioned database tables. Included are technologies for making use of order-preserving properties of table metadata, for adding a partition equality predicate to an explicit predicate in merge-join processing on partitioned tables, and for selecting execution of a logical skip scan on a partitioned table when a query predicate does not reference a specific partition. Such technologies generally abstract from the query writer and processing systems explicit referencing of table partitions.

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: Described is a query optimizer comprising a query tuner that performs actual execution of query fragments to obtain actual results during compilation time, and uses those actual results to select a query plan. The actual results may be combined with estimates for fragments that were not executed. The tree may be traversed in a top-down traversal, processing every node. Alternatively, the tree may be traversed in a bottom-up traversal, re-deriving data for higher nodes as each lower level is completed. A limit, such as a time limit or level limit, may be used to control how much time is taken to determine the execution plan.

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 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: 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: 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.