Mumbai University > Information Technology > Sem 3 > Database Management System

Marks: 10M

Year: Dec 2015

• Query optimization is one of the most important tasks of a relational DBMS.
• The Query optimizer generates alternative plans and chooses the best plan with the least estimated cost.
• The query optimizer module is used to find out an execution plan which is the execution strategy to retrieve the result of the query from the database files.
• A query can have many possible execution strategies each with different performances the process of selecting a reasonably efficient execution plan is known s query optimization.

Goals of query optimization:

• Eliminate all unwanted data

  Query Optimization tries to eliminate unwanted tuples, or rows from given relation.

  Eg:

  Select *

  From Employees

  If we require only employee names no need to select all the columns from the table by specifying * is select clause. So, Optimized query will be

  Select Emp_Name

  From Employees

• Speed up Queries

  Query optimization try to find out query which gives results very fast.

• Increase query performance

  Break up the single complex SQL statement into several simple SQL statements which will increase performance of execution of query.

• Select best query plan out of alternative query plan

  Consider following query to “Find all employees having age above 30 and workig for department HR”

  Select e.Emp_Name

  From EMPLOYEE e

  JOIN

  DEPARTMENT d

  ON e.did=d.did

  WHERE e.age>30

  AND d.name=’HR’

  Above query we can have alternative query like

  πe.Emp_Name (σe.Age>25 AND d.name=’HR’(EMPLOYEE e ⋈e.did=d.didDepartment d))

  πe.Emp_Name ((σe.Age>25 (EMPLOYEE e)) ⋈e.did=d.did(σd.dame=’HR’(Department d)))

  Now, the query option is to be selected is identified by query optimizer.