Evaluacija poizvedb

1. Evaluacija poizvedb Predavanje 8

2. Pregled evaluacije poizvedb • Kaj je program v SUPB? • Drevo operacij relacijske algebre. • Izberemo lahko algoritem za vsako posamezno operacijo. • Drevo iteratorjev. • Vsaka operacija je implementirana z iteratorjem. • Vmesnik iteratorja: open(), next(), close() • Klic operacije next() sproži evaluacijo operacije next() na otrocih operacije; nad rezultati izvede svojo kodo. • Dobimo drevesno strukturiran cevovod po katerem se pretakajo n-terice.

3. Primer drevesa operacij Projekcija mime Selekcija ocena > 5 mid=100 Stik mid=mid Relaciji Mornarji Rezervacije

4. Evaluacija drevesa iteratorjev • Kaj optimiziramo? • Pretok – št. pretočenih n-teric -- naj bo čim manjše. • Velikost n-teric naj bo čim manjša – navzgor ne prenašamo atributov, ki jih ne potrebujemo v višjem delu drevesa. • Učinkovito izvajanje iteratorja -- programa dane operacije. • Kako optimiziramo izvajanje? • Preiskovanje prostora rešitev – ekvivalentnih izrazov. • Izračun ocene plana je zelo pomemben. • Samo optimizacijo si bomo ogledali na naslednjem predavanju (9). • Optimizator System-R (IBM) – uporaba dinamičnega programiranja. • Predavanje 8 • pregled algoritmov za izvajanje operacij.

5. Pogosto uporabljene tehnike • Algoritmi za evaluacijo rel. operacij pogosto uporabljajo nekaj enostavnih idej: • Indeksiranje:Uporaba pogojev iz stavka WHERE za izbiro majhnega št. n-teric pri selekciji in stikih. • Iteracija:Včasih hitreje pregledamo vse n-terice čeprav je na razpolago indeks. • Včasih je koristno izvesti iteracijo po podatkovnih vpisih indeksa namesto na sami tabeli. • Particije:Velikokrat koristi razdeliti problem na več enakih delov – s tem zamenjamo izvajanje časovno potratnih operacij z podobnimi operacijami nad manjšim številom n-teric. * Bodimo pozorni na te tehnike pri opisu izvajanja operacij!

6. Statistike in katalogi • Podatki o relacijah in indeksih v podatkovni bazi. • Katalogiobičajno vsebujejo: • # n-tericza vsako relacijo. • # strani za vsako relacijo. • # št. različnih vrednosti za ključ indeksa. • # št. strani za vsak indeks. • višina indeksa, najnižja/najvišja vrednost ključa za vsak drevesni indeks. • Katalogi se ažurirajo periodično. • Sprotno ažuriranje je preveč drago. • Ker je veliko pribljižkov je majhna nekonsistenca OK. • Včasih so shranjene bolj podrobne vrednosti. • Npr. število posamezni vrednosti za atribut relacije.

7. Metode dostopa • Metoda dostopaje alg. za iskanje n-teric relacije: • Pregled datoteke. • Dostop z indeksom, ki se ujema z atributi v pogoju izbire. • Drevesni indeks: • se ujema z atributi v pogoju izbire v primeru da so atributi prefiks iskalnega ključa. • Primer: drevesni indeks nad <a, b, c>se ujemaziskalnim pogojema=5 AND “b=3” in z “a=5 AND b>6”, toda ne tudi z ”b=3”. • Razpršilni indeks: • se ujemaz atributi v pogoju oblike atribut=vrednost za vsak atribut iz iskalnega ključa indeksa. • Primer: razpršilni indeks nad <a, b, c> se ujema s pogojem“a=5 AND b=3 AND c=5”; ne ujema pa se s pogoji “b=3”, “a=5 AND b=3”, ali“a>5 AND b=3 AND c=5”.

8. Selekcija (dan<8/9/94 ANDmime=‘Pavel’) ORlid=5 ORmid=3 • Pogoj izbire je najprej preveden vkonjuktivno normalno obliko(KNO): (dan<8/9/94 ORlid=5 ORmid=3 ) AND (mime=‘Pavel’ ORlid=5 ORmid=3) • Obravnavamo samo primere brez OR; knjiga predstavlja tudi splošne primere.

9. Osnovni pristop • Algoritem: • Poišči najbolj selektivne metode dostopa in z njimi poišči n-terice. • Ovrednoti preostale pogoje nad izbranimi n-tericami. • Selektivnost pogoja: • Delež relacije, ki je rezultat selekcije z danim pogojem. • Čim bolj je selektiven pogoj manjši je rezultat selekcije. • Selektivnost metode dostopa: • Metoda dostopa izraza za katerega ocenimo, da bo zahtevala najmanjše število prenosov blokov iz diska. • Selektivnost metode dostopa upošteva selektivnost pogoja ter samo metodo dostopa.

