CSE-4/562 Spring 2019 - Materialized Views

Materialized Views

CSE-4/562 Spring 2019

March 25, 2019

Textbook: Ch. 8.5

... but first ...

Checkpoint 1 - NBA_TEST_1

Rank Group Time (s)
1Group10.88
2theMasterMINDS0.89
3Dumbledore's Army0.93
4cnn_is_fake_news0.95

Checkpoint 1 - NBA_TEST_3

Rank Group Time (s)
1Group10.51
2theMasterMINDS0.61
3cnn_is_fake_news0.65
4Dumbledore's Army0.67
5Administrators0.71
6Megalodons0.86
7Chwilio0.91

Checkpoint 1 - NBA_TEST_6

Rank Group Time (s)
1Root3.49
2Group14.65
3theMasterMINDS4.87
4cnn_is_fake_news5.27
5Megalodons6.03
6Sentinels7.03
7Lannisters7.57
8Netflix and Chill7.61
9Dumbledore's Army8.27
10Chwilio9.24
11Data for Dayz9.95

Checkpoint 1 - NBA_TEST_7

Rank Group Time (s)
1Root4.48
2theMasterMINDS5.28
3cnn_is_fake_news5.58
4Group15.68
5Megalodons5.97
6Sentinels7.02
7Lannisters7.67
8Dumbledore's Army8.00
9Netflix and Chill8.82
10Chwilio9.33

      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:}\hspace{2in}\\ \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|)$$

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 ($+$ is not "closed")

(but that's a topic for 662)

$\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\hspace{7in}\\ \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)$$