Abstract: Techniques for generating a set of one or more materialized query table (MQT) candidates for a workload are provided. The techniques include receiving a workload, wherein the workload comprises a set of one or more queries, generating one or more best matching MQTs (BMQTs) based on one or more query blocks of the one or more queries by removing syntax that is not qualified for a MQT re-write, determining one or more frequently used multi-joins in the workload, using the one or more BMQTs and the one or more frequently used multi-joins to generate a set of one or more workload MQTs (WMQTs), and grouping one or more WMQTs and one or more BMQTs into one or more groups to merge into a set of a smaller number of MQTs and to cover the workload.

Abstract: A method and system are presented for quantifying a data page repetition pattern for a database index in a database management system. In one embodiment, the method includes identifying a database index to provide a basis for collecting a data page repetition statistic, the database index having a database index key. The method may also include collecting the data page repetition statistic based on the database index key, wherein the data page repetition statistic quantifies a data page repetition pattern associated with database queries that reference sequential entries of the database index. The method may further include optimizing a data page access process based on the data page repetition statistic. In a further embodiment, the method may utilize both cluster ratio and data page repetition statistics to evaluate data page I/O and CPU cost.

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

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

Abstract: A method, system, and computer program product are provided for retrieving records into a main memory. A first number of gaps and a first total gap size are received for a list of records from a database subsystem. A determination is made of a first average gap size using the first number of gaps and the first total gap size. A determination is made as to whether the first average gap size is greater than a prestaging threshold value. Responsive to the first average gap size being equal to or less than the prestaging threshold value, a prestaging flag is set for the list of records. Then, the list of records is retrieved into the main memory using prestaging.

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

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

Abstract: A method and system are presented for quantifying a data page repetition pattern for a database index in a database management system. In one embodiment, the method includes identifying a database index to provide a basis for collecting a data page repetition statistic, the database index having a database index key. The method may also include collecting the data page repetition statistic based on the database index key, wherein the data page repetition statistic quantifies a data page repetition pattern associated with database queries that reference sequential entries of the database index. The method may further include optimizing a data page access process based on the data page repetition statistic. In a further embodiment, the method may utilize both cluster ratio and data page repetition statistics to evaluate data page I/O and CPU cost.