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 relationalalgebra expressions are equivalent if both the expressions produce the same set of tuples on each legal database instance. A legal database instance refers to that database system which satisfies all the integrity constraints specified in the database schema. However, the sequence of the generated tuples may vary in both expressions, but they are considered equivalent until they produce the same tuples set. 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 queryevaluation plan uses such an equivalence rule or method for transforming expressions into the logically equivalent one. The optimizer uses various equivalence rules on relationalalgebra expressions for transforming the relational expressions. For describing each rule, we will use the following symbols: θ, θ_{1}, θ_{2} … : Used for denoting the predicates. L_{1}, L_{2}, L_{3} … : Used for denoting the list of attributes. E, E_{1}, E_{2} …. : Represents the relationalalgebra expressions. Let's discuss a number of equivalence rules: Rule 1: Cascade of σ This rule states the deconstruction of the conjunctive selection operations into a sequence of individual selections. Such a transformation is known as a cascade of σ. σ_{θ1 ᴧ θ 2} (E) = σ_{θ1} (σ_{θ2} (E)) Rule 2: Commutative Rule a) This rule states that selections operations are commutative. σ_{θ1} (σ_{θ2} (E)) = σ _{θ2} (σ_{θ1} (E)) b) Theta Join (θ) is commutative. E_{1} ⋈ _{θ} E _{2} = E _{2} ⋈ _{θ} E _{1} (θ is in subscript with the join symbol) 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. Rule 3: Cascade of ∏ 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 cascade of ∏. ∏L1 (∏L2 (. . . (∏Ln (E)) . . . )) = ∏L1 (E) Rule 4: We can combine the selections with Cartesian products as well as theta joins Rule 4: We can combine the selections with Cartesian products as well as theta joins
Rule 5: Associative Rule a) This rule states that natural join operations are associative. (E1 ⋈ E2) ⋈ E3 = E1 ⋈ (E2 ⋈ E3) b) Theta joins are associative for the following expression: (E_{1} ⋈ _{θ1} E_{2}) ⋈ _{θ2ᴧθ3} E_{3} = E_{1} ⋈ _{θ1ᴧθ3} (E_{2} ⋈ _{θ2} E_{3}) 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.Rule 6: Distribution of the Selection operation over the Theta join. Under two following conditions, the selection operation gets distributed over the thetajoin operation: a) When all attributes in the selection condition θ_{0} include only attributes of one of the expressions which are being joined. σ_{θ0} (E_{1} ⋈ _{θ} E_{2}) = (σ_{θ0} (E_{1})) ⋈ _{θ} E_{2} b) When the selection condition θ_{1} involves the attributes of E_{1} only, and θ_{2} includes the attributes of E_{2} only. σ_{θ1ꓥ θ2} (E_{1} ⋈ _{θ} E_{2}) = (σ_{θ1} (E_{1})) ⋈ _{θ }((σ_{θ2} (E_{2})) Rule 7: Distribution of the projection operation over the theta join. Under two following conditions, the selection operation gets distributed over the thetajoin operation: a) Assume that the join condition θ includes only in L_{1} υ L_{2} attributes of E_{1} and E_{2} Then, we get the following expression: ∏_{L1υL2} (E_{1} ⋈ _{θ} E_{2}) = (∏_{L1} (E_{1})) ⋈ _{θ} (∏_{L2} (E_{2})) b) Assume a join as E_{1} ⋈ E_{2}. Both expressions E_{1} and E_{2} have sets of attributes as L_{1} and L_{2}. Assume two attributes L_{3} and L_{4} where L_{3} be attributes of the expression E_{1}, involved in the θ join condition but not in L_{1} υ L_{2} Similarly, an L_{4} be attributes of the expression E_{2 }involved only in the θ join condition and not in L_{1} υ L_{2 }attributes. Thus, we get the following expression: ∏_{L1υL2} (E_{1} ⋈ _{θ} E_{2}) = ∏_{L1υL2} ((∏_{L1υL3} (E_{1})) ⋈ _{θ} ((∏_{L2υL4} (E_{2}))) Rule 8: The union and intersection set operations are commutative. E_{1} υ E_{2} = E_{2} υ E_{1} E_{1} ꓵ E_{2} = E_{2} ꓵ E_{1} However, set difference operations are not commutative. Rule 9: The union and intersection set operations are associative. (E_{1} υ E_{2}) υ E_{3} = E_{1} υ (E_{2} υ E_{3}) (E_{1} ꓵ E_{2}) ꓵ E_{3} = E_{1} ꓵ (E_{2} ꓵ E_{3}) Rule 10: Distribution of selection operation on the intersection, union, and set difference operations. The below expression shows the distribution performed over the set difference operation. σ_{p} (E_{1} − E_{2}) = σ_{p}(E_{1}) − σ_{p}(E_{2}) We can similarly distribute the selection operation on υ and ꓵ by replacing with . Further, we get: σ_{p} (E_{1} − E_{2}) = σ_{p}(E_{1}) −E_{2 } Rule 11: Distribution of the projection operation over the union operation. This rule states that we can distribute the projection operation on the union operation for the given expressions. ∏_{L} (E_{1} υ E_{2}) = (∏_{L }(E_{1})) υ (∏_{L }(E_{2})) Apart from these discussed equivalence rules, there are various other equivalence rules also.
Next TopicDBMS Tutorial
