BACKGROUND OF THE INVENTION

1. The Field of the Invention

The present invention relates to database systems and more specifically, to manners of utilizing summary tables in a distributed database environment.

2. The Relevant Technology

A summary table, otherwise known as a materialized view, is a pre-computed and stored result of a query. A standard view is a derived relation defined in terms of base relations. A view defines a function mapped from a set of base tables to a derived table. This function is typically recomputed every time the view is referenced. A view becomes a materialized view when the tuples generated by the view are stored in a database. Index structures can be built on the materialized view. Consequently, database accesses to the materialized view can be much faster than recomputing the view. A materialized view or summary table is thus like a cache, essentially embodying a copy of selected data that can be accessed quickly.

The data stored in a database is generally accessed using a query formatted in the SQL language native to most existing database systems. It is a primary objective in designing database systems to be able to service queries with the least cost, that is, in the lowest amount of time. One manner of decreasing query response time is with the use of summary tables. It is often the case that certain data can be accessed more quickly by accessing one or more summary tables in which copies of the data have been stored. Summary tables are generally quite small relative to the entire database, and scanning a summary table is much more efficient than scanning multiple relations of a database. Thus, one technique for speeding up query servicing is to maintain a plurality of summary tables and to selectively direct queries to the appropriate summary table for which the query can be most rapidly serviced. It would be advantageous to employ summary tables directed to the various heterogeneous database systems in query optimization.

As a further complication to the employment of summary tables in multiple database systems (MDBS), some database systems do not support summary tables. In order to provide more efficient query servicing, it is desirable to utilize summary tables in such an environment.

Additionally, it is considered by the inventors to be advantageous in some instances to be able to support the summary tables locally within the respective databases of an MDBS, rather than within a centralized database management program. The benefits of so doing include better performance for a class of queries that involve computation on large amounts of data but that yields relatively smaller result sets; the possibility for using the remote source's replication utilities for maintaining the currency of the summary table; and the enablement of the remote database's applications to take advantage of the summary table.

It would be further advantageous if a single central program could be employed to support and manage summary tables on a plurality of heterogeneous database systems, including where necessary, on databases that do not natively support summary tables.

Accordingly, a need exists for a distributed database system that capitalizes on the use of summary tables. To best utilize such a system, the capability should be provided to centrally generate, maintain, and query the summary tables. The system preferably makes provisions for communicating with database systems that do not support summary tables. Such a distributed database system and its method of use are disclosed herein.

OBJECTS AND BRIEF SUMMARY OF THE INVENTION

The apparatus of the present invention has been developed in response to the present state of the art, and in particular, in response to the problems and needs in the art that have not yet been fully solved by currently available database management systems. Thus, it is an overall objective of the present invention to provide a distributed database apparatus, system, and article of manufacture that capitalizes on summary tables within a plurality of heterogeneous database tables.

To achieve the foregoing object, and in accordance with the invention as embodied and broadly described herein in the preferred embodiment, a distributed database system and method are provided. The distributed database system is preferably implemented with modules for execution by a processor. In one embodiment, the modules comprise a central program comprising a communication module configured to communicate with a plurality of databases and an identification module configured to identify as a summary table a relation containing therein summary data from the plurality of databases.

At least one of the databases may be remote to the central program, and the databases may be heterogeneous. Each database may comprise a database system having database storage and a database manager. In one embodiment, at least one of the database systems does not directly support summary tables.

In one embodiment, the identification module comprises a single catalog resident in the central program. A nickname corresponding to the relation containing therein summary data from one or more of the plurality of heterogeneous database systems may be stored in the catalog. A description of the relation containing therein summary data as a summary table may also be stored in the catalog in conjunction with the nickname.

The central program may also comprise a summary table creation module within the central program, the summary table creation module configured to initiate the generation of a relation containing therein summary data from one or more of the plurality of databases.

The summary table may be created within the central program or, alternatively, remotely within the database system. In embodiments where the database system does not support summary tables, the database system is unaware that a relation containing therein summary data is a summary table.

The central program preferably comprises a query processing module configured to receive a user query and process the query for submission to one or more of the database systems. In one embodiment, the query processing module, generates an optimized query plan, considering in so doing, a cost model and the contents of one or more summary tables within one or more of the database systems.

