| Informatikai
képzés: INFORMÁCIÓS
RENDSZEREK 10.GYAKORLAT: SQL 3.2.feladatok [dr. Hajas Csilla] |
|
Előadás: 09.ea.pdf (Adatbázis-kezelő rendszerek felépítése)
--- Ullman-Widom: Adatbázisrendszerek Alapvetés Tk.1.fejezet
--- --- --- --- --- ---
> 1.rész Metaadatbázis, adatszótár-nézetek. Adatbázisbeli objektumok
objektumok.txt + 01-DBA nézetek.pdf
- Milyen dolgok vannak az adatbázisban? Például táblák, nézetek, indexek, stb.
- Minden fel van sorolva a típusával együtt a DBA_OBJECTS adatszótárban.
- Minden adatbázisbeli objektumnak van egy egyedi objektum azonosítója (OBJECT_ID),
és amelyikhez tartozik szegmens, annak egy úgynevezett adatobjektum azonosítója is van
(DATA_OBJECT_ID). Amelyik objektumhoz nem tartozik szegmens, annak az adatobjektum
azonosítója NULL vagy 0, és ezeknek csak a definíciója van (szövegesen) tárolva a szótárban.
Ezen utóbbi objektumokra azt mondjuk, hogy tényleges tárolást nem igénylő objektumok.
Feladatok:
1.01. Milyen típusú objektumai vannak az orauser nevű felhasználónak az adatbázisban?
(a katalógustáblákban nagy betűvel vannak az adatok 'orauser' helyett 'ORAUSER')
1.02. Hány különböző típusú objektum van nyilvántartva az adatbázisban? Melyek ezek a típusok?
1.03. Melyek azok az objektum típusok, amelyek tényleges tárolást igényelnek, vagyis
tartoznak hozzájuk adatblokkok? (A többinek csak a definíciója tárolódik adatszótárban)
1.04. Kik azok a felhasználók, akiknek több mint 10 féle objektumuk van?
1.05. Kik azok a felhasználók, akiknek van triggere és nézete is?
1.06. Kik azok a felhasználók, akiknek van nézete, de nincs triggere?
1.07. Kik azok a felhasználók, akiknek több mint 40 táblájuk, de maximum 37 indexük van?
--- --- --- --- --- ---
> 2.rész Táblák és oszlopai, megszorítások
Táblák létrehozása, oszloptípusok.txt
Mely táblák vannak egy adott felhasználó tulajdonában?
- A DBA_TABLES adatszótár az összes táblát felsorolja, például HR séma táblái ezek:
SELECT table_name FROM dba_tables WHERE owner='HR';
Mit tudhatunk a táblák oszlopairól, milyen nevű és típusú oszlopai vannak, sorrendjük
- A DBA_TAB_COLUMNS adatszótár a táblák oszlopairól tartalmaz információt.
Hozzunk létre táblákat, majd keressük meg az adatszótár nézetekben, hogy
milyen információkat találhatunk a táblákról és a táblák oszlopairól.
Adatszótár nézetek: DBA_OBJECTS, DBA_TABLES és DBA_TAB_COLUMNS
Hozzunk létre táblákat megszorításokkal, majd nézzük meg hogyan jelennek meg
az oszlopok és a megszorítások a DBA_CONSTRAINTS katalógustáblában is.
Feladatok:
2.01. Hány oszlopa van a sila.emp táblának?
2.02. Milyen típusú a sila.emp tábla 6. oszlopa?
2.03. Adjuk meg azoknak a tábláknak a tulajdonosát és nevét, amelyeknek van
'Z' betűvel kezdődő oszlopa.
2.04. Adjuk meg azoknak a tábláknak a nevét, amelyeknek legalább 8 darab
dátum tipusú oszlopa van.
2.05. Adjuk meg azoknak a tábláknak a nevét, amelyeknek 1. es 4. oszlopa is
VARCHAR2 tipusú.
--- --- --- --- --- ---
> 3.rész ROWID
18 karakteren írodik ki, a következő formában: OOOOOOFFFBBBBBBRRR
OOOOOO - az objektum azonosítója
FFF - fájl azonosítója (táblatéren belüli relativ sorszám)
BBBBBB - blokk azonosító (a fájlon belüli sorszám)
RRR - sor azonosító (a blokkon belüli sorszám)
A ROWID megjelenítéskor 64-es alapú kódolásban jelenik meg.
Az egyes számoknak (0-63) a következő karakterek felelnek meg:
A-Z -> (0-25), a-z -> (26-51), 0-9 -> (52-61), '+' -> (62), '/' -> (63)
Pl. 'AAAAAB' -> 000001
SELECT rowid, empno, ename,
substr(rowid, 1, 6), DBMS_ROWID.rowid_object(ROWID),
substr(rowid, 7, 3), DBMS_ROWID.rowid_relative_fno(ROWID),
substr(rowid, 10, 6), DBMS_ROWID.rowid_block_number(ROWID),
substr(rowid, 16, 3), DBMS_ROWID.rowid_row_number(ROWID)
FROM sila.emp;
Feladatok:
3.01. Az SH felhasználó CUSTOMERS táblájának adatai hány blokkban helyezkednek el?
3.02. Az egyes blokkokban hány sor van?
--- --- --- --- --- ---
> 4.rész Indextáblák
Hozzunk létre egy vagy több táblához több különböző indexet!
Legyen köztük több oszlopos, csökkenő sorrendű, függvény alapú,
fordított kulcsú (reverse), bitmap index, lásd create index példák.txt
Állapítsuk meg ezeknek az indexeknek a különböző tulajdonságait
az alábbi katalógusokból:
DBA_INDEXES, DBA_IND_COLUMNS, DBA_IND_EXPRESSIONS
-- Oracle Példák 1/10.lecke és 2/1.lecke SQL DDL (megszorítások, indexek)
>> OracleDoc: SQL Language Reference >> innen CREATE INDEX példák
>> Alapfogalmak Oracle Database Concepts
például Concepts (11g) 3.fej. Indexek vagy Concepts (10g) 5.fej.Indexek
Feladatok:
4.01. Adjuk meg azoknak a tábláknak a nevét, amelyeknek van csökkenő
sorrendben indexelt oszlopa.
4.02. Adjuk meg azoknak az indexeknek a nevét, amelyek legalább 9 oszloposak
(vagyis a táblának legalább 9 oszlopát vagy egyéb kifejezését indexelik).
4.03. Adjuk meg az SH.SALES táblára létrehozott bitmap indexek nevét.
4.04. Adjuk meg azon kétoszlopos indexek nevét és tulajdonosát, amelyeknek
legalább az egyik kifejezése függvény alapú. Adjuk meg az egyikükre,
például az OE tulajdonában lévőre, hogy milyen kifejezések szerint vannak
indexelve a soraik (vagyis mi a függvény, ami alapján a bejegyzések készülnek).
--- --- --- ---
> 5. rész: Végrehajtási tervek előállítása
Beküldendő feladatoknál egyszerű szöveges .txt állományba bemásolva
1.) SELECT /*+ tipp lista */ ... vagyis a lekérdezés (hintekkel együtt),
2.) lekérdezés outputja ("SCIPT OUTPUT"-ból az eredménytábla első 5 sorát),
3.) végrehajtási tervek szöveges megjelenítése ("EXPLAIN PLAN OUTPUT"-ból)
-- Ehhez az 1.lépés: Készítsük el az utasítások végrehajtási tervét:
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);
Feladatok:
Lekérdezések és végrehajtási tervek az EMP, DEPT, SALGRADE táblák alapján
Három lépésben végezzük el a végrehajtási tervek előállítására a feladatokat:
a.) 1.lépcső: 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 create_dept_no_constr.txt - fontos,
hogy először kulcs és integritási megszorítások nélkül hozzuk létre a saját tábláinkat!
Legelőször így megszorítások és indexek nélkül fogjuk használni a saját tábláinkat, és
minél változatosabb és érdekesebb lekérdezéseket próbáljunk ki a táblákon, miközben
nézzük meg a végrehajtási terveket (és mentsük is le egyszerű .txt szöveges állományba).
b.) 2.lépcső: Most megszorításokkal együtt hozzuk létre a táblákat, lásd create_dept_const.txt
és az előző lekérdezéseket újból futtatva nézzük meg mi változott a végrehajtási terveken.
c.) 3.lépcső: Ezután hozzunk létre olyan (különböző típusú) 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).
Figyeljük meg, hogy a különböző megszorítások és indexek hozzáadásával milyen
változások történnek a korábbi végrehajtási terveken? (ezeket a terveket is mentsük le).
Feladatok - lekérdezések:
- 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.
Példák a feladatokra:
a.) Adjuk meg a dolgozók adatait és azt is, hogy milyen elnevezésű osztályon dolgoznak,
vagyis az EMP és a DEPT táblák különböző összekapcsolásaira (left/right outer-joinra is)
nézzük meg a régi illetve az új szintaxissal megadott lekérdezésekre a végrehajtási terveket.
A joint fejezzük ki IN (alkérdéssel) illetve korrelált EXISTS (alkérdéssel) is: semi-join.
b.) 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é esik a fizetés: theta-join) és már legalább 20 éve munkában áll (HIREDATE).
c.) Adjuk meg azoknak az osztályoknak az azonosítóját, nevét és az osztályon dolgozók
átlagfizetését, amely osztályoknak nincs olyan dolgozója, aki a 3-as fizetési kategóriába
esik. Tehát itt a nincs olyan dolgozókra korrelált NOT EXISTS (alkérdés) lesz: anti-join.
- 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).
A különböző megszorítások és indexek hozzáadásával milyen változások történnek
a korábbi végrehajtási terveken? (mentsük is el a végrehajtási terveket szöveges fájlba).
lásd még tervek1.txt
--- --- --- ---
> 6. rész: Végrehajtási tervek megváltoztatása hintek segítségével
--- Segédanyagok:
- Oracle Database Documentation Library: Sample Schemas HTML PDF
HR - Human Resource: Figure 4-1 HR and OE Schemas
SH -Sales History: Figure 4-3 SH Schema
- Oracle 11g Doc. Performance Tuning Guide 11-21.fejezetek HTML PDF
Emlékeztető: Hintekről: 19. Using Optimizer Hints >> hintek.txt
--- Feladatok:
- A lekérdezésekhez az Oracle demo tábláit, a HR és SH sémához tartozó táblákat
használjuk, 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: HR.<táblanév>, SH.<táblanév>
- A táblákhoz különböző indexek is vannak létrehozva, ezek tulajdonságait a
katalógusokból (dba_ind_columns) nézhetjük meg, amikor szükségünk van rá.
- Adjuk meg azoknak a vevőknek a nevét (SH.CUSTOMERS), akik nőneműek (vagyis
cust_gender = 'F') és szinglik (vagyis cust_marital_status = 'single'), vagy
1917 és 1920 között születtek.
a) Vegyük rá az Oracle-t, hogy a meglévő bitmap indexek alapján érje el a tábla sorait.
b) Vegyük rá, hogy ne használja ezeket az indexeket.
- Lásd még a tervek.txt és a hint_peldak.txt