Efficiently updating a key table during outline restructure of a multi-dimensional database

• US 6,629,102 B1
• Filed: 07/28/2000
• Issued: 09/30/2003
• Est. Priority Date: 07/28/2000
• Status: Expired due to Term

• Current Assignee: SAP SE
• Original Assignee: International Business Machines Corporation
• Inventors: Tomlyn, Craig R., Malloy, William E., Poelman, John S.
• Primary Examiner(s): Mizrahi, Diane D.
• Application Number:
  US09/628,597
• Time in Patent Office: 1,159 Days
• Field of Search: 707/1, 707/2, 707/3, 707/10, 707/100, 707/102, 707/101, 707/104.1, 707/103.R
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A technique for efficiently updating a key table during outline restructure of a multi-dimensional database. 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. It is determined that a multi-dimensional database has been restructured. Then, a key table of the relational database is rebuilt to correspond to the restructured multidimensional database.

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:

determining that a multi-dimensional database has been restructured; and

rebuilding a key table of the relational database to correspond to the restructured multidimensional database.

2. The method of claim 1, wherein the key table comprises rows having attributes for block keys and key data information and wherein said rebuilding comprises:

creating a new key table; and

for each row of an old key table,

obtaining one or more block keys and key data information; and

inserting the obtained block keys and key data information as a new row in the new key table.

3. The method of claim 2, further comprising:

determining, for each said obtained one or more block keys, whether the inserting was unsuccessful because of duplication of an existing block key in a row of the new key table by the obtained one or more block keys and, based on the determining that the inserting was unsuccessful, performing the steps of;

obtaining the duplicated row and associated new key data information from the new key table;

aggregating the old key table key data information and the new key data information to obtain an aggregated result;

determining whether the aggregated result matches the new key data information of the row of the new key table; and

updating the row of the new key table with the aggregated key data information based on the determining that the aggregated result matches the new key data information.

4. The method of claim 2, further comprising deleting the old key table.

5. The method of claim 1, wherein the key table comprises rows having attributes for block keys and key data information and wherein said rebuilding comprises:

allocating memory for rows of a new key table; and

for each row of an old key table,

obtaining one or more block keys and key data information; and

inserting the obtained block keys and key data information as a new row in memory.

6. The method of claim 5, wherein the memory comprises data and index memory caches that would otherwise be used by input/output processes that were halted when the database was restructured.

7. The method of claim 5, further comprising:

determining for each obtained block key whether the inserting was unsuccessful because of duplication of an existing block key in a row of the memory by the obtained block key and, based on the determining that the inserting was unsuccessful, performing the steps of;

obtaining the duplicated row and associated new key data information from the memory;

aggregating the old key table key data information and the new key data information to obtain an aggregated result;

determining whether the aggregated result matches the new key data information of the block key in a row of the memory; and

updating the row in memory with the aggregated key data information based on the determining that the aggregated result matches the new key data information.

8. The method of claim 5, wherein each row comprises time attributes and further comprising updating the time attributes in each row in memory.

9. The method of claim 5, further comprising inserting all rows of the memory into a new key table.

10. The method of claim 5, further comprising inserting all rows of the memory into a new key table with a single insert without logging.

11. The method of claim 5, further comprising:

determining that the memory is full;

inserting rows of the memory into a new key table; and

processing remaining rows of the old key table by inserting each row into the new key table.

12. The method of claim 5, further comprising:

determining that the memory is full;

inserting rows of the memory into a new key table; and

processing remaining rows of the old key table by,

inserting each row into a sub-table in memory; and

merging the inserted rows into the new key table.

13. The method of claim 12, wherein merging comprises comparing a row of the sub-table with a row of the new key table.

14. The method of claim 13, wherein block keys of the compared rows match and further comprising:

merging values from the rows; and

updating the new key table with the merged values.

15. The method of claim 13, wherein a block key of the row of the sub-table is smaller than the block key of a row of the new key table and further comprising moving a sub-table cursor to the next row of the sub-table.

16. The method of claim 13, wherein a block key of the row of the new key table is smaller than the block key of a row of the sub-table and further comprising moving a new key table cursor to the next row of the new key table.

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

one or more computer programs, performed by the computer, for determining that a multi-dimensional database has been restructured and rebuilding a key table of the relational database to correspond to the restructured multidimensional database.

18. The apparatus of claim 17, wherein the key table comprises rows having attributes for block keys and key data information and wherein rebuilding comprises:

creating a new key table; and

for reach row of an old key table,

obtaining one or more block keys and key data information; and

inserting the obtained block keys and key data information as a new row in the new key table.

19. The apparatus of claim 18, wherein, the rebuilding further comprises:

determining for each obtained block key whether the inserting was unsuccessful because of duplication of an existing block key in a row of the new key table by the obtained block key and, based on the determining that the inserting was unsuccessful, performing the steps of;

obtaining the duplicated row and associated new key data information from the new key table;

aggregating the old key table key data information and the new key data information to obtain an aggregated result;

determining whether the aggregated result matches the new key data information of the row of the new key table; and

updating the row of the new key table with the aggregated key data information based on the determining that the aggregated result matches the new key data information.

20. The apparatus of claim 18, wherein the rebuilding further comprises deleting the old key table.

21. The apparatus of claim 17, wherein the key table comprises rows having attributes for block keys and key data information and wherein said rebuilding comprises:

allocating memory for rows of a new key table; and

for each row of an old key table,

obtaining one or more block keys and a key data information; and

inserting the obtained block keys and key data information as a new row in memory.

