Ultra-shared-nothing parallel database

• US 20050187977A1
• Filed: 02/17/2005
• Published: 08/25/2005
• Est. Priority Date: 02/21/2004
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Datallegro Incorporated (Microsoft Corporation)
• Inventors: Frost, Stuart
• Granted Patent: US 7,818,349 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/278 Data partitioning, e.g. horizontal or vertical partitioning

Abstract

An ultra-shared-nothing parallel database system includes at least one master node and multiple slave nodes. A database consisting of at least one fact table and multiple dimension tables is partitioned and distributed across the slave nodes of the database system so that queries are processed in parallel without requiring the transfer of data between the slave nodes. The fact table and a first dimension table of the database are partitioned across the slave nodes. The other dimension tables of the database are duplicated on each of the slave nodes and at least one of these other dimension tables is partitioned across the slave nodes.

Claims

1. A parallel database system comprising:

a master node;

a plurality of slave nodes;

means for distributing a database across said plurality of slave nodes, the database comprising a fact table and a plurality of dimension tables,

wherein the fact table and a first dimension table are partitioned across said plurality of slave nodes,

wherein all other dimension tables of the database are duplicated on each of said plurality of slave nodes, and

wherein up to a plurality of the other dimension tables having a minimum size are also partitioned across said plurality of slave nodes.

2. The parallel database system according to claim 1, wherein the up to a plurality of the other dimension tables are partitioned by row.

3. The parallel database system according to claim 1, wherein the up to a plurality of the other dimension tables are partitioned by column.

4. The parallel database system according to claim 1, wherein the up to a plurality of the other dimension tables are partitioned both by row and by column.

5. The parallel database system according to claim 1, wherein the fact table and the first dimension table are partitioned by row.

6. The parallel database system according to claim 5, wherein the fact table and the first dimension table are hash partitioned on a common key.

7. The parallel database system according to claim 5, where the fact table and the first dimension table are partitioned by column.

8. The parallel database system according to claim 1, wherein the database tables are partitioned by date across a plurality of said plurality of slave nodes.

9. The parallel database system according to claim 1, wherein said partitioning means automatically partitions and distributes database tables across said plurality of slave nodes.

10. The parallel database system according to claim 1, further comprising means for translating a query against the database into at least one sub-query that is executable by said parallel database system without transferring data between said slave nodes.

11. The parallel database system according to claim 10, further comprising means for caching query results produced by said parallel database system.

12. The parallel database system according to claim 11, wherein said master node includes means for caching query results produced by said master node.

13. The parallel database system according to claim 12, wherein each of said plurality of slave nodes includes means for caching sub-query results produced by the respective slave node.

14. The parallel database system according to claim 1, wherein for each of said plurality of slave nodes, a copy of the dataset stored on the respective slave node is stored on another of said plurality of slave nodes assigned as a failover partner to the respective slave node.

15. The parallel database system according to claim 14, wherein sub-queries to be executed by a failed slave node are executed by the failed slave node'"'"'s failover partner.

16. A method for managing a database within a parallel database system, the database comprising a fact table and a plurality of dimension tables, said method comprising the steps of:

identifying a fact table and a first dimension table within the database;

partitioning the fact table and the first dimension table across a plurality of slave nodes;

duplicating each of the other dimension tables on each of the plurality of slave nodes; and

partitioning up to a plurality of the other dimension tables having a minimum size across the plurality of slave nodes.

17. The method according to claim 16, wherein the up to a plurality of the other dimension tables are partitioned by row.

18. The method according to claim 16, wherein the up to a plurality of the other dimension tables are partitioned by column.

19. The method according to claim 16, wherein the up to a plurality of the other dimension tables are partitioned both by row and by column.

20. The method according to claim 16, wherein the fact table and the first dimension table are partitioned by row.

21. The method according to claim 20, wherein the fact table and the first dimension table are hash partitioned on a common key.

22. The method according to claim 20, wherein the fact table and the first dimension table are also partitioned by column.

23. The method according to claim 16, further comprising partitioning the database tables by date across a plurality of slave nodes.

24. The method according to claim 16, further comprising translating a query against the database into at least one sub-query that is executable by the parallel database system without transferring data between the slave nodes.

25. The method according to claim 24, further comprising caching the results of the query on a master node.

26. The method according to claim 25, further comprising caching the results of the at least one sub-query on the respective slave nodes.

27. The method according to claim 16, further comprising storing a copy of the dataset from a slave node on a failover partner assigned to the slave node.

28. The method according to claim 27, further comprising executing sub-queries to be executed by a failed slave node on the slave node'"'"'s failover partner.

29. Computer executable process steps stored on a computer readable memory medium, said process steps for performing the method described in any one of claims 16 to 28.