Abstract: A reverse routing system optimizes execution of a query that accesses data stored in one or more materialized query tables in a database of a computer system. The system receives a query directly referencing the materialized query table. The system identifies the referenced materialized query tables in a catalogue of materialized query tables and a defining query associated with the referenced materialized query table. The system substitutes the defining query for the referenced materialized query table in the received query. The system adds the referenced materialized query table to the set of eligible materialized query tables that are selected using query matching algorithms so that they can be considered for routing by the query optimizer.

Abstract: A method, apparatus, and article of manufacture for optimizing database queries using an automatic summary table. A query is analyzed using matching and compensation tests between the query at least one correlated subquery within the query and the automatic summary table to determine whether expressions occurring in the query, but not in the automatic summary table, can be derived using the automatic summary table. If so, the query is rewritten so that the automatic summary table is used.

Abstract: A system for incrementally maintaining non-distributive aggregate functions in a relational database includes a data storage device in which a relational database is stored. A processor communicates with the data storage device and includes a database maintenance module. The database maintenance module includes a program for incrementally maintaining non-distributive aggregate functions in a relational database. The method embodied in the program includes determining whether all functions in a relational database query are distributive. Based on this determination, a basic propagate phase graph is selectively altered to yield a new propagate phase graph. Changes to an automatic summary table are then applied thereto based on the new propagate phase graph.

Abstract: A system, method and computer readable medium for sampling data from a relational database are disclosed, where an information processing system chooses rows from a table in a relational database for sampling, wherein data values are arranged into rows, rows are arranged into pages, and pages are arranged into tables. Pages are chosen for sampling according to a probability P and rows in a selected page are chosen for sampling according to a probability R, so that the overall probability of choosing a row for sampling is Q=PR. The probabilities P and R are based on the desired precision of estimates computed from a sample, as well as processing speed. The probabilities P and R are further based on either catalog statistics of the relational database or a pilot sample of rows from the relational database.

Abstract: A method, apparatus, and article of manufacture for incrementally maintaining a summary table derived from a plurality of base tables in a database stored on the computer. A plurality of modifications are applied to the base tables, and the applied modifications are then propagated to the summary table. The propagated modifications include an associated tag column containing a tag value, and the tag value indicates per row of the propagated modifications how many rows are to be added to or deleted from the summary table.

Abstract: A method, apparatus, and article of manufacture for optimizing database queries using a derived summary table, wherein a definition of the summary table is based on a full select statement, including, but not limited to, a derived table involving nested GROUP BY operations and complex HAVING clauses with subqueries or joins, that is materialized in the table and describes how the summary table was derived. A query is analyzed using matching/compensation tests between the query and the definition of the summary table (that is, a query by itself) to determine whether expressions occurring anywhere in the query, but not in the summary table, can be derived using either the content in the summary table alone, or after combining (through some relational operator) the content of the summary table with other base tables, and hence the query is subsumed by or overlaps with the summary table definition.

Abstract: A system and method transform queries with subqueries, using window aggregation. An optimizer in a relational database management system transforms queries to optimize their efficiency and speed. The method transforms queries that have a subquery, replacing the subquery with a window aggregation function. In the case of a correlated subquery, the window aggregation function is partitioned by a correlated column of a correlated table. All data in the main select clause, or outer block, of the query that was obtained through references to the correlated table is instead obtained through the new window aggregation subquery. By using window aggregation, the aggregation is performed at the same time as the selection of relevant data from the correlated table, thereby compiling all needed data in a single pass through the table or view. Reducing the number of times that tables or views are accessed reduces the computational demands of a query.

Abstract: Disclosed is a system, method, and program for specifying multidimensional calculations. Selection of a subset of a cube model metadata object that is generated from a facts metadata object and one or more dimension metadata objects is received. The facts metadata object references one or more measure metadata objects. A statement is generated for retrieving multidimensional information using metadata in the cube model metadata object and the measure metadata objects, wherein each of the measure metadata objects specifies one or more aggregations.

Abstract: A method, apparatus, and article of manufacture for the incremental maintenance of summary tables with complex grouping expressions where the change (insert/delete/update) of a single row of the base data can affect multiple rows in the summary table. The invention applies the complex grouping expression to the raw delta, yielding a delta stream consisting of multiple (but distinct) grouping combinations. The invention then inserts/deletes/updates delta values into/from the existing summary table so that each grouping combination of the delta stream modifies its corresponding grouping combination in the summary table.

