- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- 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 query optimization and explain its two forms(DBMS)?
For any given query, there may be a number of different ways to execute it. The process of choosing a suitable one for processing a query is known as query optimization.
Forms
The two forms of query optimization are as follows −
Heuristic optimization − Here the query execution is refined based on heuristic rules for reordering the individual operations.
Cost based optimization − the overall cost of executing the query is systematically reduced by estimating the costs of executing several different execution plans.
Example
Select name from customer, account where customer.name=account.name and account.balance>2000;
There are two evaluation plans −
Πcustomer.name(σcustomer.name=account.name ^ account.balance>2000(customerXaccount)
Πcustomer.name(σcustomer.name=account.name(customerXσ account.balance>2000(account)
Cost evaluator evaluates the cost of different evaluation plans and chooses the evaluation plan with lowest cost. Disk access time, CPU time, number of operations, number of tuples, size of tuples are considered for cost calculations.
Heuristic approach is also called rule-based optimization. There are three ways for transforming relational-algebra queries are −
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 set 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.
Hence we see throughout these approaches, our motive is cost optimization only.
- Related Articles
- What is query processing and optimization in DBMS?
- What is heuristic optimization in DBMS?
- What is Query Optimization?
- What is a query language in DBMS?
- What is meant by asexual reproduction? List any two of its different forms.
- Explain how normal forms can be transformed from lower to higher(DBMS)
- Explain about insert command in Structured query language in DBMS
- What is 1NF in DBMS? Explain with examples
- Explain about two phase locking (2PL) protocol(DBMS)
- What is B-tree and explain the reasons for using it (DBMS)?
- Explain about Create, Insert, Select command in structure query language (DBMS)?
- What is OLAP? Explain its advantages and disadvantages
- What are copy elision and return value optimization?
- What is JSON.parse() and and explain its use in javascript?
- What is JSON.stringify() and explain its use in javascript?
