What is Heuristic Optimization in DBMS

Cost-based optimization is expensive. Heuristics are used to reduce the number of choices that must be made in a cost-based approach.

Rules

Heuristic optimization transforms the expression-tree by using a set of rules which improve the performance. These rules are as follows −

• Perform the SELECTION process foremost in the query. This should be the first action for any SQL table. By doing so, we can decrease the number of records required in the query, rather than using all the tables during the query.

• Perform all the projection as soon as achievable in the query. Somewhat like a selection but this method helps in decreasing the number of columns in the query.

• Perform the most restrictive joins and selection operations. What this means is that select only those sets of tables and/or views which will result in a relatively lesser number of records and are extremely necessary in the query. Obviously any query will execute better when tables with few records are joined.

Some systems use only heuristics and the others combine heuristics with partial cost-based optimization.

Steps in heuristic optimization

Let’s see the steps involve in heuristic optimization, which are explained below −

• Deconstruct the conjunctive selections into a sequence of single selection operations.

• Move the selection operations down the query tree for the earliest possible execution.

• First execute those selections and join operations which will produce smallest relations.

• Replace the cartesian product operation followed by selection operation with join operation.

• Deconstructive and move the tree down as far as possible.

• Identify those subtrees whose operations are pipelined.