Autonomic recommendation and placement of materialized query tables for load distribution

• US 20070174292A1
• Filed: 01/26/2006
• Published: 07/26/2007
• Est. Priority Date: 01/26/2006
• Status: Active Grant

• Current Assignee: Twitter Inc (X Holdings Corp.)
• Original Assignee: International Business Machines Corporation
• Inventors: Li, Wen-Syan, Zilio, Daniele
• Granted Patent: US 7,689,538 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A system and method of evaluating queries in distributed databases with MQTs comprises deriving MQTs; replicating the derived MQTs from a local server to at least one remote server; and distributing data and replicated derived MQTs to a plurality of other remote servers, wherein the distributing increases overall query execution efficiency. The databases may comprise heterogeneous databases. The query execution efficiency comprises observed response time at a frontend database and associated costs comprising computational central processing unit costs, input/output costs, and network communication costs. All of the associated costs comprise statistically estimated costs. The method further comprises running a MQT advisor at a frontend database, and considering the costs of at least one MQT placed at the frontend database. The method further comprises running a MQT advisor at a non-frontend database. Additionally, the increased overall query execution efficiency may consider all dependencies of all involved database instances and associated costs.

Claims

1. A method of evaluating queries in distributed databases with materialized query tables (MQTs), said method comprising:

deriving MQTs;

replicating the derived MQTs from a local server to at least one remote server; and

distributing data and replicated derived MQTs to a plurality of other remote servers, wherein said distributing increases overall query execution efficiency.

2. The method of claim 1, wherein said databases comprise heterogeneous databases.

3. The method of claim 1, wherein said query execution efficiency comprises observed response time at a frontend database and associated costs comprising computational central processing unit costs, input/output costs, and network communication costs.

4. The method of claim 3, wherein all of said associated costs comprise statistically estimated costs.

5. The method of claim 4, further comprising running a MQT advisor at a frontend database, and considering said costs of at least one MQT placed at said frontend database.

6. The method of claim 4, further comprising running a MQT advisor at a non-frontend database.

7. The method of claim 3, wherein the increased overall query execution efficiency considers all dependencies of all involved database instances and associated costs.

8. The method of claim 1, wherein said deriving of the MQTs occurs using a data placement advisor database management system.

9. A program storage device readable by computer, tangibly embodying a program of instructions executable by said computer to perform a method of evaluating queries in distributed databases with materialized query tables (MQTs), said method comprising:

deriving MQTs;

replicating the derived MQTs from a local server to at least one remote server; and

distributing data and replicated derived MQTs to a plurality of other remote servers, wherein said distributing increases overall query execution efficiency.

10. The program storage device of claim 9, wherein said databases comprise heterogeneous databases.

11. The program storage device of claim 9, wherein said query execution efficiency comprises observed response time at a frontend database and associated costs comprising computational central processing unit costs, input/output costs, and network communication costs.

12. The program storage device of claim 11, wherein all of said associated costs comprise statistically estimated costs.

13. The program storage device of claim 12, wherein said method further comprises running a MQT advisor at a frontend database, and considering said costs of at least one MQT placed at said frontend database.

14. The program storage device of claim 12, wherein said method further comprises running a MQT advisor at a non-frontend database.

15. The program storage device of claim 11, wherein the increased overall query execution efficiency considers all dependencies of all involved database instances and associated costs.

16. The program storage device of claim 9, wherein said deriving of the MQTs occurs using a data placement advisor database management system.

17. A system of evaluating queries in distributed databases with materialized query tables (MQTs), said system comprising:

MQTs;

a replicator adapted to replicate the derived MQTs from a local server to at least one remote server; and

an information integrator adapted to distribute data and replicated derived MQTs to a plurality of other remote servers, wherein the distributing increases overall query execution efficiency.

18. The system of claim 17, wherein said databases comprise heterogeneous databases.

19. The system of claim 17, wherein said query execution efficiency comprises observed response time at a frontend database and associated costs comprising computational central processing unit costs, input/output costs, and network communication costs.

20. The system of claim 18, wherein all of said associated costs comprise statistically estimated costs.

21. The system of claim 20, further comprising means for running a MQT advisor at a frontend database, and considering said costs of at least one MQT placed at said frontend. database.

22. The system of claim 20, further comprising means for running a MQT advisor at a non-frontend database.

23. The system of claim 19, wherein the increased overall query execution efficiency considers all dependencies of all involved database instances and associated costs.