- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
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.
- What is query processing and optimization in DBMS?
- What is query optimization and explain its two forms(DBMS)?
- What is Query Optimization?
- Bad Character Heuristic
- What is Decomposition in DBMS?
- What is 4NF in DBMS?
- What is RAID in DBMS?
- What is a database(dbms)?
- What is Multivalued Dependency (DBMS)?
- What is an instance in DBMS?
- What is Hierarchical model in DBMS?
- What is functional dependency in DBMS?
- What is Data Independence in DBMS?
- What is Transitive dependency in DBMS?
- What is multivalued dependency in DBMS?