- Materialized Views

Materialized Views

April 1, 1921

Garcia-Molina/Ullman/Widom: Ch. 8.5

      CREATE VIEW salesSinceLastMonth AS
        SELECT l.*
        FROM lineitem l, orders o
        WHERE l.orderkey = o.orderkey
        AND o.orderdate > DATE(NOW() - '1 Month')
    

      SELECT partkey FROM salesSinceLastMonth
      ORDER BY shipdate DESC LIMIT 10;
    

      SELECT suppkey, COUNT(*)
      FROM salesSinceLastMonth
      GROUP BY suppkey;
    

      SELECT partkey, COUNT(*)
      FROM salesSinceLastMonth
      GROUP BY partkey;

Opportunity: Views exist to be queried frequently

Idea: Pre-compute and save the view’s contents!
(like an index)

openclipart.org

When the base data changes,
the view needs to be updated too!

$$\texttt{VIEW} \leftarrow Q(\mathcal D)$$

Our view starts off initialized

Idea: Recompute the view from scratch when data changes.

$$\texttt{WHEN } \mathcal D \leftarrow \mathcal D+\Delta\mathcal D \texttt{ DO: }\\ \texttt{VIEW} \leftarrow Q(\mathcal{D}+\Delta\mathcal{D})$$

Not quite facepalm-worthy, but not ideal.

$$\texttt{WHEN } \mathcal D \leftarrow \mathcal{D}+\Delta\mathcal D \texttt{ DO: }\\ \texttt{VIEW} \leftarrow \texttt{VIEW} + \Delta Q(\mathcal D,\Delta\mathcal D)$$
$\Delta Q$ (ideally) Small & fast query
$+$ (ideally) Fast "merge" operation

Intuition

$$\mathcal{D} = \{\ 1,\ 2,\ 3,\ 4\ \} \hspace{1in} \Delta\mathcal{D} = \{\ 5\ \}$$ $$Q(\mathcal D) = \texttt{SUM}(\mathcal D)$$
$$ 1 + 2 + 3 + 4 + 5 $$
$$Q(\mathcal D+\Delta\mathcal D) \sim O(|\mathcal D| + |\Delta\mathcal D|)$$
$$ 10 + 5 $$
$$\texttt{VIEW} + SUM(\Delta\mathcal D) \sim O(|\Delta\mathcal D|)$$

Intuition

$$\mathcal{R} = \{\ \textbf{A, B, C}\ \},\ \mathcal S = \{\ \textbf{X, Y}\ \} \hspace{1in} \Delta\mathcal{R} = \{\ \textbf{D}\ \}$$ $$Q(\mathcal R, \mathcal S) = \texttt{COUNT}(\mathcal R \times \mathcal S)$$
$$ \texttt{COUNT}(\textbf{AX, AY, BX, BY, CX, CY, }\underline{\textbf{DX, DY}}) $$
$$Q(\mathcal R+\Delta\mathcal R, \mathcal S) \sim O( (|\mathcal R| + |\Delta\mathcal D|) \cdot |\mathcal S|)$$
$$ 6 + \texttt{COUNT}(\underline{\textbf{DX, DY}}) $$
$$\texttt{VIEW} + \texttt{COUNT}(\Delta\mathcal R \times \mathcal S) \sim O(|\Delta\mathcal R| \cdot |\mathcal S|)$$

$+$ is like $\uplus$

$\bowtie$ is like $\times$

Are these types of patterns common?

Rings/Semirings

Semiring: $\left< \mathbb S, +, \times, \mathbb 0, \mathbb 1\right>$

Any set of 'things' $\mathbb S$ such that...

"Closed"

$$S_i + S_j = S_k$$ $$S_i \times S_j = S_k$$

Additive, Multiplicative Identities

$$S_i + 0 = S_i$$ $$S_i \times 1 = S_i$$ $$S_i \times 0 = 0$$
$$S_i \times (S_j + S_k) = (S_i \times S_j) + (S_j \times S_k)$$

