International Business Machines Corporation

Calisto Paul Zuzarte

Ian R Finlay

Vincent Corvinelli

Qi Cheng

Mir Hamid Pirahesh

Adam J Storm

Nattavut Sutyanyong

Wenbin Ma

Christian Marcelo Garcia-Arellano

John Frederick Hornibrook

David C Sharpe

Abhinav Goyal

Jens Peter Seifert

Ting Yu Leung

Roberta Jo Cochrane

Daniele Costante Zilio

Daniel Costante Zilio

Linqi Liu

Joern Klauke

Huaxin Liu

Ziting Yu

Mingbin Xu

Guy Maring Lohman

Richard Sefton Sidle

Kevin Scott Beyer

David Everett Simmen

Robert Neugebauer

Mohamad F Kalil

Mohammed Fahd Alhamid

Sam Sampson Lightstone

Bruce Gilbert Lindsay

Ihab Francis Ilyas

Timothy Ray Malkemus

Yang Sun

Xin Wu

Naresh K Chainani

David C Kalmuk

Liping Zhang

Andrey Balmin

Volker Gerhard Markl

Jun Rao

Marion Else Behnen

Kiran Chinta

Hebert Walter Pereyra

Robin Grosman

Xiaohui Yu

Thomas Rech

Ashraf Aboulnaga

Piotr Mierzejewski

John E Lumby

Fei Yen Chiang

Mokhtar Kandil

Victor Muntes-Mulero

Josep Aguilar Saborit

Iman Elghandour

Wing Yan Lau

Amr El-Helw

Seyed Mohammad Amin Kamali

Josep L Lariba-Pey

Josep Aguilar-Saborit

Peter Haas

Mark Steven Ramsey

Michael Jeffrey Winer

Walid Rjaibi

Jeffrey M Keller

Tuong Chanh Truong

Vincent Kulandaisamy

Gary Valentin

Monica Sachiye Urata

George Lapis

William T O'Connell

Ivan Popivanov

Martin Keller

Miroslaw Adam Flasza

Serge Philippe Rielau

Michael Joshua Snowbell

Markos Zaharioudakis

Dieu Quang La

Milind Chitgupakar

Xiaoyan Qian

Jonathan Sloan

Tony Wen Hsun Lai

Hamid Pirahesh

Haider Abbas Rizvi

Herbert Walter Pereyra

Petrus Kai Chung Chan

Edward Gust Branish, Ii

Robin D Van Boeschoten

Brandon Lewis Frendo

Ning Wang

Vasiliki Kantere

Josep L Larriba-Pey

Michael Arthur Franciscus Stuy

Alberto Lerner

Victor Muntés-Mulero

Kit Man Cheung

Samuel S Lightstone

Johann Christoph Freytag

Joshua D Gross

Mustafa Dawoud

A computer implemented method for processing database queries includes receiving a query and a set of runtime metrics corresponding to the query, wherein the query includes a set of elements, generating a set of encoded elements corresponding to the set of elements, processing the set of encoded elements and the set of runtime metrics to identify one or more possibly query classifications, determining a query execution plan according to the identified one or more possible query classifications, and executing the query according to the determined query execution plan. The computer implemented method may additionally include providing one or more runtime metrics corresponding to the executed query. A computer program product and a computer system corresponding to the method are also disclosed.

Adaptive query optimization using machine learning

Aspects of the invention include techniques for robust query execution plan selection. A non-limiting example method includes training a model to estimate, for an input including a query and one or more plans, an execution time for each plan and a respective uncertainty of the execution time. A new query and a search space including a plurality of candidate plans are input to the model. Given an estimated distribution for the execution time of each candidate plan, a suboptimality risk is computed for each candidate plan. A plan of the plurality of candidate plans is selected according to a plan selection policy. The plan selection policy includes at least one of: selecting a plan by assuming that plans have higher costs proportional to an estimated standard deviation of their respective uncertainty; and selecting a plan with minimum risk using model uncertainty, data uncertainty, or a total uncertainty.

Robust query execution plan selection using machine learning with predictive uncertainties

Aspects of the invention include techniques for providing a learned join cardinality estimation using a join graph representation. A non-limiting example method includes building a join cardinality estimation model. The model can be built by generating a training query having a known join cardinality, generating an adjacency matrix encoding a join graph of the training query, encoding one side of a diagonal axis of the adjacency matrix, and training the join cardinality estimation model using the encoded adjacency matrix and the known join cardinality. The method includes performing an inference using the join cardinality estimation model. The inference includes a predicted join cardinality for a query. The method includes executing a query execution plan for the query using the predicted join cardinality.

Learned join cardinality estimation using a join graph representation

Examples described herein provide a computer-implemented method that includes training a machine learning model. The model is trained by generating a set of training queries using at least one of a query workload and relationships between tables in a database, building a query graph for each of the set of training queries, computing, for each training query of the set of training queries, a selectivity based at least in part on the query graph, and building, based at least in part on the set of training queries, an initial join result distribution as a collection of query graphs.

Automated query selectivity predictions using query graphs

A method, a computer system, and a computer program product for cardinality estimation is provided. Embodiments of the present invention includes accessing database relations. The database relations are utilized to collect a random sample from each of the database relations. Training data is then generated from the random sample. The training data is used to build a cumulative frequency function (CFF) model. The cumulative frequency function (CFF) model then provides a cardinality estimation for an output for SQL operators.

Automated query predicate selectivity prediction using machine learning models

