basi_di_dati/compitino/secondo_compitino/piani_di_accesso.tex

% !TEX root = ../main.tex
\subsection{Query a}

\paragraph{Piano di accesso logico della query a}
\begin{center}
  \begin{forest}, baseline, qtree
      [{$\pi^{b}$ r.IdRicetta , r.Nome}
          [{$\bowtie$ p.IdPersona = r.IdCreatrice}
              [{$\sigma$ p.Nome = 'Giovanni'}
                  [Persone p]
              ]
              [Ricette r]
          ]
      ]
  \end{forest}
\end{center}

Non c'è in questo caso differenza tra $\pi^{b}$ e $\pi$: non ci possono essere
duplicati.
\paragraph{Piano di accesso fisico della query a senza indici}
\begin{center}
  \begin{forest}, baseline, qtree
      [{Project(\{r.IdRicetta , r.Nome\})}
          [{SortMerge(p.IdPersona = r.IdCreatrice)}
              [{Sort([p.IdPersona])}
                  [{Project(\{p.IdPersona\})}
                    [{Filter(p.Nome = 'Giovanni')}
                      [{TableScan(Persone p)}]
                    ]
                  ]
              ]
              [{Sort([r.IdCreatrice])}
                [{Project(\{r.IdRicetta, r.Nome, r.IdCreatrice\})}
                  [{TableScan(Ricette r)}]
                ]
              ]
          ]
      ]
  \end{forest}
\end{center}

\paragraph{Piano di accesso fisico della query a con due indici}
\begin{center}
  \begin{forest}, baseline, qtree
      [{Project(\{r.IdRicetta , r.Nome\})}
          [{IndexNestedLoop(p.IdPersona = r.IdCreatrice)}
              [{IndexFilter(Persone p,\\ IndPN, p.Nome = 'Giovanni')}]
              [{IndexFilter(Ricette r,\\IndRC, r.IdCreatrice = p.IdPersona)}]
          ]
      ]
  \end{forest}
