Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.