Optimized Query Execution Plan

Optimized query execution plan is the execution plan prepared by DBMS for a user query. Its steps might differ from the steps in user query, but it will deliver the same result efficiently.

untitled

In any Query Language, a user can specify a task in multiple ways. An inexperienced user may write a query, which is not the best best option in terms of the Query language. Here the DBMS will try to optimize the syntax by analyzing semantic equivalence.

Further, if the Query is accessing multiple database objects, they can be accessed in different orders. Also there may be multiple ways of reading each object. There many be N ways for getting the result for a requested query.

Every DBMS must have a plan optimization module, that selects the data access options  and combines into a set of steps called a single execution plan. This plan is sent to execution engine and file system.

The scope for optimization depends on the attributes of the entities and data distribution. Consider a simple SQL query –

Can be modified to