An attendant method of use of the distributed database system in one embodiment comprises communicating with a plurality of databases from a central location and identifying as a summary table a relation containing therein summary data from the plurality of heterogeneous database systems. The step of communicating with a plurality of databases from a central location may comprise communicating with at least one database remote to the central program.

In one embodiment, the step of identifying as a summary table a relation containing therein summary data from the plurality of databases further comprises placing information indicative of the relation containing therein summary data within a single catalog resident in the central program.

The step of placing information indicative of the relation containing therein summary data within a single catalog resident in the central program may comprise placing a nickname corresponding to the relation containing therein summary data from one or more of the plurality of databases in the catalog. Additionally, placing information indicative of the relation containing therein summary data within a single catalog resident in the central program may comprise placing a description of the relation containing therein summary data as a summary table in the catalog and linking the description with the nickname.

The step of communicating with a plurality of databases from a central location may comprise communicating with a plurality of database management systems, one of the database management systems local to each of the heterogeneous database systems, at least one of the database management systems not directly supporting summary tables.

The method may further comprise communicating with a plurality of database systems, in which at least one of the database management systems does not directly support summary tables. This may comprise creating a relation containing therein summary data within the database management system without informing the database management system that the relation containing therein summary data is a summary table.

The method may further comprise initiating the generation of a relation containing therein summary data from one or more of the plurality of heterogeneous database systems. The relation may be generated within the central program or within the one or more of the plurality of heterogeneous database systems.

The method may further comprise receiving a user query and in response, considering the usage of one or more summary tables within one or more of the heterogeneous database systems in generating an optimized query plan based upon the user query.

In yet another aspect of the invention, an article of manufacture comprises a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform the above-described method of use of a distributed database system.

These and other objects, features, and advantages of the present invention will become more fully apparent from the following description and appended claims, or may be learned by the practice of the invention as set forth hereinafter.

BRIEF DESCRIPTION OF THE DRAWINGS

In order that the manner in which the above-recited and other advantages and objects of the invention are obtained will be readily understood, a more particular description of the invention briefly described above will be rendered by reference to specific embodiments thereof which are illustrated in the appended drawings. Understanding that these drawings depict only typical embodiments of the invention and are not therefore to be considered to be limiting of its scope, the invention will be described and explained with additional specificity and detail through the use of the accompanying drawings in which:

FIG. 1

is a schematic block diagram illustrating one embodiment of a computer system for use with the present invention.

FIG. 2

is a schematic block diagram illustrating one embodiment of a distributed database system of the present invention.

FIG. 3

is a schematic flow chart illustrating on embodiment of a method of supporting summary tables in a distributed database system of the present invention.

FIG. 4

is a schematic flow chart diagram illustrating in greater detail one manner of conducting a generate summary table operation of FIG.

3

.

FIG. 5

is a schematic flow chart diagram illustrating in greater detail steps for conducting a register summary table operation of FIG.

3

.

FIG. 6

is a schematic flow chart diagram illustrating in greater detail one manner of conducting a query of a summary table of FIG.

3

.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

The presently preferred embodiments of the present invention will be best understood by reference to the drawings, wherein like parts are designated by like numerals throughout. It will be readily understood that the components of the present invention, as generally described and illustrated in the figures herein, could be arranged and designed in a wide variety of different configurations. Thus, the following more detailed description of the embodiments of the apparatus, system, and method of the present invention, as represented in

FIGS. 1 through 12

, is not intended to limit the scope of the invention, as claimed, but is merely representative of presently preferred embodiments of the invention.

FIGS. 1 through 12

are schematic block diagrams and flow chart diagrams that illustrate in more detail the preferred embodiments of the present invention. The schematic block diagrams illustrate certain embodiments of modules for performing various functions of the present invention. In general, the represented modules include therein executable and operational data for operation within a computer system of

FIG. 1

in accordance with the present invention.

As used herein, the term executable data, or merely an “executable,” is intended to include any type of computer instructions and computer executable code that may be located within a memory device and/or transmitted as electronic signals over a system bus or network. An identified module of executable code may, for instance, be organized as an object, procedure, or function. Nevertheless, the executables of an identified module need not be located together, but may comprise disparate instructions stored in different locations which together comprise the module and achieve the purpose stated for the module. Indeed, an executable could be a single instruction, or many instructions, and may even be distributed over several different code segments, among different programs, and across several memory devices.

