Method and system for pipelined database table functions

• US 7,103,590 B1
• Filed: 08/24/2001
• Issued: 09/05/2006
• Est. Priority Date: 08/24/2001
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Murthy, Ravi, Ghosh, Bhaskar, Sethi, Ajay, Yoaz, Adiel M., Thusoo, Ashish, Agrawal, Shashaanka
• Primary Examiner(s): Robinson, Greta L.
• Assistant Examiner(s): Veillard, Jacques
• Application Number:
  US09/938,982
• Time in Patent Office: 1,838 Days
• Field of Search: 707 1- 5, 707/100, 707/103.R, 707/203, 707/206, 707/10, 707/102, 707/104.1, 707/200, 707/7, 707/20, 707/204, 709/203, 709/222, 709/223, 709/224, 709/228, 709/201, 715/513, 715/514, 715/141, 715/530
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24553 of query operations
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A method and mechanism for pipelined table functions is disclosed. With pipelining, data from a first table function does not have to be materialized into a complete collection before it is consumed by a succeeding table function. Instead, a producer of data creates a stream of data that is immediately utilized by a consumer of that data. Also disclosed is a method and mechanism for parallel processing of table functions, in which the set of work operated upon by a table function is sub-divided into smaller portions that are assigned to a plurality of table function slaves. Also disclosed is an integration between pipelining and parallelized execution for table functions.

Claims

1. A method for pipelining a table function in a database system, comprising:

a) performing a set up operation when the table function is called, the table function being a user-defined function that produces rows of data and used in selection, iteration, or aggregation database query language statements;

b) fetching a subset of output data from a data producer;

c) sending the subset of the output data to a first consumer of the output data, wherein the first consumer is the table function;

d) repeating steps b) and c) until all the output data has been fetched from the data producer.

2. The method of claim 1 in which the act of performing a setup operation comprises setting up a context object to maintain state.

3. The method of claim 1 in which the data producer comprises a second table function.

4. The method of claim 1 in which the subset of the output data comprises a single data object or row of data.

5. The method of claim 1 in which the subset of the output data comprises a plurality of data objects or rows of data.

6. The method of claim 1 further comprising:

e) performing a close operation after all the output data has been fetched from the data producer.

7. The method of claim 6 in which the act of performing the close operation comprises garbage collection operations.

8. The method of claim 7 in which the garbage collection operations comprises removal of a context object.

9. The method of claim 1 in which the table function executes in a different execution thread than the data producer.

10. The method of claim 1 in which the table function and the data producer execute from an identical execution thread.

11. The method of claim 1 in which a callback function is passed from the table function.

12. The method of claim 11 in which the callback function is executed on each subset of the output data fetched from the data producer.

13. The method of claim 1 in which the data producer comprises a dynamically configurable return type.

14. The method of claim 13 in which the dynamically configurable return type is established at compile time.

15. The method of claim 1 in which steps a) through d) are implemented within a database query language statement.

16. The method of claim 15 in which the database query language statement comprises SQL.

17. The method of claim 1 in which the subset of the output data is pipelined to a database query language statement.

18. The method of claim 17 in which a callback function is invoked for the subset of the output data.

19. The method of claim 18 in which the callback function filters inappropriate data.

20. The method of claim 1 further comprising:

e) send the subset of the output data to a second consumer of the output data.

21. The method of claim 20 further comprising the step of determining whether the subset of the output data should be routed to the first consumer or the second consumer;

executing step c) if the subset of the output data should be routed to the first consumer;

and

executing step e) if the subset of the output data should be routed to the second consumer.

22. The method of claim 21 in which a partitioning definition is applied to determine whether the subset of the output data should be routed to the first consumer or the second consumer.

23. The method of claim 22 in which the partitioning definition comprises either hash or range based partitioning.

24. The method of claim 1 in which the first consumer processes the subset of the output data in parallel.

25. The method of claim 24 in which multiple slaves exist to process the subset of the output data.

26. The method of claim 25 further comprising the step of determining which of the multiple slaves operate upon the subset of the output data.

27. The method of claim 26 in which a partitioning definition is established to route the subset of the output data to an appropriate one of the multiple slaves.

28. The method of claim 27 in which the partitioning definitions comprises either hash or range based partitioning.

29. The method of claim 1 further comprising:

optimizing a query comprising the table function.

30. The method of claim 29 in which statistics for the table function are passed to an optimizer.

31. The method of claim 29 in which an optimizer self-determines statistics to optimize the query.

32. A system for pipelining table functions, comprising:

a) means for performing a set up operation when the table function is called, the table function being a user-defined function that can produce rows of data and used in selection, iteration, or aggregation database query language statements;

b) means for fetching a subset of output data from the table function;

c) means for sending the subset of the output data to a first consumer of the output data;

d) means for repeating steps b) and c) until all the output data has been fetched from the table function.

33. The system of claim 32 further comprising means for a data producer to produce data to be sent to the table function, in which the data producer comprises a second table function.

34. The system of claim 33 in which the table function executes in the same or in a different execution thread than the data producer.

35. The system of claim 32 in which a callback function is passed from the table function.

36. The system of claim 33 in which the data producer comprises a dynamically configurable return type.

37. The system of claim 32 in which the subset of the output data is pipelined to a database query language statement.

38. A computer program product comprising a storageable computer useable storage medium having executable code to execute a process for pipelining table functions, comprising:

a) executable code for performing a set up operation when the table function is called, the table function being a user-defined function that can produce rows of data and used in selection, iteration, or aggregation database query language statements;

b) executable code for fetching a subset of output data from the table function;

c) executable code for sending the subset of the output data to a first consumer of the output data;

d) executable code for repeating steps b) and c) until all the output data has been fetched from the table function.

39. The computer program product of claim 38 further comprising executable code for a data producer to produce data to be sent to the table function, in which the data producer comprises a second table function.

40. The computer program product of claim 39 in which the executable code for the table function executes in the same or in a different execution thread than the executable code for the data producer.

41. The computer program product of claim 38 further comprising executable code in which a callback function is passed from the table function.

42. The computer program product of claim 39 in which the executable code for the data producer comprises a dynamically configurable return type.

43. The computer program product of claim 38 further comprising executable code in which the subset of the output data is pipelined to a database query language statement.