Method and mechanism for extending native optimization in a database system

• US 6,738,782 B2
• Filed: 03/11/2002
• Issued: 05/18/2004
• Est. Priority Date: 03/18/1999
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Das, Dinesh, Krishnamurthy, Viswanathan, Murthy, Ravi, Agarwal, Nipun, Srinivasan, Jagannathan, Nori, Anil
• Primary Examiner(s): Mizrahi, Diane D.
• Application Number:
  US10/096,008
• Publication Number:
  US 20030009446A1
• Time in Patent Office: 799 Days
• Field of Search: 707/102, 707/2, 707/3, 707/10, 707/104.1, 707/1
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method and system for optimizing the execution of database statement is described. Optimization of database statements having non-boolean predicates is disclosed. Also disclosed is the determination and use of different granularity levels of information for submitting database statement predicates to optimizer-related functions.

Claims

1. A method of optimizing execution of a database statement, comprising:

receiving a database statement comprising a predicate;

submitting a lower bounding value to an optimizer function, said lower bounding value defining a lower boundary of a range of values for said predicate;

submitting an upper bounding value to said optimizer function, said upper bounding value defining an upper boundary of said range of values for said predicate; and

using said optimizer function to estimate costs of an execution plan for said database statement, said optimizer function being selected from the group consisting of cost estimation function, selectivity function, and statistics collection function.

2. The method of claim 1 further comprising:

submitting information to said optimizer function indicating whether said lower bounding value is included in said range of values.

3. The method of claim 1 further comprising:

submitting information to said optimizer function indicating whether said upper bounding value is included in said range of values.

4. The method of claim 1 in which said optimizer function is a defined optimizer function for non-native objects.

5. The method of claim 1 in which said non-native objects is selected from the group consisting of user-defined index, user-defined index type, user-defined data type, user-defined column, and user-defined package.

6. The method of claim 1 in which said predicate is not a Boolean function.

7. The method of claim 1 in which said predicate is of the form Function( ) Relop Value-Expression.

8. The method of claim 7 in which Relop is selected from <

, <

=, =, >

=, or >

.

9. A method of optimizing execution of a database statement comprising:

receiving a database statement comprising a predicate, wherein the predicate is not a Boolean function;

submitting one or more range values to an optimizer function corresponding to the predicate;

determining an execution plan for the database statement based upon the one or more ranges; and

executing the execution plan.

10. The method of claim 9 in which the one or more range values comprise a lower bounding value for a range.

11. The method of claim 9 in which the one or more range values comprise a higher bounding value for a range.

12. The method of claim 9 in which the one or more range values indicates an inclusion or an exclusion from a range.

13. The method of claim 9 in which said optimizer function is selected from the group consisting of cost estimation function, selectivity function, and statistics collection function.

14. The method of claim 9 in which said predicate is of the form Function( ) Relop Value-Expression.

15. The method of claim 14 in which Relop is selected from <

, <

=, =, >

=, or >

.

16. A method of optimizing execution of a database statement, comprising:

receiving a database statement for execution;

generating a plurality of possible execution plans to execute said database statement;

determining a granularity level of information regarding said database statement to use to estimate costs of said plurality of possible execution plans;

sending said information regarding said database statement at said granularity level to one or more optimizer functions, said one or more optimizer functions being functions selected from the group consisting of statistics functions, selectivity functions, and cost functions; and

estimating said costs for each of said plurality of possible execution plans.

17. The method of claim 16 in which said granularity level is determined based upon characteristics of one or more database entities accessed by said database statement.

18. The method of claim 16 in which said granularity level is determined based upon system conditions at time of execution of said database statement.

19. The method of claim 18 in which said granularity level is determined when said one or more optimizer functions are created.

20. A computer program product that includes a medium readable by a processor, the medium comprising a sequence of instructions which, when executed by said processor, causes said processor to execute a process to optimize execution of a database statement, said process comprising:

receiving a database statement comprising a predicate;

submitting a lower bounding value to an optimizer function, said lower bounding value defining a lower boundary of a range of values for said predicate;

submitting an upper bounding value to said optimizer function, said upper bounding value defining an upper boundary of said range of values for said predicate; and

using said optimizer function to estimate costs of an execution plan for said database statement, said optimizer function being selected from the group consisting of cost estimation function, selectivity function, and statistics collection function.

21. The computer program product of claim 20, wherein said process further comprises:

submitting information to said optimizer function indicating whether said lower bounding value is included in said range of values.

22. The computer program product of claim 20, wherein said process further comprises:

submitting information to said optimizer function indicating whether said upper bounding value is included in said range of values.

