Representing user-defined routines with defined data structures

• US 8,332,373 B1
• Filed: 12/18/2002
• Issued: 12/11/2012
• Est. Priority Date: 12/18/2002
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Milby, Gregory H.
• Primary Examiner(s): Ly, Anh
• Application Number:
  US10/324,267
• Time in Patent Office: 3,646 Days
• Field of Search: 707 2- 4, 707200-201, 707/1, 707/10, 707100-1041, 707/8, 707/702, 707/713, 707/791, 707/718, 707/E17.017, 707/E17.131, 707/999.002
• US Class Current: 707/702
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/245 Query processing

Abstract

A database system constructs representation for predicate expressions invoking user-defined routines. A first representation is constructed if an indicator has a first value, and a second representation is constructed if the indicator has a second value. Based on which representation is used, an optimizer in the database system selects use of indexing and/or statistics and/or other performance-enhancement features in generating a query plan.

Claims

1. A method for use in a database system having at least one processor, comprising:

creating a user-defined routine in the database system;

selectively setting an indicator to have one of plural values;

representing a predicate expression invoking the user-defined routine with one of plural types of data structures based on the value of the indicator; and

selecting, by an optimizer executed on at least one processor in the database system, whether or not to use indexing and statistics in a query plan for a query containing the predicate expression based on determining which of the plural types of data structures is used to represent the predicate expression invoking the user-defined routine.

2. The method of claim 1, further comprising categorizing the predicate expression as one of plural operator types in response to determining that the predicate expression is represented with a first one of the plural types of data structures.

3. The method of claim 2, wherein categorizing the predicate expression as one of plural operator types comprises categorizing the predicate expression as one of an explicit term type, a range term type, a bind term type, and a cross term type.

4. The method of claim 3, wherein representing the predicate expression with the first type data structure comprises representing the predicate expression with an operator-based parse tree.

5. The method of claim 3, further comprising categorizing the predicate expression as a miscellaneous type in response to determining that the predicate expression is represented as a second one of the plural types of data structures.

6. The method of claim 1, wherein the optimizer selects the use of at least one of indexing and statistics based on the predicate expression being represented as a first one of the plural types of data structures.

7. The method of claim 6, wherein the optimizer does not select the use of indexing and statistics based on the predicate expression being represented as a second one of the plural types of data structures.

8. The method of claim 6, further comprising categorizing the predicate expression that is represented by the first type of data structure as one of plural operator types,

wherein the optimizer selects the use of at least one of indexing and statistics based on the categorizing.

9. The method of claim 1, further comprising representing the predicate expression with an operator-based parse tree structure if the indicator has a first value, and representing the predicate expression with a function-based parse tree structure if the indicator has a second value, the operator-based parse tree structure and function-based parse tree structure being two of the plural types of data structures.

10. The method of claim 9, further comprising receiving a statement to create the user-defined routine, wherein the statement contains the indicator and an associated value assigned to the indicator.

11. The method of claim 9, further comprising receiving a statement to set the value of the indicator for the user-defined routine.

12. The method of claim 9, further comprising storing the indicator in a data dictionary.

13. The method of claim 9, further comprising storing the indicator in a data dictionary table associated with the user-defined routine.

14. The method of claim 1, wherein selectively setting the indicator to have one of plural values is performed using a data definition language (DDL) statement.

15. The method of claim 1, further comprising outputting, by the at least one processor, a result produced by executing the query.

16. An article comprising at least one machine-readable storage medium containing instructions that when executed cause at least one processor in a database system to:

receive a database query containing a predicate expression that invokes a user-defined routine;

receive a statement that sets the indicator to one of a first value and a second value;

construct a first representation of the predicate expression in response to the indicator having the first value; and

construct a second, different representation of the predicate expression in response to the indicator having the second value.

17. The article of claim 16, wherein the instructions when executed cause the database system to retrieve a value of the indicator from a data dictionary table.

18. The article of claim 16, wherein receiving the statement comprises receiving a CREATE METHOD statement to set the indicator to one of the first and second values.

19. The article of claim 16, wherein receiving the statement comprises receiving a CREATE FUNCTION statement to set the indicator to one of the first and second values.

20. The article of claim 16, wherein constructing the first representation comprises constructing a function-based parse tree.

21. The article of claim 20, wherein constructing the second representation comprises constructing an operator-based parse tree.

22. The article of claim 16, wherein the instructions when executed cause the database system to further use at least one of indexing and statistics in generating a query plan for the database query in response to categorization of the predicate expression that is represented by the second representation.

23. The article of claim 22, wherein the instructions when executed cause the database system to not use indexing and statistics in generating the query plan in response to categorization of the predicate expression that is represented by the first representation.

24. The article of claim 16, wherein the instructions when executed cause the at least one processor to further output a result produced by execution of the database query.

25. A database system, comprising:

a storage to store an indicator; and

a controller including at least one processor to receive a database query containing a predicate expression that invokes a user-defined routine, and to receive a statement to set a value of the indicator,

the controller to construct a first representation of the predicate expression in response to the indicator having a first value, and

the controller to construct a second representation of the predicate expression in response to the indicator having a second value.

26. The database system of claim 25, the storage to store a data dictionary containing the indicator.

27. The database system of claim 26, wherein the user-defined routine comprises a user-defined method, wherein the data dictionary comprises a data dictionary table for the user-defined method.

28. The database system of claim 26, wherein the user-defined routine comprises a user-defined function, and the data dictionary comprises the data dictionary table for the user-defined function.

29. The database system of claim 25, wherein the statement comprises a data definition language (DDL) statement to assign the value of the indicator.

30. The database system of claim 29, wherein the DDL statement comprises one of a CREATE METHOD statement and a CREATE FUNCTION statement.

31. The database system of claim 25, wherein the first representation comprises an operator-based parse tree, and the second representation comprises a function-based parse tree.

32. The database system of claim 25, the controller to use at least one of indexing and statistics in response to categorization of the predicate expression that is represented by the first representation but to not use indexing and statistics in response to categorization of the predicate expression that is represented by the second representation.

33. The database system of claim 25, wherein the controller comprises a parser.

34. The database system of claim 33, wherein the parser comprises an optimizer.

35. The database system of claim 25, wherein the controller is configured to further output a result produced by execution of the database query.