Addition distributes over multiplication

Rings/Semirings

Ring: $\left< \mathbb S, +, \times, \mathbb 0, \mathbb 1, -\right>$

Any semiring with an additive inverse....

$$S_i + (-S_i) = 0$$

THIS TANGENT ENDS NOW

Futurama is © 20th Century Fox Television
$$\texttt{WHEN } \mathcal D \leftarrow \mathcal{D}+\Delta\mathcal D \texttt{ DO: }\\ \texttt{VIEW} \leftarrow \texttt{VIEW} + \Delta Q(\mathcal D,\Delta\mathcal D)$$

Basic Questions

  • What does $\Delta \mathcal R$ mean?
  • What is $\mathcal R + \Delta \mathcal R$?
  • How do we derive $\Delta Q$?

What is $\Delta \mathcal R$?

Insertions

Deletions

Updates

What is $\Delta \mathcal R$?

A Set/Bag of Insertions

+

A Set/Bag of Deletions

What is $\Delta \mathcal R$?

$\mathcal R$ $+$ $\Delta \mathcal R$
A Set/Bag "+" A Set/Bag of Insertions
A Set/Bag of Deletions
$\mathcal R$
$\cup$/$\uplus$
$-$
$\Delta \mathcal R_{ins}$
$\Delta \mathcal R_{del}$

... this feels a bit wrong

$\texttt{VIEW} \leftarrow \texttt{VIEW} + $$\Delta Q(\mathcal D, \Delta \mathcal D)$

Given $Q(\mathcal R, \mathcal S, \ldots)$

Construct $\Delta Q(\mathcal R, \Delta \mathcal R, \mathcal S, \Delta \mathcal S, \ldots)$

$\sigma(\mathcal R) \rightarrow \sigma(\mathcal R \uplus \Delta \mathcal R)$

$ \equiv $ $\sigma(\mathcal R)$ $ \uplus $ $\sigma(\Delta \mathcal R)$

$Q(\mathcal D) = \sigma(\mathcal R)$

$\Delta Q(\mathcal D, \Delta \mathcal D) = \sigma(\Delta \mathcal R)$

Set/Bag difference also commutes through selection

$\pi(\mathcal R) \rightarrow \pi(\mathcal R \uplus \Delta \mathcal R)$

$ \equiv $ $\pi(\mathcal R)$ $ \uplus $ $\pi(\Delta \mathcal R)$

$Q(\mathcal D) = \pi(\mathcal R)$

$\Delta Q(\mathcal D, \Delta \mathcal D) = \pi(\Delta \mathcal R)$

Does this work under set semantics?

$\mathcal R_1 \uplus \mathcal R_2 \rightarrow \mathcal R_1 \uplus \Delta \mathcal R_1 \uplus \mathcal R_2 \uplus \Delta \mathcal R_2$

$ \equiv $ $\mathcal R_1 \uplus \mathcal R_2$ $ \uplus $ $\Delta \mathcal R_1 \uplus \Delta \mathcal R_2$

$Q(\mathcal D) = \mathcal R_1 \uplus \mathcal R_2$

$\Delta Q(\mathcal D, \Delta \mathcal D) = \Delta \mathcal R_1 \uplus \Delta \mathcal R_2$

How do we derive $\Delta Q$?

So far: $$\Delta Q_{ins}(\mathcal D, \Delta \mathcal D) = Q(\Delta \mathcal Q_{ins})$$ $$\Delta Q_{del}(\mathcal D, \Delta \mathcal D) = Q(\Delta \mathcal Q_{del})$$