22. The apparatus of claim 21, wherein the memory comprises data and index memory caches that would otherwise be used by input/output processes that were halted when the database was restructured.

23. The apparatus of claim 21, wherein, the rebuilding further comprises:

determining for each obtained block key whether the inserting was unsuccessful because of duplication of an existing block key in a row of the memory by the obtained block key and, based on the determining that the inserting was unsuccessful, performing the steps of;

obtaining the duplicated row and associated new key data information from the memory;

aggregating the old key table key data information and the new key data information to obtain an aggregated result;

determining whether the aggregated result matches the new key data information of the block key in a row of the memory; and

updating the row in memory with the aggregated key data information based on the determining that the aggregated result matches the new key data information.

24. The apparatus of claim 21, wherein each row comprises time attributes and further comprising updating the time attributes in each row in memory.

25. The apparatus of claim 21, wherein the rebuilding further comprises inserting all rows of the memory into a new key table.

26. The apparatus of claim 21, wherein the rebuilding further comprises inserting all rows of the memory into a new key table with a single insert without logging.

27. The apparatus of claim 21, wherein the rebuilding further comprises:

determining that the memory is full;

inserting rows of the memory into a new key table; and

processing remaining rows of the old key table by inserting each row into the new key table.

28. The apparatus of claim 21, wherein the rebuilding further comprises:

determining that the memory is full;

inserting rows of the memory into a new key table; and

processing remaining rows of the old key table by;

inserting each row into a sub-table in memory; and

merging the inserted rows into the new key table.

29. The apparatus of claim 28, wherein merging comprises comparing a row of the sub-table with a row of the new key table.

30. The apparatus of claim 29, wherein block keys of the compared rows match and further comprising:

merging values from the rows; and

updating the new key table with the merged values.

31. The apparatus of claim 29, wherein a block key of the row of the sub-table is smaller than the block key of a row of the new key table and further comprising moving a sub-table cursor to the next row of the sub-table.

32. The apparatus of claim 29, wherein a block key of the row of the new key table is smaller than the block key of a row of the sub-table and further comprising moving a new key table cursor to the next row of the new key table.

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

determining that a multi-dimensional database has been restructured; and

rebuilding a key table of the relational database to correspond to the restructured multidimensional database.

34. The article of manufacture of claim 33, wherein the key table comprises rows having attributes for block keys and key data information and wherein said rebuilding comprises:

creating a new key table; and

for each row of an old key table,

obtaining one or more block keys and key data information; and

inserting the obtained block keys and key data information as a new row in the new key table.

35. The article of manufacture of claim 34, wherein the rebuilding further comprises:

determining for each obtained block key whether the inserting was unsuccessful because of duplication of an existing block key in a row of the new key table by the obtained block key and, based on the determining that the inserting was unsuccessful, performing the steps of;

obtaining the duplicated row and associated new key data information from the new key table;

aggregating the old key table key data information and the new key data information to obtain an aggregated result;

determining whether the aggregated result matches the new key data information of the row of the new key table; and

updating the row of the new key table with the aggregated key data information based on the determining that the aggregated result matches the new key data information.

36. The article of manufacture of claim 34, further comprising deleting the old key table.

37. The article of manufacture of claim 33, wherein the table comprises rows having attributes for a block keys and key data information and wherein rebuilding comprises:

allocating memory for rows of a new key table; and

for each row of an old key table,

obtaining one or more block keys and key data information; and

inserting the obtained block keys and key data information as a new row in memory.

38. The article of manufacture of claim 37, wherein the memory comprises data and index memory caches that would otherwise be used by input/output processes that were halted when the database was restructured.

39. The article of manufacture of claim 37, wherein, the rebuilding further comprises:

determining for each obtained block key whether the inserting was unsuccessful because of duplication of an existing block key in a row of the memory by the obtained block key and, based on the determining that the inserting was unsuccessful, performing the steps of;

obtaining the duplicated row and associated new key data information from the memory;

aggregating the old key table key data information and the new key data information to obtain an aggregated result;

determining whether the aggregated result matches the new key data information of the block key in a row of the memory; and

updating the row in memory with the aggregated key data information based on the determining that the aggregated result matches the new key data information.

40. The article of manufacture of claim 37, wherein each row comprises time attributes and further comprising updating the time attributes in each row in memory.

41. The article of manufacture of claim 37, wherein the rebuilding further comprises inserting all rows of the memory into a new key table.

42. The article of manufacture of claim 37, wherein the rebuilding further comprises inserting all rows of the memory into a new key table with a single insert without logging.

43. The article of manufacture of claim 37, wherein the rebuilding further comprises:

determining that the memory is full;

inserting rows of the memory into a new key table; and

processing remaining rows of the old key table by inserting each row into the new key table.

44. The article of manufacture of claim 37, wherein the rebuilding further comprises:

determining that the memory is full;

inserting rows of the memory into a new key table; and

processing remaining rows of the old key table by;

inserting each row into a sub-table in memory; and

merging the inserted rows into the new key table.

45. The article of manufacture of claim 44, wherein merging comprises comparing a row of the sub-table with a row of the new key table.

46. The article of manufacture of claim 45, wherein block keys of the compared rows match and further comprising:

merging values from the rows; and

updating the new key table with the merged values.

47. The article of manufacture of claim 45, wherein a block key of the row of the sub-table is smaller than the block key of a row of the new key table and further comprising moving a sub-table cursor to the next row of the sub-table.

48. The article of manufacture of claim 45, wherein a block key of the row of the new key table is smaller than the block key of a row of the sub-table and further comprising moving a new key table cursor to the next row of the new key table.