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

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

Abstract: In accordance with aspects of the present invention, provided are systems and computer program products for incrementally estimating the cardinality of a derived relation including statistically correlated partially applicable predicates for a range-partitioned table. During the generation of a QEP a cardinality estimate is calculated in which one or more partially applicable predicates is correlated to another partially applicable predicate and/or to one or more fully applicable predicates. The cardinality includes a number of rows expected to be returned by the QEP and is computed in an incremental fashion for each operator of the QEP.

Abstract: In accordance with aspects of the present invention, provided are systems and computer program products for incrementally estimating the cardinality of a derived relation including statistically correlated partially applicable predicates for a range-partitioned table. During the generation of a QEP a cardinality estimate is calculated in which one or more partially applicable predicates is correlated to another partially applicable predicate and/or to one or more fully applicable predicates. The cardinality includes a number of rows expected to be returned by the QEP and is computed in an incremental fashion for each operator of the QEP.

Abstract: In accordance with aspects of the present invention, provided are methods for incrementally estimating the cardinality of a derived relation including statistically correlated partially applicable predicates for a range-partitioned table. During the generation of a QEP a cardinality estimate is calculated in which one or more partially applicable predicates is correlated to another partially applicable predicate and/or to one or more fully applicable predicates. The cardinality includes a number of rows expected to be returned by the QEP and is computed in an incremental fashion for each operator of the QEP.

Abstract: A system and method are provided for updating database statistics for use in generating query execution plans. A first query for a database is received, for example by a compiler, and the compiler accesses statistics associated with the first query for optimizing a query execution plan for the first query. The statistics may be accessed from a first cache. If the statistics retrieved from the first cache are determined to be stale, the system obtains updated statistics and may store them in a temporary cache. The stale statistics in the first cache are marked as invalid. Existing queries using the first cache may continue to use the first cache, while new queries may be provided with updated statistics.

Abstract: A system and method are provided for updating database statistics for use in generating query execution plans. A first query for a database is received, for example by a compiler, and the compiler accesses statistics associated with the first query for optimizing a query execution plan for the first query. The statistics may be accessed from a first cache. If the statistics retrieved from the first cache are determined to be stale, the system obtains updated statistics and may store them in a temporary cache. The stale statistics in the first cache are marked as invalid. Existing queries using the first cache may continue to use the first cache, while new queries may be provided with updated statistics.

Abstract: In accordance with aspects of the present invention, provided are methods for incrementally estimating the cardinality of a derived relation including statistically correlated partially applicable predicates for a range-partitioned table. During the generation of a QEP a cardinality estimate is calculated in which one or more partially applicable predicates is correlated to another partially applicable predicate and/or to one or more fully applicable predicates. The cardinality includes a number of rows expected to be returned by the QEP and is computed in an incremental fashion for each operator of the QEP.

Abstract: In accordance with aspects of the present invention, provided are methods for incrementally estimating the cardinality of a derived relation including statistically correlated partially applicable predicates for a range-partitioned table. During the generation of a QEP a cardinality estimate is calculated in which one or more partially applicable predicates is correlated to another partially applicable predicate and/or to one or more fully applicable predicates. The cardinality includes a number of rows expected to be returned by the QEP and is computed in an incremental fashion for each operator of the QEP.

Abstract: An apparatus, system, and method are disclosed for autonomically analyzing a database management system (DBMS). This is accomplished by recording database commands in a datastore while imposing minimal performance effects on the database management system. Then, an optimal playback window is determined for replaying the recorded database commands while imposing minimal performance effects on the DBMS. The recorded database commands are replayed during the determined optimal playback window, and performance statistics are gathered in response to the replayed database commands. The gathered performance statistics are sent to one or more autonomic consumer applications which are responsible for optimizing the DBMS.

Abstract: The present system improves the performance of a query in a database system when a plan for the query comprises sorting an input that is at least partially sorted such that a slow materialization sort can be applied. The invention applies the slow materialization sort by determining a sequence of subsets in accordance with the partially sorted input. As each of the subsets is determined, the subset is output for further processing. Advantageously, the invention reduces the waiting period for obtaining results from a sorting operation under certain circumstances.

Abstract: The present system improves the performance of a query in a database system when a plan for the query comprises sorting an input that is at least partially sorted such that a slow materialization sort can be applied. The invention applies the slow materialization sort by determining a sequence of subsets in accordance with the partially sorted input. As each of the subsets is determined, the subset is output for further processing. Advantageously, the invention reduces the waiting period for obtaining results from a sorting operation under certain circumstances.

Abstract: In a multiprocessing system, multiple concurrently operating task execution units are operated to perform an aggregate task by using incremental and on-demand sub-task allocation. A command is received to perform a machine-executed task divisible into multiple sub-tasks, i.e., an “aggregate task”. A granularity is then established, for dividing the aggregate task into sub-tasks. Preferably, the granularity is not too large to permit potentially uneven sub-task allocation, and not too small to incur excessive overhead in allocating sub-tasks. Having established the granularity, multiple task execution units are independently operated on-demand to sequentially self-allocate and execute sub-tasks of the aggregate tasks. Each sub-task is sized according to the established granularity. Operating “on-demand”, each task execution unit sequentially allocates and executes one sub-task at a time, then proceeding to the next unexecuted sub-task.