Hajas Csilla Adatbazisok-I.gyakorlat

Adatbázisok EA+GY -  Lekérdezések végrehajtása, optimalizálás
--- --- --- ---
> Emlékeztető: 5.rész Indextáblák, lásd az előző 4.3.feladatsor (5.rész)
-- Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása, Panem, 2001.
4.fejezete -> Indexstruktúrák.pdf -->> Innen a fontosabb részletek:
   Elsődleges indexek, Másodlagos indexek, B-fák, Hasító indexek
   -->> lásd még Molina-Ullman könyv: B-fák és Hasító indexek.pdf
-- 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 [Lásd 5.rész] Hozzunk létre a tábláinkhoz különböző indexeket,
   lásd create index példák.txt legyen köztük több oszlopos, csökkenő sorrendű,
   függvény alapú, fordított kulcsú (reverse), bitmap index. Majd á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

--- --- --- ---
> 6.gépes rész: Index szervezett táblák, IOT részei (index, tábla) lásd IOT.txt
> OracleDoc: Concepts >> 3 Overview of Index-Organized Tables
> Állapítsuk meg az IOT táblák a különböző tulajdonságait a katalógusokból.
DBA_INDEXES, DBA_TABLES, DBA_OBJECTS, DBA_SEGMENTS

6.01. Hozzuk létre a IOT.txt segédletben szereplő cikk_iot indexszervezésű táblát.
   Adjuk meg mely felhasználók tulajdonában vannak ezek ('CIKK_IOT'-vel
kezdődő) index-szervezett táblák. Adjuk meg a táblák és táblaterek nevét is.
   (Melyik táblatéren vannak ezek a táblák? -> miért nem látható?)

6.02. Adjuk meg a fenti táblák index részét, és azt, hogy ezek az index részek (szegmensek)
melyik táblatéren vannak? Keressük meg a szegmensek között az előző táblákat
illetve indexeket, és adjuk meg a méretüket. Keressük meg az adatbázis objektumok
között a fenti táblákat és indexeket, és adjuk meg az objektum azonosítójukat és
adatobjektum azonosítójukat (DBA_INDEXES,DBA_TABLES,DBA_OBJECTS).

6.03. Adjuk meg a fenti táblák túlcsordulási részeit (ha van). Keressük meg
a túlcsordulási részeket a szegmensek között és adjuk meg a méretüket.
Keressük meg az objektum azonosítóikat és az adatobjektum azonosítóikat is.

6.04. Adjuk meg azokat az index szervezett táblákat, amelyeknek pontosan egy
dátum típusú oszlopa van.

--- --- --- ---
> 7.papíros rész: lásd 9ituning 83-84.o: Bitmap index + Oracle Concepts doksi
Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása, Panem, 2001.
Adattárházaknál hasznos: 5.4. Bittérképindexek szakaszhossz_kodolas.pdf
> Oracle Doc: Concepts >> 3 Overview of Indexes
> Oracle10g Concepts >> 5. Schema Objects >>Overview of Indexes

--- --- --- ---
> 8.papíros rész: Lekérdezések végrehajtása (7.fej) Optimalizálás két módja:
(1) Heurisztikus szabályokon alapuló optimalizálásl: rAlgOpt.pdf
(2) Költség alapú optimalizálás: Végrehajtási tervek, költségek: VegrKolts.pdf
Feladatok: Adjuk meg, hogy az egyes algoritmusok hogyan működnek és
   adjuk meg a műveletek költségbecslését (I/O blokkok számát).
Tankönyv: Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása, 2001.
  Tk.2.3.fejezete: Adatok rendezése másodlagos tárolókon
  - Tegyük fel, hogy a dolgozó tábla 140 sorból áll és 10 sora fér el 1 blokkban,
és a memóriánk 4 blokknyi. Rendezzük a tábla sorait fizetés szerint a
kétfázisú többutas összefésülő rendezéssel (sort-merge algoritmussal).
Adjuk meg az első menet után a rendezett részlistákat (elég a dnev, fizetes).
Hány menetes algoritmusra lesz szükségünk? optimization-pp17-22.pdf
Tk.6.fejezet: Lekérdezések végrehajtása, műveletek I/O költsége
  - Tegyük fel, hogy a dolgozó tábla 140 sorból áll és 10 sora fér el 1 blokkban,
és a memóriánk most 6 blokknyi és van még egy vásárlás tábla,  amelynek
a szerkezete a következő: VASARLAS(dkod, cikk, mennyiseg, ar).
Ennek a táblának is 10 sora fér el 1 blokkban, és a tábla kb.1200 sorból áll.
Mennyi a műveletigénye
- egy beágyazott ciklusú algoritmusnak  nested_loop_algoritmus.pdf
- egy rendezés alapú algoritmusnak sort_join_algoritmus.pdf
- egy hash alapú algoritmusnak hash_join_algoritmus.pdf
amely egyenlőséges összekapcsolást (equijoin) végez a két táblán, arra a
lekérdezésre válaszol, hogy az egyes dolgozók összesen mennyit költöttek?
Feltehetjük, hogy az összegeket gyűjtő számlálók még beférnek a memóriába
a blokkok mellett. Írjuk le röviden, hogy az egyes algoritmusok hogyan fognak
működni. Adjuk meg a kosarakat a hasítás alapú algoritmus első menete után.

--- --- --- ---
> 9.gépes rész: Végrehajtási tervek előállítása
   ZH illetve 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);

Példák: Oracle 9i példákat futtassuk le Oracle 11gR2-ben, figyeljük meg a változást:
--- 9ituning 6-8.o: Áttekintés.pdf,
--- 9ituning 27-40.o: Indexes.pdf,
--- 9ituning 48-55.o: Joins.pdf
--- 9ituning 75-76.o: Hints.pdf

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

--- --- --- ---
> 10.gépes 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