23. The computer program product of claim 20 in which said optimizer function is a defined optimizer function for non-native objects.

24. The computer program product of claim 20 in which said non-native objects is selected from the group consisting of user-defined index, user-defined index type, user-defined data type, user-defined column, and user-defined package.

25. The computer program product of claim 20 in which said predicate is not a Boolean function.

26. A computer program product that includes a medium readable by a processor, the medium comprising a sequence of instructions which, when executed by said processor, causes said processor to execute a process to optimize execution of a database statement, said process comprising:

receiving a database statement for execution;

generating a plurality of possible execution plans to execute said database statement;

determining a granularity level of information regarding said database statement to use to estimate costs of said plurality of possible execution plans;

sending said information regarding said database statement at said granularity level to one or more optimizer functions, said one or more optimizer functions being functions selected from the group consisting of statistics functions, selectivity functions, and cost functions; and

estimating said costs for each of said plurality of possible execution plans.

27. The computer program product of claim 26 in which said granularity level is determined based upon characteristics of one or more database entities accessed by said database statement.

28. The computer program product of claim 26 in which said granularity level is determined based upon system conditions at time of execution of said database statement.

29. The computer program product of claim 28 in which said granularity level is determined when said one or more optimizer functions are created.

30. A computer program product that includes a medium readable by a processor, the medium comprising a sequence of instructions which, when executed by said processor, causes said processor to execute a process to optimize execution of a database statement, said process comprising:

receiving a database statement comprising a predicate, wherein the predicate is not a Boolean function;

submitting one or more range values to an optimizer function corresponding to the predicate;

determining an execution plan for the database statement based upon the one or more ranges; and

executing the execution plan.

31. The computer program product of claim 30 in which the one or more range values comprise a lower bounding value for a range.

32. The computer program product of claim 30 in which the one or more range values comprise a higher bounding value for a range.

33. The computer program product of claim 30 in which the one or more range values indicates an inclusion or an exclusion from a range.

34. The computer program product of claim 30 in which said optimizer function is selected from the group consisting of cost estimation function, selectivity function, and statistics collection function.

35. A system for optimizing execution of a database statement, said system comprising:

means for receiving a database statement comprising a predicate;

means for submitting a lower bounding value to an optimizer function, said lower bounding value defining a lower boundary of a range of values for said predicate;

means for submitting an upper bounding value to said optimizer function, said upper bounding value defining an upper boundary of said range of values for said predicate; and

means for using said optimizer function to estimate costs of an execution plan for said database statement, said optimizer function being selected from the group consisting of cost estimation function, selectivity function, and statistics collection function.

36. The system of claim 35 wherein said system further comprises:

means for submitting information to said optimizer function indicating whether said lower bounding value is included in said range of values.

37. The system of claim 35, wherein said system further comprises:

means for submitting information to said optimizer function indicating whether said upper bounding value is included in said range of values.

38. The system of claim 35 in which said optimizer function is a defined optimizer function for non-native objects.

39. The system of claim 35 in which said non-native objects is selected from the group consisting of user-defined index, user-defined index type, user-defined data type, user-defined column, and user-defined package.

40. The system of claim 35 in which said predicate is not a Boolean function.

41. A system for optimizing execution of a database statement, said system comprising:

means for receiving a database statement for execution;

means for generating a plurality of possible execution plans to execute said database statement;

means for determining a granularity level of information regarding said database statement to use to estimate costs of said plurality of possible execution plans;

means for sending said information regarding said database statement at said granularity level to one or more optimizer functions, said one or more optimizer functions being functions selected from the group consisting of statistics functions, selectivity functions, and cost functions; and

means for estimating said costs for each of said plurality of possible execution plans.

42. The system of claim 41 in which said granularity level is determined based upon characteristics of one or more database entities accessed by said database statement.

43. The system of claim 42 in which said granularity level is determined based upon system conditions at time of execution of said database statement.

44. The system of claim 43 in which said granularity level is determined when said one or more optimizer functions are created.

45. A system for optimizing execution of a database statement, said system comprising:

means for receiving a database statement comprising a predicate, wherein the predicate is not a Boolean function;

means for submitting one or more range values to an optimizer function corresponding to the predicate;

means for determining an execution plan for the database statement based upon the one or more ranges; and

means for executing the execution plan.

46. The system of claim 45 in which the one or more range values comprise a lower bounding value for a range.

47. The system of claim 45 in which the one or more range values comprise a higher bounding value for a range.

48. The system of claim 45 in which the one or more range values indicates an inclusion or an exclusion from a range.

49. The system of claim 45 in which said optimizer function is selected from the group consisting of cost estimation function, selectivity function, and statistics collection function.