Partition exchange loading technique for fast addition of data to a data warehousing system

• US 7,020,656 B1
• Filed: 05/23/2002
• Issued: 03/28/2006
• Est. Priority Date: 05/08/2002
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gong, Yu
• Primary Examiner(s): Gaffin, Jeffrey
• Assistant Examiner(s): Wu, Yicun
• Application Number:
  US10/154,751
• Time in Patent Office: 1,405 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/5, 707/6, 707/7, 707/8, 707/9, 707/10, 707/100, 707/101, 707/102, 707/104.1, 707/206, 707/204, 714/6, 709/201
• US Class Current: 1/1
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A method and apparatus is provided for adding data to a database system. A data structure is formed by a plurality of partitions is stored. A first identifier is assigned to an available partition in the data structure. A new set of data is received. The new set of data is re-assigned the first identifier so that the new set of data is identified as one of the plurality of partitions that forms the data structure.

Claims

1. A method for adding data to a database system, the method comprising:

storing a set of existing data in a first data structure of the database;

receiving a new set of data from an external data source;

materializing the new set of data within the database, in a second data structure, wherein the second data structure is separate, and does not form any part of, the first data structure; and

after the new set of data is materialized in the second data structure, inserting the new set of data into the first data structure of the database by causing the second data structure to become part of the first data structure.

2. The method of claim 1, wherein:

said first data structure is formed by a plurality of partitions, wherein a first identifier identifies a particular partition of the plurality of partitions;

wherein the step of inserting the new set of data into the first data structure includes re-assigning the first identifier to identify the second data structure so that the second data structure is identified as one of the plurality of partitions that forms the first data structure.

3. The method of claim 2, wherein the new set of data is materialized in the database before the first identifier is re-assigned to identify the second data structure.

4. The method of claim 3, wherein the first data structure is a table, and the step of materializing the new set of data includes materializing the new set of data as a new table within the database system.

5. The method of claim 4, wherein the step of materializing the new set of data includes materializing the new set of data in an unpartitioned table.

6. The method of claim 5, wherein the step of materializing the new set of data in an unpartitioned table includes performing an insert operation so that the new data is inserted into the unpartitioned table.

7. The method of claim 2, wherein the step of inserting the new set of data into the first data structure further comprises:

assigning a second identifier to the new set of data; and

replacing the second identifier with the first identifier so that the first identifier identifies the second data structure.

8. The method of claim 2, wherein said first data structure is partitioned according to a plurality of partition conditions, and wherein each partition of the plurality of partitions corresponds to a partition condition.

9. The method of claim 8, wherein the first data structure is partitioned according to a plurality of time periods.

10. The method of claim 2, further comprising:

maintaining an index for the first data structure;

generating a new index for the new set of data; and

wherein the step of inserting the new set of data into the first data structure comprises the step of combining the new index with the index of the first data structure.

11. The method of claim 10, wherein

said first data structure is partitioned according to a plurality of partition conditions;

each partition of the plurality of partitions corresponds to a partition condition;

the particular partition is associated with a particular partition condition;

maintaining an index for the first data structure includes maintaining an index that has a branch associated with a particular partition condition; and

combining the new index with the index of the first data structure comprises attaching the new index to the branch associated with the particular partition condition.

12. The method of claim 2, wherein the method further comprises:

identifying a constraint for the particular partition;

before re-assigning the first identifier to identify the second data structure, checking the new set of data to determine if the new set of data satisfies the constraint; and

if the new set of data satisfies the constraint, then re-assigning the first identifier to identify the second data structure.

13. The method of claim 12, wherein the step of re-assigning the first identifier to identify the second data structure is only performed if the new set of data satisfies the constraint.

14. The method of claim 2, wherein the step of re-assigning the first identifier to identify the second data structure includes replacing an existing identifier that currently identifies a partition of the plurality of partitions with a new identifier that identifies the second data structure.

15. The method of claim 14, wherein materializing the new set of data into the second data structure includes materializing the new set of data into a plurality of external partitions that are separate from the first data structure.

16. The method of claim 2, wherein materializing the new set of data into the second data structure comprises:

structuring the second data structure as one or more tables external to the first data structure; and

re-assigning the first identifier that identifies a specific partition, of the plurality of partitions, forming the first data structure, to identify a specific table of the one or more tables external to the first data structure; and

re-assigning, a second identifier that identifies the specific table, to identify the specific partition.

17. The method of claim 2, wherein each of the plurality of partitions, forming the first data structure, are associated with a partition condition that corresponds to a time period.

