Advanced Query Optimization in DBMS

Query Optimization is a technique of analyzing and deciding an execution plan that computes the result of the query using less number of resources. The main goal of query optimization is to find an execution plan for that query to reduce the time required to process it.

Two main objectives of Query Optimization are:

• Determine the optimal plan to access the database.
• Reduce the time required to execute the query.

Components of Optimizer

There are three components of the optimizers:

• Transformer
• Estimator
• Plan Generator

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.

Methods Of Query Optimization in DBMS

There are following two methods of Query Optimization in DBMS:

• Cost Based Query Optimization in DBMS
• Adaptive Query Optimization

Cost Based Query Optimization in DBMS

In Cost Based Query Optimization, optimizer associates a numerical value (known as cost) for each step of feasible plan for a given query. Then, all these values are collectively analyzed to get a cost estimate for that plan. After evaluating cost of all feasible plans, optimizer finds the plan with lowest cost estimate.

Adaptive Query Optimization in DBMS

In Adaptive Query Optimization, optimizer is allowed to make run time changes to the execution plans and can find new information to improve the optimizations. It is helpful when existing statistics are not sufficient to generate the plans.

Feature set for Adaptive Query Optimization includes:

Advanced Query Optimization Techniques

Query Explainers: Query explainer tools helps to understand how database query executes. Query explainer helps in understanding and optimizing the query plan.

Example :- In SQL, EXPLAIN is example of query explainer

EXPLAIN SELECT * FROM department WHERE students > 80

Index Optimizations: While creating indexes, it is important to choose suitable index type. This will improve the the performance of query search. It also reduce the time for scanning full table and reduce resource consumption.

Batch Query: Multiple queries are processed in same batch which helps in reducing system overhead by minimizing the number of database connections and queries. It can process multiple operations in single transactions which reduce overhead.

In-Memory Storage: By using in-memory databases can help in improving the speed for read operations for those queries which require low latency. This can be helpful for caching results of frequently executed queries.

Data Denormalization: Data denormalization can be helpful in reducing the need for complex joins. It helps to improve the read performance. It also helps in frequent query execution in the read intensive systems.

Bitmap Index Usage: It is useful when field values have a limited number of different occurrence. It is effective for the queries involving filtering on fields with low cardinality.

Automatic Tuning Optimizers

Optimizers perform different actions based upon how they are invoked.

This includes following two types:

• Normal Optimization: In Normal Optimization, optimizer parses the query and produces an execution plan within a specific time limits.
• SQL Tuning Advisor Optimization: In this, optimizer perform additional analysis to further produce more efficient plan. The output of the optimizer is a series of action along with their expected benefits to improve the plan.