International Business Machines Corporation

Patrick Dooling Bossman

You-Chin Gene Fuh

Terence Patrick Purcell

Shuanglin Guo

Chan-Hua Liu

Thomas Abel Beavin

Jason L Alpers

Adarsh R Pannu

Andrei Fedorovich Lurie

John Joseph Campbell

Brian L Baggett

Yoichi Tsuji

Martina Simova

Frank Darl Bower

Yun Yu Wang

Ping Wang

Xiang Sean Zhou

Hong Min

Curt Lee Cotner

Ke Wei Wei

Maryela Evelin Weihrauch

Fen-Ling Lin

Jing Zhou

Yefim Shuf

Jerry Mukai

Brian Thinh-Vinh Tran

Tammie Dang

Lee-Chin Hsu Liu

Yumi Kimura Tsuji

Jie Zhang

Ou Jin

Kun Peng Ren

Euna Park

Changjiu Xian

Peter Kuang

Terrence P Purcell

An access plan including multiple indexes is selected for accessing data of a database system. A filtering status of each index within the selected access plan is determined based on information available at execution of the selected access plan. The order of the indexes within the selected access plan is modified based on the filtering status of the indexes, and the selected access plan is executed with the modified order of the indexes. The execution of the selected access plan is interrupted in response to accessing a threshold amount of data and the filtering of the indexes of the selected access plan is estimated. A processing order for the indexes is determined based on the estimated filtering. The execution of the selected access plan resumes based on the determined processing order.

Adaptive multi-index access plan for database queries

According to an embodiment of the present invention, methods, systems, and computer readable media are provided for collecting statistics for use in optimizing a database query. Collected statistics for the database are compared to corresponding predetermined states of conditions for optimizing the database query. One or more collected statistics in compliance with the corresponding predetermined states of conditions are identified based on the comparison. Collection of the one or more identified statistics for the query optimization is disabled.

Identification and elimination of non-essential statistics for query optimization

A database system searches for any statements that satisfy one or more of a set of filtering criteria. When a given dynamic query statement fully satisfies the set of filtering criteria, the given statement is stabilized by persistently storing a bound form of the given dynamic query statement. When the given dynamic query statement partly satisfies the set of filtering criteria, a monitoring request is created with the set of filtering criteria to begin monitoring subsequent executions of the statement, and the statement is linked to the monitoring request. When the given dynamic query statement subsequently fully satisfies the set of filtering criteria, the statement is stabilized by persistently storing the bound form of the statement. When the given dynamic query is re-executed, the stabilized statement is loaded, and the PREPARE process is bypassed.

Method to monitor dynamic SQL statements for automatic stabilization in a data sharing environment

A technique for factoring uncertainty into cost-based query optimization includes: determining the degree of uncertainty involved in the cost estimates for the query, determining the degree of sensitivity the query has to that uncertainty, and determining if there is an access path that performs well across the range of possible conditions that could occur at execution time, reducing the risk of performance spikes and performance volatility. If such an access path exists, select that access path; if not, perform parametric query optimization or query re-optimization.

Technique for factoring uncertainty into cost-based query optimization

Apparatuses and methods for optimizing access path are provided. Execution of a query statement is sampled to obtain the information about a plurality of executing instances, the information about each executing instance comprises the literal value of the query statement in the instance and the filter factor corresponding to the literal value. The plurality of instances are grouped into a plurality of groups based on the filter factors corresponding to the literal values. The average filter factor of each group and the occurrence frequency of the group are obtained. The candidate access path created for the average filter factor of each group is accordingly obtained, and by taking the occurrence frequencies as weights, the weighted execution cost of each candidate access path is calculated for each group. The candidate access path with the minimum sum of the weighted execution costs for all groups is determined as the optimal access path.

Method and apparatus for optimizing access path in database

An adaptive query execution plan enhancement is provided by: selecting a sample of literal sets from an execution history of a query statement; determining a plurality of access paths by applying each literal set in the sample to the query statement; for each given access path of the plurality of access paths, determining a total execution cost by applying each literal set in the sample to the given access path; and selecting a preferred access path from the plurality of access paths based on the total execution costs for each given access path. A plurality of preferred access paths for a plurality of query statements in an application workload is collected and may be presented as a query execution plan enhancement recommendation to users.

Adaptive query execution plan enhancement

An apparatus, system, and method are disclosed for improving query execution plan efficiency in a database management system. The present invention binds both static database queries and dynamic database queries to new query execution plans (QEPs) that produce the same result set as a former QEP bound to the database queries. Next, the present invention identifies one of the former QEPs and the new QEP as an active QEP and automatically collects execution statistics for one of the former QEPs and the new QEP. Finally, the present invention automatically selects one of the former QEPs and the new QEP as the active QEP in response to completion of the automatic collection of execution statistics, the active QEP selected such that the automatically selected QEP provides optimal execution performance based on performance criteria in comparison to the automatically unselected QEP, the active QEP selected based on the execution statistics.

Query execution plan efficiency in a database management system

Disclosed is a technique for maintaining statistics. A query is received. Column group generation is performed to form one or more column groups. Column group refinement is performed based on statistics data generated for a previously received query. A statistics check is automatically performed. A statistics collection command is generated to obtain statistics.

Statistics management

Techniques for partitioning a query are provided. The techniques include establishing one or more criterion for partitioning a query, wherein the query comprises one or more tables, materializing a first of the one or more tables, partitioning the first of the one or more tables until the one or more criterion have been satisfied, and partitioning and joining a remainder of the one or more tables of the query.

Method for partitioning a query

A method of, a computer program product for and a computer system for managing statistics in a database management system are provided. A value of a volatility of a statistic is determined. A value of a staleness of the statistic is determined using the value of the volatility. A statistics collection task is generated in response to, at least in part, the value of the staleness exceeding a value of a staleness threshold. The statistics collection task is executed to collect an additional value of the statistic. The additional value of the statistic is stored.

Management of database statistics

A method for performing disjunctive single-index access on a database is disclosed. The method includes a query engine determining whether a first OR predicate and a second OR predicate map to a shared index. Responsive to the query engine determining that the first OR predicate and the second OR predicate map to the shared index, the first OR predicate and the second OR predicate are ordered in an ascending sequence. A first range of the first OR predicate is queued. A first row of the shared index is probed for the first range of the first OR predicate. Whether the first row is disqualified by the first OR predicate is determined. Responsive to determining that the first row is not disqualified by the first OR predicate, the row is reported in a result reporting structure. Responsive to determining that the row is disqualified by the first OR predicate, whether the first range of the first OR predicate overlaps a second range of the second OR predicate is determined, and, responsive to determining that the first range of the first OR predicate overlaps the second range of the second OR predicate, the row is checked against second range of the second or predicate.

Method and system for disjunctive single index access

An apparatus, system, and method are disclosed for ensuring query execution plan stability in a database management system. The present invention binds a static database query to a new query execution plan (QEP) that produces the same result set as an original QEP bound to the static database query. Next, the present invention identifies one of the original QEP and the new QEP as a primary QEP and automatically collects execution statistics for the original QEP and the new QEP. Finally, the present invention automatically selects one of the original QEP and the new QEP as the primary QEP in response to completion of the automatic collection of execution statistics, the primary QEP selected such that the automatically selected QEP provides optimal execution performance based on performance criteria in comparison to the automatically unselected QEP, the primary QEP selected based on the execution statistics.

Apparatus, system, and method for ensuring query execution plan stability in a database management system

Growing community of inventors

Alexandria, VA, United States of America

Patrick Dooling Bossman