Dynamic generation of optimizer hints

Details Dynamic generation of optimizer hints Dynamic generation of optimizer hints

2003-05-07

2004-11-02

Kindred, Alford (Department: 2172)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000, C707S793000

Reexamination Certificate

active

06813617

ABSTRACT:

FIELD OF THE INVENTION
The present invention relates to database systems.
BACKGROUND OF THE INVENTION
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.
A database server stores data in one or more data containers, each container contains records, and the data within each record is organized into one or more fields. In a database system that stores data in a relational database, the data containers are referred to as tables, the records are referred to as rows, and the attributes are referred to as columns. In object oriented databases, the data containers are referred to as object classes, the records are referred to as objects, and the attributes are referred to as object attributes. Other database architectures may use other terminology.
The present invention is not limited to any particular type of data container or database architecture. However, for the purpose of explanation, the examples and the terminology used herein shall be that typically associated with relational databases. Thus, the ter ms “table”, “row” and “column” shall be used herein to refer respectively to the data container, record, and field.
A DBMS retrieves and manipulates data in response to receiving a database statement. Typically the database statement conforms to a database language, such as Structured Query Language (SQL). A database statement can specify a query operation, a data manipulation operation, or a combination thereof. A database statement that specifies a query operation is referred to herein as a query. The present invention is not limited to database statements that specify a particular type of operation. However, for the purpose of explanation, embodiments of the present invention are illustrated using queries.
One of the most important functions in a database server is to control access to database data. Security mechanisms on database servers control what data may be accessed by a query issued by a user. A very powerful type of security mechanism is referred as a fine-grained access control mechanism. Fine-grained access control allows important capabilities. These include row-level filtering, as described in Database Fine-Grained Access Control (both applications), virtual partitioning of user data in a table as described in Partitioned Access Control To A Database, and controlling access to aggregate information, as described in Enforcing Data Privacy Aggregations.
FINE-GRAINED ACCESS CONTROL MECHANISM
A fine-grained access control mechanism uses one or more 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 function returns a predicate that is appended to the query to generate a modified query. The predicate restricts access to data according to a policy implemented in one or more of the invoked policy functions. In addition, a policy function can also modify context information associated with a user which can affect subsequent database access control. In this way, user access is transparently restricted by transparently modifying queries issued by users to limit access to their data.
Policy functions can be implemented in a variety of ways. According to an embodiment, policy functions are implemented as stored procedures which are associated with a policy for a table or view through an administrative interface. The stored procedures are not native software of the database server, but are user supplied. A system package may be used to define an API through which policy functions may be administered. The database server is designed to interface with the policy functions through the API. A user may register a policy function by invoking a database server procedure for registering the policy functions in a system package.
For convenience of expression, various entities that represent sets of instructions (e.g. functions, queries) are described as performing actions, when in fact, a computer, process, database server, or other executing entity performs those actions in response to executing or interpreting the set of instructions. For example, a function may be described as determining that a condition exists or a query may be described as accessing information. These are just convenient ways of expressing that a computer, process, database server or other executing entity is determining that a condition exists in response to executing a function or is accessing data in response to executing or computing a query.
Despite its power and flexibility, fine-grained access control has some drawbacks. Evaluating a policy function requires a non-negligible amount of work. Because one or more policy functions associated with a database object can be invoked by a database server anytime it detects that a query is issued against the database object, considerable overhead can be added to processing the query.
Furthermore, fine-grained access control complicates or hinders a powerful optimization technique that uses optimizer hints. Optimizer hints are commands that can be added to a database statement to instruct or guide how the query optimizer should execute a query. A query optimizer is a component of a database server that generates an execution plan to execute queries received by the database server. An execution plan defines the steps and operations performed by a database server to process a query. A query optimizer generates execution plans that are optimized for efficiency. When determining what steps to include in an execution plan, and the order in which the steps are performed, a query optimizer accounts for many factors that affect efficiency. These factors include optimizer hints included in the query. For example, an optimizer hint in a query can specify to use a particular index. Based on the fact the query includes the optimizer hint, the query optimizer generates an execution plan that includes a step for scanning the index. Optimizer hints are described in greater detail in Oracle
9
i
Database Performance Guide and Reference, Release 1 (9.0.1), Part Number A87503-02, the contents of which are incorporated herein by reference.
In general, database users add optimizer hints to queries assuming the queries will not be modified by adding predicates. However, under fine-grained access control, this assumption does not hold true. In fact, a user may not be able predict what predicates a fine-grained access control mechanism will add, and may not even be aware that the predicates could be added, and when they may be added. Optimizer hints may be used for queries based on assumptions that are invalid; reliance on such assumptions may in fact worsen execution of a query. Further, because the user is not able to anticipate the predicates to be added, the user is unable to take of advantage of predicates when analyzing a query to determine what hints can be added to more efficiently execute a query.
Based on the foregoing, clearly there is a need for a mechanism that allows policy functions to be evaluated more efficiently and that improves the users ability to take advantage of optimizer hints in environments that use fine-grained access control.


REFERENCES:
patent: 5265221 (1993-11-01), Miller
patent: 5276901 (1994-01-01), Howell et al.
patent: 5428778 (1995-06-01), Brookes
patent: 5751949 (1998-05-01), Thomson et al.
patent: 5765154 (1998-06-01), Horikiri et al.
patent: 5787428 (1998-07-01), Hart
patent: 5815574 (1998-09-01), Fortinsky
patent: 5940818 (1999-08-01), Malloy et al.
patent: 6026388 (2000-02-01), Liddy et al.
patent: 6134549 (2000-10-01), Regnier et al.
patent: 6275824 (2001-08-01), O'Flaherty et al.
patent: 6308273 (2001-10-01), Goertzel et al.
patent: 6363387 (2002-03-01), Ponnekanti et al.
pat

Affiliated with

Also associated with

Rating

Dynamic generation of optimizer hints does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Dynamic generation of optimizer hints, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Dynamic generation of optimizer hints will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3285598