10. Osnovni pristop • Komentarji: • Izbira n-teric z najbolj selektivnimi potmi optimizira število izbranih n-teric • Preostali pogoji dodatno izberejo podmnožico n-teric izbranih z najbolj selektivnimi izrazi. • Preostali pogoji ne vplivajo na št. strani prebranih iz diska. • Primer: • Primer izraza: dan<8/9/94 AND lid=5 AND mid=3. • Lahko uporabimo B+ drevoza izraz “dan<8/9/94” –je najbolj selektiven. • Nato uporabimo pogoj “lid=5 and mid=3”za selekcijo iz izbranih z prejšnjim pogojem. • Podobno: lahko uporabimo razpršilni indeks na <lid, mid>;preveriti moramo še “dan<8/9/94”.

11. Projekcija SELECTDISTINCT R.mid, R.lid FROMRezervacije R • Primer: • Enostavna rešitev brez izločitve duplikatov: • Pregled vseh n-teric relacije in izbor atributov. • Najdražja operacija je odstranitev duplikatov. • SUPB ne odstranijo duplikate, če ni specificirana ključna beseda DISTINCT v stavku SELECT. • Sortiranje: • Sortiraj po <mid, lid> in odstrani duplikate. • Optimizacija: odstrani nepotrebne podatke med sortiranjem.

12. Projekcija SELECTDISTINCT R.mid, R.lid FROMRezervacije R • Primer: • Uporaba razpršilnega indeksa: • S kreacijo razpršilnega indeksa nad <mid, lid> dobimo particije; funkcija h1. • Preberi vsako particijo v din. pomnilniku, sortiraj tabelo v din. pomnilniku ter eliminiraj duplikate. • Če se particije prevelike rekurzivno ponovi postopek z razpršilno funkcijo h2. • Če že obstaja indeks z R.midin R.lidv iskalnem ključu enostavno sortiramo podatkovne vpise.

13. Stik z vgnezdeno zanko foreach tuple r in R do foreach tuple s in S do if ri == sj then add <r, s> to result • Za vsako n-terico v zunanji relaciji pregledamo celotno relacijo notranje relacije S. • Delo z diskovnimi bloki: • Za vsako stran R preberi vse strani relacije S in izpiši n-terice <r,s>, ki se ujemajo. • V primeru, da so podatkovni zapisi porazdeljeni po različnih straneh je ta pristop veliko hitrejši.

14. Stik z indeksom foreach tuple r in R do foreach tuple s in S where ri == sj do add <r, s> to result • Če obstaja indeks na eni izmed relacij ga uporabimo za notranjo zanko. • Za vsako n-terico iz R z indeksom poiščemo n-terice, ki se ujemajo v S. • Cena za preverjanje vsake n-terice iz R je branje: • 1.2 strani v primeru razpršilnega indeksa, • 2-4 v primeru B+ drevesa. • Cena iskanja n-teric v S je v veliki meri odvisna od povezanosti S z relacijo – št. prebranih strani se precej zmanjša.

15. Stik z zlivanjem • Algoritem: • Sortiraj R in S po atributih stika. • Pregled (scan) sortiranih tabel z zlivanjem. • Izpis parov n-teric, ki se ujemajo. • Zlivanje: • “R-zapis = S-zapis” pomeni, da se zapisa ujemata v atributih stika. • Ponavljaj. • Beri R dokler R-zapis >= S-zapis. • Beri S dokler S-zapis >= R-zapis. • Izpiši vse pare <r,s>, ki se ujemajo v vrednostih atributov stika.

16. Stik z zlivanjem • Zlivanje bolj natančno. • Relacija R je pregledana enkrat. • Eni n-terici iz R ustreza skupina n-teric v S tako da je R-zapis = S-zapis. • Za vsako n-terico iz R: • Preglej vse n-terice iz skupine S, kjer R-zapis = S-zapis. • Izpiši pare <r,s>, ki se ujemajo. • Število pregledanih n-teric. • Ocena: |R| +|S|*(1+<pov. selektivnost atributov stika v S>) • Kompleksnost stika z zlivanjem. • |R|*log |R|+|S|*log |S|+|R| +|S|*C • C ≈ 1.1

17. Optimizator SUPB “System R” • System R je nastal leta 1972 (IBM). • Vpliv: • Najbolj razširjen algoritem za optimizacijo. • Deluje dobro za < 10 stikov. • Ocena plana izvajanja: • Vedno približek. • Statistika je uporabljena za ocene operacij in velikost rezultatov. • Considers combination of CPU and I/O costs. • Prostor planov izvajanja: • Prostor je omejen na “left-deep” plane. • Notranji operand stika je vedno lahko uporabljen brez začasne relacije. • Izognemo se kartezičnim produktom.

18. Cena planov izvajanja • Za vsak plan je potrebno narediti oceno: • Potrebno je narediti oceno hitrosti izvajanja vsake operacije v drevesu. • Odvisna je od kardinalnosti vhodnih relacij. • Pogledali smo si že ocene za nekatere operacije, npr. pregled tabele, indeksni dostop, itd. • Potrebno je narediti oceno velikosti rezultata vsake posamezne operacije drevesa! • Uporabi podatke o vhodnih relacijah. • Za selekcije in stike predpostavi neodvisnost pogojev.

