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.

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.