- Parsing and translation
- Optimization
- Evaluation
- The query is sent to the parser and translator.
- From there a relational-algebra expr is generated which is then used by the optimizer.
- The optimizer uses satistics about data like min max etc to optmize.
- from there the execution plan is made which is given to the eveluation engine to execute.
- The evaluation engine uses the data to execute the query.
- The o/p is sent to user.
The actual data is accessed after the optimal execution plan was generated. This shows that there maybe more than one execution plan and its very imp to choose the optimal plan for execution.
Two mains ways to evaluate a given query:
- Equivalent expressions: from the intital relational algebra expr
- Different algorithms for each operation
In this way we can get more than one way to evaluate the query and choose the one with the lowest cost.
Cost difference between a good and a bad way of evaluating a query can be enormous.
The two methods for selecting the best way query evaluation plans are:
- Cost based optimization
- Rule based optimization : the idea is to use certain order to execute certain operators or to execute certain operators in a certain way.
on the other hand , the cost based optimization method needs to estimate the cost of operations in order to estimate the total cost of executing the plan.
- Depends critically on statistical info about relations which the database must maintain
- need to estimate statistics for intermediate results to compute costs of complex expressions.
Sometimes the cost-based query optimization might provide better accuracy in choosing the optimal plan as compared to the rule based optimization but at the expense of the need to store statical data and to run complex optimization algos that may sometime add significant overhead over the overall query processing time.