Evaluation plan

from Wikipedia, the free encyclopedia

In an evaluation plan (ger .: query evaluation plan (QEP), short query plan ), and implementation plan called, is a description in which individual steps a relational database management system , a database query executes, and in which order. It is generated by the query optimizer of the database management system when a database query is made.

Inquiry processing

A database query is made using a database language such as SQL . In a relational database system , this is declarative , that is, it specifies what is to be generated as the result set and not how the data is accessed in detail. The query is therefore not dependent on the physical memory structure of the database ( data independence ); the database management system (DBMS) handles the physical data access. In general, for a declarative query, there are several options for physical data access, one of which the DBMS must choose. Different data access strategies generally differ in their efficiency. The query optimizer component of the DBMS is responsible for identifying the most efficient option; it generates several possible evaluation plans for this - a description of the individual steps and in which order individual operations are carried out - and tries to select the most efficient of these for the execution of the query. The evaluation plan selected in this way can then be compiled or, in the case of interactive queries, can be executed directly using an interpreter .

Since it is generally not possible for the query optimizer to identify the most efficient of the possible evaluation plans without great effort, the query optimizer works with heuristics when selecting an evaluation plan. Software developers and database administrators are responsible for interpreting the evaluation plan chosen by the query optimizer and assessing its efficiency if performance problems occur during query processing, because the query optimizer often does not decide on the most efficient way of query processing or the evaluation plan reveals the lack of suitable ones Access structures that the DBMS can use when accessing data. The software developer or the database administrator can then, if necessary, provide the query optimizer with further information in addition to the query to be processed, which influences the generation of an evaluation plan, he can formulate the query differently or supplement the database with further access structures, for example a database index .

example

Let KUNDEN be a database table with the column ( attribute ) CUSTOMER NUMBER, which acts as a primary key and is referenced by means of an index (KUNDEN_PK), and the column ANZAHL_KAEUFE. The following question is now made: 

select ANZAHL_KAEUFE+1
from   KUNDEN
where  KUNDENNUMMER = 4

On the basis of this, the query optimizer of a DBMS could generate the following evaluation plan: 

---------------------------------------------------------------------------------
| Id | Operation        | Options        | Object    | Rows | Access Predicates |
---------------------------------------------------------------------------------
| 0  | SELECT STATEMENT |                |           | 1    |                   |
| 1  |   TABLE ACCESS   | BY INDEX ROWID | KUNDEN    | 1    |                   |
| 2  |     INDEX        | UNIQUE SCAN    | KUNDEN_PK | 1    | KUNDENNUMMER = 4  |
---------------------------------------------------------------------------------

For example, the following can be read from this evaluation plan:

  • The result of the query is generated by performing three different operations ("Operation" column); the indentation of an operation means that this operation is a child operation of the operation above and that the result of that operation is the input for its parent operation. The operations in detail are:
  • First the operation with Id = 2 is carried out; this accesses the index KUNDEN_PK and delivers the address of the data record sought (the so-called ROWID) to the parent operation with ID = 1. With this operation the predicate CUSTOMER NUMBER = 4 of the request is evaluated (column "Access Predicates")
  • The operation is then carried out with Id = 1. The ROWID is used to directly access the data record in the KUNDEN table; this is now sent to the operation with ID = 0
  • Finally, the operation with ID = 0 picks up the value of the ANZAHL_KAEUFE column from the input data record and forms the sum of the value of the ANZAHL_KAEUFE column and the number 1. The sum is returned as the result to the caller of the above query.
  • The estimated number of rows generated by each operation is 1 ("Rows" column). The query optimizer collects statistical information during productive operation, which can be used to make estimates of the size of (interim) results.

An evaluation plan can contain even more information than in the example above, with which the efficiency of the evaluation plan can be analyzed in detail. There are also programs that can generate such an evaluation plan in a graphical representation instead of a textual one.

See also

Notes and individual references

  1. ^ Alfons Kemper, André Eickler: Database systems. Oldenbourg Verlag 2004, ISBN 3-486-25706-4 , page 229
  2. ↑ This part of an evaluation plan was generated by the DBMS of the XE edition of an Oracle database , version 10.2.0.1.0