19. Ocena velikosti rezultatov operacij Poizvedba: • Maksimalno število n-teric: • Produkt kardinalnosti relacij v FROM stavku. • Selektivnost izraza stavka WHERE: • Selektivnost izraza je sestavljena iz selektivnosti vseh pogojev. • Selektivnost pogoja (SP) = delež relacije, ki je rezultat selekcije. • Kardinalnost rezultata • Max # n-teric * produkt vseh SP. SELECTseznam-izbire FROMseznam-relacij WHERE term1 AND ... ANDtermk

20. Ocene selektivnosti pogojev • Ocene posameznih tipov pogojev: • Predpostavka, da so pogoji med seboj neodvisni. • Pogojatr=vrednostima SP = 1/ Nključev(I); I je indeks na atr. • Pogojatr1=atr2 imeSP = 1/MAX(Nključev(I1),Nključev(I2)) • pogojatr>vrednostimeSP = (High(I)-value)/(High(I)-Low(I)) • Selektivnost pogoja odseva vpliv pogoja na zmanjšanje velikosti rezultata. • Kardinalnost rezultata • Max # n-teric * produkt vseh SP.

21. Shema za zgled Mornarji (mid: integer, mime: string, ocena: integer, star: real) Rezervacije (mid: integer, lid: integer, dan: dates, rime: string) • Podobno kot stara shema; rimedodana. • Rezervacije: • Velikost zapisa = 40 zlogov, 100 zapisov na stran, 1000 strani. • Mornarji: • Velikost zapisa = 50 zlogov, 80 zapisov na stran, 500 strani.

22. mime ocena > 5 lid=100 lid=lid Mornarji Rezervacije (Pregled) mime (Pregled) ocena > 5 lid=100 (Vgnezdena zanka) lid=lid Mornarji Rezervacije RA Drevo: Zgled SELECTM.mime FROMRezervacije R, MornarjiM WHERE R.lid=S.lid AND R.lid=100 ANDM.ocena>5 • Najslabši plan! • Cena: 500+500*1000I/O blokov • Ne uporabi več možnosti: selekcije bi lahko “spustili” proti listom. • Ni uporabe indeksov. • Cilj optimizacije: Poiskati bolj optimalen plan, ki izračuna isti rezultat. Plan:

23. (Pregled) mime (Zlivanje) mid=mid (Pregled; (Pregled shrani v shrani v cena > 5 lid=100 temp T2) temp T1) Rezervacije Mornarji Plan 1(brez indeksov) • Osnovna razlika: spusti selekcije. • Cena je veliko bolj ugodna. • Tabela po selekciji na tabeli Rezervacije je majhna. • Tabela po selekciji na tabeli Mornarji je precej manjša kot original. • Stik se izvaja nad majhnimi tabelami. • Ne prenašamo vseh atributov n-teric. • Prenašajo se samo tisti atributi, ki so potrebni. • Primer: T1 vsebuje samo še atribut mid, T2 pa samo atributa mid in mime. • Velikost n-teric tudi prispeva k ceni.

24. (Pregled) mime (Zlivanje) mid=mid (Pregled; (Pregled shrani v shrani v cena > 5 lid=100 temp T2) temp T1) Rezervacije Mornarji Plan 1(brez indeksov) • Cena plana: • Pregled Rezervacij (1000) + vpiši temp T1 (10 strani, 100 ladij, enakomerna porazdelitev). • Pregled Mornarjev (500) + vpiši temp T2 (250 strani, 10 ocen). • Sortiranje T1 (2*2*10), sortiranje T2 (2*3*250), zlivanje (10+250) • Skupaj: 3560 I/O blokov.

25. Plan 2(z indeksi) (Pregled) sname • Pohitritev selekcije nad Rezervacij. • Atribut lid v tabeli Rezervacije zelo selektiven (ena sama ladja). • Uporaba razprš. indeksa omogoča branje v povprečju samo 10% redundantnih podatkov. (Pregled) cena > 5 (Vgn. zanka z ind.) mid=mid (Uporaba Sailors lid=100 razprš. indeksa; • Pohitritev stika. • Indeks za mid na tabeli Mornarji. • Majhno št. n-teric “notranje” relacije pri stiku. • Projekcija se pogosto “pridruži” selekciji. • Cena: 1210 I/O blokov (ne bomo delali ocene) shrani v temp) Rezervacije

26. Povzetek • Obstaja veliko različnih alternativnih algoritmov za evaluacijo relacijskih operacij. • Poizvedba se ovrednoti s prevedbo v drevo in evaluacijo operacij drevesa. • Uporabnik mora razumeti optimizacijo poizvedb, da bi lahko razumel vpliv načrtovalskih odločitev na izvajanje poizvedb aplikacije. • Relacijski SUPB vsebujejo ukaze za izpis plana poizvedb. • Dva dela optimizacije vprašanj: • Pregledati je potrebno vse alternativne plane izvajanja poizvedbe. • Iskalni prostor je potrebno omejiti zaradi velikega števila ekvivalentnih poizvedb. • Potrebno je narediti oceno izvajanja vsakega plana poizvedbe. • Oceniti je potrebno velikost rezultatov ter hitrost izvajanja operacij. • Ključni elementi: Statistike, indeksi, implementacije operacij.