Similarly, operational data may be identified and illustrated herein within modules, and may be embodied in any suitable form and organized within any suitable type of data structure. The operational data may be collected as a single data set, or may be distributed over different locations including over different storage devices, and may at least partially exist merely as electronic signals on a system bus or network.

FIG. 1

is a schematic block diagram that illustrates a computer system

10

in which executable and operational data, operating in accordance with the present invention, may be hosted on one or more computer stations

12

in a network

14

. The network

14

may comprise a wide area network (WAN) and may also comprise an interconnected system of networks, one particular example of which is the Internet and the World Wide Web supported on the Internet.

A typical computer station

12

may include a processor or CPU

16

. The CPU

16

may be operably connected to one or more memory devices

18

. The memory devices

18

are depicted as including a non-volatile storage device

20

such as a hard disk drive or CD ROM drive, a read-only memory (ROM)

22

, and a random access volatile memory (RAM)

24

.

The computer station

12

or system

10

in general may also include one or more input devices

26

for receiving inputs form a user or form another device. Similarly, one or more output devices

28

may be provided within or be accessible from the computer system

10

. A network port such as a network interface card

30

may be provided for connecting to outside devices through the network

14

. In the case where the network

14

is remote from the computer station, the network interface card

30

may comprise a modem, and may connect to the network

14

through a local access line such as a telephone line.

Within any given station

12

, a system bus

32

may operably interconnect the CPU

16

, the memory devices

18

, the input devices

26

, the output devices

28

the network card

30

, and one or more additional ports

34

. The system bus

32

and a network backbone

36

may be thought of as data carriers. As such, the system bus

32

and the network backbone

36

may be embodied in numerous configurations. For instance, wire, fiber optic line, wireless electromagnetic communications by visible light, infrared, and radio frequencies may be implemented as appropriate.

In general, the network

14

may comprise a single local network (LAN), a wide area network (WAN), several adjoining networks, an intranet, or as in the manner depicted, a system of interconnected networks such as the Internet

40

. The individual stations

12

communicate with each other over the backbone

36

and/or over the Internet

40

with varying degrees and types of communications capabilities and logic capability. The individual stations

12

may include a mainframe computer on which the modules of the present invention may be hosted.

Different communication protocols, e.g., ISO/OSI, IPX, TCP/IP, may be used on the network, but in the case of the Internet, a single, layered communications protocol (TCP/IP) generally enables communications between the differing networks

14

and stations

12

. Thus, a communication link may exist, in general, between any of the stations

12

.

The stations

12

connected on the network

14

may comprise application servers

42

, and/or other resources or peripherals

44

, such as printers and scanners. Other networks may be in communication with the network

14

through a router

38

and/or over the Internet

40

.

Referring now to

FIG. 2

, the distributed database system

50

of the present invention, in one embodiment, includes a plurality of modules containing executable code and operational data suitable for execution by the CPU

16

and for operation within the memory devices

18

of FIG.

1

. Of course, the memory devices

18

in which the modules of the present invention are located may also be distributed across both local and remote computer stations

12

.

The distributed database system

50

is shown in

FIG. 2

comprising a central database communications program (central program)

52

operating in conjunction with a plurality of database systems

54

. The database systems

54

may be local to the central program

52

, existing on a common hard disk drive

20

within a common computer station

12

. Alternatively, the database systems

54

may be remote to the central program

52

and communicate across a remote link

56

. The remote link

56

may comprise a network

14

(of

FIG. 1

) which may be a LAN or a WAN. The remote link

56

may also comprise a more global communication network, such as the Internet

40

.

Each of the database systems

54

is preferably provided with a data storage mechanism, such as a database

58

. A database management module

60

is also preferably provided for conducting such tasks as user interface, maintenance, query response, and the like. Within the database systems

54

may also be provided a summary table

80

, as will be discussed below. Examples of database systems

54

with which the central program

52

may communicate include Sybase™, Informix™, Oracle™, and SQL Server™, SQL Anywhere™, Terradata™, and IBM's DB2™ in its various forms and configurations.

The central program

52

is preferably a linking and communicating program that allows a user at a central location to communicate with a plurality of database systems, regardless of where the databases are located. Preferably, the central program

52

