What is query optimization and explain its two forms(DBMS)?

For any given query, there may be a number of different ways to execute it. The process of choosing a suitable one for processing a query is known as query optimization.

Forms

The two forms of query optimization are as follows −

• Heuristic optimization − Here the query execution is refined based on heuristic rules for reordering the individual operations.

• Cost based optimization − the overall cost of executing the query is systematically reduced by estimating the costs of executing several different execution plans.

Example

Select name from customer, account where customer.name=account.name and account.balance>2000;

There are two evaluation plans −

• Π_{customer.name}(σ_{customer.name}=account.name ^ account.balance>2000(customerXaccount)

• Π_{customer.name}(σ_{customer.name}=account.name(customerXσ account.balance>2000(account)

Cost evaluator evaluates the cost of different evaluation plans and chooses the evaluation plan with lowest cost. Disk access time, CPU time, number of operations, number of tuples, size of tuples are considered for cost calculations.

Heuristic approach is also called rule-based optimization. There are three ways for transforming relational-algebra queries are −

• Perform the SELECTION process foremost in the query. This should be the first action for any SQL table. By doing so, we can decrease the number of records required in the query, rather than using all the tables during the query.

• Perform all the projection as soon as achievable in the query. Somewhat like a selection but this method helps in decreasing the number of columns in the query.

• Perform the most restrictive joins and selection operations. What this means is that select only those set of tables and/or views which will result in a relatively lesser number of records and are extremely necessary in the query. Obviously any query will execute better when tables with few records are joined.

Hence we see throughout these approaches, our motive is cost optimization only.