Method and mechanism for extending native optimization in a database system

• US 6,370,522 B1
• Filed: 03/18/1999
• Issued: 04/09/2002
• Est. Priority Date: 03/18/1999
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Das, Dinesh, Krishnamurthy, Viswanathan, Murthy, Ravi, Agarwal, Nipun, Srinivasan, Jagannathan, Nori, Anil
• Primary Examiner(s): MIZRAHI, DIANE D
• Application Number:
  US09/272,691
• Time in Patent Office: 1,118 Days
• Field of Search: 707/2, 707/3, 717/4, 709/104
• 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. An aspect is related to providing a framework for the creation, association, and use of optimizer-related functions to calculate the cost of execution plans involving non-native database entities. Examples of these optimizer-related functions include selectivity, statistics, and costs functions. Another aspect is related to the optimization of database statements having non-boolean predicates. Yet another aspect is related to the determination and use of different granularity levels of information for submitting database statement predicates to optimizer-related functions. Optimizer-related functions can also be defined for system-supplied objects and entities.

Claims

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

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein a database table is employed to register an association between said function with said database object, said database table comprising a first field containing a first identifier of said database object and a second field containing a second identifier related to said function.

2. The method of claim 1 in which said database table identifies default cost values.

3. A method for optimizing an execution of a database statement, comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein said function is grouped with one or more optimizer-related functions to form an optimizer object type.

4. The method of claim 3 in which said act of associating said function with said database object comprises the registration of said optimizer object type to said database object.

5. A method for optimizing an execution of a database statement, comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object;

calling said function to obtain a computed cost of said possible execution plan; and

associating said function with a category of database entities, said category of database entities not natively supported by a database system.

6. A method for optimizing an execution of a database statement, comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein said database object is an object selected from the group consisting of user-defined index, user-defined indextype, user-defined data type, user-defined column, and user-defined package.

7. A method for optimizing an execution of a database statement, comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein said function is a statistics collection function.

8. The method of claim 7 further comprising:

calling said statistics collection function to collect statistics for said database object.

9. The method of claim 8 in which said statistics are stored in a raw data format.

10. The method of claim 7 in which said statistics are stored in a database table, said database table comprising a first field that contains said statistics, said database table comprise a second field containing an identifier of said database object.

11. A method for optimizing an execution of a database statement, comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein said function comprises a selectivity function.

12. The method of claim 11 in which said selectivity function accesses collected statistics to compute a selectivity value.

13. A method for optimizing an execution of a database statement, comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein said function comprises a cost estimation function.

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

receiving a database statement for execution, said database statement involving a database object not natively supported by a database system;

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

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; and

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

wherein said one or more optimizer functions are functions selected from the group consisting of statistics functions, selectivity functions, and cost functions.

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

receiving a database statement for execution, said database statement involving a database object not natively supported by a database system;

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; and

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

wherein said granularity level is determined based upon characteristics of one or more database entities accessed by said database statement.

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

receiving a database statement for execution, said database statement involving a database object not natively supported by a database system;

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; and

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

wherein said granularity level is determined based upon system conditions at time of execution of said database statement.

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

receiving a database statement for execution, said database statement involving a database object not natively supported by a database system;

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; and

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

wherein said granularity level is determined when said one or more optimizer functions are created.

18. 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 an execution of a database statement, said process comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein a database table is employed to register an association between said function with said database object, said database table comprising a first field containing a first identifier of said database object and a second field containing a second identifier related to said function.

19. The computer program product of claim 18 in which said database table identifies default cost values.

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 an execution of a database statement, said process comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein said function is grouped with one or more optimizer-related functions to form an optimizer object type.

21. The computer program product of claim 20 in which said act of associating said function with said database object comprises the registration of said optimizer object type to said database object.

22. 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 an execution of a database statement, said process comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object;

calling said function to obtain a computed cost of said possible execution plan; and

associating said function with a category of database entities, said category of database entities not natively supported by a database system.

23. 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 an execution of a database statement, said process comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein said database object is an object selected from the group consisting of user-defined index, user-defined indextype, user-defined data type, user-defined column, and user-defined package.

24. 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 an execution of a database statement, said process comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein said function is a statistics collection function.

25. The computer program product of claim 24 further comprising:

calling said statistics collection function to collect statistics for said database object.

26. The computer program product of claim 25 in which said statistics are stored in a raw data format.

27. The computer program product of claim 24 in which said statistics are stored in a database table, said database table comprising a first field that contains said statistics, said database table comprise a second field containing an identifier of said database object.

28. 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 an execution of a database statement, said process comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein said function comprises a selectivity function.

29. The computer program product of claim 28 in which said selectivity function accesses collected statistics to compute a selectivity value.

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 an execution of a database statement, said process comprising:

generating a function, said function used to compute costs of an execution plan, said execution plan involving a database object not natively supported by a database system;

associating said function with said database object;

receiving a database statement for which a possible execution plan involves said database object; and

calling said function to obtain a computed cost of said possible execution plan;

wherein said function comprises a cost estimation function.

31. 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 an execution of a database statement, said process comprising:

receiving a database statement for execution, said database statement involving a database object not natively supported by a database system;

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; and

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

wherein said one or more optimizer functions are functions selected from the group consisting of statistics functions, selectivity functions, and cost functions.

32. 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 an execution of a database statement, said process comprising:

receiving a database statement for execution, said database statement involving a database object not natively supported by a database system;

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; and

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

wherein said granularity level is determined based upon characteristics of one or more database entities accessed by said database statement.

33. 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 an execution of a database statement, said process comprising:

receiving a database statement for execution, said database statement involving a database object not natively supported by a database system;

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; and

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

wherein said granularity level is determined based upon system conditions at time of execution of said database statement.

34. 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 an execution of a database statement, said process comprising:

receiving a database statement for execution, said database statement involving a database object not natively supported by a database system;

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; and

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

wherein said granularity level is determined when said one or more optimizer functions are created.