is also configured to allow a user to communicate with different types of database systems

54

in a federated environment, and to conduct operations upon the database systems

54

. These operations may include, for example, generation or maintenance of tables within the database systems

54

, maintenance, or communications such as querying the database systems

54

. One example of a central program

52

, given by way of example, is DataJoiner™, distributed by IBM Corporation of Rochester, N.Y.

In one embodiment, the database systems

54

are heterogeneous. That is, they are different types of database systems with different configurations and possibly from different manufacturers. For instance, one database system

54

might be an IBM DB2 database system, while another database system

54

might comprise a Sybase database system. In addition, the different database systems

54

may contain tables exhibiting heterogeneous formatting, or “schemas.” That is, the information within tables of the database systems

54

maybe organized into different combinations of relations, rows, and columns, possibly with different relation names, row names, and column names.

In the depicted embodiment, the central program

52

comprises an interface module

62

for allowing a human user to interact with the central program

52

, a communication module

64

for effecting communications with the database systems

54

, and a management module

66

.

The management module

66

is preferably configured to store information regarding the differing types of database systems

54

are communicating with the central program

52

and their locations. The management module may also store information relating to the specific database types

54

within models

94

. Preferably, one model

94

is stored for each type of database system

54

that the central program

52

is allowed to communicate with. The models may also contain information regarding the different configurations that are possible for a particular type of database system

54

. Among the information preferably stored in the models

94

or in the management module

66

generally is information regarding how the particular database system

54

handles summary tables.

Thus, for instance, a separate model

94

may be provided for the Sybase database, a model

94

may be provided for the SQL Server database, a model

94

may be provided for the Oracle database, a model

94

may be provided for the DB2 database, etc. The management module

66

also preferably includes an index

96

of the different database systems

54

communicating with the central program

52

. Thus, various modules of the central program

52

preferably consult the management module

66

when dealing with a particular database system

54

in order to determine how to properly communicate with the database system

54

. One example, of dealings with the database systems, as will be discussed, is the particular manner of establishing summary tables

80

within the database systems

54

and/or summarizing data of the database systems

54

, maintaining the summary tables

80

, and querying the summary tables

80

.

The central program

52

may also comprise a centralized summary table

68

in lieu of, or as a supplement to, summary tables

80

located remotely within the database systems

54

. In one embodiment, the central program

52

comprises IBM's DataJoiner™ program, which is combined into an IBM DB2™ database system, and the summary table

68

is located within the DB2™ database

58

. In this instance, the IBM DB2™ database system is considered to be local to the central program

52

(DataJoiner™).

Any one of the summary tables

68

,

80

may comprise summary information

98

summarizing data on one or more of the databases

58

of the database systems

54

. In fact, one advantage of the present invention is that a Summary Table A

80

within a database system A

54

may, for instance, summarize data located within Database System A as well as summarizing information located within Database System B and/or others of the database systems

54

. Likewise, the centralized summary table

68

may contain summary data

98

summarizing the contents of one or more of the databases

58

.

Also included in the central program

52

may be a summary table creation module

70

, a summary table maintenance module

72

, a query processing module

74

, and a catalog

76

. In one embodiment, the summary table creation module is configured to initiate the generation of a summary table

80

,

68

. The summary table generation may be initiated at a user's request or in accordance with the answering of a query

85

.

The initiation of the summary table generation may be conducted in any manner, but is conducted in one embodiment using a DDL transparency feature. DDL transparency takes advantage of DDL commands inherent to the SQL language. That is, SQL DDL commands

78

are used to generate a table within a given database

54

. The summary table

68

,

80

is generated containing summary data

98

and is effectively a summary table, though the database system

54

need not recognize the table (or relation)

68

,

80

as a summary table, and indeed, the database system

54

may not even support the generation of summary tables

80

,

68

. In such a case, the summary table is referred to herein as an “unrecognized summary table.” Where the summary table

68

is generated within the central program

52

, the database system(s) that it summarizes need not even know of the existence of the summary table

68

.

The summary table creation module

70

, when initiating the generation of a summary table

80

,

68

preferably instructs the generating entity, whether the central program

52

or a database manager

60

, to assign the summary table

68

,

80

an alias or “nickname”

90

. The nickname

90

is preferably transmitted to the catalog

76

and stored there with other information

