sql-query-optimizer

SQL Query Optimizer

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 Query Optimizer steps, 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 shows the most efficient method for a SQL statement to access requested data.

In other words, optimizer 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 candidate plans.
  • The optimizer uses available statistics to calculate 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 most efficient method. However, if statistics indicate that very few students are boys, then reading an index followed by a table access 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 tree data structure representing a relation algebra expression.

Points to be remembered –

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

Query Graph

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

If you find anything incorrect in the above-discussed topic and have further questions, please comment below.

Connect on:

Recent Post

Popular Post

Top Articles

Archives
Categories

Share on