Are you an EPFL student looking for a semester project?
Work with us on data science and visualisation projects, and deploy your project as an app on top of Graph Search.
Concept
Query plan
Summary
A query plan (or query execution plan) is a sequence of steps used to access data 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 many 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 option. 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.
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 EXPLAIN PLAN statement.
The Microsoft SQL Server Management Studio tool, which ships with Microsoft SQL Server, for example, shows this graphical plan when executing this two-table join example against an included 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.
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].
Official source
About this result
This page is automatically generated and may contain information that is not correct, complete, up-to-date, or relevant to your search query. The same applies to every other page on this website. Please make sure to verify the information with EPFL's official sources.