Query Optimization

What is query optimization?

what

Query Optimization is a crucial aspect of database management systems (DBMS) that seeks to determine the most efficient way to execute a given query by considering a variety of query execution strategies. The goal is to minimize the system resources required to fulfill the query and increase the speed of returned results.

At its core, Query Optimization involves the evaluation of different query plans and choosing the one with the least estimated cost.

Key features of Query Optimization include:

Query Plan ?

really

A query plan, also known as an execution plan, is a detailed strategy that a database management system (DBMS) creates to execute a query. It outlines the steps and operations the DBMS will take to retrieve the desired data from the database efficiently.

MaterialisedViews

A materialized view is a duplicate data table created by combining data from multiple existing tables for faster data retrieval.

materilaize_view

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:

Components of query optimization

There are three components of optimizer :

Transformer

A Query Transformer is a part of the query optimization process in a database management system (DBMS) that takes a parsed query and attempts to rewrite it in a way that potentially reduces the cost of executing the query.

The parsed query is typically represented as a set of query blocks, which are logical units of the query, such as SELECT statements, subqueries, and clauses (e.g., WHERE, GROUP BY).

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:

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 two methods of Query Optimization in DBMS:

  1. Cost Based Query Optimization in DBMS
  2. Adaptive Query Optimization

Cost Based Query Optimization in DBMS

Cost-Based Query Optimization (CBO) is a sophisticated and systematic approach used by database management systems (DBMS) to determine the most efficient way to execute a query.

1. What is Cost-Based Query Optimization?

Cost-Based Query Optimization is a method where the optimizer assigns a numerical value, known as cost, to each step in the execution plans of a given query.

The optimizer evaluates all possible execution plans and selects the one with the lowest estimated cost.

2. Adaptive Query Optimization in DBMS

Adaptive Query Optimization (AQO) is a dynamic approach in database management systems (DBMS) where the optimizer can make changes to execution plans during query execution.

This flexibility allows the optimizer to respond to new information that becomes available as the query runs, leading to more efficient query execution, especially when initial statistics are insufficient or outdated.

imgwe