0. Introduzione alla esecuzione delle interrogazioni Approcci alla

Fonti di riferimento
Basi di Dati – Complementi
2. Tecnologie per DBMS
-2.3 Esecuzione e ottimizzazione delle
interrogazioni
Carlo Batini 2005 - 2006
yQueste trasparenze parte 2.6
yBasi di Dati Vol. 2 (Atzeni, Ceri Paraboschi,
Torlone) cap. 1.5, 1.6
yElmasri/Navathe, Fundamentals of Database
Systems
yGarcia Molina, Ullman, Widom, Database Systems
– the complete book, Prentice Hall, 2002
Query Optimization – @ Carlo Batini 2006
Componenti del DBMS interessati
Utente/Applicazione
Queries
Transazioni
Aggiornamenti
0. Introduzione alla esecuzione
delle interrogazioni
Amministratore della BD
Comandi DDL
Query compiler
Gestore di
Interrogazioni e aggiornamenti
Gestore dei
metodi d’accesso
DDL Compiler
Gestore delle
transazioni
Gestore della
concorrenza
Gestore della
affidabilità
Gestore
del buffer
Gestore della
memoria secondaria
Memoria
secondaria
Query Optimization – @ Carlo Batini 2006
Approcci alla compilazione di queries
y Compila e memorizza: la query è compilata una
volta ed eseguita piu’ volte
– Il codice interno e’ memorizzato nel DB, insieme alle
dipendenze del codice da particolari versioni delle
tabelle e degli indici
– In presenza di cambiamenti, la compilazione della
interrogazione è invalidata e ripetuta
y Compila ed esegui: esecuzione immediata,
nessuna memorizzazione
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Passi di elaborazione: parsing
Query (SQL)
Data
Catalog
Scanning, Parsing
Query tree
(intermediate)
Usa informazioni sullo schema logico
Effettua una analisi lessicale, sintattica e
semantica, usando il dizionario e traduzione in
algebra relazionale, corrispondente a un qeury
tree
Query Optimization – @ Carlo Batini 2006
1
Passi di elaborazione:
costruzione query plan logico e sua ottimizzazione
Query (SQL)
Data
Catalog
Scanning, Parsing
Calcolo del query plan logico
E sua Ottimizzazione
Query tree
astratto
Query plan
logico
Trasforma il query tree in un query plan logico
contenente operazioni di algebra relazionale
sulle tabelle logiche dello schema, e lo
ottimizza rispetto al costo
Query plan logico
yLa query viene rappresentata come un albero nel
quale:
– Le foglie corrispondono alle strutture dati logiche
(tabelle) e
–I nodi intermedi rappresentano operazioni
algebriche:
ySelezione
yProiezione
yJoin
yProdotto cartesiano
yOperazioni insiemistiche
Query Optimization – @ Carlo Batini 2006
Esempio: query sul seguente schema
Query Optimization – @ Carlo Batini 2006
Parte (gialla) dello schema coinvolta
y Employee [Ename, lname, ssn (social security number),
bdate (birthdate), address]
y Department [Dname, dnumber, mgrssn (manager ssn)]
y Project [Pname, pnumber, plocation, dnum]
y Works-on [Essn (Employee ssn), pno (pnumber), hours]
y Employee [Ename, lname, ssn (social security number),
bdate (birthdate), address]
y Department [Dname, dnumber, mgrssn (manager ssn)]
y Project [Pname, pnumber, plocation, dnum]
y Works-on [Essn (Employee ssn), pno (pnumber), hours]
y Trovare Progetti localizzati a Stafford e loro Manager,
con mome, indirizzo, e data nascita
Trovare Progetti localizzati a Stafford e loro Manager, con
mome, indirizzo, e data nascita
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Corrispondente query plan logico
Query in SQL
y Employee [Ename, lname, ssn (social security number),
bdate (birthdate), address]
y Department [Dname, dnumber, mgrssn (manager ssn)]
y Project [Pname, pnumber, plocation, dnum]
Trovare Progetti localizzati a Stafford e loro Manager
SELECT Pnumber, Dnum, Lname, Address, Bdate
yFROM Project P, Dept D, Emp E
yWhere P.dnum=D.dnumber and E.ssn = D.mgrssn
and P.location = ‘Stafford’
R1 = (σplocation=“stafford”(project)) joindnum=dnumber dept
R = (πPnumber, dnum, lname, address, bdate (R1 joinmgrssn=ssn emp))
πPnumber, dnum, lname, address, bdate
SELECT Pnumber, Dnum, Lname,
Address, Bdate
FROM Project P, Dept D, Emp E
Where P.dnum=D.dnumber
and E.ssn = D.mgrssn
and P.location = ‘Stafford’
joinmgrssn=ssn
joindnum=dnumber
σplocation=“stafford”
Emp
Dept
Project
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
2
Ottimizzazione del query plan logico - 1
Ottimizzazione del query plan logico – 2
yA una interrogazione SQL possono corrispondere
diverse espressioni in algebra relazionale e quindi
diversi query plan logici.
In questa fase viene scelto, tra tutti i piani logici
equivalenti, il query plan logico che ottimizza il
costo di esecuzione
yNel precedente esempio posso esprimere
nell’algebra le clausole della
Ad esempio, in genere conviene che le selezioni
siano anticipate rispetto ai join, perche’ in questo
modo i join operano su tabelle di minore
dimensione.
y Where P.dnum=D.dnumber and E.ssn = D.mgrssn and
P.location = ‘Stafford’
ysecondo diversi ordini possibili
Query Optimization – @ Carlo Batini 2006
Ottimizzazione del query plan logico - 3
Query Optimization – @ Carlo Batini 2006
Rappresentazione iniziale del query plan
Query (SQL)
Data
Catalog
Scanning, Parsing
Ottimizzazione
del query plan logico
πPnumber, dnum, lname, address, bdate
Query tree
(intermediate)
Query plan
logico
Ottimizzazione algebrica:
Trasformazione delle espressioni di Algebra
relazionale rappresentate dal query plan logico
in altre equivalenti, ma piu’ efficienti
joinmgrssn=ssn
joindnum=dnumber
σplocation=“stafford”
Query (SQL)
Usa statistiche sulla
storia delle esecuzioni
delle interrogazioni
Statistics
Data
Catalog
Scanning, Parsing
Calcolo del qplan logico
E sua ottimizzazione
Calcolo query plan fisico
e ottimizzazione
Query tree
(intermediate)
Query plan
logico
Query plan
fisico
Trasformazione delle tabelle logiche in strutture fisiche e
metodi di accesso alla memoria
Trasformazione delle operazioni algebriche nelle loro
implementazioni sulle strutture fisiche Query Optimization – @ Carlo Batini 2006
Tabelle
Dept
Project
Query Optimization – @ Carlo Batini 2006
Passi di elaborazione:
query plan fisico e sua ottimizzazione
Emp
Operazioni
dell’algebra
relazionale
Query Optimization – @ Carlo Batini 2006
Traduzioni da operazioni dell’algebra in
operazioni elementari
– Select,
– Project,
– Join
– scan sequenziali,
– ricerca binaria,
– ordinamenti,
– accessi con indice,
– vari tipi di join (vedi in
seguito)
Dalle operazioni logiche alla loro implementazione
sui dati fisici
Query Optimization – @ Carlo Batini 2006
3
Esempio di query plan fisico
R1 = (σplocation=“stafford”(project)) joindnum=dnumber dept
R = (πPnumber, dnum, lname, address, bdate (R1 joinmgrssn=ssn emp))
πPnumber, dnum, lname, address, bdate
joinmgrssn=ssn
joindnum=dnumber
σplocation=“stafford”
ÆScelta delle
Operazioni
elementari
Emp
ÆScelta delle
Rappresentazioni
Fisiche delle
tabelle
Dept
Ottimizzazione del query plan fisico
basata sui costi
yLa trasformazione e’ effettuata mediante una
strategia che prevede di utilizzare:
–1. proprieta’ algebriche;
–2. una stima dei costi delle operazioni
fondamentali per diversi metodi di accesso;
yIn generale, il problema di ottimizzare la query
ha complessita’ esponenziale
yIn pratica, si introducono delle approssimazioni
ragionevoli in base a euristiche
Project
Query Optimization – @ Carlo Batini 2006
Riassumendo, il problema generale e’..
Passi di elaborazione:
esecuzione del query plan
Query (SQL)
Scanning, Parsing
Data
Catalog
Statistics
Calcola query plan logico
E sua ottimizzazione
Query Optimization – @ Carlo Batini 2006
Query tree
(intermediate)
Query plan
logico
Calcolo query plan Fisico
e sua ottimizzazione
Piano ottimo
logico/fisico
di esecuzione
delle operazioni
Interrogazioni
SQL
Carico di
Lavoro
Ottimizzazione delle
Interrogazioni
Scelta ottima
rappr. fisica
delle tabelle
Query plan
fisico
Codifica ed esecuzione del query plan
DATI
Query Optimization – @ Carlo Batini 2006
Noi nel seguito
affronteremo il problema
Interrogazione
SQL
Carico di
Lavoro
Rappr. fisica
delle tabelle
Ottimizzazione delle
Interrogazioni
Piano ottimo
Logico/fisico
di esecuzione
delle operazioni
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Piano di lavoro
y Preliminare 1: Buffer Management
y Preliminare 2: Modelli per la valutazione dei costi
ySeguiremo un percorso bottom up:
– 1. Rappresentazione fisica delle tabelle: metodi di
accesso ai dati
– 2. Ottimizzazione del piano di esecuzione fisico:
implementazione delle operazioni dell’algebra
– 3. Ottimizzazione del piano di esecuzione logico:
ottimizzazione algebrica
– 4. Piano di esecuzione logico/fisico: ottimizzazione
basata sui costi
Query Optimization – @ Carlo Batini 2006
4
Accesso ai dati tramite buffer
Preliminare 1: buffer management
y Assumeremo che l’implementazione degli operatori
relazionali e i metodi di accesso ai dati dispongano di un
numero M sufficiente di blocchi (pagine) in memoria
principale
y Ogni pagina corrisponde a un blocco in memoria secondaria
y Le pagine sono allocate e gestite nel buffer, zona di
memoria non persistente pre-allocata al DBM
Scan
Sequenziale
Gestore
B-Trees
Read/
Writes
Buffer
Manager
Buffer
Memoria
persistente
Gestore
Ordinamenti
Gestore
Tabelle Hash
Query Optimization – @ Carlo Batini 2006
Buffer – caratteristiche principali
y Organizzato in pagine:
– Dimensioni multiple di quelle dei blocchi di I/O usati dal file system
– Tipiche: 2K -> 4K -> 8k fino a 64k
– (Tempo di accesso file system) / (tempo di accesso buffer) ≥ 106
y Il buffer e’ gestito da un buffer manager
y In maniera simile a quanto avviene per la gestione di
gerarchie di memoria nei file systems
– Operazioni primitive di gestione dei blocchi in cache
– Principio di localita’ dei dati e strategie di sostituizione
dei blocchi
– Euristica: 80% delle applicazioni accede al 20% delle
pagine
– Valutazione dell’hit ratio come per i Sistemi Operativi
Query Optimization – @ Carlo Batini 2006
Funzioni del buffer manager- 2
yQuando tratteremo le transazioni,
descriveremo altre richieste al Buffer
Manager oltre a read/write
Richieste
Query Optimization – @ Carlo Batini 2006
Funzioni del buffer manager - 1
yIl buffer manager media tutte le richieste di
lettura/scrittura dei blocchi da parte degli
operatori
–Traduce indirizzi su disco in indirizzi su blocchi
di memoria buffer e viceversa
yPagina buffer Æ pagina fisica (file, blocco)
–Gli indirizzi logici dei records nei blocchi
diventano indirizzi fisici in memoria
–Ritorna indirizzi fisici all’operazione che sta
accedendo ai dati
Query Optimization – @ Carlo Batini 2006
Configurazione dei buffer
yIl numero M di pagine disponibili al Buffer
Manager e’ di solito un parametro di
configurazione del DB, detto buffer pool
–Definito in fase di creazione e non modificabile
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
5
Tipologie dei costi - 1
yI costi ricadono in due categorie:
Preliminare 2:
Valutazione dei costi
Query Optimization – @ Carlo Batini 2006
Tipologie dei costi – 2
y Tipologie di costi
y Tempo
1. Costi di accesso a memoria secondaria
y Costi per acquisire i dati in m. centrale
y Costi per memorizzare i risultati
2. Costi di memorizzazione di files intermedi
y Costi di elaborazione sul canale
3. Costi di elaborazione in memoria centrale
y Spazio
y Costi di utilizzo della memoria principale
Query Optimization – @ Carlo Batini 2006
Percio’ nel seguito
y Tipologie di costi
y Tempo
Costi di accesso a memoria secondaria
y Costi per acquisire i dati in m. centrale
y Costi per memorizzare i risultati
Costi di memorizzazione di files intermedi
Costi di utilizzo del canale
Costi di elaborazione in memoria centrale
y Spazio
y Costi di utilizzo della memoria principale
Query Optimization – @ Carlo Batini 2006
yTempi di esecuzione
ySpazio occupato
Query Optimization – @ Carlo Batini 2006
Scelte nella valutazione dei costi
Il fattore di costo piu’ importante e’ il primo,
Costi di accesso a memoria secondaria
misurato con:
Il numero di blocchi caricati nel buffer durante il
calcolo dellee operazioni
Nel valutarlo si trascurano i costi di
memorizzazione dei risultati su memoria
secondaria (blocchi caricati su disco)
Query Optimization – @ Carlo Batini 2006
Valutazione dei costi
conclusioni
yIl costo dominante che studiamo e’ il numero di
operazioni di I/O
– trasferimenti di blocchi da disco nel buffer
yL’obiettivo e’ di minimizzare l’ I/O sfruttando al
meglio gli M buffer/pagine disponibili
Query Optimization – @ Carlo Batini 2006
6
Metodi di accesso ai dati
yRiguardano tre aspetti:
1. Rappresentazione fisica
delle tabelle
–scansione/scan
–ordinamenti
–accesso con indice
Query Optimization – @ Carlo Batini 2006
Scansione
Query Optimization – @ Carlo Batini 2006
Ordinamenti
y Realizza un accesso sequenziale a tutti i record di
una tabella, eseguendo diverse operazioni:
– 1. Proiezione su un insieme di attributi – senza
eliminazione dei duplicati
– 2. Selezione su un predicato semplice, di tipo
– Ai = v
– 3. Inserimenti, cancellazione e aggiornamento
delle tuple accedute durante lo scan
Query Optimization – @ Carlo Batini 2006
yVari metodi per ordinare i dati in
memoria principale
yDBMSs usualmente non possono caricare
tutta la BD nel buffer; percio’, ordinano
separatamente e poi effettuano il merge
di tabelle, usando lo spazio disponibile nel
buffer
Query Optimization – @ Carlo Batini 2006
Accesso diretto
yPuò essere eseguito solo se le
strutture fisiche lo permettono:
–Indici
–Strutture hash
2. Piano di esecuzione fisico:
implementazione delle operazioni
dell’algebra: selezione, join
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
7
Implementazione della selezione
tipologie dei predicati
y1. Predicati di selezione semplici
–Es. σdno=5 (employee)
y2. Predicati con congiunzione di condizioni
–Es. σdno=5 and salary>30000 and sex=F(employee)
y3. Predicati con disgiunzione di condizioni
–Es. σdno=5 or salary>30000 or sex=F(employee)
Selezione
Query Optimization – @ Carlo Batini 2006
Selezioni semplici
calcolo costi
yMetodi di accesso disponibili
– A. Scan lineare
– Æ n. blocchi = dimensione del file/ dimensione del
blocco
– B. Ricerca binaria, per files ordinati sulla stessa
chiave del predicato
– Æ n. blocchi = log (dimensione del file/ dimensione
del blocco)
– C. Accesso a indice
– Æ n. blocchi = n. blocchi struttura a indice
(profondita’ dell’albero)
Query Optimization – @ Carlo Batini 2006
Selettivita’ dei predicati
definizione e proprieta’
y La selettivita’ di un predicato e’ definita come il rapporto
tra il numero di record che soddisfano il predicato e il
numero di record totale del file (relazione)
y Assumendo uniformita’ di distribuzione dei valori di un
attributo, e’ pari alla probabilita’ che un record soddisfi la
condizione su un attributo
y Selettivita’ del predicato semplice Ai=v su una relazione R:
sel(Ai=v, R)
Query Optimization – @ Carlo Batini 2006
Congiunzione di condizioni/predicati
Strategie - premessa
y In questo caso (e nel successivo) entrano in gioco:
y la valutabilita’ di un predicato, cioe’ il fatto che il predicato
coinvolge attributi su cui e’ definito un indice, che
permettono un accesso piu’ efficiente
y e la selettivita’ di un predicato Æ
y Esempio per la valutabilita’:
y Es. Cerca le persone con piu’ di 100 anni e nati a Milano
y I predicati potrebbero essere:
y Eta’ predicato non valutabile e Nascita predicato valutabile
Query Optimization – @ Carlo Batini 2006
Predicati con congiunzione di condizioni
Strategie
yIl DBMS sceglie il predicato valutabile piu’
selettivo per l’accesso primario e valuta gli
altri predicati in ordine di selettivita’
– quando Ai e’ chiave: 1/|R|
– quando sono presenti n valori distinti per Ai: 1/n
y Il DBMS stima la selettivita’ in base a statistiche raccolte durante
l’esecuzione di query precedenti
y L’ attributo piu’ selettivo e’ quello per cui mini (sel(Ai=v, R))
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
8
Congiunzione di predicati
Strategie - esempio
–Es: trova tutte le persone con piu’ di
100 anni e gli occhi neri
ySe sel (piu’ di 100 anni) = 2% e
ySel (colore occhi = “neri”) = 50%
ychiaramente e’ preferibile partire dal
primo
Considerazione sulla congiunzione di predicati
yPoiche’ il DBMS sceglie il predicato valutabile piu’
selettivo per l’accesso primario e valuta gli altri
predicati nel buffer, normalmente e’ sufficiente
per l’ottimizzazione scegliere solo il primo
predicato.
Query Optimization – @ Carlo Batini 2006
Disgiunzione di predicati - Strategie
Es.: trova tutte le persone con meno di 100 anni o gli occhi
neri
y 1. Se qualche predicato non e’ valutabile e’ necessario uno
scan in cui su ogni n-pla si valuta tutta la selezione
disgiuntiva
y 2. Se tutti i predicati sono valutabili, due metodi:
– A. si possono utilizzare gli indici (ma allora occorre
eliminare i duplicati che si ottengono dalle varie
selezioni)
– B. si utilizza uno scan, in cui su ogni n-pla si valuta tutta la
selezione disgiuntiva
Query Optimization – @ Carlo Batini 2006
Disgiunzione di predicati - Strategie
yConfronto qualitativo tra i due metodi
–Conviene uno scan quando la interrogazione e’
poco selettiva, perche’ in questo caso e’
inefficiente la eliminazione dei duplicati
–Es1: trova tutte le persone con meno di 100 anni
o gli occhi neri Æ molto poco selettiva
–Es2: trova tutte le persone con piu’ di 100 anni o
gli occhi neri Æ poco selettiva
–Es3: trova tutte le persone con piu’ di 100 anni o
gli occhi azzurri Æ selettiva, utilizzare gli indici
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Metodi per il calcolo del join
Join
nel seguito consideriamo il caso di
join binari di tipo equijoin
yI join sono le operazioni piu’ costose (e spesso piu’
frequenti) per un DBMS
yEsistono vari metodi per il calcolo di join, tra essi
vedremo:
–A. nested-loop
–B. single loop
–C. merge-scan
–D. hash
yTali metodi sono basati su uso combinato di
metodi di accesso ai dati visti sopra:
yScan, indici, Hash, Sort
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
9
Il problema, dato R join S, diventa
S
R
Strutture
Strutture
accesso
accesso
A. Nested loop
Minimo numero
di blocchi trasferiti
Buffer
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Nested loop join R A=B S: algoritmo
yPer ogni record in r in R (tabella esterna)
–Per ogni record s in S (tabella interna, scan
lineare)
–Verifica se r(A) = s(B)
Realizzato sempre con scan sequenziale sui due files
R Tabella esterna
Scansione
esterna
S Tabella interna
Efficienza di nested-loop join
Conviene usare il file piu’ piccolo per il ciclo
esterno
Intuitivamente:
– Ottimizza l’uso dei blocchi di buffer e
quindi il numero di caricamenti di blocchi
y Vediamolo numericamente
Scansione
interna
– Æ applichiamo il metodo di valutazione dei
costi con un esempio
Query Optimization – @ Carlo Batini 2006
Esempio relativo allo schema
Query Optimization – @ Carlo Batini 2006
Nested-loop: Quanti blocchi vengono caricati?
Esempio: EMP joindno=dnumber DEPT
Assunzioni:
yEmployee
–Ename, lname, ssn (social security number),
bdate (birthdate), address, dno (department
number)
yDepartment
–Dname, dnumber, mgrssn (manager ssn)
Query Optimization – @ Carlo Batini 2006
DEPT occupa bD = 10 blocchi e
EMP occupa bE = 2.000 blocchi
nB = 6 blocchi di buffer disponibili
A. Assumiamo di usare EMP (la tabella piu’ grande) per il ciclo esterno
Ogni blocco in EMP viene letto una volta
L’ intero file DEPT viene letto ogni volta che viene caricato un nuovo insieme
di blocchi di EMP
- Strategia di allocazione dei blocchi:
- Usiamo nB –1 blocchi per EMP, 1 blocco per DEPT, perche’ cosi’ minimizziamo
il numero di volte che carichiamo blocchi di DEPT
- Numero totale di blocchi caricati:
- EMP: bE
- DEPT: bD * ⎡bE / (nB –1)⎤ dove ⎡ ⎤ esprime la parte intera superiore
Totale = 2.000 + 10*(2.000/5) = 6.000 blocchi
Query Optimization – @ Carlo Batini 2006
10
Nested-loop: Quanti blocchi vengono caricati?
Esempio: EMP joindno=dnumber DEPT
Assunzioni:
Nested-loop: Quanti blocchi vengono caricati?
Esempio: EMP joindno=dnumber DEPT
DEPT occupa bD = 10 blocchi e
B. Usiamo DEPT (la tabella piu’ piccola) per il ciclo
esterno
nB = 6 blocchi di buffer disponibili
-Strategia di allocazione dei blocchi:
EMP occupa bE = 2.000 blocchi
B. Usiamo DEPT (la tabella piu’ piccola) per il ciclo
esterno
- Strategia di allocazione dei blocchi:
- Usiamo nB –1 blocchi per DEPT, 1 blocco per EMP
- Numero totale di blocchi caricati:
- DEPT: bD
- EMP: bE * ⎡bD / (nB –1)⎤
Totale = 10 + 2.000* ⎡10/5⎤ = 4.010 blocchi
- Usiamo nB –1 blocchi per DEPT, 1 blocco per EMP
-Numero totale di blocchi caricati:
- DEPT: bD
- EMP: bE * ⎡bD / (nB –1)⎤
Totale = 10 + 2.000* ⎡10/5⎤ = 4.010 blocchi
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Esercizio
yProvare formalmente la proprieta’ per
cui conviene sempre la tabella piu’
piccola nel loop piu’ esterno
B. Single loop
Query Optimization – @ Carlo Batini 2006
Single loop join R A=B S: algoritmo
yVariante al nested loop se esiste una
struttura di accesso diretto su B di S
Per ogni record r in R
Cerca con accesso diretto i record s in
S tali che s(B) = r(A)
R Tabella esterna
Scansione
esterna
S Tabella interna
Accesso
con indice
Query Optimization – @ Carlo Batini 2006
Single loop join R A=B S: idea
Per ogni record r in R
Cerca con accesso diretto i record s in S
tali che s(B) = r(A)
In questo caso e’ possibile sfruttare l’accesso con
indice per accedere ai record della tabella interna,
invece che lo scan sequenziale
R Tabella esterna
Scansione
esterna
S Tabella interna
Accesso
con indice
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
11
Single-loop: Fattori che influenzano il costo
Il numero di blocchi trasferiti e’ influenzato da:
1. bD num. di blocchi di DEPT, bE num. di blocchi di EMP
2. Join selection factor (JSF) di una relazione nel join,
cioe’ la percentuale di record che sono messi in join con
l’altra relazione
3. IL (Index level), il numero di livelli della struttura a
indici che deve essere visitata
Assumiamo che sia di tipo B tree, e quindi si visiti un blocco per
ogni livello
4. La Cardinalita’ |R| e |S| delle relazioni
Esempio relativo allo schema
yEmployee
–Ename, lname, ssn (social security number),
bdate (birthdate), address, dno (department
number)
yDepartment
–Dname, dnumber, mgrssn (manager ssn)
–Un manager per dipartimento
–Con |Employee| = 5.000, |Department| = 50
Query Optimization – @ Carlo Batini 2006
Esempio di calcolo del JSF
Query Optimization – @ Carlo Batini 2006
Assunzioni
Consideriamo il join:
EMP joinssn = mgrssn DEPT
“ogni dipartimento con il proprio manager”
Nell’esempio
JSF (DEPT) =
1 (perche’ ogni dipartimento ha un manager)
JSF (EMP) =
50/5000 = 0,01 (perche’ solo gli impiegati che sono
manager partecipano al join)
Query Optimization – @ Carlo Batini 2006
Single loop calcolo costi - 1
A. Single loop su EMP
Numero di blocchi
= # blocchi di E + #record di E *
# accessi su DEPT per ogni record di E
= bE + (|EMP| *(IL(DEPT) + 1))
= 2.000 + (5.000*3)= 17.000
Query Optimization – @ Carlo Batini 2006
Assumiamo che siano definiti indici
secondari su:
ySSN con IL(SSN) = 4
yMGRSSN con IL(MGRSSN) = 2
Assumiamo |DEPT| = 50, |EMP| = 5000
Query Optimization – @ Carlo Batini 2006
Single loop calcolo costi - 2
B. Single loop su DEPT
Numero di blocchi = bD + (|DEPT| *(IL(EMP) + 1))
= 10 + (50 * 5)= 260
In altre parole:
- dato d in DEPT: la probabilita’ di trovare e in EMP tale
che d.mgrssn = e.ssn e’ pari ad 1 (nessuno spreco di
accessi)
- dato e in EMP: la probabilita’ di trovare d in DEPT tale
che d.mgrssn = e.ssn = 50/5000 (carico inutilmente
4950 records di EMP!!)
Conviene usare DEPT (cioe’ la relazione con selection
factor piu’ alto) nel ciclo esterno, EMP nel ciclo interno
con indice secondario su EMP.SSN
Query Optimization – @ Carlo Batini 2006
12
Esercizio
yEffettuare le valutazioni di costi per
yNested loop per la query
–EMP joinssn = mgrssn DEPT
ySingle loop per la query
C. Merge scan
–EMP joindno=dnumber DEPT
Query Optimization – @ Carlo Batini 2006
Merge scan join - precondizione
yAssume le tabelle ordinate in base agli
attributi di join
Query Optimization – @ Carlo Batini 2006
Merge-scan join
yInizia dalle prime tuple r e s delle due relazioni
–Verifica se r e s sono in join
–Passa alternativamente alla successiva di r e s
yFinche’ le tuple non sono concluse
R
Query Optimization – @ Carlo Batini 2006
S
Query Optimization – @ Carlo Batini 2006
Costo del merge scan join
yRichiede lo scan lineare, una sola volta, di
ciascun file
-unico passaggio su ciascun file
-# blocchi caricati =
-#blocchi R + #blocchi S
-Richiede pero’ l’ordinamento sugli attributi di
join!
Query Optimization – @ Carlo Batini 2006
D. Hash join
Query Optimization – @ Carlo Batini 2006
13
Hash join
Assunzione: le tabelle sono implementate mediante hash
sugli attributi di join. Inoltre usano la stessa funzione
hash h
Osservazione: Dati r in R e s in S, se r[A]= s[B], allora
h(r[A]) = h(s[B]): r e s appartengono allo stesso blocco
Quindi, e’ sufficiente effettuare join limitati ad ogni
blocco
R
S
Hash-join su R join
R.A=S.B
S – algoritmo
yPasso a):
yI record di R e S sono memorizzati in una tabella
hash:
yUsando come chiavi R.A e S.B e la stessa hash
function h per R.A e S.B
yCalcola h(r[A]) per ogni r in R,e h(s[B]) per ogni s in
S. Richiede uno scan di R e S
yPasso b):
–Per ogni blocco indotto dalla funzione h( ),
effettua il join dei record all’interno del blocco
yIl join globale e’ l’unione dei join di ogni blocco
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Costo dell’ hash-join
-unico passaggio su ciascun file
-non richiede ordinamento
3. Ottimizzazione algebrica
-Non approfondiamo la formula
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Query plan logico
Query trees e ottimizzazione algebrica
Strategia
yOsservazione: l’algebra relazionale e’
caratterizzata da molte regole di trasformazione
di equivalenza (gia’ viste nel corso di Elementi di
BD)
yStrategia approssimata (euristica): si applicano
all’albero trasformazioni equivalenti che riducono
il costo di esecuzione
Query tree
Trasformazioni
di equivalenza
Query plan
logico
Query tree
equivalente
ottimo
y Non giustificheremo formalmente le regole
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
14
Regole di trasformazione di equivalenza nell’algebra
relazionale: alcuni esempi 1
Trasformazioni di equivalenza
y1. ATOMIZZAZIONE σ Una selezione congiuntiva
puo’ essere sostituita da una sequenza di
operazioni di selezione individuali
– σc1 AND c2 AND .. cn (R) equivalente σc1 (σc2(…
σcn(R)..))
yEsempio
y SEL Eta’ > 30 AND Stipendio < 50 (Impiegati)
yEquivalente a
y SEL Eta’ > 30 (SEL Stipendio < 50 (Impiegati))
Query Optimization – @ Carlo Batini 2006
Regole di trasformazione di equivalenza nell’algebra
relazionale: alcuni esempi 2
y2. COMM σ Commutativita’ della selezione
–σc1 (σc2(R)) equivalente σc2 (σc1( R ))
y3. COMM σ X Commutativita’ di selezione e
prodotto cartesiano
–σ c (R X S) equivalente (σ c (R)) X S (se fa
riferimento solo ad attributi di R)
Query Optimization – @ Carlo Batini 2006
Regole di trasformazione di equivalenza
nell’algebra relazionale: alcuni esempi 3
y 4. COMM σ π Commutativita’ di selezione e proiezione
–σ c(π (R)) equivalente π(σ c (R))
y 5. EQIUV X σ e Join Equivalenza di prodotto cartesiano e
selezione con Join
–σ c(E1 X E2) equivalente E1 JOIN c E2
y 6. COMM π e Join Commutativita’ di proiezione e join
Query Optimization – @ Carlo Batini 2006
La piu’ importante
y 7. ANTICIPAZIONE DELLA SELEZIONE RISPETTO AL JOIN
y σ c (E1 JOIN E2) EQUIV E1 JOIN σ c (E2)
yse c fa riferimento solo ad attributi in E2
yVale anche sotto opportune condizioni la
y ANTICIPAZIONE DELLA PROIEZIONE RISPETTO AL JOIN
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Euristiche per scegliere le
trasformazioni di equivalenza - 1
yOsservazione
yIl costo di join e’ proporzionale alle dimensioni dei
files, perche’ consideriamo il numero di blocchi
caricati nel buffer
ySelezioni e proiezioni riducono le dimensioni dei files
Æ migliorano la performance del join
yEuristica 1:
–Applicare selezione e proiezione prima possibile
e in ogni caso prima di calcolare i join
yEuristica 2
yTra le selezioni, applicare prima quelle con
predicati piu’ selettivi
Query Optimization – @ Carlo Batini 2006
15
Esempio di query
Esempio: riconsideriamo lo schema
yEmployee
–Ename, lname, ssn (social security number),
bdate (birthdate), address
yDepartment
–Dname, dnumber, mgrssn (manager ssn)
yProject
–Pname, pnumber, plocation, dnum
Seleziona gli impiegati che lavorano nel
progetto ‘x’ e sono nati a partire dall’anno
1958
SELECT lname
FROM emp, works_on, project
WHERE pname = ‘x’ AND pnumber = pno AND
essn=ssn AND bdate > ’dec-31-1957’
yWorks-on
–Essn, pno, hours
Query Optimization – @ Carlo Batini 2006
Esempio di trasformazione algebrica – 1
albero iniziale (nessuna ottimizzazione)
πlname
σpname=‘x’ and pnumber = pno and
Query Optimization – @ Carlo Batini 2006
2–Le selezioni vengono spinte in basso lungo l’albero
πlname
X
X
X
Employee
σPnumber= pno
σ BDATE>’dec-31-1957’
Project
Works_on
Employee
Employee
Lname, ssn, bdate
(birthdate), ...
Project
Pname, pnumber, plocation, ...
Works-on
Essn, pno, hours
Query Optimization – @ Carlo Batini 2006
3 – Le selezioni vengono invertite spostando
all’inizio le piu’ efficienti
πlname
Pname e’ chiave Î
selettivita’ ottima
X
Employee
Lname, ssn, bdate
(birthdate), ...
Project
Pname, pnumber, plocation, ...
Works-on
Essn, pno, hours
σpname=‘x’
2. COMM
Query Optimization – @ Carlo Batini 2006
4 –Il Prodotto cartesiano + la selezione sono
sostituiti da join
πlname
σ
Regola adottata
joinESSN=SSN 5. EQIUV X
m
X
m
X
m
σpname=‘x’
Project
join pnumber = PNO
σ BDATE>’dec-31-1957’
σ pnumber = PNO
Works_on
Project
Regola adottata
σESSN=SSN
1
σX
3. COMM
σ ESSN=SSN
essn = ssn and BDATE>’dec-31-1957’
Regola adottata
σ e Join
σ BDATE>’dec-31-1957’
nxm
n
Works_on
Emp
Employee
Lname, ssn, bdate
(birthdate), ...
Project
Pname, pnumber, plocation, ...
Works-on
Essn, pno, hours
Query Optimization – @ Carlo Batini 2006
σpname=‘x’
Project
Works_on
Emp
Employee
Lname, ssn, bdate
(birthdate), ...
Project
Pname, pnumber, plocation, ...
Works-on
Essn, pno, hours
Query Optimization – @ Carlo Batini 2006
16
5- Le proiezioni vengono spinte in basso
πlname
joinESSN=SSN
πESSN
πESSN, PNO
σpname=‘x’
Project
4. Ottimizzazione basata sui costi
πSSN, lname
join pnumber = PNO
πpnumber
Regola adottata
6. COMM π e Join
Works_on
σ BDATE>’dec-31-1957’
Emp
Employee
Lname, ssn, bdate (birthdate), ...
Project
Pname, pnumber, plocation, ...
Works-on
Essn, pno, hours
Query Optimization – @ Carlo Batini 2006
Ottimizzazione basata sui costi
y Un problema di ottimizzazione, i cui
parametri sono (quelli gia’ visti):
– La scelta dei metodi di accesso ai dati (e.g., scan
vs accesso a indice)
– L’algoritmo, ad es nel caso di join l’algoritmo di
join scelto tra quelli visti
– L’ordine delle operazioni (e.g., l’ordine dei join
elementari in un multi-join)
Query Optimization – @ Carlo Batini 2006
Esempio di albero di decisione
Ordine delle op.
Algoritmo
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Approccio per la ottimizzazione -1
y 1. Calcolo dei profili dimensionali delle
relazioni (vedi tra poco) e di formule
di costo approssimate per il calcolo
delle relazioni risultati intermedi del
calcolo
y 2. Costruzione di un albero di
decisione (vedi tra poco), in cui ogni
nodo corrisponde a una scelta e ogni
foglia corrisponde a un piano di
esecuzione
Query Optimization – @ Carlo Batini 2006
Profili dimensionali delle relazioni
y Elementi presenti nei profili
1. CARD(T): numero di record nella tabella T
2. SIZE(T): dimensione in byte di un record di T
(fissa, media)
3. SIZE(Aj,T): dimensione in byte di T.Aj
4. VAL(Aj,T): numero di valori distinti di T.Aj
5. MIN(Aj,T), MAX(Aj,T), valori minimo e max di Aj
6. Bfr (numero di blocchi) = # records / record in
blocco in T
y L’amministratore periodicamente calcola i valori
dei profili (primitiva update statistics)
Query Optimization – @ Carlo Batini 2006
17
Obiettivo (intermedio)
della valutazione basata sui costi
Approssimazioni nelle formule
Formulare ipotesi sulla dimensione dei risultati
intermedi prodotti dalla valutazione di operazioni
algebriche con un approccio statistico
Quindi, data T, o T1 e T2, vogliamo calcolare il
profilo di
yT’ = σAi=v (T)
yT’ = πA1…,An (T)
yT’ = T1 join A=B T2
yUniforme distribuzione dei dati nelle
tabelle
yAssenza di correlazione tra le varie
condizioni presenti nella interrogazione
y(es. Eta’ vs colore dei capelli)
yAssenza di informazione ulteriore sui
domini di valori
Query Optimization – @ Carlo Batini 2006
Profilo per selezione T’ = σAi=v (T)
yCARD(T’) = (1/ VAL(Ai, T)) * CARD(T)
ySIZE(T’) = SIZE(T)
yVAL(Ai, T’) = 1 (unico valore selezionato…)
yVAL(Aj, T’) j≠i: colori(CARD(T), VAL(Aj, T),
CARD(T’)) (vedi testo, facoltativo)
yMAX(Ai, T’) = MIN(Ai, T’) = v (ovvio)
yMAX(Aj, T’) e MIN(Aj, T’) j≠i : stessi valori di
MAX(Aj, T) e MIN(Aj, T) (… non ci sono
abbastanza informazioni)
Query Optimization – @ Carlo Batini 2006
Profilo per equijoin
T’ = T1 join
facolatativo
A=B
T2
yCARD(T’) = ( 1/ VAL(A, T1)) * CARD(T1) * CARD(T2)
ySIZE(T’) = SIZE(T1) + SIZE(T2)
yGli altri parametri mantengono gli stessi valori
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Profilo per proiezione T’ =
facoltativo
πA1…,An (T)
yCARD(T’) = MIN( CARD(T), Πj=1.,n VAL(Aj, T))
ySIZE(T’) = Σ i=1.,n SIZE(Aj, T)
yGli altri parametri mantengono gli stessi valori
Query Optimization – @ Carlo Batini 2006
Approccio per la ottimizzazione - 1
y1. Calcolo dei possibili piani di esecuzione
y2. Assegnazione a ogni piano di esecuzione
di un costo, secondo i modelli visti in
precedenza
y3. Scelta del piano di esecuzione con il
costo piu’ basso, con tecniche di ricerca
operativa (branch and bound)
ySi ottengono in genere soluzioni subottime
Query Optimization – @ Carlo Batini 2006
18
Altre tecniche
y1. Realizzazione ad hoc di strutture temporanee
y2. Pipelining, cioe’ possibilita’ di eseguire l’intero
albero delle operazioni per una parte delle tuple
estratte, invece che realizzare interamente ogni
operazione
y3. Parallelismo, cioe’ possibilita’ di eseguire parti
delle query indipendentemente con diversi
processori (lo vedremo nella parte 3)
Progettazione fisica di basi di dati
cenni
yTalvolta e’ necessario riscrivere i risultati in
memoria secondaria, cio’ aumenta i costi
Query Optimization – @ Carlo Batini 2006
Riassumendo, il problema generale e’..
Query Optimization – @ Carlo Batini 2006
Progettazione fisica di basi di dati
Schema logico: relazioni
Interrogazioni
SQL (e transazioni)
Carico di
Lavoro
Progettazione
fisica
Scelta ottima
rappr. fisica
delle tabelle
Progettazione fisica
Carico
applicativo
Schema fisico: relazioni + strutture di accesso
•La fase finale del processo di progettazione di basi di dati
•Input
•lo schema logico e informazioni sul carico applicativo
•Output
•schema fisico, costituito dalle definizione delle relazioni con le
relative strutture fisiche (e molti parametri, spesso legati allo
specifico DBMS)
Query Optimization – @ Carlo Batini 2006
Query Optimization – @ Carlo Batini 2006
Progettazione fisica nel modello relazionale
y La caratteristica comune dei DBMS relazionali è la
disponibilità degli indici:
– la progettazione fisica spesso coincide con la scelta
degli indici (oltre ai parametri strettamente dipendenti
dal DBMS)
y Le chiavi (primarie) delle relazioni sono di solito coinvolte
in selezioni e join: molti sistemi prevedono (oppure
suggeriscono) di definire indici sulle chiavi primarie
y Altri indici vengono definiti con riferimento ad altre
selezioni o join "importanti"
y Se le prestazioni sono insoddisfacenti, si "tara" il sistema
aggiungendo o eliminando indici
y È utile verificare se e come gli indici sono utilizzati con il
comando SQL show plan
Query Optimization – @ Carlo Batini 2006
19