Query plan

In an evaluation plan (English: query evaluation plan ( QEP ), short query plan), also called execution plan, it is a description, in which individual steps, a relational database management system performs a database query and in what order. It is generated by the query optimizer of the database management system when a database query is made.

Query Processing

A database query is sent to a database language such as SQL. Such is declaratively in a relational database system, that is, it is specified what is to be generated as a result set and not know how to access the data in detail is done. The query is not from the physical storage structure of the database -dependent ( data independence ); the physical data access accomplished the database management system ( DBMS). In general, are added to a declarative query several ways in which the physical data access can be made, of which the DBMS must choose one. Different strategies in data access generally differ in their efficiency. The component " query optimizer " of the DBMS is responsible for identifying the most efficient way; they generated for several possible evaluation plans - a description, in which individual steps and the order in which individual operations are performed - and tries to select the most efficient of them for the execution of the query. The thus selected evaluation plan can then be compiled or can be performed at interactive inquiries directly by means of an interpreter.

Since it is not possible for the query optimizer generally to identify easily the efficient evaluation of the potential schedules, the query optimizer uses heuristics for the selection of an analysis plan. Software developers and database administrators responsible for the task of interpreting the chosen by the query optimizer evaluation plan upon the occurrence of performance problems in query processing and to assess its efficiency, because often, the query optimizer does not choose the most efficient way to query processing or evaluation plan reveals the lack of appropriate access structures, which the DBMS can use when accessing data. The software developer or database administrator can then, if necessary, the query optimizer supply not only the query to be processed further information, which influences the generation of an evaluation plan, he can formulate the query differently, or database for further access structures, for example, a database index, supplement.

Example

Be a CUSTOMER database table with the column (attribute ) CUSTOMER NUMBER, which acts as the primary key and using an index ( KUNDEN_PK ) is referenced, and the column ANZAHL_KAEUFE. Now following query will set:

Select ANZAHL_KAEUFE 1 from CUSTOMER where CUSTOMER NUMBER = 4 Based on this, the query optimizer of a DBMS following evaluation plan could generate:

-------------------------------------------------- ------------------------------- | Id | Operation | Options | Object | Rows | Access Predicates | -------------------------------------------------- ------------------------------- | 0 | SELECT STATEMENT | | | 1 | | | 1 | TABLE ACCESS | BY INDEX ROWID | CLIENTS | 1 | | | 2 | INDEX | UNIQUE SCAN | KUNDEN_PK | 1 | CUSTOMER NUMBER = 4 | -------------------------------------------------- ------------------------------- In this evaluation plan can be seen, for example, the following:

• Generating the result of the query proceeds by performing three operations (column " operation "); the indentation of an operation means that this operation is a child operation the supernatant operation and that the result of this operation is the input for his father operation. The operations in detail are:

• First, the operation is carried out with ID = 2; this accesses the index to KUNDEN_PK and returns the address of data record ( the so-called ROWID ) to the Father operation with id = 1 In this operation, the predicate CUSTOMER NUMBER = 4, the request will be evaluated (column " Access Predicates " )
• After the operation is performed with id = 1. It is accessed using the ROWID directly on this record in the table CUSTOMER; this is now supplied to the operation with the id = 0
• Finally, the operation engages with Id = 0 the value of the column ANZAHL_KAEUFE from the input data set and forms the sum of the value of the column ANZAHL_KAEUFE and the number 1 The sum is returned as a result to the caller of the above query.

• The estimated number of rows that will be generated by the respective operation is always 1 (column " Rows" ). The query optimizer collects statistical information in productive operation, the aid of which can make estimates on the size of ( intermediate) results.

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