International Business Machines Corporation

Thomas Abel Beavin

Terence Patrick Purcell

Andrei Fedorovich Lurie

Mengchu Cai

Shuanglin Guo

Patrick Dooling Bossman

Li Jun Xia

Hong Sang Tie

Shengxi Suo

You-Chin Gene Fuh

Fen-Ling Lin

Yao-Ching Stephen Chen

Jerry Mukai

Akira Shibamiya

Brian L Baggett

Hsiuying Yen Cheng

Dengfeng Gao

Balakrishna Raghavendra Iyer

Garth Allen Dickie

Sam Sampson Lightstone

Ian R Finlay

Jae Hwang Lee

Min Wang

Naresh K Chainani

Oliver Draese

Paramesh Sampatrai Desai

Sauraj Goswami

Shaorong Liu

Li-Mey P Lee

Michael R Shadduck

Yoichi Tsuji

Gary Howard Sockut

Irene Ching-Hua Liu

Ying Zeng

Jason L Alpers

Patrick Michael Malone

Demai Ni

Xun Li

Qianyang Yu

Yun Yu Wang

Shuo Li

Xiaobo Wang

Curt Lee Cotner

Ke Wei Wei

Nigel G Slinger

James Z Teng

James Alan Ruddy

Gerald George Kiernan

Walid Rjaibi

Ruiping Li

Donald J Haderle

John Benjamin Tobler

John Marland Garth

Timothy Jon Vincent

John Joseph Campbell

Laura Michiko Kunioka-Weis

Christopher John Crone

Randy Mitchell Nakagawa

Peter Hoa

Joseph F Sinnott, Jr

Adarsh R Pannu

Gregory R Stager

Irene C Liu

Brian Tran

Liyan Zhou

Harley W Boughton

Zhang Hao

Yuping Ding

Brandon Jabr

Joyce A Taylor

Xiazhen Wang

Martin Dinh

David R Moy

Eric L Alton

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

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

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

Performing a join of first and second database tables for a query includes applying a predicate of the query to the first table as a first predicate and determining how many distinct join key values the first table has that survive the applying of the first predicate, wherein a join key value of the first table that survives the applying of the first predicate is a surviving join key value for a second predicate. A selection includes selecting among applying the second predicate to the second table, probing the second table with the second predicate, and neither applying the second predicate to the second table nor probing the second table with the second predicate, wherein the selecting is responsive to the number of distinct, surviving join key values.

Technology for join processing

Disclosed aspects relate to query processing management in a database management system (DBMS). The DBMS may detect that a query has a set of subqueries. The DBMS may detect that the set of subqueries indicates a set of data structures. A lead data structure may be selected from the set of data structures using a selection criterion. A set of input columns may be identified from the lead data structure of the set of data structures. A set of output columns may be identified from the set of data structures other than the lead data structure. A hash data structure may be constructed using the set of input columns and the set of output columns. A cache may be built based on the hash data structure.

Query processing management in a database management system

Embodiments presented herein provide techniques for setting different methods of skipping duplicate values when executing a query statement in a relational database. A distance between a two distinct keys in an index, a current index key and a next distinct index key, are estimated. Based on the estimated distance, an appropriate duplicate-skipping method is determined. If the proximity between the distinct keys is relatively far apart (e.g., the keys reside in index pages that are at least an index page apart), then a 'big skip' method is performed. Otherwise, if the proximity between the distinct keys is relatively near (e.g., the keys reside in the same index page), then a 'little skip' method is performed.

Dynamically adjust duplicate skipping method for increased performance

Growing community of inventors

Milpitas, CA, United States of America

Thomas Abel Beavin