## Transforming Relational ExpressionsThe first step of the optimizer says to implement such expressions that are logically equivalent to the given expression. For implementing such a step, we use the equivalence rule that describes the method to transform the generated expression into a logically equivalent expression. Although there are different ways through which we can express a query, with different costs. But for expressing a query in an efficient manner, we will learn to create alternative as well as equivalent expressions of the given expression, instead of working with the given expression. Two relational-algebra expressions are equivalent if both the expressions produce the same set of tuples on each legal database instance. A ## Equivalence RulesThe equivalence rule says that expressions of two forms are the same or equivalent because both expressions produce the same outputs on any legal database instance. It means that we can possibly replace the expression of the first form with that of the second form and replace the expression of the second form with an expression of the first form. Thus, the optimizer of the query-evaluation plan uses such an equivalence rule or method for transforming expressions into the logically equivalent one. The optimizer uses various equivalence rules on relational-algebra expressions for transforming the relational expressions. For describing each rule, we will use the following symbols:
Let's discuss a number of equivalence rules:
This rule states the deconstruction of the conjunctive selection operations into a sequence of individual selections. Such a transformation is known as a
a) This rule states that selections operations are commutative.
b) Theta Join (θ) is commutative.
However, in the case of theta join, the equivalence rule does not work if the order of attributes is considered. Natural join is a special case of Theta join, and natural join is also commutative. However, in the case of theta join, the equivalence rule does not work if the order of attributes is considered. Natural join is a special case of Theta join, and natural join is also commutative.
This rule states that we only need the final operations in the sequence of the projection operations, and other operations are omitted. Such a transformation is referred to as a ∏L1 (∏L2 (. . . (∏Ln (E)) . . . )) = ∏L1 (E)
*σ*_{θ}(E_{1}x E_{2}) = E_{1θ}⋈ E_{2}*σ*_{θ1}(E_{1}⋈_{θ2}E_{2}) = E_{1}⋈_{θ1ᴧθ2}E_{2}
a) This rule states that natural join operations are associative.
b) Theta joins are associative for the following expression:
In the theta associativity, θ2 involves the attributes from E2 and E3 only. There may be chances of empty conditions, and thereby it concludes that Cartesian Product is also associative. ## Note: The equivalence rules of associativity and commutatively of join operations are essential for join reordering in query optimization.
Under two following conditions, the selection operation gets distributed over the theta-join operation: a) When all attributes in the selection condition θ
b) When the selection condition θ
Under two following conditions, the selection operation gets distributed over the theta-join operation: a) Assume that the join condition θ includes only in L
b) Assume a join as E
However, set difference operations are not commutative.
The below expression shows the distribution performed over the set difference operation.
We can similarly distribute the selection operation on υ and ꓵ by replacing with -. Further, we get:
This rule states that we can distribute the projection operation on the union operation for the given expressions.
Apart from these discussed equivalence rules, there are various other equivalence rules also. Next TopicCandidate Key |