codingstreets
sql-query-optimizer

SQL : Query Optimizer in Structured Query Language

In this article, you will learn about the SQL Query Optimizer that will describe some of the most important key terms of SQL, such as Query Optimizer, Query Graph, and Query Tree.

Before moving ahead – let’s take a look at Basic concepts of Query Processing.

Table of Contents

Query Optimizer

The query optimizer (simply called optimizer) is built-in database software that shows the most efficient method for a SQL statement to access requested data.

In other words, optimizers have a goal to choose the most optimal execution plan for a SQL statement (query) at the lowest cost among all considered plans.

Points to be remembered –

  • The optimizer attempts to generate the most optimal execution plan for a SQL statement.
  • The optimizer chooses the plan with the lowest cost among all considered plans.
  • The optimizer uses available statistics to calculate the cost.
  • For a specific query in a given environment, the cost computation accounts for factors of query execution such as I/O, CPU., and communication.
  • SQL is a nonprocedural language, so the optimizer is free to merge, reorganize, and process in any order.
  • The database optimizes each SQL statement based on statistics collected about the accessed data.
  • The aim of query optimization is improving query processing, which means that the query optimizer needs to take into account the runtime effect of different alternatives. This is done by estimating the costs of executing an alternative.

Example: Query Optimizer

A query might request information about students who are boys. If the optimizer statistics indicate that 85% of students are boys, then the optimizer may decide that a full table scan is the most efficient method. However, if statistics indicate that very few students are boys, then reading an index followed by a table accessed by rowid may be more efficient than a full table scan.

sql-query-optimizer

sql-query-optimizer

sql-query-optimizer

Query Tree

What is Query Tree?

A Query Tree is a data structure represented as relational algebra expressions.
The leaf node is denoted as the internal relations, and the root is referred to as the whole
relational algebra expression.

Points to be remembered –

  • The root represents the query as a whole, and the query table represents lead nodes.
  • During execution, an internal node is executed whenever its operand tables are available.
  • The result table then replaces the node.
  • A query tree represents a specific order of operations for executing a query.

Query Graph

  • Relation nodes are displayed as single circles
  • Constants represented by constant nodes
    • Double circles or ovals
  • Selection or join conditions represented as edges
  • Attributes to be retrieved are displayed in square brackets

Please comment below if you find anything incorrect in the above-discussed topic and have further questions.

Connect on:

Recent Post

Popular Post

Top Articles

Archives
Categories

Share on