18. The method of claim 2, wherein the first data structure is a table, and the second data structure is a monolithic table.

19. The method of claim 1, wherein:

said first data structure is formed by a plurality of partitions, the plurality of partitions including one or more available partitions, each partition having a partition condition;

the method includes assigning a first identifier to at least one of the one or more available partitions to identify, to the database system, a particular available partition of the one or more available partitions;

the second data structure is a partition, the second data structure having the same particular partition condition as a first available partition, of the one or more available partitions;

the method includes assigning a second identifier to the second data structure that identifies, to the database system, the second data structure; and

wherein the step of inserting the new set of data into the first data structure includes re-assigning the first identifier to identify, to the database system, the second data structure, so that the second data structure is identified as one of the plurality of partitions that forms the first data structure.

20. The method of claim 19, further comprising re-assigning the second identifier of the external partition to identify the first available partition in conjunction with re-assigning the identifier of the first available partition to identify the external partition.

21. The method of claim 19, further comprising:

identifying a constraint of the first available partition;

after receiving the new set of data, checking the external partition to determine if the external partition satisfies the constraint; and

re-assigning the identifier of the first available partition to identify the external partition if the external partition satisfies the constraint.

22. The method of claim 19, wherein the partition condition for each partition corresponds to a time period.

23. The method of claim 19, further comprising:

maintaining an index for the first data structure;

generating a new index for the external partition; and

combining the new index with the index of the first data structure in conjunction with re-assigning the first identifier to the external partition.

24. The method of claim 23, further comprising arranging the index of the first data structure into a top node and a plurality of branches to the top node, each of the plurality of branches corresponding to one of the partition conditions, each branch having a top node that corresponds to the partition of the partition condition corresponding to that branch.

25. The method of claim 24, further comprising including the new set of data in the first data structure as a partition defined by the partition condition of the first available partition, and wherein combining the index of the external partition with the index of the first data structure includes assigning the index of the external partition to be a new branch to the top node of the index of the first data structure.

26. The method of claim 19, wherein re-assigning the identifier of the first available partition to identify the external partition includes replacing an identifier of the external partition with the identifier of the first available partition.

27. The method of claim 1, further comprising:

generating an index for the set of existing data; and

modifying the index to account for the new set of data in response to inserting the new set of data into the first data structure.

28. The method of claim 27, wherein:

inserting the new set of data into the first data structure of the database includes inserting the new set of data as a replacement partition for another partition in the first data structure;

generating an index for the set of existing data includes arranging the index into a first node and a plurality of branches, each partition in the first data structure corresponding to a branch in the index; and

modifying the index to account for the new set of data includes integrating a new index portion corresponding to the new set of data into the index for the first data structure, the new index portion being attached to a branch of the index that corresponds to the replacement partition.

29. A computer-readable medium carrying instructions for adding data to a database system, the instructions comprising instructions for performing steps of:

storing a first data structure formed by a plurality of partitions, wherein a first identifier identifies a particular partition of the plurality of partitions;

receiving a new set of data into a second data structure that is separate from the first data structure; and

re-assigning the first identifier to the second data structure so that the second data structure is identified as one of the plurality of partitions that forms the first data structure.

30. The computer readable medium of claim 29, further comprising instructions for materializing the new set of data before re-assigning the first identifier to identify the second data structure.

31. The computer readable medium of claim 30, wherein the first data structure is a table, and wherein instructions for materializing the new set of data include instructions for materializing the new set of data as a new table within the database system.

32. The computer readable medium of claim 31, wherein instructions for materializing the new set of data include instructions for materializing the new set of data in an unpartitioned table.

33. The computer readable medium of claim 32, wherein instructions for materializing the new set of data in an unpartitioned table include instructions for performing an insert operation so that the new data is inserted into the unpartitioned table.

34. The computer readable medium of claim 29, further comprising instructions for assigning a second identifier to the new set of data, and wherein instructions for re-assigning the first identifier to the second data structure include instructions for replacing the second identifier with the first identifier so that the first identifier identifies the second data structure.

35. The computer readable medium of claim 29, wherein the first data structure is partitioned according to a plurality of partition conditions, and wherein each partition of the plurality of partitions corresponds to a partition condition.

36. The computer readable medium of claim 35, wherein the first data structure is partitioned according to a plurality of time periods.

37. The computer readable medium of claim 29, further comprising instructions for:

maintaining an index for the first data structure;

generating a new index for the new set of data; and

