Abstract: Embodiments of the invention provide techniques for maintaining I/O value caches for database queries. Each maintained cache may be configured for use with a particular database query. Each cache may be persistently maintained in a system, meaning the cache is not automatically deleted after some period of time, and may thus be used to process subsequent instances of the same query. By use of the maintained cache, executing subsequent instances of the query may be avoided, thus saving time and system resources. Further, the maintained cache may be adapted to process other queries having similar characteristics to the initial query. The data included in each cache may be refreshed as required by changes to the underlying data.

Abstract: Embodiments of the invention provide techniques for aggregating database queries for energy efficiency. In one embodiment, queries received by a DBMS are aggregated and staged according to hard-disk drives required for query execution. Each group of queries accessing a given drive may be dispatched for execution together. Further, the queries received by a DBMS may be matched to patterns of previously received queries. The matching patterns may be used to predict other queries which are likely to be received by the DBMS. The received queries may be staged to be dispatched with the predicted queries. By aggregating queries to be executed, access to each hard-disk drive may be optimized, thus reducing the overall energy consumption required for executing the queries.

Abstract: Embodiments of the invention provide techniques for aggregating database queries for energy efficiency. In one embodiment, queries received by a DBMS are aggregated and staged according to hard-disk drives required for query execution. Each group of queries accessing a given drive may be dispatched for execution together. Further, the queries received by a DBMS may be matched to patterns of previously received queries. The matching patterns may be used to predict other queries which are likely to be received by the DBMS. The received queries may be staged to be dispatched with the predicted queries. By aggregating queries to be executed, access to each hard-disk drive may be optimized, thus reducing the overall energy consumption required for executing the queries.

Abstract: A method, computer program product and computer system for providing SQL query performance advices to optimize SQL queries of a database, which includes providing a query cache to store records of optimized queries of the database, creating an event-driven web service, sending the records from the query cache to the web service, and analyzing the records using the web service to form SQL query performance advices. The method, computer program product and computer system can further includes outputting the SQL query performance advices to a viewer for display, or outputting the advices to a post-processing application for additional actions.

Abstract: Embodiments of the invention provide techniques for optimizing database queries for energy efficiency. In general, a query optimizer is configured to compare energy requirements of query plans, and to select a query plan requiring minimal energy to execute. In one embodiment, the query optimizer may also compare time performance of the query plans, and may select a query plan by matching to a user preference for a relative priority between energy requirements and time performance.

Abstract: Embodiments of the invention provide techniques for optimizing database queries for energy efficiency. In general, a query optimizer is configured to compare energy requirements of query plans, and to select a query plan requiring minimal energy to execute. In one embodiment, the query optimizer may also compare time performance of the query plans, and may select a query plan by matching to a user preference for a relative priority between energy requirements and time performance.

Abstract: Embodiments of the invention provide techniques for optimizing database queries for energy efficiency. In general, a query optimizer is configured to compare energy requirements of query plans, and to select a query plan requiring minimal energy to execute. In one embodiment, the query optimizer may also compare time performance of the query plans, and may select a query plan by matching to a user preference for a relative priority between energy requirements and time performance.

Abstract: A plan for executing a query in a relational database is obtained. A query for accessing data in the relational database is received. The query specifies N tables in the relational database from which data is to be retrieved. A determination is made whether a syntax of the query matches a syntax of a plan in a plan cache for executing the query. Matches are identified between generic table formats of the N tables specified in the query to generic table formats of N tables specified in the plan responsive to the syntax of the query matching the syntax of a plan in the plan cache for executing the query. The plan for executing the query is obtained based on whether the syntax of the query matches the syntax of the plan and based on identified matches between the generic table formats of the N tables specified in the query to the generic table formats of the N tables specified in the plan.

Abstract: A process combines multiple grouping sets into single rollup sets with depth lists defining the levels of grouping that must be performed. Grouping sets are identified that are contained within other sets and combined into single rollups with depth lists. Cube aggregation conversion to rollup aggregation is provided for optimizing database query processing. Natural sets of rollup hierarchies within a cube are recognized and the cube is converted into those rollup hierarchies. Once converted, the rollup aggregation is performed to significantly reduce required processing.

Abstract: Methods, systems, and computer program products are provided for improving the processing of database queries. Some embodiments include generating volatility scores for various plans for executing similar database queries. Different embodiments may utilize: database statistics, the variable values being selected for, and/or historical run time data, to generate the plan volatility scores. In some embodiments, the volatility scores are used to determine whether to generate a new plan for a query, whether to prune an existing plan, and/or how many different plans to store for a query.

Abstract: A method, apparatus and program product dynamically activate and/or deactivate buffers during execution of a database query. The dynamic deactivation and activation is based on the monitoring of information associated with the consumption of data by a plurality of threads during execution of an access plan for the database query. By doing so, the selection of a less optimal buffer position during optimization may be replaced with a more optimal buffer position during runtime, often resulting in improved query performance and parallelism.

