Performance of table insertion by using multiple tables or multiple threads

• US 6,665,682 B1
• Filed: 07/19/1999
• Issued: 12/16/2003
• Est. Priority Date: 07/19/1999
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Robinson, Gary, Malloy, William Earl, DeKimpe, Daniel Martin, Moore, Simon Edward
• Primary Examiner(s): Vu, Kim
• Assistant Examiner(s): Nguyen, Tam V
• Application Number:
  US09/356,647
• Time in Patent Office: 1,611 Days
• Field of Search: 707/100, 707/101, 707/3, 707/2, 707/102, 707/1, 707/4
• US Class Current: 1/1
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method, apparatus, and article of manufacture for improved performance of table insertion by using multiple tables or multiple threads. A command is executed in a computer to perform a database operation on a relational database stored on a data store connected to the computer. A multi-dimensional database is represented as a relational schema in the relational database, wherein the relational schema includes one or more base tables, related dimension tables, and a key table. Each of the base tables and the key table is accessed concurrently to perform the database operation.

Claims

1. A method of executing a command in a computer to perform a database operation on a relational database stored on a data store connected to the computer, the method comprising the steps of:

representing a multi-dimensional database as a relational schema in the relational database, wherein the relational schema includes one or more base tables, related dimension tables, and a key table; and

concurrently accessing each of the base tables and the key table to perform the database operation.

2. The method of claim 1, wherein the multi-dimensional database has one or more dimensions having one or more members, with each value in the multi-dimensional database being identified by the intersection of one member from each dimension.

3. The method of claim 2, wherein the relational schema has a dimension table corresponding to each dimension of the multi-dimensional database.

4. The method of claim 1, further comprising the step of concurrently accessing each of the base tables and the key table with an independent thread.

5. The method of claim 4, wherein each thread maintains a separate database connection.

6. The method of claim 5, wherein the step of concurrently accessing further comprises the step of writing data into the base tables and the key table.

7. The method of claim 1, further comprising the step of reading data from one of the base tables and the key table using a single thread.

8. The method of claim 1, further comprising the step of creating a view of the multiple base tables so that they appear as a single base table.

9. The method of claim 1, further comprising the step of identifying which data goes into which table using a hashing function based on a set of sparse dimension identifiers that identify dimensions in the multi-dimensional database.

10. The method of claim 1, further comprising the step of:

when the set of sparse dimensions of the multi-dimensional database change, modifying the relational schema; and

redistributing the data according to the changed set of sparse dimensions.

11. The method of claim 10, wherein the base tables are old base tables, and wherein the step of redistributing data further comprising-the steps of:

creating new base tables; and

moving data from the old base tables into the new base tables using independent threads that use an insert statement with a subselect clause to minimize data movement.

12. The method of claim 11, further comprising the step of creating the new base tables while eliminating logging on an initial transaction.

13. The method claim 1, wherein data is stored in a cache and further comprising the steps of:

determining that a predetermined amount of data is stored in the cache; and

copying that predetermined amount of data from the cache into the relational database.

14. The method of claim 1, wherein data is stored in a cache and further comprising the steps of:

receiving an indication to commit data;

copying the data from the cache into the relational database; and

committing the data in the relational database.

15. The method of claim 1, wherein the key table stores information about a data block in the multi-dimensional database.

16. An apparatus for executing a command in a computer, comprising:

a computer having a data store coupled thereto, wherein the data store stores a relational database; and

one or more computer programs, performed by the computer, the one or more computer programs representing a multi-dimensional database as a relational schema in the relational database, wherein the relational schema includes one or more base tables, related dimension tables, and a key table, and the one or more computer programs further concurrently accessing each of the base tables and the key table to perform a database operation.

17. The apparatus of claim 16, wherein the multi-dimensional database has one or more dimensions having one or more members, with each value in the multi-dimensional database being identified by the intersection of one member from each dimension.

18. The apparatus of claim 17, wherein the relational schema has a dimension table corresponding to each dimension of the multi-dimensional database.

19. The apparatus of claim 16, further comprising means for concurrently accessing each of the base tables and the key table with an independent thread.

