USER DEFINED FUNCTION DATABASE PROCESSING

• US 20120130963A1
• Filed: 11/24/2010
• Published: 05/24/2012
• Est. Priority Date: 11/24/2010
• Status: Abandoned Application

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Luo, Congnan, Long, Yu, Reed, Michael Leon, Wang, Guilian, Pampati, Phanibhushan Rao
• Application Number:
  US12/953,995
• Publication Number:
  US 20120130963A1
• Time in Patent Office: Days
• US Class Current: 707/693
• CPC Class Codes: G06F 16/2443 Stored procedures

Abstract

Apparatus, systems, and methods may operate to retrieve multiple rows of a database in response to receiving a request to execute an aggregate user defined function (UDF) over the multiple rows, to sort each of the multiple rows into common groups, grouping together individual ones of the multiple rows that share one of the common groups, and to send UDF execution requests to apply the aggregate UDF to aggregate buffers of the common groups to produce an aggregate result, so that one of the UDF execution requests and one context switch are used to process each of the aggregate buffers used within one of the groups to provide at least one intermediate result that can be processed to form the aggregate result. Other apparatus, systems, and methods are disclosed.

Claims

1. An apparatus, comprising:

a reception module to retrieve multiple rows of a database in response to receiving a request to execute an aggregate user defined function (UDF) over the multiple rows; and

a processor to sort each of the multiple rows into common groups, to group together individual ones of the multiple rows that share one of the common groups, and to send UDF execution requests to apply the aggregate UDF to aggregate buffers of the common groups to produce an aggregate result, so that one of the UDF execution requests and one context switch are used to process each of the aggregate buffers used within one of the groups to provide at least one intermediate result that can be processed to form the aggregate result.

2. The apparatus of claim 1, further comprising:

a display to receive publication of at least a portion of the aggregate result.

3. The apparatus of claim 1, further comprising.

multiple database processors, including the processor, coupled to corresponding storage nodes that each include some of the multiple rows that form a portion of the database.

4. The apparatus of claim 1, further comprising:

a first node to house the reception module comprising a request dispatcher; and

a second node to house the processor comprising an access module processor.

5. The apparatus of claim 1, further comprising:

a user input device comprising a client machine to transmit the request.

6. A processor-implemented method to execute on one or more processors that perform the method, comprising:

retrieving multiple rows of a database in response to receiving a request to execute an aggregate user defined function (UDF) over the multiple rows;

sorting each of the multiple rows into common groups, to group together individual ones of the multiple rows that share one of the common groups; and

sending UDF execution requests to apply the aggregate UDF to aggregate buffers of the common groups to produce an aggregate result, so that one of the UDF execution requests and one context switch are used to process each of the aggregate buffers used within one of the groups to provide at least one intermediate result that can be processed to form the aggregate result.

7. The method of claim 6, wherein receiving the request comprises:

receiving a single structured query language (SQL) request.

8. The method of claim 6, wherein the sorting further comprises:

sorting each of the multiple rows on one or more columns listed in a GROUP BY clause.

9. The method of claim 6, further comprising:

buffering member rows of the common groups to fill the aggregate buffers.

10. The method of claim 6, wherein the sending further comprises:

sending the UDF execution requests to a UDF processor.

11. The method of claim 6, further comprising:

compacting individual ones of the aggregate buffers by replacing repeated input data from individual ones of the common groups with a frequency and common value pair.

12. The method of claim 6, further comprising:

copying input data from one of the aggregate buffers into a shared memory mapping file.

13. The method of claim 12, further comprising:

sending one of the UDF execution requests to execute the aggregate UDF for all the input data in the shared memory mapping file; and

updating the at least one intermediate result as part of the aggregate result.

14. The method of claim 6, further comprising:

returning the aggregate result for each of the common groups in a resultant row.

15. The method of claim 6, wherein the retrieving comprises:

retrieving multiple rows of the database from a base table or a spool table.

16. The method of claim 6, wherein the sorting comprises:

selecting all of the multiple rows from the database comprising an original source table; and

inserting all of the multiple rows into a temporary table having a primary index to order the multiple rows into a desired sort order.

17. The method of claim 16, wherein the selecting and the inserting are accomplished using a single INSERT INTO . . . SELECT SQL statement.

18. A processor-implemented method to execute on one or more processors that perform the method, comprising:

retrieving multiple rows of a database in response to receiving a request to execute a Java aggregate user defined function (UDF) over the multiple rows;

sorting each of the multiple rows into common groups, to group together individual ones of the multiple rows that share one of the common groups;

sending Java UDF execution requests to apply the Java aggregate UDF to aggregate buffers of the common groups to produce an aggregate result, so that one of the Java UDF execution requests and one context switch are used to process each of the aggregate buffers used within one of the groups to provide at least one intermediate result that can be processed to form the aggregate result.

19. The method of claim 18, further comprising:

loading commonly used supporting Java classes and instantiating supporting objects of the supporting Java classes once in response to receiving one of the Java UDF execution requests;

reusing previously instantiated parameter Java class objects for repeated input data in the aggregate buffers; and

instantiating new parameter Java class objects to process non-repeated input data in the aggregate buffers.

20. The method of claim 19, further comprising:

copying the parameter Java class objects to a Java UDF stack in a UDF processor.