Parallel transfer of data from one or more external sources into a database system

• US 8,606,744 B1
• Filed: 05/31/2002
• Issued: 12/10/2013
• Est. Priority Date: 09/28/2001
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Dageville, Benoit, Brumm, Allen
• Primary Examiner(s): Thangavelu, Kandasamy
• Assistant Examiner(s): OSBORNE, LUKE R
• Application Number:
  US10/159,812
• Time in Patent Office: 4,211 Days
• Field of Search: 707/3, 707/203, 707/1, 707/602, 707/713, 707/607, 707/609, 707/802, 707/756, 707/975, 707/809, 718/105
• US Class Current: 707/602
• CPC Class Codes: G06F 16/2386 Bulk updating operations data conversion details G06F16/258
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems

Abstract

Embodiments of the invention enable data to be moved from one or more external sources into a database system. A plurality of processes are executed concurrently. Each of the processes are executable to move data from at least a portion of the one or more external sources into the database system, so that executing the plurality of processes concurrently moves data from different portions of the one or more external sources into the database system in parallel. Execution of the plurality of processes are coordinated from the database system.

Claims

1. A method for moving data, into a database system that includes a database server and a database, from one or more external sources external to the database, the method comprising:

the database server executing a database command that specifies movement of the data from said one or more external sources into said database, wherein executing the database command comprises;

the database server segmenting the data on the one or more external sources into a first plurality of segments that collectively cover the data specified by the database command, wherein segmenting the data comprises segmenting a single file, that is stored on an external source of the one or more external sources, into a second plurality of segments that is included in the first plurality of segments, wherein the second plurality of segments cover an entirety of the single file, and wherein segmenting the single file is based at least in part on a data format of the single file; and

the database server executing a plurality of processes concurrently, wherein each process of said plurality of processes is assigned to move data from a different segment of said first plurality of segments into the database system.

2. The method of claim 1, further comprising performing one or more operations on the data moved from the one or more external sources into the database system.

3. The method of claim 2, wherein performing one or more operations includes incorporating the data moved from each of the one or more external sources into one or more target tables stored by the database system.

4. The method of claim 2, wherein performing one or more operations includes executing logic to analyze the data once the data is moved from each of the one or more external sources into the database system.

5. The method of claim 4, wherein performing one or more operations includes incorporating at least some of the data from the one or more external sources into one or more tables maintained in the database system after executing the logic to analyze the data.

6. The method of claim 1, wherein segmenting the data on the one or more external sources into the first plurality of segments includes using information specified by a user to determine at least some of the first plurality of segments.

7. The method of claim 1, wherein segmenting the single file based on the data format of the single file includes:

determining if a first external source in the one or more external sources has identifiable delimiters to delimit data within the first external source,

if the first external source has identifiable delimiters, using one or more of the delimiters to segment that external source into multiple segments.

8. The method of claim 1, further comprising:

detecting that at least one process in the plurality of processes completes moving data into the database system;

reassigning at least a portion of the segments assigned to a plurality of processes which have not completed moving data into the database system to the processes which have completed moving data into the database system.

9. The method of claim 8, wherein the reassigning breaks down at least one segment belonging to a first particular process which has not completed moving data into the database system into a plurality of sub-segments and assigns at least one sub-segment to the first particular process and at least another sub-segment to a second particular process which has completed moving data into the database system.

10. The method of claim 1, further comprising the database server coordinating execution of the plurality of processes in order to reduce a time gap between when each of the plurality of processes are completed.

11. The method of claim 1, wherein segmenting the data on the one or more external sources into the first plurality of segments includes using one or more attributes of each of the one or more external sources to segment the data.

12. The method of claim 11, wherein using one or more attributes of each of the one or more external sources to segment the data includes determining at least one of (i) whether each of the external sources allows concurrent access to data stored in that external source, (ii) whether each of the external sources allow random, non-sequential operations to be performed on data in that external source.

13. The method of claim 1 wherein the database command specifies at least one external table.

14. A database system for retrieving data from one or more external sources, the database system comprising:

a memory to store one or more target tables; and

one or more processors configured to;

execute a database command that specifies movement of the data from said one or more external sources into said database system, wherein executing the database command comprises;

segmenting the data on the one or more external sources into a first plurality of segments that collectively cover the data specified by the database command, wherein segmenting the data comprises segmenting a single file, that is stored on an external source of the one or more external sources, into a second plurality of segments that is included in the first plurality of segments, wherein the second plurality of segments cover an entirety of the single file, and wherein segmenting the single file is based at least in part on a data format of the single file; and

