CSE-4/562 Spring 2019

If X and Y are equivalent and Y is better,
then replace all Xs with Ys

Today's focus: Provable Equivalence for RA Expressions

Only Relational Values Matter: Obviously $Q_1 \neq Q_2$ . What we care about is whether $Q_1(R) = Q_2(R)$ ...
Data Independent: ... for all valid input data $R$ .; However, it's fair to talk about equivalence when we know the data has some properties. (more on this later)
Data-Model Dependent: It's important to be clear whether we're talking about sets, bags, or lists.

Selection
$\sigma_{c_1 \wedge c_2}(R) \equiv \sigma_{c_1}(\sigma_{c_2}(R))$	(Decomposability)
Projection
$\pi_{A}(R) \equiv \pi_{A}(\pi_{A \cup B}(R))$	(Idempotence)
Cross Product
$R \times (S \times T) \equiv (R \times S) \times T$	(Associativity)
$R \times S \equiv S \times R$	(Commutativity)
Union
$R \cup (S \cup T) \equiv (R \cup S) \cup T$	(Associativity)
$R \cup S \equiv S \cup R$	(Commutativity)

Selection + Projection
$\pi_{A}(\sigma_{c}(R)) \equiv \sigma_{c}(\pi_{A}(R))$	(Commutativity)

Selection + Cross Product
$\sigma_c(R \times S) \equiv (\sigma_{c}(R)) \times S$	(Commutativity)

Projection + Cross Product
$\pi_A(R \times S) \equiv (\pi_{A_R}(R)) \times (\pi_{A_S}(S))$	(Commutativity)

Intersection
$R \cap (S \cap T) \equiv (R \cap S) \cap T$	(Associativity)
$R \cap S \equiv S \cap R$	(Commutativity)
Selection +
$\sigma_c(R \cup S) \equiv (\sigma_c(R)) \cup (\sigma_c(R))$	(Commutativity)
$\sigma_c(R \cap S) \equiv (\sigma_c(R)) \cap (\sigma_c(R))$	(Commutativity)
Projection +
$\pi_A(R \cup S) \equiv (\pi_A(R)) \cup (\pi_A(R))$	(Commutativity)
$\pi_A(R \cap S) \equiv (\pi_A(R)) \cap (\pi_A(R))$	(Commutativity)
Cross Product + Union
$R \times (S \cup T) \equiv (R \times S) \cup (R \times T)$	(Distributivity)


      SELECT R.A, T.E

      FROM R, S, T

      WHERE R.B = S.B
       AND S.C < 5
       AND S.D = T.D

➔

Selection Pushdown: Always commute Selections as close to the leaves as possible.
Join Construction: Joins are always better than cross-products. (if there's a good join algorithm)
(Optional) Projection Pushdown: Commuting Projections down to the leaves removes redundant columns, and may be beneficial for some systems.
Join Algorithm Selection: Joins can be implemented differently, depending on the join predicate.
Join/Union Ordering: The order in which joins are evaluated may affect query runtimes.
Access Paths: $(\sigma_c(R))$ and $(Q(\ldots) \bowtie_c R)$ are special cases that we can make fast!

Some rewrites are situational... we need more information to decide when to apply them.