CSE-4/562 Spring 2019

Materialized Views

March 25, 2019

Textbook: Ch. 8.5

... but first ...

Checkpoint 1 - NBA_TEST_1

Rank	Group	Time (s)
1	Group1	0.88
2	theMasterMINDS	0.89
3	Dumbledore's Army	0.93
4	cnn_is_fake_news	0.95

Checkpoint 1 - NBA_TEST_3

Rank	Group	Time (s)
1	Group1	0.51
2	theMasterMINDS	0.61
3	cnn_is_fake_news	0.65
4	Dumbledore's Army	0.67
5	Administrators	0.71
6	Megalodons	0.86
7	Chwilio	0.91

Checkpoint 1 - NBA_TEST_6

Rank	Group	Time (s)
1	Root	3.49
2	Group1	4.65
3	theMasterMINDS	4.87
4	cnn_is_fake_news	5.27
5	Megalodons	6.03
6	Sentinels	7.03
7	Lannisters	7.57
8	Netflix and Chill	7.61
9	Dumbledore's Army	8.27
10	Chwilio	9.24
11	Data for Dayz	9.95

Checkpoint 1 - NBA_TEST_7

Rank	Group	Time (s)
1	Root	4.48
2	theMasterMINDS	5.28
3	cnn_is_fake_news	5.58
4	Group1	5.68
5	Megalodons	5.97
6	Sentinels	7.02
7	Lannisters	7.67
8	Dumbledore's Army	8.00
9	Netflix and Chill	8.82
10	Chwilio	9.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}}) $$

$$ 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)$$