executing a plurality of processes concurrently, wherein each process of said plurality of processes is assigned to move data from a different segment of said first plurality of segments into the database system;

incorporate the data moved from the one or more external sources into the one or more target tables.

15. The database system of claim 14, wherein the one or more processors are configured to execute one or more agents residing on the database system in order to read data from the one or more external sources.

16. The database system of claim 14, wherein the one or more processors are configured to communicate with one or more agents residing externally to the database system in order to read data from the one or more external sources.

17. The database system of claim 16, wherein the one or more processors are configured to perform one or more operations on the data moved from the one or more external sources prior to incorporating the data into the one or more target tables.

18. The database system of claim 14, wherein the one or more processors are configured to perform a load balancing technique to reduce a time gap between when each of the plurality of processes competes moving data from the segment assigned to that process.

19. A non-transitory computer readable storage medium storing instructions for moving data, into a database system that includes a database server and a database, from one or more external sources external to the database, the instructions comprising:

the database server executing a database command that specifies to movement of the data from said one or more external sources into said database, wherein executing the database command comprises;

the database server segmenting the data on the one or more external sources into a first plurality of segments that collectively cover the data specified by the database command, wherein segmenting the data comprises segmenting a single file, that is stored on an external source of the one or more external sources, into a second plurality of segments that is included in the first plurality of segments, wherein the second plurality of segments cover an entirety of the single file, and wherein segmenting the single file is based at least in part on a data format of the single file; and

the database server executing a plurality of processes concurrently, wherein each process of said plurality of processes is assigned to move data from a different segment of said first plurality of segments into the database system.

20. The non-transitory computer readable storage medium of claim 19, further comprising instructions for performing one or more operations on the data moved from the one or more external sources into the database system.

21. The non-transitory computer readable storage medium of claim 20, wherein instructions for performing one or more operations include instructions for incorporating the data moved from each of the one or more external sources into one or more target tables stored by the database system.

22. The non-transitory computer readable storage medium of claim 20, wherein instructions for performing one or more operations include instructions for executing logic to analyze the data once the data is moved from each of the one or more external sources into the database system.

23. The non-transitory computer readable storage medium of claim 22, wherein instructions for performing one or more operations include instructions for incorporating at least some of the data from the one or more external sources into one or more tables maintained in the database system after executing the logic to analyze the data.

24. The non-transitory computer readable storage medium of claim 19, wherein instructions for segmenting the data on the one or more external sources into the first plurality of segments includes using information specified by a user to determine at least some of the first plurality of segments.

25. The non-transitory computer readable storage medium of claim 19, wherein instructions for using the data format of the data on the one or more external sources to determine the first plurality of segments includes:

determining if a first external source in the one or more external sources has identifiable delimiters to delimit data within the first external source,

if the first external source has identifiable delimiters, using one or more of the delimiters to segment that external source into multiple segments.

26. The non-transitory computer readable storage medium of claim 19, further comprising instructions for:

detecting that at least one process in the plurality of processes completes moving data into the database system;

reassigning at least a portion of the segments assigned to a plurality of processes which have not completed moving data into the database system to the processes which have completed moving data into the database system.

27. The non-transitory computer readable storage medium of claim 26, wherein the reassigning breaks down at least one segment belonging to a first particular process which has not completed moving data into the database system into a plurality of sub-segments and assigns at least one sub-segment to the first particular process and at least another sub-segment to a second particular process which has completed moving data into the database system.

28. The non-transitory computer readable storage medium of claim 19, further comprising instructions for the database server coordinating execution of the plurality of processes in order to reduce a time gap between when each of the plurality of processes are completed.

29. The non-transitory computer readable storage medium of claim 19, wherein instructions for segmenting the data in the one or more external sources into a first plurality of segments includes using one or more attributes of each of the one or more external sources to segment the data.

30. The non-transitory computer readable storage medium of claim 29, wherein instructions for using one or more attributes of each of the one or more external sources to segment the data include instructions for determining at least one of (i) whether each of the external sources allows concurrent access to data stored in that external source, (ii) whether each of the external sources allow random, non-sequential operations to be performed on data in that external source.

31. The non-transitory computer readable storage medium of claim 19 wherein the database command specifies at least one external table.