In an approach to improve query optimization in a database management system, embodiments identify opportunities for improvement in a cardinality estimate using a workload feedback process using a query feedback performed during query compilation. Embodiments identify correlations and relationships based on the structure of the query feedback and the runtime feedback performed, and collects data from the execution of a query to identify errors in estimates of the query optimizer. Further, embodiments submit the query feedback and the runtime feedback to a machine learning engine to update a set of models. Additionally, embodiments update a set of models based on the submitted query feedback and runtime feedback, and output a new, updated, or re-trained model based on collected data from the execution of the query to identify the errors in estimates of the query optimizer, the submitted query feedback and the runtime feedback, or a trained generated mode.

Automated feedback and continuous learning for query optimization

The method includes identifying at least one of a minimum value, a maximum value, and a Bloom filter value for a row of data in a metadata table, wherein the metadata table contains metadata corresponding to a row of data in a main table. The method includes adjusting at least one of an identified first minimum value to a second minimum value, an identified first maximum value to a second maximum value, and an identified first Bloom filter value to a second Bloom filter value.

Optimization of metadata via lossy compression

A maintenance subsystem of a database-management system (DBMS) receives a database query that requests access to data stored in a database column. The subsystem retrieves or infers frequent-value statistics for that column, each of which specifies the number of times one distinct value is stored in the column. The statistics are partitioned into Keep and Discard clusters and, using statistical or other computational methods based on the column's data distribution, the subsystem determines an optimal number of the statistics that should be kept by the DBMS in order to minimize cost, errors, or other parameters desired by an implementer. The subsystem then directly or indirectly directs a query-optimizer component of the DBMS to consider the optimal number of frequent-value statistics when selecting an optimal data-access plan. The selected plan is then used by the DBMS's storage-manager component to access the column when servicing the received query.

Automated optimization of number-of-frequent-value database statistics

A computer-implemented method for a partitioned bloom filter merge is provided. A non-limiting example of the computer-implemented method includes partitioning, by a processing device, a bloom filter into N equal size filter partitions. The method further includes distributing, by the processing device, each of the filter partitions to an associated node. The method further includes merging, by the processing device, the filter partitions in each of the associated nodes. The method further includes redistributing, by the processing device, the merged filter partitions to each of the N nodes. The method further includes joining, by the processing device, the merged filter partitions in each of the N nodes to assemble a complete merged bloom filter.

Partitioned bloom filter merge for massively parallel processing clustered data management

A database query comprising predicates may be received. Each predicate may operate on database columns. The database query may be determined to comprise strict operators. An upper bound neural network may be defined for calculating an adjacent upper bound and a lower bound neural network may be defined for calculating an adjacent lower bound. The upper bound neural network and the lower bound neural network may be trained using a selected value from data of a database table associated with the database query to be executed through the upper bound neural network and the lower bound neural network. The upper bound neural network and the lower bound neural network may be adjusted by passing in an expected value using an error found in expressions. The adjacent lower bound and the adjacent upper bound may be calculated in response to completion of initial training for the database columns.

Estimating cardinality selectivity utilizing artificial neural networks

A method, system and computer program product includes receiving and parsing an SQL query, identifying at least one common sub expression, or sub-query, or combination thereof, used multiple times within the SQL query, constructing for the at least one common sub expression, or sub-query, or combination thereof, a query execution plan that maintains as part of an initial result set a bit vector for a fact table, storing a result bit vector and an indicator that tracks a last valid tuple processed to produce the result bit vector when a TEMP operation is indicated in the query execution plan, reassessing a TEMP result in other portions of the query execution plan, priming a list of tuples using the TEMP result, and retrieving respective columns for further processing in the query execution plan.

Reducing temp size over a base table

Embodiments described herein provide a solution for optimizing a generating of query search results. A filtering search term (e.g., a search term that is used in a query to perform filtering aggregations of the query search results) is identified. A filtering bitmap that has a plurality of mapped locations corresponding to data values for the filtering search term is created. As a data value in the filtering search term is encountered during a scan of the query search results, the corresponding mapped location is updated. Each mapped location in the filtering bitmap is read to determine whether the value corresponding to the mapped location satisfies the filtering aggregation. The filtering aggregation can then be performed (e.g., prior to any grouping aggregation) by removing any of the query search results determined, based on the filtering bitmap, as having data values for which the filtering aggregation is not satisfied.

Grouping aggregation with filtering aggregation query processing

Provided herein are techniques for processing a database query aggregating data. Data tuples of a database object each including a grouping element and a data element are analyzed to determine a length of the data element for each data tuple. A plurality of tables each accommodate a successively greater length for the data element. A corresponding table of the plurality of tables to store each data tuple is determined based on the length of the data element of that data tuple relative to the accommodated lengths of the plurality of tables. Each data tuple in the determined corresponding table is stored to group the data tuples within each of the plurality of tables based on the grouping element, and an indication of corresponding tables containing members for each group is provided. The groups are combined across the plurality of tables to aggregate the data tuples for a database query.

Efficient database query aggregation of variable length data

Merging adjacent rows of a synopsis table so as to increase the effectiveness of the synopsis table regarding data skipping. Adjacent rows for merging are identified based on statistics regarding: (i) queries of the database; (ii) effectiveness of the synopsis table for data skipping; and (iii) usage of predicates in queries of the database. Once merged, the synopsis table is smaller, and more effective with respect to data skipping, while fewer computing resources (administrative, maintenance, memory, clock cycles, storage space, etc.) are needed to process the database queries.

Automatically revising synopsis table structure

Growing community of inventors

Pickering, Canada

Calisto Paul Zuzarte