88

regarding the contents of the database

58

. This information

88

preferably includes lists of tables within each database

58

, the nature of the data stored in the tables, and the particular schema used in each table.

A description

92

of each summary table

68

,

80

, its schema, and its contents, is also preferably generated and stored within the catalog

76

, preferably together with the information

88

of the database or databases

58

which the summary table

68

,

80

summarizes. Thus, the catalog

76

and information stored therein serve as one manner of identifying the summary tables

68

,

80

as constituting summary tables and as such may be referred to herein as an identification module. Of course, other types of identification modules for identifying the summary data may be utilized, including identification of the summary tables

68

,

80

within indexes such as the index

96

.

The summary table maintenance module

72

preferably maintains or coordinates the maintenance of the summary tables

68

,

80

. That is, the summary table maintenance module

72

preferably instructs the database systems

54

to maintain the summary tables

68

,

80

in a selected manner. The particular manner may be automatic and preselected, or may be selected on a case by case basis by the user. The particular manners of maintaining the summary tables

68

,

80

include immediate, SQL-synchronous maintenance in which maintenance is triggered by any modifications to the tables within the scope of the view definition and incremental, non-SQL-synchronous maintenance in which a data propagator or replication tool periodically computes updates to be done on the summary table and maintains these updates on a periodic basis. One example of a replication tool is DPROP-R™ from IBM. A further type of maintenance is deferred maintenance in which a user initiates a full refresh of the summary table and populates it when desired by the user.

The query processing module

74

is shown including a cost model

84

, a query

85

, and a query plan

86

. In one basic embodiment, the query processing module

74

is configured to receive a query

85

from a user through the interface module

62

and transmit the query

85

to the relevant database systems

54

to answer the query

85

.

In a more preferred embodiment, the query processing module

74

also optimizes the query

85

into an optimized query plan

86

. In so doing, the query processing module

74

preferably consults a cost model

84

. The query processing module

74

also preferably considers the particular summary tables

68

,

80

that may exist either locally on the central program

52

and/or remotely on the database systems

54

.

The query processing module may be configured to generate an optimized query plan

86

that directly references one or more summary tables

68

,

80

in answering the query

85

. It may also be configured to provide instructions to the database systems

54

without referring to the summary tables

80

, such that the database systems

54

are allowed to decide locally whether or not to reference the summary tables

80

. One example of a manner of configuring a query processing module is described in our copending and pending patent application Ser. No. 09/412,034 filed on Oct. 4, 1999 for Query Optimization System and Method, which is hereby incorporated by reference into this document.

The catalog

76

preferably contains information

88

regarding each of the communicating database systems

54

. As mentioned, together with that information

88

is preferably stored information regarding each summary table

68

,

80

the nickname

90

of each summary table

80

together with a description of the schema and contents of the summary tables

80

.

FIG. 3

is a schematic flow chart diagram illustrating one embodiment of a general method

100

of use of the distributed database system

50

of FIG.

2

. As depicted, the method

100

starts at a step

102

and progresses to a step

104

. In the step

104

, the database systems

54

are preferably provided. Preferably, the database systems

54

are configured in the manner described above. In one embodiment, the database systems

54

are heterogeneous. That is, the database systems

54

are of different types and/or configurations, as discussed above.

In a step

106

, a central database communications program (central program)

52

is provided. Preferably, the central program

52

is configured in the manner described above.

In a step

108

, one or more summary tables

68

,

80

are generated. The summary tables

68

,

80

may be created local to the central program

52

or may be created remotely on one or more of the database systems

54

. Preferably, the summary tables

68

,

80

comprise information that constitutes a summary of data of or more of the databases

58

. Thus, the summary tables

68

,

80

are useful in many instances in reducing query cost and are thus preferably considered by the query processing module

74

in generating an optimized query plan

86

. A method

130

of

FIG. 4

describes in greater detail one manner of generating a summary table

68

,

80

.

In a step

110

, information pertaining to one or more of the summary tables

68

,

80

generated at step

108

is registered in the central program

110

. A method

150

of

FIG. 5

describes in greater detail one manner of registering a summary table

68

,

80

in the central program

110

.

In a step

112

, the summary table(s)

68

,

80

generated in step

108

are maintained. The manners of maintaining the summary tables are selected, either by a user or automatically by the central program

