Question

With the help of an example SQL query, explain the different steps that are carried out when cost- based optimization is used by the optimization engine. Cleary state any assumption that you make.

Answer 1

Answer)

For the cost-based optimization of the query processing below are the components being used:

1) Parser to check for the syntax and semantics analysis

2) An optimizer which would use the costing method, and the cost based optimizer with internal rules, a rule based optimizer for determining the most efficient way of producing the results of the query

3) It also contains the row source generator that receives the optimal plan from that of the optimizer and the outputs for the execution plan for the SQL statement.

4) The SQL execution engine that leads to operations on the plan as executed and associated with the SQL statement which produces the results of the query.

It can be executed as a full table scan or the indexed scanning or the nested scanning or the Hash joins

The steps involved are as follows:

1) Evaluating the expression and the conditions

2) Statement transformation

3) Choosing the optimizer approaches

4) Choosing the access path

5) Choosing the join orders

6) Choosing the join methods

Example:

Use of subqueries in place of Joins are one such example of a query which helps to achieve cost based optimization

If you found the answer useful, hit like
Hope this answer helps.
Thanks