$$\mathcal R \times \mathcal S\ \rightarrow\ (\mathcal R \uplus \Delta \mathcal R) \times \mathcal S$$
$$\mathcal{R} = \{\ \textbf{A, B, C}\ \},\ \mathcal S = \{\ \textbf{X, Y}\ \}$$ $$Q(\mathcal R, \mathcal S) = \texttt{COUNT}(\mathcal R \times \mathcal S)$$
$$\mathcal R \times \mathcal S = \{\ \textbf{AX, AY, BX, BY, CX, CY}\ \}$$
$$\Delta\mathcal{R} = \{\ \textbf{D}\ \}$$
$$(\mathcal R \uplus \Delta\mathcal R) \times \mathcal S =\\ \{\ \textbf{AX, AY, BX, BY, CX, CY, }\underline{\textbf{DX, DY}}\ \}$$
$\equiv $ $\mathcal R \times \mathcal S$ $\uplus$ $\Delta \mathcal R \times \mathcal S$

... but what if $\mathcal R$ and $\mathcal S$ both change

$$(\mathcal R_1 \uplus \Delta \mathcal R_1) \times (\mathcal R_2 \uplus \Delta \mathcal R_2)$$
$$\left(\mathcal R_1 \times (\mathcal R_2 \uplus \Delta \mathcal R_2)\right) \uplus \left(\Delta \mathcal R_1 \times (\mathcal R_2 \uplus \Delta \mathcal R_2)\right)$$
$$\left(\mathcal R_1 \times \mathcal R_2\right) \uplus \left(\mathcal R_1 \times \Delta \mathcal R_2\right) \uplus \left(\Delta \mathcal R_1 \times (\mathcal R_2 \uplus \Delta \mathcal R_2)\right)$$
$\left(\mathcal R_1 \times \mathcal R_2\right)$ $\uplus$   $\left(\mathcal R_1 \times \Delta \mathcal R_2\right) \uplus \left(\Delta \mathcal R_1 \times \mathcal R_2\right) \uplus \left(\Delta \mathcal R_1 \times \Delta \mathcal R_2\right)$

Multisets

$$\{1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 4, 4, 4, 4, 4, 4, 5\}$$

(not compact)

$$\{1 \rightarrow \times 3; 2 \rightarrow \times 5; 3 \rightarrow \times 2; 4 \rightarrow \times 6; 5 \rightarrow \times 1\}$$

Multiset representation: Tuple $\rightarrow$ # of occurrences

multiplicity

Multiset Deltas

Insertions = Positive Multiplicity

Deletions = Negative Multiplicity

+ = Bag/Multiset Union

Multiset Deltas

What does Union Do?

$\{ A \rightarrow 1, B \rightarrow 2 \} \uplus \{ B \rightarrow 2, C \rightarrow 4 \} = $

$=\{$ $A \rightarrow 1,$ $B \rightarrow 5,$ $C \rightarrow 4$ $\}$

$\{ A \rightarrow 1 \} \uplus \{ A \rightarrow -1 \}$ $= \{A \rightarrow 0\}$ $ = \{\}$

Multiset Deltas

What does Cross-Product Do?

$\{ A \rightarrow 1, B \rightarrow 3 \} \times \{ C \rightarrow 4 \} $

$= \{ \left< A,C \right> \rightarrow$ $4$ $\left< B,C \right> \rightarrow$ $12$ $\}$

Multiset Deltas

What does Projection Do?

$\pi_{\text{1st attr}}\{ \left< A,X \right> \rightarrow 1, \left< A,Y \right> \rightarrow 2, \left< B,Z \right> \rightarrow 5, \}$

$= \{$ $A \rightarrow 1, A \rightarrow 2,$ $B \rightarrow 5\}$

$= \{A \rightarrow 3, B \rightarrow 5\}$

$$\bowtie\; \equiv \times$$ $$\pi, \uplus \equiv +$$ Rings strike again!

Multiset Deltas

What does Selection Do?

$\sigma_{A}\{ A \rightarrow 1, B \rightarrow 5, C \rightarrow 3 \}$

$= \{ A \rightarrow 1, B \rightarrow 0, C \rightarrow 0 \}$

$= \{ A \rightarrow 1\}$

Next class: Updates