\end{center}
Indici necessari: \texttt{IndPN} (indice della tabella Persone sull’attributo
Nome) e \texttt{IndRC} (indice della tabella Ricette sull'attributo Creatrice).

\clearpage
\subsection{Query b}

\paragraph{Piano di accesso logico della query b}
\begin{center}
  \begin{forest}, baseline, qtree
    [{$\tau$[-DiversiFornitori]}
      [{$\pi^{b}$ fa.IdBirrificio, COUNT(DISTINCT fa.IdFornitore) DiversiFornitori}
        [{$\sigma$ COUNT(DISTINCT fa.IdFornitore) $>=$ 3}
            [\{fa.IdBirrificio\} {$\gamma$ \{COUNT(DISTINCT fa.IdFornitore)\}}
                [{$\sigma$ fa.Data $>=$ '2020-01-01'}
                  [Fatture fa]
                ]
            ]
        ]
      ]
    ]
  \end{forest}
\end{center}

Non c'è in questo caso differenza tra $\pi^{b}$ e $\pi$: non ci possono essere
duplicati, in quanto la GROUP BY raggruppa per IdBirrificio.
\paragraph{Piano di accesso fisico della query b senza indici}
\begin{center}
  \begin{forest}, baseline, qtree
    [{Sort[-DiversiFornitori]}
        [{Project(\{fa.IdBirrificio, COUNT(DISTINCT fa.IdFornitore) DiversiFornitori\})}
            [{Filter(COUNT(DISTINCT fa.IdFornitore) $>=$ 3)}
                [{GroupBy(\{fa.IdBirrificio\}, \{COUNT(DISTINCT fa.IdFornitore)\})}
                    [{Sort([fa.IdBirrificio])}
                        [{Filter(fa.Data $>=$ '2020-01-01')}
                            [{TableScan(Fatture fa)}]
                        ]
                    ]
                ]
            ]
        ]
    ]
  \end{forest}
\end{center}
Il sort sull'attributo dimensione di analisi prima della GroupBy è necessario,
in quanto non è garantito che i record della tabella Fatture siano raggruppati
per IdBirrificio.
Lo sarebbero se l'organizzazione primaria della tabella fosse sequenziale
proprio su questo attributo, il che è estremamente poco probabile.
\clearpage
\paragraph{Piano di accesso fisico della query b con un indice}
\begin{center}
  \begin{forest}, baseline, qtree
      [{Sort[-DiversiFornitori]}
        [{Project(\{fa.IdBirrificio, COUNT(DISTINCT fa.IdFornitore) DiversiFornitori\})}
            [{Filter(COUNT(DISTINCT fa.IdFornitore) $>=$ 3)}
                [{GroupBy(\{fa.IdBirrificio\}, \{COUNT(DISTINCT fa.IdFornitore)\})}
                    [{Sort([fa.IdBirrificio])}
                        [{IndexFilter(Fatture fa, IndFD, fa.Data $>=$ '2020-01-01')}]
                    ]
                ]
            ]
        ]
    ]
  \end{forest}
\end{center}
Indice necessario: \texttt{IndFD} (indice della tabella Fatture sull’attributo
Data).
Il sort sull'attributo IdBirrificio prima della GroupBy è necessario, in quanto
i record in input sono ordinati per data, il che non ci garantisce che siano
raggruppati per IdBirrificio (che è dimensione di analisi).

\subsection{Query c}

\paragraph{Piano di accesso logico della query c}
\begin{center}
  \begin{forest}, baseline, qtree
      [{$\pi^{b}$ fo.RagioneSociale, SUM(fa.Importo) ImportoTotale, AVG(fa.Importo) ImportoMedio}
          [$\sigma$ SUM(fa.Importo) $>$ 10
              [{\{fo.IdFornitore, fo.RagioneSociale\} $\gamma$ \{SUM(fa.Importo), AVG(fa.Importo)\}}
                  [{$\bowtie$ fa.IdFornitore = fo.IdFornitore}
                      [{Fornitori fo}]
                      [{$\bowtie$ fa.IdBirrificio = b.IdBirrificio}
                          [{$\sigma$ b.Nome = 'Pirati Rossi'}
                              [{Birrifici b}]
                          ]
                          [{Fatture fa}]
                      ]
                  ]
              ]
          ]
      ]
  \end{forest}
\end{center}

In questo caso non ci dovrebbe essere differenza tra $\pi^{b}$ e $\pi$: non ci
devono essere due fornitori con la stessa ragione sociale (la ragione sociale
è chiave naturale); è comunque possibile un errore di inserimento se non ho
impostato un vincolo di unicità anche su questo attributo, che non ho scelto
come chiave primaria della tabella: ecco perché ho raggruppato anche per
IdFornitore e non solo per RagioneSociale.

Ho scelto l'ordine di giunzione in modo da avere la restrizione il più distale
possibile.

\clearpage

\paragraph{Piano di accesso fisico della query c senza indici}
\begin{center}
  \begin{forest}, baseline, qtree
      [{Project(\{fo.RagioneSociale,\\SUM(fa.Importo) ImportoTotale, AVG(fa.Importo) ImportoMedio\})}
          [{Filter(SUM(fa.Importo) $>$ 10)}
              [{GroupBy(\{fo.IdFornitore,fo.RagioneSociale\}, \{SUM(fa.Importo), AVG(fa.Importo)\})}
                  [{MergeSort(fa.IdFornitore = fo.IdFornitore)}
                      [{Sort([fo.IdFornitore])}
                          [{Project(\{fo.IdFornitore,\\fo.RagioneSociale\})}
                              [{Fornitori fo}]
                          ]
                      ]
                      [{Sort([fa.IdFornitore])}
                          [{Project(\{fa.IdFornitore, fa.Importo\})}
                              [{MergeSort(fa.IdBirrificio = b.IdBirrificio)}
                                  [{Sort([b.IdBirrificio])}
                                      [{Project(\{b.IdBirrificio\})}
                                          [{Filter(b.Nome = 'Pirati Rossi')}
                                              [{TableScan(Birrifici b)}]
                                          ]
                                      ]
                                  ]
                                  [{Sort([fa.IdBirrificio])}
                                      [{Project(\{fa.IdBirrificio,\\fa.IdFornitore fa.Importo\})}
                                          [{Fatture fa}]
                                      ]
                                  ]
                              ]
                          ]
                      ]
                  ]
              ]
          ]
      ]
  \end{forest}
\end{center}
Non è necessario ordinare per \texttt{[fo.IdFornitore, fo.RagioneSociale]} prima
della GroupBy: per costruzione, l'ordine dell'operatore esterno della SortMerge
viene mantenuto nell'output, e questo ordine è sull'attributo fo.IdFornitore,
che a sua volta determina funzionalmente l'altra dimensione di analisi, fo.RagioneSociale.

Pertanto, è garantito che l'input della GroupBy sarà già raggruppato per gli
attributi che sono dimensione di analisi e non occorre un ordinamento
preventivo.

\clearpage
\paragraph{Piano di accesso fisico della query c con tre indici}
\begin{center}
  \begin{forest}, baseline, qtree
      [{Project(\{fo.RagioneSociale,\\SUM(fa.Importo) ImportoTotale, AVG(fa.Importo) ImportoMedio\})}
          [{Filter(SUM(fa.Importo) $>$ 10)}
              [{GroupBy(\{fo.IdFornitore, fo.RagioneSociale\},\\\{SUM(fa.Importo), AVG(fa.Importo)\})}
                  [{Sorted([fo.IdFornitore])}
                      [{Project(\{fo.IdFornitore, fo.RagioneSociale, fa.Importo\})}
                          [{IndexNestedLoop(fa.IdFornitore = fo.IdFornitore)}
                              [{IndexNestedLoop\\(fa.IdBirrificio = b.IdBirrificio)}
                                  [{IndexFilter(Birrifici b, IndBN,\\b.Nome = 'Pirati Rossi')}]
                                  [{IndexFilter(Fatture fa, IndFaIdB,\\fa.IdBirrificio = b.IdBirrificio)}]
                              ]
                              [{IndexFilter(Fornitori fo, IndFoIdF,\\fo.IdFornitore = fa.IdFornitore)}]
                          ]
                      ]
                  ]
              ]
          ]
      ]
  \end{forest}
\end{center}
Indici necessari: \texttt{IndBN} (indice della tabella Birrifici sull’attributo
Nome), \texttt{IndFaIdB} (indice della tabella Fatture sull'attributo
IdBirrificio) e \texttt{IndFoIdF} (indice della tabella Fornitori sull'attributo
IdFornitore).

Occorre ordinare per IdFornitore prima della \texttt{GroupBy}, in quanto
l'output della IndexNestedLoop è ordinato come l'operatore esterno, ovvero
per nome del birrificio.

Potrei spostare l'ordinamento tra le due giunzioni con IndexNestedLoop, tanto
ogni fattura ha un fornitore e l'output non andrà a decrecere dopo la seconda
giunzione (anzi, si arricchirà di campi).
Il sort andrebbe fatto con il minor numero possibile di dati, dato l'alto costo
dell'algoritmo, eliminando i campi superflui con una project prima.

Il vantaggio dell'IndexNestedLoop sul SortMerge si ha solo se la condizione è
sufficientemente restrittiva da essere soddisfatta da una piccola minoranza
di record.
In questo caso, la restrizione sul nome del birrificio dovrebbe essere
abbastanza restrittiva (se ci sono abbastanza birrifici, il numero di
birrifici con il nome `Pirati Rossi' sarà trascurabile rispetto al totale) ed
è ragionevole che le fatture che riguardano quel birrificio siano una esigua
minoranza rispetto al totale delle fatture.
Se così non fosse, pur avendo i tre indici a disposizione, converrebbe
utilizzare comunque il SortMerge.