Abstract: A system for incrementally maintaining non-distributive aggregate functions in a relational database includes a data storage device in which a relational database is stored. A processor communicates with the data storage device and includes a database maintenance module. The database maintenance module includes a program for incrementally maintaining non-distributive aggregate functions in a relational database. The method embodied in the program includes determining whether all functions in a relational database query are distributive. Based on this determination, a basic propagate phase graph is selectively altered to yield a new propagate phase graph. Changes to an automatic summary table are then applied thereto based on the new propagate phase graph.

Abstract: A system, method and computer readable medium for sampling data from a relational database are disclosed, where an information processing system chooses rows from a table in a relational database for sampling, wherein data values are arranged into rows, rows are arranged into pages, and pages are arranged into tables. Pages are chosen for sampling according to a probability P and rows in a selected page are chosen for sampling according to a probability R, so that the overall probability of choosing a row for sampling is Q=PR. The probabilities P and R are based on the desired precision of estimates computed from a sample, as well as processing speed. The probabilities P and R are further based on either catalog statistics of the relational database or a pilot sample of rows from the relational database.

Abstract: An optimization technique that reorders outerjoins and antijoins with inner joins in a bottom-up optimizer of a relational database management system (RDBMS). Each join predicate is associated with a normal eligibility list (NEL) that includes tables that are referenced in the join predicate and an extended eligibility list (EEL) that includes additional tables that are referenced in conflicting join predicates. An EEL includes all the tables needed by a predicate to preserve the semantics of the original query. During join enumeration, the optimizer determines whether a join predicate's EEL is a subset of all the tables in two subplans to be merged, i.e., whose EEL is covered. If so, the two subplans are combined using the join predicate. Otherwise, the two subplans cannot be joined. Two approaches are used to reordering: without compensation and with compensation. The “without compensation” approach only allows join reorderings that are valid under associative rules.

Abstract: A method, apparatus, and article of manufacture for optimizing database queries. The query is analyzed to determine whether at least a portion of the query can be evaluated using a plurality of parallel operations without data redistribution. If so, then the most efficient query execution plan that uses these parallel operations is constructed and executed.

Abstract: A method, apparatus, and article of manufacture for maintaining a materialized view derived from at least one base table in a database stored on a computer. An update is performed to the base table in a transaction. Thereafter, the materialized view is accessed to identify affected records therein using at least one mechanism, such as an isolation level or update- and exclusive-locks, to guarantee consistency while avoiding deadlocks with other transactions that modify at least one base table of the materialized view and to improve concurrency with other transactions that are accessing the materialized view when the modification to the base table is applied to the materialized view.

Abstract: A method, apparatus, and article of manufacture for the incremental maintenance of summary tables with complex grouping expressions.

Abstract: A method, apparatus, and article of manufacture for optimizing database queries using an automatic summary table. A query is analyzed using matching and compensation tests between the query at least one correlated subquery within the query and the automatic summary table to determine whether expressions occurring in the query, but not in the automatic summary table, can be derived using the automatic summary table. If so, the query is rewritten so that the automatic summary table is used.

Abstract: A method, apparatus, and article of manufacture for optimizing database queries using subsumption tests between the query and at least one summary table that comprises a cube index to determine whether an expression in the query can be subsumed in the summary table. The summary table stores at least one materialized view involving at least one GROUP BY operation that computes at least one of the following: (1) a cube, (2) a rollup, (3) a grouping set, and (4) a concatenation of cubes, rollups, grouping sets, and one or more grouping items. When the expression in the query can be subsumed in the summary table, the query is rewritten to use the summary table.

Abstract: A system, method, and article of manufacture for supporting summary tables in a database system that does not otherwise support summary tables is disclosed. The system generally comprises a central program and one or more database systems that may be heterogeneous. At least one of the database systems does not support the generation, maintenance, and/or querying of summary tables. The central program is configured to communicate with the database systems and to identify relations corresponding to summary tables (also referred to as materialized views) within one or more of the database systems. The central program may initiate the generation of summary tables, which may be populated local to the central program or local to one or more of the database systems. The central program may also maintain or coordinate maintenance of the summary tables.

Abstract: A system, method, and article of manufacture for supporting summary tables in a distributed database environment is disclosed. The system generally comprises a central program and a plurality of remote database systems that may be heterogeneous. The central program is configured to communicate with the database systems and to support summary tables (also referred to as materialized views) within the central program or within one or more of the database systems. The summary tables may contain summary data from one or more of the database systems. The central program may initiate the generation of summary tables, which may be populated local to the central program or local to one or more of the database systems. The central program may also maintain or coordinate maintenance of the summary tables.

Abstract: An optimization technique that reorders outerjoins and antijoins with inner joins in a bottom-up optimizer of a relational database management system (RDBMS).