Univerzita Pardubice Fakulta elektrotechniky a informatiky
´ ln´ı pra ´ ce na Semestra ´ zovy ´ ch Architektury databa ´m˚ syste u
ˇj Trakal Mate
Posledn´ı u ´prava: 8. listopadu 2010
ˇ ak) INADS 2010 (Z´
OBSAH
Obsah 1 Zad´ an´ı
2
2 Vypracov´ an´ı 2.1 Dotaz 1: Zjistˇete seznam poˇst v okrese Pardubice . . . . . . . . . . 2.1.1 Postup vykon´av´an´ı dotaz˚ u . . . . . . . . . . . . . . . . . . . 2.1.2 Zrychlen´ı dotazu . . . . . . . . . . . . . . . . . . . . . . . . 2.1.3 Postup vykon´av´an´ı dotaz˚ u po optimalizaci . . . . . . . . . . ˇ 2.2 Dotaz 2: Najdˇete PSC vˇsech ˇca´st´ı obc´ı v obci Hradec Kr´alov´e . . . 2.2.1 Postup vykon´av´an´ı dotaz˚ u . . . . . . . . . . . . . . . . . . . 2.2.2 Zrychlen´ı dotazu . . . . . . . . . . . . . . . . . . . . . . . . 2.2.3 Postup vykon´av´an´ı dotaz˚ u po optimalizaci . . . . . . . . . . 2.3 Dotaz 3: Zjistˇete, kolik procent dom´acnost´ı v okrese Kol´ın si nepˇreje vkl´adat reklamn´ı materi´aly . . . . . . . . . . . . . . . . . . . . . . . 2.3.1 Postup vykon´av´an´ı dotaz˚ u . . . . . . . . . . . . . . . . . . . 2.3.2 Zrychlen´ı dotazu . . . . . . . . . . . . . . . . . . . . . . . . 2.3.3 Postup vykon´av´an´ı dotaz˚ u po optimalizaci . . . . . . . . . .
2 2 2 3 3 4 4 4 5
Matˇej Trakal – fei.trtkal.net
1
5 6 7 7
ˇ ak) INADS 2010 (Z´
1
´ ´I 2 VYPRACOVAN
Zad´ an´ı
Vybran´e exekuˇcn´ı pl´any 3 dotaz˚ u zkop´ırujte jako obr´azky do pdf souboru a odevzdejte do 9.11.2010 vˇcetnˇe pˇres STAG – odevzd´av´an´ı prac´ı – blok Exekuˇcn´ı pl´any – t´ema Skupina (den v t´ ydnu t´ ydnu a hodina, kdy chod´ıte na cviˇcen´ı), oznaˇcen´ı souboru Prijmeni Jmeno dotazy Posty.pdf Vˇsechny 3 exekuˇcn´ı pl´any popiˇste, aby z popisu bylo zˇrejm´e: • jak´e druhy spojen´ı a pˇr´ıstup˚ u jsou pouˇzity (ˇcesky), • jak jdou operace za sebou, • jak´e indexy se pouˇz´ıvaj´ı a k ˇcemu. Minim´alnˇe jeden popisovan´ y exekuˇcn´ı pl´an mus´ı b´ yt nad dotazem, kter´ y spojuje minim´alnˇe 3 tabulky.
2
Vypracov´ an´ı
U popisk˚ u vynech´av´am v n´azvech tabulek a pˇr´ıpadn´ ych indexech n´apis CV04, pro zjednoduˇsen´ı a pˇrehlednost pr´ace.
2.1
Dotaz 1: Zjistˇ ete seznam poˇ st v okrese Pardubice
s e l e c t d i s t i n c t s 1 . ∗ from s t 2 2 3 1 2 . c v 0 4 p o s t a s 1 l e f t j o i n c v 0 4 c o b c e s 2 on s 1 . p s c = s 2 . p s c l e f t j o i n c v 0 4 o b c e s 3 on s 2 . i d o b c e = s 3 . i d o b c e where s 3 . k o d o k r e s u = ( s e l e c t k o d o k r e s u from s t 2 2 3 1 2 . c v 0 4 o k r e s where n a z o k r e s u = ’ Pa r dubi ce ’ );
2.1.1
Postup vykon´ av´ an´ı dotaz˚ u
1. Cel´e prohled´an´ı tabulky posta 2. Prohled´an´ı cel´e tabulky cobce 3. Cel´e prohled´an´ı tabulky okres s filtrem na n´azev okresu Pardubice 4. Hash spojen´ı vˇsech tabulek s ohledem na prim´arn´ı kl´ıˇce
Matˇej Trakal – fei.trtkal.net
2
ˇ ak) INADS 2010 (Z´
´ ´I 2 VYPRACOVAN
Obr´azek 1: Prvn´ı dotaz pˇred u ´pravou exekuˇcn´ıho pl´anu 2.1.2
Zrychlen´ı dotazu
Z obr´azku 1 je patrn´e, ˇze pro vykon´av´an´ı naz_okresu = ’Pardubice’ je pouˇzito pln´e prohled´an´ı tabulky. Vytvoˇren´ım indexu nad sloupeˇckem naz okresu se nemus´ı chodit pro v´ ysledek do tabulky a lze vyuˇz´ıt indexu, coˇz urychl´ı vykon´an´ı dotazu, viz obr´azek 2. 2.1.3
Postup vykon´ av´ an´ı dotaz˚ u po optimalizaci
1. Cel´e prohled´an´ı tabulky posta, 2. rychl´e prohled´an´ı indexu cobce, 3. prohled´an´ı indexu s unik´atn´ımi kl´ıˇci idx_naz_okresu, 4. prohled´an´ı tabulky okres dle index˚ u ˇr´adk˚ u, 5. prohled´an´ı cel´e tabulky obce, 6. hash spojen´ı vˇsech tabulek.
Matˇej Trakal – fei.trtkal.net
3
ˇ ak) INADS 2010 (Z´
´ ´I 2 VYPRACOVAN
Obr´azek 2: Prvn´ı dotaz po u ´pravˇe exekuˇcn´ıho pl´anu
2.2
ˇ vˇ Dotaz 2: Najdˇ ete PSC sech ˇ c´ ast´ı obc´ı v obci Hradec Kr´ alov´ e
select s3 . psc from c v 0 4 p o s t a s3 , c v 0 4 c o b c e s2 , c v 0 4 o b c e s 1 where s 1 . n a z o b c e = ’ Hradec Kr´a lov´e ’ and s 1 . i d o b c e = s 2 . i d o b c e and s 2 . p s c = s 3 . p s c order by s 3 . p s c ;
2.2.1
Postup vykon´ av´ an´ı dotaz˚ u
1. Prohled´an´ı unik´atn´ıho indexu psc_pkx, 2. prohled´an´ı indexu metudou range scan, 3. pˇr´ıstup do tabulky cobce dle indexu ˇr´adk˚ u, 4. prohled´an´ı cel´e tabulky obce s restrikc´ı na n´azev obce Hradec Kr´alov´e, 5. seˇrazen´ı tabulky. 2.2.2
Zrychlen´ı dotazu
Z obr´azku 4 je patrn´e, ˇze pˇrid´an´ım index˚ u nad sloupce, kde nast´aval samotn´ y pˇr´ıstup do tabulky a jej´ı u ´pln´e prohled´an´ı, se v´ ysledky zlepˇsily. Matˇej Trakal – fei.trtkal.net
4
ˇ ak) INADS 2010 (Z´
´ ´I 2 VYPRACOVAN
Obr´azek 3: Druh´ y dotaz pˇred u ´pravou exekuˇcn´ıho pl´anu 2.2.3
Postup vykon´ av´ an´ı dotaz˚ u po optimalizaci
1. Prohled´an´ı unik´atn´ıho indexu psc_pkx, 2. range scan prohled´an´ı indexu idx_cobce, 3. range scan nad indexem idx_naz_okresu, 4. prohled´an´ı tabulky obce dle index˚ u ˇr´adk˚ u, 5. seˇrazen´ı tabulky.
2.3
Dotaz 3: Zjistˇ ete, kolik procent dom´ acnost´ı v okrese Kol´ın si nepˇ reje vkl´ adat reklamn´ı materi´ aly
with ws1 as ( s e l e c t d i s t i n c t s 1 . psc , s 1 . pocdom rpm from c v 0 4 p o s t a s 1 l e f t j o i n c v 0 4 c o b c e s 2 on s 1 . p s c = s 2 . p s c l e f t j o i n c v 0 4 o b c e s 3 on s 2 . i d o b c e = s 3 . i d o b c e where s 3 . k o d o k r e s u = ( s e l e c t k o d o k r e s u from c v 0 4 o k r e s where n a z o k r e s u = ’ K o l´ın ’ ) ), ws2 as ( s e l e c t count ( ∗ ) celkem from c v 0 4 s c h r a n k y s 1
Matˇej Trakal – fei.trtkal.net
5
ˇ ak) INADS 2010 (Z´
´ ´I 2 VYPRACOVAN
Obr´azek 4: Druh´ y dotaz po u ´pravˇe exekuˇcn´ıho pl´anu l e f t j o i n ws1 s 2 on s 1 . p s c = s 2 . p s c ) , ws3 as ( s e l e c t sum( ws1 . pocdom rpm ) celkem rpm from ws2 , ws1 ) s e l e c t ( ws2 . celkem / ( ws2 . celkem−ws3 . celkem rpm ) ) ∗ 1 0 0 NO RPM from ws2 , ws3 ;
Obr´azek 5: Tˇret´ı dotaz pˇred u ´pravou exekuˇcn´ıho pl´anu
2.3.1
Postup vykon´ av´ an´ı dotaz˚ u
´ e prohled´an´ı tabulky cobce, 1. Upln´ Matˇej Trakal – fei.trtkal.net
6
ˇ ak) INADS 2010 (Z´
´ ´I 2 VYPRACOVAN
2. u ´pln´e prohled´an´ı tabulky obce a jejich spojen´ı, 3. u ´pln´e prohled´an´ı tabulky posta, 4. spojen´ı tˇechto tabulek na unik´atnost. 5. Druhou ˇca´st dotazu pro zjednoduˇsen´ı neuv´ad´ım (vych´az´ım z obr´azk˚ u), 6. jej´ı seˇrazen´ı. 2.3.2
Zrychlen´ı dotazu
Z obr´azku 6 je patrn´e, ˇze pˇrid´an´ım indexu nad nejn´aroˇcnˇejˇs´ı ud´alost (´ upln´e proch´azen´ı tabulky cobce) zmˇen´ı jej´ı u ´pln´e proch´azen´ı za rychl´e proch´azen´ı indexu, ˇc´ımˇz klesne cena v´ ypoˇctu. 2.3.3
Postup vykon´ av´ an´ı dotaz˚ u po optimalizaci
1. Cel´e prohled´an´ı tabulky posta, 2. rychl´e prohled´an´ı indexu idx_cobce, 3. u ´pln´e prohled´an´ı tabulky obce (coˇz nech´apu, jelikoˇz je nad vˇsemi sloupci index), 4. spojen´ı dle unik´atnosti z´aznam˚ u.
Obr´azek 6: Tˇret´ı dotaz po u ´pravˇe exekuˇcn´ıho pl´anu
Matˇej Trakal – fei.trtkal.net
7