52

and are preferably selected from among the three manners described above.

In a step

114

, a query

85

is generated by a user. Preferably, the query

85

is directed to information stored on one or more of the database systems

54

. The query is preferably submitted first to the central program. The central program

52

may submit the query

85

directly to the one or more database systems

54

to which it is directed, or may optimize or otherwise process the query. Preferably, a response such as a query plan

86

is generated and submitted to the database systems

54

. In one embodiment, in generating a response to the query

85

, one or more summary tables

68

,

80

is considered. One method

170

for responding to the query using summary tables

68

,

80

is illustrated below with respect to FIG.

6

. At a step

116

, the method

100

ends.

FIG. 4

illustrates one method

130

of generating a summary table. Thus, the method

130

may be considered to be one manner of conducting the step

108

of FIG.

3

. The method

130

starts at a step

132

and progresses to a step

134

where a DDL command is generated. The DDL command may be generated by a user using standard SQL syntax. Alternatively, the DDL command may be generated in response to a user command which requests the generation of a summary table. One example of a DDL command is given in Example 1.0.

A distributed database system consisting of three tables on a Sybase database system

54

has the following Schema:

EMP (empno, ename, salary, dno)

DEPT (dno, dname, budget)

EMP_PERS (ename, dob, address, date-recruited)

These three tables are represented as nicknames EMP, DEPT, and EMP_PERS on the central program

52

. One example of a DDL command

78

to generate a summary table

68

,

80

on two of these tables is as follows:

CREATE SUMMARY TABLE RICHDEPT_EMP (name, salary, dept) AS

(SELECT ename, salary, dname

FROM EMP, DEPT

WHERE budget >1,000,000

AND EMP.dno =DEPT.dno)

REFRESH DEFERRED

in SYBASE_TABLESPACE;

The DDL command identifies those employees that work in departments that have a budget that is greater than one million. The summary table is created locally on one of the Sybase database systems

54

and a nickname, RICHDEPT_EMP, corresponding to the summary table is created on the central program

52

.

EXAMPLE 1.0

At a step

136

, the DDL command

78

is transmitted to the database system

54

on which the command

78

is to be executed. In the case of Example 1.1, the DDL command is transmitted to the selected Sybase database system

54

.

At a step

138

, the DDL command

78

is executed, thereby summarizing the contents of the database(s)

58

. Thus, in the case of Example 1.1, the tables EMP and DEPT, which correspond to databases

58

on separate database systems

54

are summarized according to the query parameters selected by the user. The table is then stored, either on the central program

52

or on one of the database systems

54

, which as discussed, may be remote to the central program

52

.

Of course, if the database system

54

does not support summary tables, the summary table may still be created directly on the database system

54

. The DDL command is issued, but while summarizing contents of the database system

54

(and/or other database systems

54

) the DDL command merely creates a normal table which is recognized by the database system

54

as a normal table, but which is recognized by the central program

52

as a summary table.

At a step

140

, a nickname

90

is assigned to the summary table

68

,

80

. In Example 1.1, the nickname is assigned with the transmission of the DDL command

78

and is accompanied by information regarding how to access the summary table

68

,

80

.

At a step

142

, a description

92

of the summary table

68

,

80

being created is generated. The description

92

is generated automatically by SQL. In one embodiment, the description comprises a flag that is turned on in the catalog

76

indicating a summary table. Additionally, the description

92

preferably comprises the view definition used in generating the summary table

68

,

80

. For instance, the DDL command preferably contains the view definition of the summary table and when the DDL command is applied to create the summary table, the central program

52

is configured to store that view definition in the catalog

76

.

In one embodiment the central program comprises a standard database manager

60

or is in communication with a database manager

60

. For instance, in the preferred embodiment where the central program

52

comprises DataJoiner®, DataJoiner is provided within or in conjunction with IBM's DB2®.

At a step

144

, the nickname

90

and description

92

are transmitted to the catalog

76

of the central program

52

and stored therein. At a step

146

, the method

130

ends.

FIG. 5

illustrates one method

150

of registering a new summary table in the central program. Thus, the method

150

may considered to be one manner of conducting the step

110

of FIG.

3

. The method

150

starts at a step

152

and progresses to a step

154

where a catalog

76

is provided within a central program

52

. Preferably, the catalog

