Query optimization in DBMS
Choosing an effective execution strategy to execute a query is a process known as query optimization.
After the decision-making process of query parsing, which determines how many distinct ways a given query can be executed, the parsed query is sent to the query optimizer, which creates several execution plans to examine the parsed query and choose the one with the lowest estimated cost.
By producing the costs of each plan, the catalog manager helps the optimizer choose the best one to execute the query.
The most effective technique to access and change the database is through query optimization. It is the art of getting the information you need in a timely, dependable, and predictable manner.
The technique of converting a query into a similar form that may be assessed more quickly is known as query optimization. Finding an execution plan that shortens the time needed to process a query is the aim of query optimization. The objective of query optimization is to process a query. We need to carry out two crucial tasks to achieve this optimization aim.
Purpose of query optimization in DBMS:
An SQL statement's best execution plan is sought after by the optimizer.
The optimizer selects the candidate plan with the lowest cost out of all those that were considered.
The optimizer determines expenses using the available data. For a specific query in a certain environment, the cost computation takes query execution factors like I/O, CPU, and communication into account.
| Name | class | Role |
| Harini | 10 | CR |
| Harsha | 10 |
For instance, one question asks for details on students who hold leadership positions, including class representatives. A full table search may be deemed to be the most effective if the optimizer statistics reveal that 50% of students hold leadership roles
Consequently, all SQL statements use the optimizer.
Components of query optimizer:
The transformer, estimator, and plan generator are the three components that make up the optimizer. These elements are represented in the image below.
- Query transformer:
The query transformer determines if it is advantageous to change some statements' original SQL statements into less expensive, semantically equivalent SQL statements. - Estimator:
The overall cost of a particular execution plan is determined by the optimizer's estimator. The estimator uses three techniques to calculate the cost: - Selectivity: With 0 denoting no rows and 1 denoting all rows, the query selects a percentage of the row set's row selectivity that can be established using a query predicate, such as where the last name LIKE X%, or a mixture of predicates. A predicate grows more selective as the selectivity value approaches zero; in contrast, a predicate becomes less selective as the value approaches one.
- Cardinality: The number of rows that each action returns is the cardinality of an execution plan. This input, which is shared by all cost functions, is crucial for choosing the appropriate course of action. The estimated cardinality is displayed in the Rows column of an execution plan.
- Cost: This measure shows how many labor or resource units were employed. The query optimizer measures effort in terms of disc I/O, CPU use, and memory usage.
- Plan generator: Due to the multiple combinations the database might utilize to get the same goal, there are numerous designs that are feasible.
Automatic tuning optimizer:
Various operations are carried out by the optimizer depending on how it is invoked.
The following categories of optimizations are offered by the database:
- Normal optimization: After parsing the SQL, the optimizer generates an execution strategy. For most SQL statements, the default option generates a reasonable plan. When operating in regular mode, the optimizer must choose the optimum course of action within severe time restrictions, which are frequently a fraction of a second.
- SQL tuning advisor optimization: When SQL Tuning Advisor activates the optimizer by passing one or more SQL queries as input, the optimizer is referred to as the Automatic Tuning Optimizer. In this case, the optimizer performs further research to enhance the plan created in regular mode. To create a better plan, the optimizer creates a set of activities, along with their reason and expected payment.
Methods of query optimization in DBMS:
Cost based:
The practice of choosing the most effective way to carry out a SQL statement is known as query optimization. The optimizer can combine, reorganize, and process data in any sequence because SQL is a nonprocedural language.
The optimizer determines a cost estimate for the plan or potential strategy by discovering these values individually for each step of a feasible plan for a given query and environment. After examining all of the costs associated with potential solutions, the optimizer searches for the plan with the lowest cost estimate. The Optimizer is also referred to as the Cost-Based Optimizer as a result.
- Execution plans: The strategy outlines the actions Oracle Database takes to carry out a SQL statement. Line 0 of an execution plan states the plan's total cost, as well as the cost of each operation. Costs are internal units that only appear in the execution plan to compare plans. The cost value cannot be modified or altered as a result.
- Query blocks: The optimizer takes as input a parsed version of a SQL statement. Internally, a query block stands in for each SELECT block in the original SQL statement. An unmerged view, a subquery, or a top-level statement can all be included in a query block.
Consider the following SQL statement, which consists of two distinct query parts. The inner query block consists of the subquery surrounded in parentheses.
The names of the employees in the departments whose IDs were provided by the subquery are retrieved in the remaining portion of the outer query block of the SQL statement. The query form displays the connections between the query blocks.
- Adaptive query optimizer in DBMS:
With adaptive query optimization, the optimizer can modify execution plans in real-time and learn new facts that could improve statistics. Adaptive optimization is useful when the available data are insufficient to generate a perfect plan.
Adaptive plans are significant because the optimizer occasionally selects a subpar default plan because of an incorrect cardinality estimation. A more ideal final plan results from the ability to adjust the plan as it is being executed based on actual execution statistics. The optimizer selects the final plan and utilizes it for further executions, making sure that the subpar plan is not utilized again.