International Business Machines Corporation

Terence Patrick Purcell

Thomas Abel Beavin

Andrei Fedorovich Lurie

Patrick Dooling Bossman

Shuanglin Guo

Hong Min

You-Chin Gene Fuh

Brian L Baggett

Yefim Shuf

Shengxi Suo

Martina Simova

Jason L Alpers

Hubertus Franke

Fen-Ling Lin

Li Jun Xia

Ying Zeng

Ou Jin

Al Chakra

Balakrishna Raghavendra Iyer

Garth Allen Dickie

Ian R Finlay

Guogen Zhang

Jae Hwang Lee

Oliver Draese

Mengchu Cai

Ruiping Li

Jeffrey Allen Berger

John Joseph Campbell

Paramesh Sampatrai Desai

Sauraj Goswami

Li-Mey P Lee

Michael R Shadduck

Yoichi Tsuji

Dengfeng Gao

Jonathan Sloan

Lily Zhou

Qianyang Yu

Xiaobo Wang

Felix O Beier

Nigel G Slinger

James Z Teng

Oliver Benke

Michael A Chang

John Benjamin Tobler

Brian Thinh-Vinh Tran

Tobias Ulrich Bergmann

Dennis Butterstein

Christopher John Crone

Allan Benjamin Lebovitz

Dongsheng Chen

Chunfeng Pei

Adarsh R Pannu

Yue Huang

Richard Joseph Brodfuehrer

Michael S Wang

Zhongliang Zhang

Kim Jon Worm

Ying-Lin Chen

David R Moy

Saghi Amirsoleymani

Kou-Horng A Yang

Martin Dinh

Brandon Jabr

According to an aspect, a computer-implemented method for improving query performance in a databased management system (DBMS) includes receiving, by a query optimizer of the DMBS, a query for execution. The method also includes creating, by the query optimizer, an initial access path for the query based on state and executing the query based on the initial access path. The method further includes observing, by a query agent of the DBMS, the execution of the query and modifying at least one of the state based on a determination by the query agent that a change to the initial access path would improve the execution of the query.

Query optimization using reinforcement learning

A method, system and computer program product for processing queries in a hybrid database system. A database management system of the hybrid database system obtains an estimated execution time of a query by an online transaction processing engine. The database management system further obtains an estimated execution time of the query by a database accelerator. Furthermore, the database management system determines a wait time (which could be zero) for the database accelerator to process the query. The database management system then determines whether the online transaction processing engine or the database accelerator will process the query using the estimated execution time of the query by the online transaction processing engine, the estimated execution time of the query by the database accelerator and the wait time (which could be zero) for the database accelerator to process the query. In this manner, the processing of queries in hybrid database systems is optimized.

Optimizing query processing and routing in a hybrid workload optimized database system

Provided are techniques for efficient subquery predicate generation to reduce processing in a multi-table join. It is determined that a query is to be rewritten, wherein the query includes a predicate for a first column of a first table and a second column of a second table. The query is rewritten with generated subquery predicates for the first table that are to be applied to the second table and with generated subquery predicates for the second table that are to be applied to the first table. Minimum and maximum ranges for columns of the generated subquery predicates at a cursor open are determined. The minimum and maximum ranges are merged to determine inclusive ranges that exist in the first table and the second table. The generated subquery predicates are rewritten as derived local subquery predicates with the inclusive ranges. The query with the derived local subquery predicates is executed.

Subquery predicate generation to reduce processing in a multi-table join

A DBMS query-optimization module receives a corpus of training data that contains data-access requests, such as SQL queries. Each request specifies data to be accessed but does not specify a query plan that the database should use to generate the requested data. The module identifies, in each received request, parameters, such as join methods and access methods, that can vary among query plans; and variables that cannot be assigned values until the query is actually processed. The system generates a set of queries, each of which implements a distinct query plan, that specify every viable permutation of values of the parameters and of the variables. The generated queries are added to the received corpus, which is forwarded to a machine-learning module in order to train the query-optimizer to select query plans that incur the lowest resource costs when servicing a particular type of query.

Permutation-based machine learning for database query optimization

A method, system and computer program product are provided. A first index scan of records of a database table is performed. A first index used by the first index scan is specified in a DBMS query statement as being ordered by one or more field values of one or more fields of the first index. A second scan of the database table is performed to determine whether each respective record of the second scan is qualified based on one or more fields of the each respective record of the second scan and a predicate. A lookup data structure is built indicating whether each of the respective records of the second scan is qualified. The lookup data structure is used to determine whether a record of the first index scan is qualified when the lookup data structure becomes available.

Helper scan in a database management system

Methods and systems for generating database statistics. Table statistics in a metadata catalog of a source database system are observed, statistics generation costs utilizing a target database system are estimated, and source statistics generation costs utilizing a source database system are estimated. The statistics generation costs are compared and statistics generation queries by the target database system are triggered in response to the statistics generation costs utilizing the target database system having a predefined relationship with the source statistics generation costs utilizing the source database system. The statistics generation queries are performed by the target database system in response to the triggering by the source database system. The generated statistics are sent from the target database system to the source database system, the table statistics in a metadata catalog are updated based on the generated statistics, and the updated table statistics are used to optimize a query plan.

Offloading statistics collection

A method, a computer program product and a system are provided. A case expression is analyzed for redundant expressions, patterns equivalent to a built-in function, and specific functions to form a simplified expression, wherein the case expression is written in a language supported by a database management system. The simplified expression is optimized based on a cost benefit evaluation of no optimization, native code generation, hashing inputs, and a results caching algorithm to form an execution plan. The execution plan is utilized in the database management system for the case expression.

Case statement optimization

Provided are techniques for minimizing processing using an index when non-leading columns match an aggregation key. A query that includes a clause and an aggregation key is received. It is determined that a non-leading key column of an index matches a column of the aggregation key comprising any of: a leading column of an ORDER BY aggregation key, any column of a duplicate removal aggregation key, a MIN aggregation key, and a MAX aggregation key. The clause is processed using an order of the aggregation key to generate a first result set. Then, the clause is processed using the non-leading key column of the index that matched the column of the aggregation key and the first result set to generate a second result set. The second result set is returned.

Minimizing processing using an index when non leading columns match an aggregation key

Minimizing processing using an index when non-leading columns match an aggregation key

Provided are techniques for progressive optimization for implicit CAST predicates. In response to receiving a query with an original predicate comparing a character value of a character data type to a literal value of a numeric data type, a number of iterations is set to a length of the character data type. For each of the number of iterations, prefix predicates are generated based on that iteration and processed to identify a qualified index key in an index. Once the iterations are completed, the character value and the literal value are normalized. The original predicate with the normalized character value and the normalized literal value is applied starting at the qualified index key identified in a last iteration to generate a result set for the query. The result set is returned.

Progressive optimization for implicit cast predicates

Disclosed aspects relate to index maintenance management of a relational database management system having a set of indexes. A subset of the set of indexes, which is indicated as a non-critical index subset, is detected with respect to the set of indexes. Related to maintenance of the subset of the set of indexes, a determination is made to perform an index maintenance impact action. Corresponding to the subset of the set of indexes, the index maintenance impact action is performed.

Index maintenance management of a relational database management system

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

Growing community of inventors

Springfield, IL, United States of America

Terence Patrick Purcell