International Business Machines Corporation

Robert Victor Downer

Robert J Bestgen

John David Dietel

Shantan Kethireddy

Brian Robert Muras

David Glenn Carlson

Robert Douglas Driesch, Jr

Curtis Neal Boger

Mi Huynh

Ulrich Thiemann

Andrew Peter Passe

Wei Hu

Techniques for replication rule evaluation are provided. A replication rule is received at a first node of a plurality of nodes where the replication rule defines object replication among the plurality of nodes. The replication rule is labeled as pending, where a second replication rule on the first node is labeled as active. Upon receiving a request to predict an effect of the replication rule, a first object of a plurality of objects is identified based on the first replication rule. Upon determining that the first object is present on the first node but is not present on a second node of the plurality of nodes, an indication of the first object is outputted.

Evaluating pending object replication rules

Techniques for dynamically detecting objects that are out of sync in an active mirrored environment are provided. A request is received at a local node of an active mirroring environment from a system function to perform a change operation on an object. Upon determining that the object is OOS, it is determined whether the local node or a remote node of the active mirroring environment has the most recent changes for the object. In one embodiment, the determination includes analyzing corresponding time stamps of changes listed in a repository of changes for objects in the mirrored environment. Upon determining that a remote node has the most recent changes for the object, a blocking attribute is set and the requested operation on the object blocked. However, upon determining that the local node has the most recent changes for the object, a tracking attribute is set and the requested operation allowed.

Method to dynamically detect objects out of sync in an active mirroring environment

A method, apparatus and program product are provided for performing a query of a database. A database query is received, which includes first and second operations respectively configured to generate first and second disjoint results sets from a dataspace. The database query is analyzed to identify a set of attributes from the dataspace that are used by at least one of the first and second operations in the database query. During execution of the database query, a plurality of records from the dataspace is iteratively processed in a single pass, including, for each of the plurality of records, processing such record by retrieving the plurality of attributes for such record from the dataspace and performing each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets.

Multiple result sets generated from single pass through a dataspace

A query optimizer optimizes a query to a partitioned database table by determining common characteristics of the partitions and generating a virtual maintained temporary index that spans multiple partitions. Using the virtual maintained temporary index allows the query optimizer to generate an access plan based on the virtual maintained temporary index, which relieves the optimizer from having to individually optimize access to each partition for partitions that share common characteristics.

Optimizing a query to a partitioned database table using a virtual maintained temporary index that spans multiple database partitions

In an embodiment, a first driver source specified by a first execution plan that implements a query is designated, and a second driver source specified by a second execution plan that implements the query is designated. A portion of the first execution plan and a portion of the second execution plan are executed. If a unique identifier of a first row returned by the executing the portion of the first execution plan does not match all unique identifiers of all rows from the second driver source that were saved to a result set by the executing the portion of the second execution plan, then the first row returned by the executing the portion of the first execution plan is added to the result set and the unique identifier of the first row that was returned by the executing the portion of the first execution plan is added to a unique identifier log.

Execution plans with different driver sources in multiple threads

In an embodiment an execution plan for a query is created. A wakeup threshold is set proportional to an amount of time taken by the creation of the execution plan. In various embodiments, the wakeup threshold is increased by a percentage equal to one minus a percentage of free resources at a computer system, is increased inversely proportional to an amount of execution time of a previous execution of the execution plan, or is decreased proportional to a number of times the execution plan was executed. A portion of the execution plan is executed to produce a portion of rows in a result set until the wakeup threshold expires. After the wakeup threshold expires, changes to the execution plan are evaluated.

Evaluating execution plan changes after a wakeup threshold time

In an embodiment, a portion of the execution plan is executed to produce a portion of records in a partial result set. If a first join of a table is performed that eliminates more than a first threshold number of records from the partial result set, a determination is made whether an estimated cost of a forced primary join is less than an estimated cost of a star join. If the estimated cost of the forced primary join is less than the estimated cost of the star join, then the table is moved first in a join order in the execution plan, the portion of the records in the partial result set is discarded, and the execution is re-started with a different portion of the execution plan to produce a different portion of the records.

Join order for a database query

A method, apparatus and program product to optimize a database query. A query plan is generated for the database query where the query plan includes first and second subplans. Cached optimization data associated with the first subplan and generated during evaluation of another query plan for a previous query that uses the first subplan is retrieved. The first subplan is evaluated for resource cost using the retrieved optimization data. The second subplan is also evaluated for resource cost. The query plan is then evaluated for resource cost based upon the evaluations of resource cost for the first and second subplans. A query plan is selected based on evaluations of resource cost and the plan is executed to generate a result set.

Portable and iterative re-usable suboptimization of database queries

A process combines multiple grouping sets into single rollup sets with depth lists defining the levels of grouping that must be performed. Grouping sets are identified that are contained within other sets and combined into single rollups with depth lists. Cube aggregation conversion to rollup aggregation is provided for optimizing database query processing. Natural sets of rollup hierarchies within a cube are recognized and the cube is converted into those rollup hierarchies. Once converted, the rollup aggregation is performed to significantly reduce required processing.

Implementing aggregation combination using aggregate depth lists and cube aggregation conversion to rollup aggregation for optimizing query processing

An apparatus, program product and method utilize a query optimizer that identifies Join types in a query plan that can be replaced to facilitate optimization of the plan. If the query plan is considered to have a tree-graph representation, then the optimizer locates Join nodes to be replaced and substitutes an Inner Join node and additional nodes that do not inhibit optimization strategies and algorithms. As a result, all Outer Joins, whether left or right, and all Exception Joins, whether left or right, can be removed from the query plan before performing optimizations such as predicate movement, GROUP BY movement, ORDER BY movement, and Join enumeration.

Outer and exception join to inner join normalization

A database query optimizer constructs a graph comprising nodes, relations, and expressions. The query optimizer then constructs execution plans for sub-parts of the graph. The combination of execution plans make up the overall execution plan for the query. The execution plan information is appended to the graph itself, allowing changing an execution plan in one portion of the graph without necessarily changing execution plans in other portions of the graph. By representing a query using the graph of the preferred embodiments that includes execution plan information, the query optimizer is able to evaluate the execution plans of different options quickly and efficiently, thereby enhancing the performance of the query optimizer.

Database query optimization apparatus and method that represents queries as graphs

An apparatus, program product and method utilize a query optimizer that relies upon a plurality of dynamically-dispatchable active strategy entities to generate an optimized execution plan for a database query. The plurality of active strategy entities form a optimizer framework from which the knowledge available to the optimizer, and the intelligence utilized by the optimizer in generating execution plans, may be represented in a structured, flexible, extensible and easily-modifiable manner.

Growing community of inventors

Rochester, MN, United States of America

Robert Victor Downer