combining the new index with the index of the first data structure in conjunction with re-assigning the first identifier to identify the second data structure.

38. The computer readable medium of claim 37, wherein:

the first data structure is partitioned according to a plurality of partition conditions, wherein each partition of the plurality of partitions corresponds to a partition condition;

the first identifier is initially associated with a particular partition;

the particular partition is associated with a particular partition condition;

instructions for maintaining an index for the first data structure include instructions for maintaining an index that has a branch associated with a particular partition condition; and

instructions for combining the new index with the index of the first data structure comprises instruction for attaching the new index to the branch associated with the particular partition condition.

39. The computer readable medium of claim 29, wherein the first identifier is initially associated with a particular partition, the computer readable medium further including instructions for performing the steps of:

identifying a constraint for the particular partition;

before re-assigning the first identifier to identify the second data structure, checking the new set of data to determine if the new set of data satisfies the constraint; and

if the new set of data satisfies the constraint, then re-assigning the first identifier to identify the second data structure.

40. The computer readable medium of claim 39, further comprising instructions for re-assigning the first identifier to identify the second data structure only if the new set of data satisfies the constraint.

41. The computer readable medium of claim 29, wherein instructions for storing the first data structure include instructions for associating one or more available partitions with the first data structure, and wherein instructions for re-assigning the first identifier to identify the second data structure includes instructions for replacing an identifier of one or more of the available partitions with an identifier of the second data structure.

42. The computer readable medium of claim 41, wherein instructions for receiving the new set of data into the second data structure include instructions for receiving the new set of data into a plurality of partitions that are external to the data structure, and wherein the computer readable medium further includes instructions for re-assigning an identifier of one or more of the available identifiers to identify each of the external partitions.

43. The computer readable medium of claim 29, wherein instructions for receiving the new set of data into the second data structure include instructions for structuring the second data structure as one or more tables external to the data structure, and wherein the computer readable medium further includes instructions for re-assigning an identifier of one or more available partitions with an identifier of a corresponding one the one or more tables external to the data structure.

44. The computer readable medium of claim 29, wherein the partition condition for the first available partition corresponds to a time period where the new set of data is received by the database system.

45. The computer readable medium of claim 29, wherein the first data structure is a table, and the external partition is a monolithic table.

46. A computer readable medium carrying instructions for adding data to a database system, the instructions including instructions for performing steps of:

storing a first data structure formed by a plurality of partitions, the plurality of partitions including one or more available partitions, each partition, of the plurality of partitions, having a partition condition;

assigning a first identifier to identify at least one of the one or more available partitions;

receiving a new set of data;

storing the new set of data as an external partition to the data structure, wherein the external partition has the same a partition condition as a specific partition of the one or more available partitions that form the data structure;

assigning an second identifier to identify the external partition; and

re-assigning the first identifier of the first available partition to the external partition so that the external partition is identified as one of the plurality of partitions that form the data structure.

47. The computer readable medium of claim 46, further comprising instructions for re-assigning the second identifier of the external partition to identify the first available partition in conjunction with re-assigning the first identifier of the first available partition to identify the external partition.

48. The computer readable medium of claim 46, further comprising instructions for performing the steps of:

identifying a constraint of the specific partition;

after receiving the new set of data, checking the external partition to determine if the external partition satisfies the constraint; and

re-assigning the first identifier of the first available partition to identify the external partition if the external partition satisfies the constraint.

49. The computer readable medium of claim 46, wherein the partition condition for each partition corresponds to a time period.

50. The computer readable medium of claim 46, further comprising instructions for performing the steps of:

maintaining an index for the first data structure;

generating a new index for the external partition; and

combining the new index with the index of the first data structure in conjunction with re-assigning the first identifier to identify the external partition.

51. The computer readable medium of claim 50, further comprising instructions for arranging the index of the first data structure into a top node and a plurality of branches to the top node, each of the plurality of branches corresponding to one of the partition conditions, each branch having a top node that corresponds to the partition of the partition condition corresponding to that branch.

52. The computer readable medium of claim 51, further comprising instructions for including the new set of data in the first data structure as a partition defined by the partition condition of the specific partition, and wherein instructions for combining the index of the external partition with the index of the first data structure includes instructions for assigning the index of the external partition to be a new branch to the top node of the index of the first data structure.

53. The computer readable medium of claim 46, wherein instructions for re-assigning the identifier of the specific partition to the external partition includes instructions for replacing an identifier of the external partition with the identifier of the first available partition.