Question

How to implement Query Optimization module for Relational Database from scratch?

Answer 1

Query Optimization is the important problem in database. SQL is a query language used in RDBMS. The optimizer does not choose the best stratergy. because its too time consuming.

In a query optimization have two methods,

1. physical optimization
2. logical optimization.

The physical optimization is based on the cost of the query. the logical is a rule based optimization.

In a query optimization the complexity is high, the number of alternate statergies increases.

Query processing

Database query scanner   parser   validation  query optimizer   code generator   database processor   result of the query

The query optimization have mainly two tasks. one is to find the best plan and second is reducing time in the executing query plan.

The optimizer process are reliable, predictable, time consumer.

To implement the query optimizer, first step is to must generate expressions equivalent to a given expression.It means that equivalence rule that specify how to transform an expression in to a logically equivalent one.

The equivalent rule says the expressions of two forms are equivalent. we can replace an expression of first form by an expression to the second form.

The query evalution plan is defined what algoritham should be used for each operation and how to execution of the operations should be coordinated. We choose one based on the estimated cost of the plans since the cost is an estimate. the selected is not necessarily the least costly plan,however the estimates are good. The plan is likely to be the least costly one. or not much more costly than it. such optimization is called cost based optimization.