76

is formatted in the manner described above.

At a step

156

, the nickname

90

is filed in an appropriate location in the central program

52

. In one embodiment, the nickname

90

is filed together with other database information

88

in the catalog

76

of the central program

52

.

At a step

158

, a description such as the description

92

generated at step

142

of

FIG. 4

is filed in an appropriate location in the central program

52

. Once again, the description

92

is preferably filed together with other information

88

in the catalog

76

of the central program

52

.

At a step

160

, the new summary table

68

,

80

is registered within the central program

52

as constituting a summary table. Thus, regardless of whether the local database system

54

recognizes the newly created summary table

68

,

80

as containing summary data or not, it is treated by the central program as a summary table. At a step

162

, the method

150

ends.

FIG. 6

illustrates one method

170

of querying a communicating database system

54

using a summary table

68

,

80

. Thus, the method

170

may be considered to be one manner of conducting the step

114

of FIG.

3

. The method

170

starts at a step

172

and progresses to a step

174

where a query

85

is received from a user. The query

85

is preferably an SQL query and may be received directly from a human user or from a separate program communicating with the central program

52

. The query

85

is received into the central program

52

, preferably into the query processing module

74

.

At a step

176

, the query processing module

74

parses the query

85

into initial data structures suitable for further operations. Such data structures in one embodiment include query graph models (QGM). A discussion of the use of query graph models can be found in Hamid Pirahesh, et. al., Extensible Rule Based Query Rewrite Optimization in Starburst, as published in the conference proceedings for SIGMOD 1992, pages 39-48, which is hereby incorporated by reference into this document.

At a step

178

, semantics of the query

85

are checked for accuracy and completeness. For instance, checks may be made to see if the various types of data such as column names and relation names referenced by the query

85

are correct and whether referenced tables exist.

At a step

180

, the query is rewritten into a more efficient form. In so doing, at a step

182

, the query processing module

74

preferably considers the contents of the summary tables as referenced by the information

92

within the catalog

76

. Preferably, this comprises consulting information

92

about all summary tables

68

,

80

that may exist within the system

50

and determining whether referencing information within one or more of the summary tables

68

,

80

in answering the query

85

would improve query response time.

At a step

184

, a plurality of alternate query plans are preferably generated, each plan directed to answering the query in a different manner. At a step

186

, the various query plans are costed according to the cost model

84

. Preferably, the cost model

84

is provided with reference costs for the various operations that may be required to answer a query

85

. At a step

188

, the query optimized for the least cost is selected according to the projected costs from step

186

. These steps are disclosed in greater detail in our co-pending and pending patent application Ser. No. 09/412,034, listed above.

In one embodiment, the optimized query plan

86

references the summary tables

68

,

80

where so doing is the most efficient manner of answering the query

85

. Where referencing the summary tables

68

,

80

is not most efficient, the selected optimized query plan

86

does not reference the summary tables.

In a step

190

, the queryplan

86

is transmitted to the databases

58

referenced therein. This generally comprises transmitting the optimized query plan

86

or relevant portions thereof across the link

56

to the referenced database systems

54

. At a step

192

, the managers

60

within the database systems

54

execute their respective portions of the query plan

86

to generate the desired results. The results are transmitted to the user either directly or through the central program and are displayed on a display device

28

for viewing by the user.

The optimized query plan

86

generated in the method

170

may directly reference summary tables

80

within the database systems

54

. Alternatively, the query plan

86

may be directed to other tables of the databases

58

without referencing any of the summary tables

80

. In these cases, the managers

60

in the target database systems consider locally whether or not to consult a summary table

80

. In so doing, the query processing module

74

preferably still considers the summary tables

80

, predicts whether the managers

60

will reference the summary tables in responding to the query plan

86

, and uses this information in generating a least cost query plan

85

.

In cases of database systems

54

, such as current Sybase database systems, that are not equipped to create, recognize, or otherwise support summary tables, the summary table can be still be created. Thus, under the present invention, summary tables can be generated summarizing the databases

58

of the database systems, whether summary tables are supported on the database systems

54

or not.

Additionally, the generation of a summary table using DDL commands is transparent to the database system

54

. Furthermore, assigning the summary table a nickname and storing the nickname and information regarding the contents and schema of the summary table within the central program

52

allows the central program