PROGETTAZIONE FISICA

PROGETTAZIONE
FISICA
IL CATALOGO DI SISTEMA
Le tabelle di catalogo
!
Un DBMS memorizza su tabelle di catalogo le
informazioni su
!
!
!
ciascuna tabella
! nome della tabella e struttura del file
! nome e tipo di ciascun attributo
! nome di ciascun indice
! vincoli di integrità
ciascun indice
! nome e struttura
! attributi della chiave di ricerca
ciascuna vista
! nome e definizione
Giorgio Giacinto 2010
Database
3
Altre informazioni presenti nel
catalogo
!
!
!
!
!
!
!
Cardinalità delle tabelle
Dimensione delle tabelle
Cardinalità degli indici
Dimensione degli indici
Altezza degli indici
Ampiezza dell’indice
Informazioni sulle autorizzazioni e sugli
accessi degli utenti
Giorgio Giacinto 2010
Database
4
LA PROGETTAZIONE FISICA
Introduzione
!
!
!
Dopo il progetto ER, la traduzione in relazionale, il
raffinamento dello schema e la definizione delle
viste, abbiamo gli schemi logico ed esterno della
nostra base di dati.
Il passo successivo consiste nella scelta degli indici,
nel prendere decisioni sul clustering, e nel
raffinamento degli schemi logico ed esterno (se
necessario) per raggiungere le prestazioni
desiderate
I DBMS mettono a disposizione strumenti per la
verifica delle prestazioni
Giorgio Giacinto 2010
Database
6
Descrizione del carico di
lavoro
!
Lista di interrogazioni
!
!
!
Con la relativa frequenza di utilizzo
Lista di aggiornamenti e relativa frequenza
Obiettivi di prestazioni per ciascun tipo di
interrogazione e aggiornamento
Giorgio Giacinto 2010
Database
7
Comprendere il carico di
lavoro
!
Per ciascuna interrogazione nel carico di lavoro
!
!
!
!
A quali relazioni accede?
Quali attributi vengono restituiti?
Quali attributi sono coinvolti nelle condizioni di
selezione/join? Quanto selettive sono, probabilmente, tali
condizioni?
Per ciascun aggiornamento nel carico di lavoro
!
!
Quali attributi sono coinvolti nelle condizioni di
selezione/join? Quanto selettive sono, probabilmente, tali
condizioni?
Il tipo di aggiornamento (INSERT/DELETE/UPDATE) e gli
attributi che vengono modificati
Giorgio Giacinto 2010
Database
8
Progettazione fisica
!
Scelta degli indici
!
Ottimizzazione dello schema logico
!
Ottimizzazione di interrogazioni e transazioni
Giorgio Giacinto 2010
Database
SCELTA DEGLI INDICI
9
Scelta degli indici
!
Quali indici dovremmo creare?
!
!
Quali relazioni dovrebbero avere indici? Quali
campi dovrebbero costituire la chiave di ricerca?
Dovremmo creare più di un indice?
Per ciascun indice, che tipo di indice
dovremmo usare?
!
Clustered? Hash/B+?
Giorgio Giacinto 2010
Database
11
Scelta degli indici (segue)
!
Un approccio
!
!
!
Considerare a turno le interrogazioni più importanti.
Considerare il miglior piano di esecuzione usando gli indici
correnti. Verificare la possibilità di un piano migliore con un
indice aggiuntivo.
Prima di creare un indice, dobbiamo considerare
l’impatto sugli aggiornamenti nel carico di lavoro
!
Compromesso: gli indici possono velocizzare le
interrogazioni, rallentare gli aggiornamenti… ma anche
richiedere spazio su disco
Giorgio Giacinto 2010
Database
12
Linee guida per la selezione degli indici
Quando indicizzare
!
Non costruire indici a meno che certe
interrogazioni non ne traggano beneficio
!
Possibilmente scegliere indici che velocizzino
più di una interrogazione
Giorgio Giacinto 2010
Database
13
Linee guida per la selezione degli indici
Scelta della chiave di ricerca
!
Gli attributi nella clausola WHERE sono
candidati come chiavi dell’indice
!
!
Condizione di selezione di uguaglianza
suggerisce l’uso di indice sugli attributi coinvolti
(idealmente: hash)
Condizione di selezione di intervallo suggerisce
indice B+
Giorgio Giacinto 2010
Database
14
Linee guida per la selezione degli indici
Chiavi di ricerca con attributi multipli
!
!
Una clausola WHERE include condizioni su
più di un attributo di una relazione
oppure
L’uso di chiavi di ricerca con attributi multipli
consente l’uso di strategie di valutazione
basate solo sull’indice
Giorgio Giacinto 2010
Database
15
Linee guida per la selezione degli indici
Quando usare i cluster
!
L’uso del cluster favorisce principalmente le
interrogazioni per intervallo
!
Se per velocizzare una interrogazione è
sufficiente l’indice, non serve usare cluster
Giorgio Giacinto 2010
Database
16
Linee guida per la selezione degli indici
Indici hash o indici B+?
!
!
Un indice a albero B+ è in genere preferibile
perché supporta sia ricerche per uguaglianza
che per intervallo
L’indice hash è preferibile quando
!
!
Ci sono interrogazioni annidate e l’indice è sulla
relazione nella interrogazione interna
Non ci sono interrogazioni per intervallo che
coinvolgono l’indice
Giorgio Giacinto 2010
Database
17
Linee guida per la selezione degli indici
Costo di manutenzione degli indici
!
!
Gli indici possono rallentare le operazioni di
aggiornamento…
…ma possono anche velocizzarle (nel caso
di indici primari)
Giorgio Giacinto 2010
Database
18
ESEMPI DI SELEZIONE
DEGLI INDICI
Selezione per uguaglianza
SELECT I.inome, R.mgr
FROM Impiegati I, Reparti R
WHERE R.rnome = ‘Giocattoli’ AND I.rnum = R.rnum
!
! inome
Un possibile piano
!"
rnum=rnum
!
In base alle linee guida,
indici hash
!
un indice su rnome e un
indice su I.rnum
Giorgio Giacinto 2010
! rnome='Giocattoli '
Impiegato
Reparto
Database
20
Selezione per uguaglianza (segue)
!
Se nella clausola WHERE ci fosse anche la condizione
I.età=25
un buon piano eseguirebbe
!
!
!
!
una selezione su rnome
una selezione su età
il join dei due risultati
In questo caso servirebbero due indici
!
!
su rnome
su età
e l’indice su rnum nella tabella Impiegati non è utile
Giorgio Giacinto 2010
Database
21
Selezione per intervallo
SELECT I.inome, R.mgr
FROM Impiegati I, Reparti R
WHERE I.sal BETWEEN 10000 AND 20000
AND I.hobby = ‘Francobolli’ §
AND I.rnum = R.rnum
!
Indice hash su hobby e B+ su sal
!
!
Se la condizione di uguaglianza è selettiva,
possiamo usare solo l’indice su hobby
Se sono disponibili i due indici, l’ottimizzatore può
valutare quale indice è più selettivo
Giorgio Giacinto 2010
Database
22
Indici che permettono piani
basati solo sugli indici
SELECT R.mgr
FROM Impiegati I, Reparti R
WHERE I.rnum = R.rnum
!
Non viene mostrato alcun campo di Impiegati
!
è sufficiente usare un indice su rnum di Impiegati e non
accedere alla tabella
SELECT R.mgr, I.iID
FROM Impiegati I, Reparti R
WHERE I.rnum = R.rnum
!
Un indice B+ su (rnum,iID) consente di ottenere le
data entry senza accedere alla tabella
Giorgio Giacinto 2010
Database
OTTIMIZZAZIONE DELLA
BASE DI DATI
23
Necessità dell’ottimizzazione
!
!
L’uso della base di dati consente di valutare
meglio il carico di lavoro rispetto ai dati usati
in fase progettuale
E’ possibile migliorare le prestazioni
!
!
!
Ottimizzando gli indici
Ottimizzando lo schema logico
Ottimizzando le interrogazioni
Giorgio Giacinto 2010
Database
25
Ottimizzazione degli indici
!
Il carico di lavoro osservato può mettere in
evidenza, rispetto alla fase di progetto
!
!
!
Alcune interrogazioni e aggiornamenti ritenuti
frequenti, in realtà non lo sono
Altre interrogazioni o aggiornamenti sono
importanti
Aggiunta/eliminazione di indici
Giorgio Giacinto 2010
Database
26
Raffinare lo schema logico
!
La scelta dello schema logico dovrebbe
essere guidata dal carico di lavoro, oltre che
da considerazioni sulla ridondanza
!
Quando le modifiche sono fatte dopo che una
base di dati è in uso si parla di evoluzione
dello schema
!
Alcune di queste modifiche possono essere
“nascoste” alle applicazioni definendo delle viste
Giorgio Giacinto 2010
Database
27
Schemi di esempio
Contratti(cid, fid, gid, did, pid, qta, val)
Dep(did, budget, report)
Fornitori(fid, indirizzo)
Pezzi(pid, costo)
Progetti(gid, mgr)
!
Ci concentreremo su Contratti, denotata come
CFGDPQV. Sono dati i seguenti VI: GP → C, FD → P,
C è la chiave primaria
!
!
Quali sono le chiavi candidate per CFGDPQV?
In quale forma normale si trova questo schema di relazione?
Giorgio Giacinto 2010
Database
28
Decidere per la 3NF o per la
BCNF
!
CFGDPQV può essere decomposta in FDP e
CFGDQV, ed entrambe le relazioni sono in BCNF
!
!
!
Supponiamo che sia molto importante
!
!
Decomposizione senza perdita, ma che non conserva le
dipendenze
L’aggiunta di CGP permette di conservare le dipendenze
Trovare il numero di copie Q di pezzi P ordinati nel contratto
C
Richiede un join sullo schema decomposto, ma si può
rispondere tramite una scansione della relazione
originale CFGDPQV
Può indurci a decidere per lo schema 3NF CFGDPQV
Giorgio Giacinto 2010
Database
29
Denormalizzazione
!
Supponiamo che la seguente interrogazione sia
importante
!
!
Per velocizzare questa interrogazione, potremmo
aggiungere un campo budget B a Contratti
!
!
!
il valore di un contratto è minore del budget del
dipartimento?
Questo introduce la DF D → B rispetto a Contratti
Contratti non è più in 3NF
Potremmo scegliere di apportare questa modifica se
l’interrogazione è importante, e non possiamo
ottenere prestazioni adeguate in altro modo
!
ad es., aggiungendo indici o scegliendo uno schema 3NF
alternativo
Giorgio Giacinto 2010
Database
30
Scelta delle decomposizioni
Ci sono 2 modi per decomporre CFGDPQV
!
1.
2.
FDP e CFGDQV: senza perdite ma non conserva le
dipendenze
FDP, CFGDQV e CGP: conserva anche le dipendenze
La differenza è il costo del mantenimento della DF
GP → C
!
!
!
2. decomposizione: indice su GP nella relazione CGP
1. decomposizione: richiede una asserzione
CREATE ASSERTION ControllaDip
CHECK (NOT EXISTS (SELECT *
FROM PezzoInfo P, ContrattoInfo C
WHERE P.fid = C.fid AND P.did = C.did
GROUP BY C.gid, P.pid
HAVING COUNT(C.cid) > 1))
Giorgio Giacinto 2010
Database
31
Scelta delle decomposizioni
(segue)
!
Valgono i seguenti VI
GP → C, FD → P, C è la chiave primaria
!
!
Supponiamo inoltre che FPQ → V
Se decidiamo di voler decomporre CFGDPQV in
BCNF, abbiamo ora una terza scelta
!
!
!
!
Decomponiamo in FPQV e CFGDPQ
Poi decomponiamo CFGDPQ in FDP, CFGDQ
Otteniamo la decomposizione senza perdita: FPQV, FDP,
CFGDQ
Per conservare GP → C, possiamo aggiungere CGP
Scelta: {FPQV, FDP, CFGDQ} oppure
{FDP,CFGDQV}?
Giorgio Giacinto 2010
Database
32
Decomposizione verticale di
una relazione BCNF
!
!
Supponiamo di scegliere {FDP, CFGDQV}
Questa è in BCNF, e non c’è ragione di decomporre
ulteriormente
Però, supponiamo che queste interrogazioni siano
importanti
!
!
!
!
Trovare i contratti con il fornitore F
Trovare i contratti che coinvolgono il dipartimento D
Decomporre ulteriormente CFGDQV in CF, CD e
CGQV potrebbe velocizzare queste interrogazioni.
D’altra parte, la seguente interrogazione è più lenta
!
Trovare il valore totale di tutti i contratti con il fornitore F
Giorgio Giacinto 2010
Database
33
Decomposizioni orizzontali
!
Definizione usuale di decomposizione
!
!
Una relazione è sostituita con una collezione di relazioni
che sono proiezioni. Caso più importante.
A volte, potremmo voler sostituire la relazione con
una collezione di relazioni che sono selezioni
(decomposizione orizzontale)
!
!
Ciascuna nuova relazione ha lo stesso schema
dell’originale, ma un sottoinsieme delle righe
Collettivamente, le nuove relazioni contengono tutte le
righe dell’originale. Tipicamente, le nuove relazioni sono
disgiunte
Giorgio Giacinto 2010
Database
34
Decomposizioni orizzontali
(segue)
!
Supponiamo che tutti i contratti con valore > 10.000 siano
soggetti a regole diverse.
!
!
!
Ciò significa che le interrogazioni su Contratti spesso
conterranno la condizione val > 10.000
Possiamo gestire questa situazione con un indice ad albero
B+ clustered sul campo val di Contratti
Un secondo approccio consiste nel sostituire i contratti con
due nuove relazioni: ContrattiGrandi e ContrattiPiccoli, con
gli stessi attributi (CFGDPQV)
! Si comporta come un indice su tali interrogazioni, ma senza
il sovraccarico degli indici
! Si possono inoltre costruire indici clustered su altri attributi!
Giorgio Giacinto 2010
Database
35
Mascherare i cambiamenti dello
schema logico
CREATE VIEW Contratti(cid, fid, gid, did, pid, qta, val)
AS SELECT *
FROM ContrattiGrandi
UNION
SELECT *
FROM ContrattiPiccoli
!
!
La sostituzione di Contratti con ContrattiGrandi e
ContrattiPiccoli può essere mascherata dalla vista
Però le interrogazioni con la condizione val > 10000
devono essere effettuate su ContrattiGrandi per una
esecuzione efficiente
!
gli utenti interessati alle prestazioni devono essere consapevoli
del cambiamento
Giorgio Giacinto 2010
Database
36
Ottimizzazione delle
interrogazioni
Minimizzare l’uso di DISTINCT: non ce n’è bisogno se
i duplicati sono accettabili, o se la risposta contiene
una chiave
Minimizzare l’uso di GROUP BY e HAVING
!
!
SELECT MIN(I.età)
FROM Impiegati I
GROUP BY I.rnum
HAVING I.rnum = 102
Giorgio Giacinto 2010
SELECT MIN(I.età)
FROM Impiegati I
WHERE I.rnum = 102
Database
37
Altre linee guida per il
raffinamento delle interrogazioni
(segue)
!
Evitare l’uso di relazioni intermedie
SELECT * INTO Temp
FROM Imp I, Rep R
WHERE I.rnum = R.rnum
AND R.nomemgr = “Joe”
e
SELECT T.rnum, AVG(T.sal)
FROM Temp T
GROUP BY T.rnum
!
!
contro
SELECT I.rnum, AVG(I.sal)
FROM ImpI, Rep R
WHERE I.rnum = R.rnum
AND D.nomemgr =
“Joe”
GROUP BY E.rnum
Non materializza la relazione intermedia Temp
Se c’è un indice ad albero B+ denso su <rnum, sal>,
si può usare un piano basato solo sull’indice
Giorgio Giacinto 2010
Database
38