Support for summary tables in a heterogeneous database environment

• US 6,496,828 B1
• Filed: 12/17/1999
• Issued: 12/17/2002
• Est. Priority Date: 12/17/1999
• Status: Expired due to Term

• Current Assignee: Red Hat, Inc. (International Business Machines Corporation)
• Original Assignee: International Business Machines Corporation
• Inventors: Pirahesh, Mir Hamid, Kleewein, James C., Venkataraman, Shivakumar, Leung, Ting Yu, Cochrane, Roberta Jo, Subramanian, Narayana
• Primary Examiner(s): Mizrahi, Diane D.
• Application Number:
  US09/466,862
• Time in Patent Office: 1,096 Days
• Field of Search: 707/2, 707/3, 707/4, 707/5, 707/100, 707/201, 707/10
• US Class Current: 707/717
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: Y10S 707/971 Federated
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

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

Claims

1. A system for supporting summary tables in a distributed database system, the apparatus comprising:

a communication module configured to communicate with a plurality of database systems; and

an identification module communicating with the communication module and configured to identify a database table containing summary data from at least one of the plurality of database systems as being a summary table.

2. The system of claim 1, wherein the plurality of database systems are heterogeneous.

3. The apparatus of claim 1, further comprising a central program, and wherein the communication module and the identification module are contained within the central program and at least one of the database systems is remote to the central program.

4. The apparatus of claim 3, wherein the database table is stored on the central program.

5. The apparatus of claim 1, wherein the summary table is located within one of the database systems that is remote to the central program.

6. The apparatus of claim 1, wherein the identification module comprises a single catalog resident in a central program.

7. The apparatus of claim 5, further comprising a nickname assigned to the database table, the nickname stored within the catalog together with an indicator that the database table is a summary table.

8. The apparatus of claim 1, further comprising a plurality of database management systems, one of the database management systems local to each of the database systems, at least one of the database management systems not directly supporting summary tables, and wherein the database management system not directly supporting summary tables is unaware that the database table is a summary table.

9. The apparatus of claim 1, further comprising a summary table maintenance module, the summary table maintenance module configured to maintain a summary table located within a remote database system.

10. The apparatus of claim 1, further comprising a summary table creation module within the central program, the summary table creation module configured to initiate the generation of a database table containing therein summary data from one or more of the plurality of database systems.

11. The apparatus of claim 10, wherein the summary table creation module is configured to initiate the generation of a database table containing therein summary data from one or more of the plurality of database systems and storage of the database table within the one or more of the plurality of database systems.

12. The apparatus of claim 1, wherein the central program further comprises a query processing module configured to receive a user query and in response generate an optimized query plan, considering the usage of a summary table summarizing data within the database systems in generating the optimized query plan.

13. The apparatus of claim 1, wherein the communication module is configured to communicate with a plurality of communicating database systems each having a different manner of supporting summary tables and further comprising a summary table management module configured to manage summary table usage on each of the communicating database systems, including accommodating the different manners of supporting summary tables.

14. A method for supporting a summary table in a distributed database system comprising a central program and one or more remote databases, the method comprising:

communicating with a plurality of database systems from a central location; and

identifying at the central location a database table containing therein summary data from at least one of the plurality of database systems as being a summary table.

15. The method of claim 14, wherein communicating with a plurality of database systems from a central location comprises communicating with a plurality of database systems that are heterogeneous and at least one of which is remote to the central location.

16. The method of claim 14, further comprising initiating the generation of a summary table summarizing the contents of at least two of the plurality of heterogeneous database systems.

17. The method of claim 14, wherein identifying a database table containing therein summary data from the plurality of heterogeneous database systems as a summary table further comprises referencing the database table within a catalog resident in the central program and identifying the database table as a summary table within the catalog.

18. The method of claim 17, wherein placing information indicative of the database table containing therein summary data within a single catalog resident in the central program further comprises placing a nickname corresponding to the database table containing therein summary data from one or more of the plurality of heterogeneous database systems in the catalog.

19. The method of claim 17, wherein placing information indicative of the database table containing therein summary data within a single catalog resident in the central program further comprises placing a description of the database table identifying the database table as a summary table in the catalog and linking the description with the nickname.

