Dynamic generation of optimizer hints

• US 20030236782A1
• Filed: 05/07/2003
• Published: 12/25/2003
• Est. Priority Date: 10/05/1998
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Wong, Daniel Manhung, Hei Lei, Chon
• Granted Patent: US 6,813,617 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99939 Privileged access

Abstract

A fine-grained access control mechanism uses policy functions that are associated with a database object (e.g. table and view). The policy functions are invoked, when, for example, a database server detects that a query is issued against the database object. The policy functions return optimizer hints, which are added to the query.

Claims

1. A method for executing a query issued to a database server by a database client, the method comprising the steps of:

said database server receiving said query;

transparently to said database client and prior to the database server executing said query;

said database server detecting that said query requires access to a database object, and

in response to detecting that said query requires access to said database object;

said database server creating a modified query by selectively adding one or more optimizer hints that indicate how a query optimizer should create an execution plan for executing said query; and

executing, within said database server, said modified query to access data managed by said database server.

2. The method of claim 1, wherein the steps that are performed transparently to said database client and prior to the database server executing said query include:

invoking a function that said database server associates with said database object; and

said function generating the one or more optimizer hints to add.

3. The method of claim 2, wherein:

the method further includes invoking an administrative interface; and

invoking said administrative interface causes said database server to generate data that associates said function with said database object.

4. The method of claim 2, wherein output of said function includes a string representing a predicate to add to the query.

5. The method of claim 2, wherein output of said function does not include a string representing a predicate to add to the query.

6. The method of claim 1, wherein the step of adding one or more optimizer hints includes:

adding a first optimizer hint when detecting a first condition; and

adding a second optimizer hint when detecting a second condition.

7. The method of claim 2, wherein the step of creating a modified query includes:

adding a first predicate when detecting the first condition, and adding a second predicate when detecting the second condition.

8. The method of claim 1, wherein the step of creating a modified query includes:

examining information available only at run-time; and

determining which set of optimizer hints to add based on the said step of examining.

9. The method of claim 1, wherein:

the database server maintains values for a plurality of context attributes associated with a session; and

the database server determines which set of optimizer hints to add based on current values of one or more of said plurality of context attributes.

10. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.

11. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 2.

12. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 3.

13. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 4.

14. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 5.

15. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 6.

16. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 7.

17. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 8.

18. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 9.

19. A method for executing a query issued to a database server by a database client, the method comprising the steps of:

said database server receiving said query;

means for transparently to said database client and prior to the database server executing said query;

said database server detecting that said query requires access to a database object, and

in response to detecting that said query requires access to said database object;

said database server creating a modified query by selectively adding one or more optimizer hints that indicate how a query optimizer should create an execution plan for executing said query; and

executing, within said database server, said modified query to access data managed by said database server.

20. The method of claim 19, wherein the means for transparently to said database client and prior to the database server executing said query include:

invoking a function that said database server associates with said database object; and

said function generating the one or more optimizer hints to add.