Rule-Based Query Optimization

The purpose of the query optimizer is to discover the query performance and execution plan that calculates the result precisely. This article is all about rule-based optimization. The most reliable method to promote a performance plan is to study the composition of the tables and indexes and generate an execution strategy based upon the edifice of the database. This method is recognized as the rule-based optimizer. Let’s take a look at it in more detail.

1. What is Rule-Based Query Optimization?
2. The Transformation is Important
3. RBO and the Application of Hints
4. How do Rules Work?
5. Conclusion

What is Rule-Based Query Optimization?

Rule-based optimization is described as a simple model to examine comparable methods adopted by modern optimizers. This is an ancient method. In other words, the RBO adopted a collection of rules to define how to perform a query. E.g. If an index is open on a table, the RBO practices can be to perpetually accept that index. So, the rule-based system indicates that when performing a query the database must support several pre-configured rules and import what data is collected in modified database tables.

In the case of a rule-based method, the optimizer executes the various robust actions to carry an execution order that joins R tables:

• The optimizer produces a collection of R join hierarchies, each with a distinct table as the original table. The optimizer creates each possible join order utilizing this algorithm.
• The optimizer then picks between the resulting collection of execution methods. The purpose of the optimizer’s option is to increase the number of nested loops join services in which the inner table is obtained by utilizing an index scan. Because a nested loops join includes obtaining the inner table, an appendix on the inner table can considerably enhance the execution of a nested loops join.

The Transformation is Important

As mentioned earlier, a query optimizer must investigate the comparable execution methods and choose the efficient one. Normally, plan B is similar to plan A if it gives the identical outcome for all potential data.

To create similar execution programs, users may utilize one or more transformations to the initial design. A transformation receives one design and generates zero, one, or more comparable designs. As query engine programmers, users may execute various transformations to produce an adequate amount of comparable plans.

Some transformations work on more influential elements of the plan or even the entire plan. For instance, execution of the join order assortment with compelling programming may specify all joins in the design, create optional join orders, and choose the best one.

RBO and the Application of Hints

This portion is one of the most crucial factors. For example, if users have created applications using the old version of the database and have precisely harmonized their SQL statements based on the laws of the optimizer, they may desire to maintain utilizing rule-based optimization when they promote these applications to higher versions. If they neither accumulate statistics nor attach hints to their SQL statements, their SQL code will apply rule-based optimization. Yet, they should ultimately move their current applications to utilize the cost-based method, because the rule-based method will not be possible in the next versions.

They can allow cost-based optimization on an experiment basis just by gathering statistics. They can then revert to rule-based optimization by removing them or by configuring either the cost of the OPTIMIZER_MODE initialization value or the OPTIMIZER_GOAL value of the ALTER SESSION command to RULE. They can also apply this parameter if they want to manage and analyze statistics for their data without utilizing the cost-based approach.

For example:
In the following query, there is an index on the job and dno columns:

SELECT *
FROM employee
WHERE job = 'ACCOUNTANT'
AND dno = 20;

The EXPLAIN PLAN output for this query is as follows:


OPERATION OPTIONS OBJECT_NAME
-----------------------------------------------------
SELECT STATEMENT
TABLE ACCESS BY ROWID EMP
INDEX RANGE SCAN JOB_DEPTNO_INDEX

In this, the top-level module(s) set the search algorithm applied to get an optimal plan. These designs heuristically pick another plan to optimize transferring it to the optimize pattern. For instance, using a particular search, this collection of patterns will select a sub-plan that has not been optimized in the given table.

The optimized patterns then implement various transformation and implementation developments on this sub-plan by utilizing heuristic components, limited by configuration rules on the algebraic description. If the preconditions are met, the sub-plan is transferred to the alteration or implementation patterns. The new mutated sub-plan is then transferred to the post-conditions patterns which assess the sub-plan and modernize properly. It is worth perceiving that the modular configuration of the method, heuristics can be computed by just attaching a rule, circumstance, or pattern at any level.

How do Rules Work?

Every optimizer supports any algorithm that determines when to implement selective transformations and how to prepare the recently designed comparable plans. As the amount of transformations rises, it becomes not really helpful to keep them in a consistent cycle. Picture a big if-else section of code that determines how to implement a thousand transformations to various relational parameters.

To promote the engine’s development, programmers may desire to obscure out some of their transformations after a simple interface. For each transformation, they may establish a guide that determines whether they can implement the transformation to the delivered portion of the system. A combination of design and transformation is described as a rule.

The rule abstraction enables users to break the optimization reasoning into pluggable components that grow separately from each other, analyzing the growth of the optimizer. The optimizer that utilizes rules to create similar plans is described as a rule-based optimizer.

So, in the case of RBO, it is a guide that allows users to disintegrate the optimizer’s codebase. The acceptance of rules doesn’t overpower users to develop a particular optimization method. It doesn’t stop users from utilizing special optimization methods, like dynamic programming for join register. It doesn’t ask users to pick among heuristic or cost-based methods. Yet, the private creation of rules may involve some elements of their engine, such as join preparation.

Conclusion

Rule-based optimization is a highly adaptable design that users may utilize when creating a query optimizer. It enables users to break the complex transformation reasoning into self-contained sections, decreasing the optimizer’s multifaceted capacity. The RBO doesn’t restrict users in how specifically to optimize their designs.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.