20. The method of claim 14, wherein communicating with a plurality of heterogeneous database systems from a central location comprises 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.

21. The method of claim 20, comprises 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 comprises creating the database table within the database management system not directly supporting summary tables without informing the database management system not directly supporting summary tables that the database table is a summary table.

22. The method of claim 14, further comprising initiating the generation of a database table containing therein summary data from one or more of the plurality of heterogeneous database systems.

23. The method of claim 14, wherein initiating the generation of a database table containing therein summary data from one or more of the plurality of heterogeneous database systems comprises initiating the generation of the database table within the one or more of the plurality of heterogeneous database systems.

24. The method of claim 14, further comprising receiving a user query and in response, considering the contents 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.

25. The method of claim 14, wherein communicating with a plurality of communicating database systems comprises communicating with database systems having differing manners of supporting summary tables therein, and further comprising managing summary table usage on each of the communicating database systems, including accommodating the differing manners of supporting summary tables.

26. The method of claim 14, wherein communicating with a remote database from a central location comprises communicating with a plurality of heterogeneous database systems from a central location, and wherein identifying a database table containing therein summary data from at least one of the plurality of heterogeneous database systems as being a summary table comprises recognizing as a summary table a database table within the remote database, the database table containing therein summary data corresponding to the remote database.

27. The method of claim 14, further comprising

generating an unrecognized summary table within one of the plurality of remote database systems; and

identifying the unrecognized summary table as a summary table.

28. An article of manufacture comprising a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform a method for supporting summary tables in a distributed database system, the method comprising:

communicating with a plurality of database systems from a central location; and

identifying at the central location a database table containing therein summary data from at least one of the plurality of database systems as being a summary table.

29. The article or manufacture of claim 28, wherein communicating with a plurality of database systems from a central location comprises communicating with a plurality of database systems that are heterogeneous and at least one of which is remote to the central location.

30. The article of manufacture of claim 29, wherein the method further comprises initiating the generation of a summary table summarizing the contents of at least two of the plurality of database systems.

31. The article of manufacture of claim 28, wherein identifying a database table containing therein summary data from the plurality of heterogeneous database systems as a summary table further comprises referencing the database table within a catalog resident in the central program and identifying the database table as a summary table within the catalog.

32. The article of manufacture of claim 31, wherein placing information indicative of the database table containing therein summary data within a single catalog resident in the central program further comprises placing a nickname corresponding to the database table containing therein summary data from one or more of the plurality of heterogeneous database systems in the catalog.

33. The article of manufacture of claim 31, wherein placing information indicative of the database table containing therein summary data within a single catalog resident in the central program further comprises placing a description of the database table identifying the database table as a summary table in the catalog and linking the description with the nickname.

34. The article of manufacture of claim 28, wherein communicating with a plurality of heterogeneous database systems from a central location comprises 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.

35. The article of manufacture of claim 34, wherein communicating with a plurality of database management systems, comprises creating the database table within the database management system not directly supporting summary tables without informing the database management system not directly supporting summary tables that the database table is a summary table.

36. The article of manufacture of claim 28, wherein the method further comprises initiating the generation of a database table containing therein summary data from one or more of the plurality of heterogeneous database systems.

37. The article of manufacture of claim 28, wherein initiating the generation of a database table containing therein summary data from one or more of the plurality of heterogeneous database systems comprises initiating the generation of the database table within the one or more of the plurality of heterogeneous database systems.

38. The article of manufacture of claim 28, wherein the method further comprises receiving a user query and in response, considering the contents 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.

39. The article of manufacture of claim 28, wherein communicating with a plurality of communicating database systems comprises communicating with database systems having differing manners of supporting summary tables therein, and further comprising managing summary table usage on each of the communicating database systems, including accommodating the differing manners of supporting summary tables.

40. The article of manufacture of claim 28, wherein communicating with a remote database from a central location comprises communicating with a plurality of heterogeneous database systems from a central location, and wherein identifying a database table containing therein summary data from at least one of the plurality of heterogeneous database systems as being a summary table comprises recognizing as a summary table a database table within the remote database, the database table containing therein summary data corresponding to the remote database.