International Business Machines Corporation

Narayana Iyer Subramanian

Shivakumar Venkataraman

Mir Hamid Pirahesh

Ting Yu Leung

Roberta Jo Cochrane

James C Kleewein

A database maintenance system and method are particularly suitable for maintaining a consistency of data between two or more database tables having schemas that may be heterogeneous (e.g., restructuring views of each other). Typically, one table is a base table and a second table is a restructuring view of the base table and may have been generated for faster query response, research, or the like. The restructuring view may have occurred naturally, or the base table may have been converted to the restructuring view using a series of operators and optionally, a middleware schema. The restructuring view maintenance system is preferably configured to monitor changes to the base table, including changes to data and to metadata. The maintenance system is configured to receive notification of a modification to a base table, investigate the modifications, and determine which operator(s) have been used to convert the base table to the restructuring view. For each modification to the base table, the system then determines, according to the type of modification and the type of transformation used, which of a selected group of actions to take to map the modification onto the restructuring view. The selected group of actions may include variously: insert, update, delete, rename relation, drop table, and rename column, according to the type of modification and the mapping of the relationship between the base table and the target table.

Restructuring view maintenance system and method

A schema mapping system and method are particularly suitable for expressing a schema mapping between two database schemas that may be heterogeneous (restructuring views of each other). The schema mapping system is useful for generating a least cost query plan expressible on a plurality of heterogeneous tables that are restructuring views of each other. The system may be configured to receive a query against one of the tables and convert it to a plurality of alternate queries, each formatted for the particular schema of a different one of the plurality of heterogeneous tables. In generating the alternate queries, the schema mapping system may utilize SchemaSQL view definitions and may employ restructuring operators expressing and capable of conducting a restructuring of a table into a restructuring view of the table. The system may employ a canonical schema and a virtual canonical table to express a mapping between restructuring views, and may utilize the schema mapping for purposes of query conversion. The alternate queries may be costed and optimized and a query plan returned that has a least cost or service time involved.

Schema mapping system and method

A query optimization system and method are particularly suitable for generating a least cost query plan expressible on a plurality of heterogeneous database schemas that are restructuring views of each other. The query optimization system receives a query against one of the tables and converts it to a plurality of alternate queries, each formatted for the particular schema of a different one of the plurality of heterogeneous tables. In generating the alternate queries, the system may utilize SchemaSQL view definitions and may employ restructuring operators expressing and capable of conducting a restructuring of a table into a restructuring view of the table. A middleware system involving a canonical schema and a virtual canonical table may also be employed to express a mapping between restructuring views for purposes of query conversion. The alternate queries may be costed and optimized and a query plan returned that has a least cost or service time involved.

Query optimization system and method

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. In addition, the central program may be configured to receive user queries on one or more of the database systems and to generate optimized query plans based upon the user queries, considering in so doing, the summary tables.

Support for summary tables in a heterogeneous database environment

The present invention discloses a method, apparatus, and article of manufacture for optimizing one or more queries. Initially, redundancies in execution steps for the one or more queries are identified. Then, a new set of equivalent execution steps is created by eliminating redundancies in the execution steps. The new set of equivalent execution steps is used to execute the one or more queries when the use results in efficient query processing.

Growing community of inventors

San Jose, CA, United States of America

Narayana Iyer Subramanian