- Materialized Views


      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;

openclipart.org

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

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

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

What is $\Delta \mathcal R$ ?

Insertions

Deletions

Updates

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

$\mathcal R \times \mathcal S\ \rightarrow\ (\mathcal R \uplus \Delta \mathcal R) \times \mathcal S$

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

$\Delta Q$	(ideally) Small & fast query
$+$	(ideally) Fast "merge" operation