20. The apparatus of claim 19, wherein each thread maintains a separate database connection.

21. The apparatus of claim 20, wherein the step of concurrently accessing further comprises the step of writing data into the base tables and the key table.

22. The apparatus of claim 16, further comprising means for reading data from one of the base tables and the key table using a single thread.

23. The apparatus of claim 16, further comprising means for creating a view of the multiple base tables so that they appear as a single base table.

24. The apparatus of claim 16, further comprising means for identifying which data goes into which base table using a hashing function based on a set of sparse dimension identifiers that identify dimensions in the multi-dimensional database.

25. The apparatus of claim 16, further comprising:

when the set of sparse dimensions of the multi-dimensional database change, means for modifying the relational schema; and

means for redistributing the data according to the changed set of sparse dimensions.

26. The apparatus of claim 25, wherein the base tables are old base tables, and wherein means for redistributing data further comprises:

means for creating new base tables; and

means for moving data from the old base tables into the new base tables using independent threads that use an insert statement with a subselect clause to minimize data movement.

27. The apparatus of claim 26, further comprising means for creating the new base tables while eliminating logging on an initial transaction.

28. The apparatus of claim 16, wherein data is stored in a cache and further comprising:

means for determining that a predetermined amount of data is stored in the cache; and

means for copying that predetermined amount of data from the cache into the relational database.

29. The apparatus of claim 16, wherein data is stored in a cache and further comprising:

means for receiving an indication to commit data;

means for copying the data from the cache into the relational database; and

means for committing the data in the relational database.

30. The apparatus of claim 16, wherein the key table stores information about a data block in the multi-dimensional database.

31. An article of manufacture comprising a program storage medium readable by a computer and embodying one or more instructions executable by the computer to perform method steps for executing a command to perform a database operation on a relational database stored on a data store connected to the computer, the method comprising the steps of:

representing a multi-dimensional database as a relational schema in the relational database, wherein the relational schema includes one or more base tables, related dimension tables, and a key table; and

concurrently accessing each of the base tables and the key table to perform the database operation.

32. The article of manufacture of claim 31, wherein the multi-dimensional database has one or more dimensions having one or more members, with each value in the multi-dimensional database being identified by the intersection of one member from each dimension.

33. The article of manufacture of claim 32, wherein the relational schema has a dimension table corresponding to each dimension of the multi-dimensional database.

34. The article of manufacture of claim 31, further comprising the step of concurrently accessing each of the base tables and the key table with an independent thread.

35. The article of manufacture of claim 34, wherein each thread maintains a separate database connection.

36. The article of manufacture of claim 35, wherein the step of concurrently accessing further comprises the step of writing data into the base tables and the key table.

37. The article of manufacture of claim 31, further comprising the step of reading data from one of the base tables and the key table using a single thread.

38. The article of manufacture of claim 31, further comprising the step of creating a view of the multiple base tables so that they appear as a single base table.

39. The article of manufacture of claim 31, further comprising the step of identifying which data goes into which base table using a hashing function based on a set of sparse dimension identifiers that identify dimensions in the multi-dimensional database.

40. The article of manufacture of claim 31, further comprising the step of:

when the set of sparse dimensions of the multi-dimensional database change, modifying the relational schema; and

redistributing the data according to the changed set of sparse dimensions.

41. The article of manufacture of claim 40, wherein the base tables are old base tables, and wherein the step of redistributing data further comprising the steps of:

creating new base tables; and

moving data from the old base tables into the new base tables using independent threads that use an insert statement with a subselect clause to minimize data movement.

42. The article of manufacture of claim 41, further comprising the step of creating the new base tables while eliminating logging on an initial transaction.

43. The article of manufacture of claim 31, wherein data is stored in a cache and further comprising the steps of:

determining that a predetermined amount of data is stored in the cache; and

copying that predetermined amount of data from the cache into the relational database.

44. The article of manufacture of claim 31, wherein data is stored in a cache and further comprising the steps of:

receiving an indication to commit data;

copying the data from the cache into the relational database; and

committing the data in the relational database.

45. The article of manufacture of claim 31, wherein the key table stores information about a data lock in the multi-dimensional database.