Abstract: A method, apparatus and program product is provided for optimizing a database query. A query syntax, system generated predicates, and requisite statistics of the database query are analyzed to determine if a predicate structure is a good candidate for a symbol table only data structure. The predicate structure is analyzed to determine if the predicate structure is already encompassed by an encoded vector index. The symbol table only data structure is generated for at least one column in a database table without generating a corresponding vector data structure from an encoded vector index if the predicate structure is a good candidate and not encompassed by an encoded vector index. The symbol table only data structure is maintained by detecting a change in data associated with the symbol table only data structure and updating the symbol table only data structure to reflect the change in the data.

Abstract: A method, apparatus and program product are provided for performing a query of a database. A database query is received, which includes first and second operations respectively configured to generate first and second disjoint results sets from a dataspace. The database query is analyzed to identify a set of attributes from the dataspace that are used by at least one of the first and second operations in the database query. During execution of the database query, a plurality of records from the dataspace is iteratively processed in a single pass, including, for each of the plurality of records, processing such record by retrieving the plurality of attributes for such record from the dataspace and performing each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets.

Abstract: Methods, systems, and computer program products are provided for executing database rollup queries. Methods can include iterating through a database table which has been grouped and ordered on the different columns which are in the ROLLUP clause. In some embodiments, a GROUP BY ROLLUP construct can be executed while only requiring an additional one storage location per ordered column per each aggregate function to be performed on each database row. The higher level aggregate functions can be executed without relying on accessing any lower level aggregate results in some embodiments. A suitably grouped and ordered database table can have a multiple level hierarchical ROLLUP function executed in a single pass without having to retrieve lower level aggregate results.

Abstract: This is an example of a new method for optimization of database queries which uses an asynchronous task which keeps the most current status of the database objects by directly interfacing the existing storage management system. This asynchronous task in combination with query plan and cache data is able to aid in detecting and choosing “warm” query costing alternatives. The cold/warm status information of the asynchronous task helps query optimizer to determine: if it is suitable to use “warm” costing for a new query optimization; validate previous decisions to use a “warm”-optimized query plan, when it comes to potentially reuse and run with such a plan again; decide whether it's worthwhile to still cache such a plan in the plan cache.

Abstract: A method, apparatus, and program product are provided for preserving a query plan cache on a backup system having a dataspace containing a copy of data and a copy of a query plan cache from a production system. An update is initiated of at least a portion of the copy of the data on the backup system with a portion of the data on the production system. A merge is initiated of updated query plans in the query plan cache from the production system with corresponding query plans in the copy of the query plan cache on the backup system. Objects are correlated in the updated query plans in the copy of the query plan cache with the updated copy of the data on the backup system.

Abstract: A method, system, and computer program product for user-driven targeted query re-optimizations using delta values are provided. The method includes displaying a query tree for a query via a graphical user interface (GUI), where the query tree includes a plurality of interconnected nodes in a tree structure, and outputting a delta value as a difference between an estimated and an actual performance metric of query execution. The method also includes receiving a request to provide alternative execution plans for a subtree of a selected node of the query tree, and displaying at least one alternative execution plan description for the subtree of the selected node. The method further includes receiving a re-optimization request for a user selected alternative execution plan description, and displaying a re-optimized query tree resulting from the re-optimization request.

Abstract: A database management system predicts a selectivity for database query conditions requiring a join of records from different tables. The system identifies at least one skewed value in a field specified in the join condition, and constructs, for each skewed value, a set of hypothetical query predicates in which the field specified in the join condition is replaced with a constant equal to the skewed value. The system then predicts the selectivity for the hypothetical predicates, using any appropriate prediction technique. The selectivities of the hypothetical predicates are used to predict a selectivity for the original query.

Abstract: Embodiments of the invention provide techniques for generating database statistics for optimizing union queries. In general, working sets including samples of values in database columns are persistently maintained in a database. To optimize a union query, the working sets describing the columns included in the union query are combined to generate an ad hoc working set. The ad hoc working set is then used to generate a database statistic describing the combined columns. In another embodiment, working sets may also be maintained for generating statistics for optimizing non-union queries, thus enabling statistics to be refreshed more frequently.

Abstract: A method, apparatus and program product to optimize a database query. A query plan is generated for the database query where the query plan includes first and second subplans. Cached optimization data associated with the first subplan and generated during evaluation of another query plan for a previous query that uses the first subplan is retrieved. The first subplan is evaluated for resource cost using the retrieved optimization data. The second subplan is also evaluated for resource cost. The query plan is then evaluated for resource cost based upon the evaluations of resource cost for the first and second subplans. A query plan is selected based on evaluations of resource cost and the plan is executed to generate a result set.