Components of Optimizer
There are three components of the optimizers:
- Transformer
- Estimator
- Plan Generator
Components of optimizers
Let’s discuss each one by one:
Transformer: It takes parsed query as input which is represented by set of query blocks. It determines that if it is advantageous to change the form of the query to reduce the cost of execution.
Estimator: It determines the over all cost of execution plan. This estimator uses three different measures to determine cost which includes:
- Selectivity: It is defined as a fraction of rows from a row set.
- Cardinality: It is defined as the number of rows returned by each operation in executed plans.
- Cost: IT defines the estimated resource consumption for a plan.
To estimate cost, optimizer uses following factors:
- System resources (CPU, Memory and I/O)
- Cardinality
- Size of initial data set
Plan Generator: It explores various plans for query block by checking various access paths, join methods and join orders. After checking various paths, optimizer picks the path with the lowest cost.
Advanced Query Optimization in DBMS
We will learn about advanced query optimization in DBMS. We will understand about components of optimizer and methods of query optimization. We will also understand about automatic tuning optimizers.