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