When the relational model was launched for the first time, one of the chief criticisms often cited was the inadequate presentation of queries. Since then, a significant amount of research has been committed to developing highly proficient algorithms for processing and dealing with queries. There are a lot of ways to do a complex query that can be performed, and one of the targets of query processing is to decide which one is the most cost-effective. In the first-generation network and hierarchical database systems, the low-level procedural query language is generally implanted in a high-level programming language such as COBOL, and it is the job of the programmer's to select the most appropriate execution strategy. In contrast, with declarative languages such as SQL, the user identifies what data is required rather than how it is to be retrieved.
In this chapter, you will be given a general idea of query processing and examine the main segments of this activity. Here you will examine the first phase of query processing, namely query decomposition, which transforms a high-level query into a relational algebra query and ensures that it is syntactically and semantically correct.
Overview of Query Processing
This query processing activity involved in parsing, validating, optimizing, and executing a query. The target of query processing is to change a query written in a high-level language, (usually SQL) into a correct and efficient execution strategy expressed in a low-level language (using the relational algebra) and to perform the strategy to retrieve the required data. An important aspect of query processing is query optimization. The activity of choosing an efficient execution strategy for processing a query is known as Query optimization. As there are many correspondent transformations of the same high-level query, the main aim of optimizing a query is to choose the one that minimizes resource usage. Generally, you will try reducing the total execution time of the query, which is the total of the execution times of all individual operations that make up the query.
Both methods of query optimization rely on database statistics to assess the different available options properly. The accuracy and currency of these statistics have a significant bearing on the efficiency of the execution strategy chosen.
Comparison of Different Processing Strategies
Find all DBAs who work at a NewDelhi branch.
You can write this query in SQL as:
SELECT *
FROM Staff s, Branch b
WHERE s.branchNo = b.branchNo AND
(s.position = 'DBA' AND b.city = 'NewDelhi');
The 3 equivalent relational algebra queries corresponding to the above SQL statement are:
- σ(position='DBA') ∧ (city='NewDelhi') ∧ (Staff.branchNo=Branch.branchNo)(Staff × Branch)
- σ(position='DBA') ∧ (city=' NewDelhi')(Staff branchNo=Branch.branchNo Branch)
- (σposition='DBA'(Staff)) Staff.branchNo=Branch.branchNo (σcity=' NewDelhi'(Branch))
What is Query Decomposition in DBMS?
Query decomposition is the first phase of query processing. The primary targets of query decomposition are to transform a high-level query into a relational algebra query and to check that the query is syntactically and semantically correct. The typical stages of query decomposition are analysis, normalization, semantic analysis, simplification, and query restructuring.