Processing of Deterministic User-Defined Functions Using Multiple Corresponding Hash Tables

• US 20090204566A1
• Filed: 02/11/2008
• Published: 08/13/2009
• Est. Priority Date: 02/11/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Barsness, Eric Lawrence, Santosuosso, John Matthew, Beuch, Daniel E., Nelsestuen, Paul Stuart, Euler, Theresa Renee
• Granted Patent: US 7,890,480 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2255 Hash tables

Abstract

A deterministic UDF processing mechanism processes user-defined functions (UDFs) using multiple hash tables. Data access patterns for a UDF are collected, and an appropriate hash table set is then determined for the UDF from the data access patterns. If a UDF accesses some similar columns and some disjoint columns, the similar columns are grouped together, and one or more hash tables are allocated to the similar columns. Disjoint columns are allocated their own hash tables. In addition, the allocation of hash tables may be adjusted based on historical access patterns collected over time. By dynamically allocating and adjusting sets of hash tables to a deterministic UDF, the performance of the UDF is greatly increased.

Claims

1. ) An apparatus comprising:

at least one processor;

a memory coupled to the at least one processor;

a deterministic user-defined function (UDF) residing in the memory and executed by the at least one processor, the deterministic UDF accessing at least one database table; and

a deterministic user-defined function processing mechanism residing in the memory and executed by the at least one processor, the deterministic user-defined function processing mechanism collecting historical access patterns that indicate how the deterministic UDF accesses the at least one database table, and allocating a plurality of hash tables to the deterministic UDF based on the historical access patterns.

2. ) The apparatus of claim 1 wherein the deterministic user-defined function processing mechanism dynamically adjusts the allocation of the plurality of hash tables according to the historical access patterns as the historical access patterns change over time.

3. ) The apparatus of claim 1 wherein the deterministic user-defined function processing mechanism allocates the plurality of hash tables by evaluating columns passed to the deterministic UDF, grouping similar columns together and disjoint columns apart, and allocating one of the plurality of hash tables to each group of columns.

4. ) The apparatus of claim 1 wherein the deterministic user-defined function processing mechanism determines from user-specified criteria whether to save the allocation of the plurality of hash tables for use with a plurality of queries.

5. ) The apparatus of claim 4 wherein the user-specified criteria include:

performance characteristics, user option to turn on or off, user-specified conditions, application identifier, user identifier, and custom conditions.

6. ) The apparatus of claim 4 wherein the deterministic user-defined function processing mechanism saves the allocation of the plurality of hash tables for use with the plurality of queries.

7. ) A computer-implemented method for processing a deterministic user-defined function (UDF), the method comprising the steps of:

(A) collecting historical access patterns that indicate how the deterministic UDF accesses at least one database table; and

(B) allocating a plurality of hash tables to the deterministic UDF based on the historical access patterns.

8. ) The method of claim 7 further comprising the step of dynamically adjusting the allocation of the plurality of hash tables according to the historical access patterns as the historical access patterns change over time.

9. ) The method of claim 7 wherein step (B) performs the steps of:

evaluating columns passed to the deterministic UDF;

grouping similar columns together and disjoint columns apart; and

allocating one of the plurality of hash tables to each group of columns.

10. ) The method of claim 7 further comprising the step of determining from user-specified criteria whether to save the allocation of the plurality of hash tables for use with a plurality of queries.

11. ) The method of claim 10 wherein the user-specified criteria includes:

performance characteristics, user option to turn on or off, user-specified conditions, application identifier, user identifier, and custom conditions.

12. ) The method of claim 10 further comprising the step of saving the allocation of the plurality of hash tables for use with the plurality of queries.

13. ) A computer-implemented method for processing a deterministic user-defined function (UDF), the method comprising the steps of:

(A) collecting historical access patterns that indicate how the deterministic UDF accessed a plurality of columns in at least one database table;

(B) evaluating columns passed to the deterministic UDF from the historical access patterns;

(C) grouping from step (B) similar columns together and disjoint columns apart;

(D) allocating one of a plurality of hash tables to each group of columns in step (C);

(E) dynamically adjusting the allocation of the plurality of hash tables in step (D) according to the historical access patterns as the historical access patterns change over time;

(F) determining from user-specified criteria whether to save the allocation of the plurality of hash tables for use with a plurality of queries wherein the user-specified criteria includes performance characteristics, user option to turn on or off, user-specified conditions, application identifier, user identifier, and custom conditions; and

(G) if the user-specified criteria specifies to save the allocation of the plurality of hash tables for use with the plurality of queries, saving the allocation of the plurality of hash tables for use with the plurality of queries.

14. ) An article of manufacture comprising:

a deterministic user-defined function processing mechanism that collects historical access patterns that indicate how a deterministic user-defined function (UDF) accesses at least one database table, and allocates a plurality of hash tables to the deterministic UDF based on the historical access patterns; and

recordable media bearing the deterministic user-defined function processing mechanism.

15. ) The article of manufacture of claim 14 wherein the deterministic user-defined function processing mechanism dynamically adjusts the allocation of the plurality of hash tables according to the historical access patterns as the historical access patterns change over time.

16. ) The article of manufacture of claim 14 wherein the deterministic user-defined function processing mechanism allocates the plurality of hash tables by evaluating columns passed to the deterministic UDF, grouping similar columns together and disjoint columns apart, and allocating one of the plurality of hash tables to each group of columns.

17. ) The article of manufacture of claim 14 wherein the deterministic user-defined function processing mechanism determines from user-specified criteria whether to save the allocation of the plurality of hash tables for use with a plurality of queries.

18. ) The article of manufacture of claim 17 wherein the user-specified criteria includes:

performance characteristics, user option to turn on or off, user-specified conditions, application identifier, user identifier, and custom conditions.

19. ) The article of manufacture of claim 17 wherein the deterministic user-defined function processing mechanism saves the allocation of the plurality of hash tables for use with the plurality of queries.