Query plan
Encyclopedia
A query plan is an ordered set of steps used to access or modify information in a SQL
relational database management system
. This is a specific case of the relational model
concept of access plans.
Since SQL
is declarative
, there are typically a large number of alternative ways to execute a given query, with widely varying performance. When a query
is submitted to the database, the query optimizer
evaluates some of the different, correct possible plans for executing the query and returns what it considers the best alternative. Because query optimizers are imperfect, database users and administrators sometimes need to manually examine and tune the plans produced by the optimizer to get better performance.
.
It indicates that the query engine will do a scan over the primary key index on the Employee table and a matching seek through the primary key index (the ContactID column) on the Contact table to find matching rows. The resulting rows from each side will be shown to a nested loops join operator, sorted, then returned as the result set to the connection.
In order to tune the query, the user must understand the different operators that the database may use, and which ones might be more efficient than others while still providing semantically correct query results.
or changes to existing indexes. It can also show that the database is not properly taking advantage of existing indexes (see query optimizer
).
The query optimizer will not always choose the best query plan for a given query. In some databases the query plan can be reviewed, problems found, and then the query optimizer
given hints
on how to improve it. In other databases alternatives to express the same query (other queries that return the same results) can be tried. Some query tools can generate embedded hints in the query, for use by the optimizer.
Some databases like Oracle provide a Plan table for query tuning. This plan table will return the cost
and time for executing a Query. In Oracle there are 2 optimization techniques:
The RBO is slowly being deprecated. For CBO to be used, all the tables referenced by the query must be analyzed. To analyze a table, a package DBMS_STATS can be made use of.
The others methods for query optimization include:
SQL
SQL is a programming language designed for managing data in relational database management systems ....
relational database management system
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
. This is a specific case of the relational model
Relational model
The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F...
concept of access plans.
Since SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
is declarative
Declarative programming
In computer science, declarative programming is a programming paradigm that expresses the logic of a computation without describing its control flow. Many languages applying this style attempt to minimize or eliminate side effects by describing what the program should accomplish, rather than...
, there are typically a large number of alternative ways to execute a given query, with widely varying performance. When a query
Information retrieval
Information retrieval is the area of study concerned with searching for documents, for information within documents, and for metadata about documents, as well as that of searching structured storage, relational databases, and the World Wide Web...
is submitted to the database, the query optimizer
Query optimizer
The query optimizer is the component of a database management system that attempts to determine the most efficient way to execute a query. The optimizer considers the possible query plans for a given input query, and attempts to determine which of those plans will be the most efficient...
evaluates some of the different, correct possible plans for executing the query and returns what it considers the best alternative. Because query optimizers are imperfect, database users and administrators sometimes need to manually examine and tune the plans produced by the optimizer to get better performance.
Generating query plans
A given database management system may offer one or more mechanisms for returning the plan for a given query. Some packages feature tools which will generate a graphical representation of a query plan. Other tools allow a special mode to be set on the connection to cause the DBMS to return a textual description of the query plan. Another mechanism for retrieving the query plan involves querying a virtual database table after executing the query to be examined. In Oracle, for instance, this can be achieved using the keywords. Explain PlanExplain Plan
An explain plan is a feature provided by the majority of relational databases. When the database engine is provided with an SQL statement it computes the access path used to execute the query...
.
Graphical plans
The SQL Server Management Studio tool which ships with Microsoft SQL Server Microsoft SQL Server Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network... , for example, shows this graphical plan when executing this two-table join against a sample database: SELECT * FROM HumanResources.Employee AS e INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID ORDER BY c.LastName The UI allows exploration of various attributes of the operators involved in the query plan, including the operator type, the number of rows each operator consumes or produces, and the expected cost of each operator's work. |
 |
Textual plans
The textual plan given for the same query in the screenshot is shown here:
StmtText
----
|--Sort(ORDER BY:([c].[LastName] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[ContactID], [Expr1004]) WITH UNORDERED PREFETCH)
|--Clustered Index Scan(OBJECT:([AdventureWorks].[HumanResources].[Employee].[PK_Employee_EmployeeID] AS [e]))
|--Clustered Index Seek(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID] AS [c]),
SEEK:([c].[ContactID]=[AdventureWorks].[HumanResources].[Employee].[ContactID] as [e].[ContactID]) ORDERED FORWARD)
It indicates that the query engine will do a scan over the primary key index on the Employee table and a matching seek through the primary key index (the ContactID column) on the Contact table to find matching rows. The resulting rows from each side will be shown to a nested loops join operator, sorted, then returned as the result set to the connection.
In order to tune the query, the user must understand the different operators that the database may use, and which ones might be more efficient than others while still providing semantically correct query results.
Database tuning
Reviewing the query plan can present opportunities for new indexesIndex (database)
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space...
or changes to existing indexes. It can also show that the database is not properly taking advantage of existing indexes (see query optimizer
Query optimizer
The query optimizer is the component of a database management system that attempts to determine the most efficient way to execute a query. The optimizer considers the possible query plans for a given input query, and attempts to determine which of those plans will be the most efficient...
).
Query tuning
The query optimizer will not always choose the best query plan for a given query. In some databases the query plan can be reviewed, problems found, and then the query optimizer
Query optimizer
The query optimizer is the component of a database management system that attempts to determine the most efficient way to execute a query. The optimizer considers the possible query plans for a given input query, and attempts to determine which of those plans will be the most efficient...
given hints
Hint (SQL)
In database query operations, various SQL implementations use hints as additions to the SQL standard that instruct the database engine on how to execute the query...
on how to improve it. In other databases alternatives to express the same query (other queries that return the same results) can be tried. Some query tools can generate embedded hints in the query, for use by the optimizer.
Some databases like Oracle provide a Plan table for query tuning. This plan table will return the cost
and time for executing a Query. In Oracle there are 2 optimization techniques:
- CBO or Cost Based Optimization
- RBO or Rule Based Optimization
The RBO is slowly being deprecated. For CBO to be used, all the tables referenced by the query must be analyzed. To analyze a table, a package DBMS_STATS can be made use of.
The others methods for query optimization include:
- SQL Trace
- Oracle Trace
- TKPROF