A
gyakorlati
foglalkozás és az
előadások
anyagára épül.
1-5.EA:
Vizsgakérdések:
1-5.vizsgakérdések voltak az I.ZH
papíros
részében
6.EA-tól: Vizsgakérdések:
6.vizsgakérdéstől -> a II.ZH
papíros
részében lesznek.
6.EA: Szabály
és költség alapú
optimalizálás
(ism.5.EA: Relációs algebrai műveletek -
szabályok algebrai_opt.ppt
(KA) 100-114.o.
Végrehajtási terv
átalakítása szabályok
alapján algebrai_opt.ppt
(KA) 115-126.o.
Kiterjeszett relációs
algebrai műveletekre vonatkozó
azonosságok a táblára felírva!!!
Az egyes műveletek végrehajtási költségei
optimization-hu.ppt
(KA) 1-22 oldal
7.EA: Költség alapú
optimalizálás
Az egyes műveletek végrehajtási költségei optimization-hu.ppt
(KA) 23-46 oldal A Q(A,B) JOIN R(B,C) JOIN S(C,D)
háromféle kiszámítási módja
és költsége sok számolás a
táblára felírva!!!
A 6-7. előadáshoz
(6-9.vizsgakérdésekhez)
kapcsolódó
további
olvasmány:
Molina-Ullman-Widom: Adatbázisrendszerek
megvalósítása, Panem, 2001.
6.fej. Lekérdezések
végrehajtása, műveletek I/O
költsége
7.fej. Szabály- és költség
alapú
optimalizálás
Előkészítés:
- A gyakorlatokon az oradb
adatbázisban az sqldeveloper-rel
dolgozunk, ahol
a SELECT-teket
az Execute
Statement
(F9)
hajtjuk végre és
az eredményt
a Result
ablakban látjuk, a
végrehajtási terveket az Execute
Explain
Plan
(F6)
segítségével
készítjük el,
annak eredményét pedig az Explain
ablakban találjuk.
- Mivel a Result
és az Explain ablakok
eredményét nem tudjuk zh-ban beküldeni,
ezért a zh-n Run
Script
(F5)-tel
futtassuk a SELECT
utasítást és az eredményt
a Script
Output-ból
másoljuk át a beadandó egyszerű
szöveges .txt állományba.
- A
végrehajtási tervek
fastruktúrájának szöveges
megjelenítéséhez (a
beküldéshez)
a végrehajtási terveket az Explain Plan
Output-ból tudjuk bemásolni, de ehhez:
Hozzuk létre a
PLAN_TABLE
nevű táblát az utlxplan.sql
scriptnek a futtatásával!
A1.)
Végrehajtási tervek
előállítása és
kiíratása hierarchikus
kérdés
segítségével
-- Történetileg
érdemes
áttekinteni a végrehajtási tervek
előállításának különböző
módjait.
-- Két (2x2)
módszert próbálunk ki
(az sqldeveloperből a Run Script
használatával) illetve
-- itt
ennél az első A.)
módszernél az sqlplus
kliens
programból is próbáljuk ki, amely
-- a panda.inf.elte.hu
(pandora,
panda, valerie) gépeken fut. Használata:
lásd AB-elérés
-- 1.lépés:
Készítsük el az
utasítások végrehajtási
tervét a
a
PLAN_TABLE
táblába.
-- Figyeljünk oda, hogy minden
lekérdezéshez statement_id='utasitas_egyedi_neve'
-- 'utasitas_egyedi_neve'helyett
minden feladatnál új egyedi
nevet adjunk az utasításnak,
-- például 'ut1', 'ut2',
stb... és arra is figyeljünk, hogy csak egyszer futtassuk a
parancsot!
EXPLAIN PLAN SET statement_id='utasitas_egyedi_neve'
-- itt írjuk át ut1
INTO
plan_table FOR
SELECT ...
-- ide írjuk be a SELECT vagy a DML utasítást
-- 2.lépés:
Ezután
vegyük
is ki a terveket a
táblából a megfelelő
lekérdezéssel.
-- Emlékeztetőül a
hierarchikus
szerkezetű adatok lekérdezéseSELECT
-- utasítás
segítségével: a hierarchia
gyökerét (gyökereit) a START
WITH-el,
-- a szülő és gyermek
sorok
közti kapcsolatot a CONNECT
BY-al kell megadni,
--
Figyeljünk oda, hogy a
végrehajtási tervek
előállításánál
amit a
fenti EXPLAIN
PLAN
-- parancsban használtunk
az utasításoknak egyedi nevet
(például 'ut1', 'ut2',
stb...),
-- ugyanezt a nevet használjuk itt a
tervek
megjelenítésénél is (és
kétszer is be kell írni)!
SET PAGESIZE 40 SET
LINESIZE 132 COLUMN
terv FORMAT
A50 COLUMN
feltetel
FORMAT A80 TRUNCATED COLUMN
"feltetel
(access--filter)" FORMAT A80 TRUNCATED
SELECT
LPAD(' ', 2*(level-1))||operation||' + '||options||' + '||object_name
terv
FROM plan_table
START WITH id = 0
AND statement_id = 'utasitas_egyedi_neve'
-- egyedi neve szerepel
CONNECT BY PRIOR
id = parent_id AND statement_id = 'utasitas_egyedi_neve' -- meg itt
ORDER SIBLINGS BY
position;
A2.) valamint a PLAN_TABLE további hasznos oszlopai is
kiírathatók
-- 1.lépés:
Készítsük el az
utasítások végrehajtási
tervét a
a
PLAN_TABLE
táblába.
EXPLAIN PLAN SET statement_id='utasitas_egyedi_neve'
-- itt írjuk át ut1
INTO
plan_table FOR
SELECT ...
-- ide írjuk be a SELECT vagy a DML utasítást
-- 2.lépés:
Ezután
vegyük
is ki a terveket a
táblából a megfelelő
lekérdezéssel.
-- A
PLAN_TABLEtábla
további hasznos oszlopai is kiírathatók
az alábbi lekérdezéssel,
-- ahol még
az első eredményoszlop (terv) hosszát is
korlátozzuk 50 karakterre, hogy
-- könyebben lehessen szöveges fájlba
menteni.
SELECT
SUBSTR(LPAD(' ', 2*(level-1))||operation||' + '||options||' +
'||object_name, 1, 50)
terv,
time, cost, io_cost -- cardinality, bytes,
cpu_cost
FROM plan_table
START WITH id = 0
AND statement_id = 'utasitas_egyedi_neve'
-- egyedi neve szerepel
CONNECT BY PRIOR
id = parent_id AND statement_id = 'utasitas_egyedi_neve' -- meg itt
ORDER SIBLINGS BY
position;
B1.)
Végrehajtási tervek
előállítása és
kiíratása dbms_xplan package
segítségével
-- 1.lépés:
Készítsük el az
utasítások végrehajtási
tervét a
PLAN_TABLE
táblába:
EXPLAIN
PLAN [SET
statement_id='utasitas_egyedi_neve'] [INTO
sajat_plan_table]
FOR SELECT ...
-- 2.lépés: A
végrehajtási tervek
megjelenítése a
dbms_xplan package
segítségével
set markup html preformat on
SELECT
plan_table_output
FROM
table(dbms_xplan.display('saját_plan_table','utasitas_egyedi_neve','all'));
-- paraméterek: első két
paraméter
lehet
null
vagy
tábla_név
vagy
statement_id,
-- harmadik paraméter pedig a
részletesség
(basic, typical, all, serial)
set
markup html
preformat off
B2.) vagy egyszerűen csak (Ez
a két
utasítás a dolgozatlapon is szerepel majd
emlékeztetőként)
-- 1.lépés:
Készítsük el az
utasítások végrehajtási
tervét
a
PLAN_TABLE
táblába:
EXPLAIN
PLAN FOR SELECT ...
-- 2.lépés: A
végrehajtási tervek
megjelenítése a
dbms_xplan package
segítségével:
SELECT
plan_table_output FROM table(dbms_xplan.display);
_______________________________________________________________
1.feladat:
Lekérdezések és
végrehajtási tervek
(index nélkül illetve indexek
létrehozásával)
az Oracle EMP, DEPT,
SALGRADE alaptáblái alapján
- Az ORAUSER
felhasználó
EMP,DEPT
és SALGRADE
(fizetési kategóriák)
tábláiból hozzunk
létre saját példányokat
(lásd script
- fontos,
hogy itt a lap tetején
leírtak szerint hozzuk
létre az ORAUSER
vagy SILA felhasználó
meglévő tábláiból
a saját
példányainkat, vagy ha
közvetlenül hozzuk létre, akkor
vegyük ki a
scriptből
a megszorításokat), mert
először megszorítások
és indexek
nélkül
fogjuk használni
a saját
tábláinkat, és csak
később kell hozzá megadni
megszorításokat
és indexeket.
- A saját EMP,DEPT,SALGRADE táblákra vonatkozóan
adjunk meg lekérdezéseket
és a hozzájuk
tartozó
végrehajtási tervek fa
struktúráját.
Adjunk meg szűrőfeltételeket,
különböző
összekapcsolásokat
(például külső joint is), majd adjunk
meg
bonyolultabb
lekérdezéseket,
vagyis nézzük meg
az alkérdéseket (NOT IN
és NOT
EXISTS
is),
a korrelált
alkérdéseket,
csoportosítást, group by .. having ..
záradékokat, halmaz- és
multihalmaz műveleteket,
továbbá az inline
nézeteket tartalmazó
lekérdezéseket, stb,
és állítsuk elő
(mentsük le szöveges fájlba) a
hozzájuk
tartozó végrehajtási terveket.
-
Lekérdezésre példa: Adjuk
meg azoknak az
osztályoknak a nevét
és telephelyét (LOC),
amelyeknek van
olyan dolgozója, aki hivatalnok (UPPER(JOB)=CLERK)
és akinek
a fizetése (SAL)
az 1-es fizetési
kategóriába esik (vagyis GRADE=1
sorban megadott LOSAL
és HISAL
értékek közé) és
már legalább 20 éve
munkában áll (HIREDATE).
- Ezután hozzunk létre
olyan indexeket
a táblákhoz, amelyeket a fenti
lekérdezésekben
használni tud a rendszer, majd
adjuk meg a
fenti lekérdezésekhez a
rendszer által
létrehozott új
végrehajtási terveket (most már az
indexek használatával).
- Lásd még a tervek1.txt
példáit
-- egyszerűbb és bonyolultabb
lekérdezések
végrehajtási tervei, valamint
különböző
megszorítások
és indexek hozzáadásával
milyen változások
történnek
ezeken a végrehajtási terveken? _______________________________________________________________
2.feladat:
Lekérdezések
és
végrehajtási
tervek
(megváltoztatása hintek
segítségével) NIKOVITS.CIKK,
SZALLITO, PROJEKT,
SZALLIT táblái alapján - A
lekérdezésekhez NIKOVITS
felhasználó tulajdonában levő
táblákat használjuk:
CIKK(ckod,
cnev, szin, suly)
SZALLITO(szkod,
sznev, statusz, telephely)
PROJEKT(pkod,
pnev, helyszin)
SZALLIT(szkod,
ckod, pkod, mennyiseg, datum)
- Vagyis most ne hozzunk létre saját
példányokat, hanem egy adott séma
tábláit
használjuk a
lekérdezéseknél,
a táblák elérése: NIKOVITS.<táblanév>
- A táblákhoz
különböző
indexek is vannak
létrehozva, ezek tulajdonságait
a katalógusokból
(dba_indexes)
nézhetjük meg, amikor
szükségünk van rá.
- NIKOVITS
felhasználó tulajdonában levő
táblákra adjuk meg a
lekérdezést
és a hozzá tartozó
végrehajtási terv fa
struktúráját
(mentsük is le
szövegfájlba).
- Adjuk meg a piros cikkekre vonatkozó
szállitások
összmennyiségét. Ezt a
feladatot a következő
héten folytatjuk a hintek
hozzáadásával!
- Lásd még a tervek2.txt
példáit, más felhasználó
tábláit és indextábláit
használjuk
az egyszerűbb és bonyolultabb lekérdezések
végrahajtási tervének
előállításánál. Beadandó
feladat -
7gyak.
Kérem, hogy a gyakorlatok
lekérdezéseit és
azok végrehajtási terveit (vagyis az
"EXPLAIN
PLAN OUTPUT"-ot, ahogy az Oracle doksikban van a
példáknál, mint itt)
mentsék
el egy "<ehakod>_7gy.txt" nevű szöveges
fájlba, ahol az
<ehakod>
helyére
a saját ETR
azonosítóját
.ELTE nélkül írja be. Ezt a .txt
fájlt az
óra végén vagy
legkésőbb a 8.gyakorlatot közvetlenül
megelőző szerda
éjfélig
küldjék el e-mailben.
A beadandó célja az
önálló gyakorlás,
lásd Gyakorlati
követelmények #Beadandó.
Vissza
az AB2
gyakorlat
oldalára
Vissza
a Kezdőlapra