Page 220 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 12: Query Processing and Optimization

12. The projection operation can be distributed over the union. Notes

The rules as above are too general and a few heuristics rules may be generated from these rules,
which help in modifying the relational expression in a better way. These rules are:

1. Combining a cascade of selections into a conjunction and testing all the predicates on the
tuples at the same time:

2. Combining a cascade of projections into single outer projection:

3. Commutating the selection and projection or vice-versa sometimes reduces cost
4. Using associative or commutative rules for Cartesian product or joining to find various
alternatives.

5. Moving the selection and projection (it may have to be modified) before joins. The selection
and projection results in the reduction of the number of tuples and therefore may reduce
cost of joining.
6. Commuting the projection and selection with Cartesian product or union.

Task What do you mean by pipelining? Discuss

12.8 Estimating Statistics of Expression Results

Let us explain use of some of these rules with the help of an example. Consider the query for the
relations:
STUDENT (enrollno, name, phone)
MARKS (enrollno, subjectcode, grade)
SUBJECTS (subjectcode, sname)
Consider the query: Find the enrolment number, name, and grade of those students who have
secured an A grade in the subject DBMS. One of the possible solutions to this query may be:

LOVELY PROFESSIONAL UNIVERSITY 213