Hajas Csilla Adatbázisok

11.GYAKORLAT (ADATBÁZISOK)

   Adatbázisok haladó témakörök: Rendszerkatalógus, indextáblák, lekérdezések
> 1.rész Adatbázisbeli objektumok: Kérdezzük le a DBA_OBJECTS nézettáblát
> 2.rész Táblák és oszlopai: DBA_TABLES és DBA_TAB_COLUMNS nézetekből
> 3.rész Adattárolással kapcsolatos fogalmak: Az adatok fizikai/logikai struktúrája
> 4.rész ROWID
> 5.rész Indextáblák
> 6.rész Végrehajtási tervek

- A rendszerkatalógus maga is egy adatbázisnak tekinthető, amit lekérdezve egyre
többet tudhatunk meg az adatbázisban tárolt dolgokról és azok tulajdonságairól.

- A rendszerkatalógus tábláinak (nézeteinek) elnevezése: DBA_..., ALL_..., USER_...
   USER (az adott user tulajdonában lévő objektumok) pl. USER_TABLES
ALL (amihez joga van az adott usernek) pl. ALL_TABLES
DBA (az adatbázis összes objektuma) pl. DBA_TABLES

- A rendszerkatalógus másik szokásos elnevezése: adatszótár (Data Dictionary)
Figyelem! Az adatszótárban nagybetűvel vannak tárolva az adatok!

--- --- --- --- --- ---
> 1.rész Adatbázisbeli objektumok: Kérdezzük le a DBA_OBJECTS nézettáblát
- Az alábbi lekérdezések segítenek feltérképezni, hogy milyen objektumok vannak
  egy adatbázisban, mi a típusa (OBJECT_TYPE), ki a tulajdonosa, mikor hozta létre.
- Minden adatbázisbeli objektumnak van egy egyedi azonosítója (OBJECT_ID), és
  amelyikhez tartozik szegmens, annak egy ún. adatobjektum azonosítója is van
(DATA_OBJECT_ID). Amelyik objektumhoz nem tartozik szegmens, annak az
  adatobjektum azonosítója NULL, és ezeknek csak a definíciója van (szövegesen)
tárolva a szótárban, ezek a tényleges tárolást nem igénylő objektumok.

Feladatok:
1.01. Hány különböző típusú objektum van nyilvántartva az adatbázisban?
1.02. 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?
1.04. Melyek azok az objektum típusok, amelyek nem igényelnek tényleges
tárolást igényelnek, vagyis tartoznak hozzájuk adatblokkok?
(az utóbbi két lekérdezés metszete nem üres, például partícionált táblák)
1.05. Kik azok a felhasználók, akiknek több mint 10 féle objektumuk van?
1.06. Kik azok a felhasználók, akiknek van triggere és nézete is?
1.07. Kik azok a felhasználók, akiknek van nézete, de nincs triggere?
1.08. Kik azok a felhasználók, akiknek több mint 20 táblájuk, de
maximum 15 indexük van?

--- --- --- --- --- ---
> 2.rész Táblák és oszlopai: DBA_TABLES és DBA_TAB_COLUMNS nézetekből
Mely táblák vannak egy adott felhasználó tulajdonában?
- A DBA_TABLES adatszótár az összes táblát felsorolja, pl 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, stb.
- A DBA_TAB_COLUMNS adatszótár a táblák oszlopairól tartalmaz információt.

Feladatok:
2.01. 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
2.02. Hozzunk létre táblákat megszorításokkal, nézzük meg hogyan jelennek meg
az oszlopok és a megszorítások a DBA_CONSTRAINTS katalógustáblában is.
2.03. Hozzunk létre nézettáblákat, nézzük meg a DBA_VIEWS katalógustáblában.
2.04. Hány oszlopa van a sila.emp táblának?
2.05. Milyen típusú a sila.emp tábla 6. oszlopa?
2.06. Adjuk meg azoknak a tábláknak a tulajdonosát és nevét, amelyeknek
van 'Z' betűvel kezdődő oszlopa.
2.07. Adjuk meg azoknak a tábláknak a tulajdonosát és nevét, amelyeknek
   legalább 8 darab dátum tipusú oszlopa van.
2.08. Adjuk meg azoknak a tábláknak a tulajdonosát és nevét, amelyeknek
1. es 4. oszlopa is VARCHAR2 tipusú.

--- --- --- --- --- ---
> 3.rész Adattárolással kapcsolatos fogalmak: Az adatok fizikai/logikai struktúrája
>> Oracle Database Concepts >> innen 12. Logical Storage Structures

  DBA_TABLES, DBA_DATA_FILES, DBA_TEMP_FILES,
DBA_TABLESPACES, DBA_SEGMENTS, DBA_EXTENTS, DBA_FREE_SPACE

Feladatok:
3.01. Adjuk meg az adatbázishoz tartozó adatfile-ok (és temporális fájlok)
nevét és méretét, méret szerint csökkenő sorrendben.
(DBA_DATA_FILES, DBA_TEMP_FILES)

3.02. Adjuk meg, hogy milyen táblaterek vannak létrehozva az adatbázisban,
az egyes táblaterek hány adatfájlból állnak, és mekkora az összméretük.
(tablater_nev, fajlok_szama, osszmeret) Vigyázat, van temporális táblatér is!

3.03. Mekkora a blokkok mérete a USERS táblatéren? (DBA_TABLESPACES)

3.04. Melyik a legnagyobb méretű tábla szegmens az adatbázisban
(a tulajdonost és a szegmens nevét is adjuk meg), hány extensből áll?
(A particionált táblákat most ne vegyük figyelembe.) (DBA_SEGMENTS)

3.05. Adjuk meg adatfájlonként, hogy az egyes adatfajlokban mennyi
a foglalt hely összesen. (DBA_EXTENTS, DBA_DATA_FILES)

3.06. Melyik felhasználó objektumai foglalnak összesen a legtöbb helyet
az adatbázisban?

3.07. Van-e valamelyik felhasználónak olyan táblája, amelyik
több adatfájlban is foglal helyet?

--- --- --- --- --- ---
> 4.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:
4.01. Az SH felhasználó CUSTOMERS táblája hány blokkot foglal le az adatbázisban?
(Vagyis azt nézzük meg, hogy hány olyan blokk van, ami ehhez a táblához van
rendelve és így azok már más táblákhoz nem adhatók hozzá?) (DBA_SEGMENTS)
Hasonlítsuk össze ezt azzal, hogy mennyi a foglalt hely összesen? (DBA_EXTENTS)

4.02. Az SH felhasználó CUSTOMERS táblájának adatai hány blokkban helyezkednek el?
(Vagyis a tábla sorai ténylegesen hány blokkban vannak tárolva?)
!!! -> Ez a kérdés nem ugyanaz mint az előző, most a ROWID-et használjuk!

4.03. Az egyes blokkokban hány sor van?

--- --- --- --- --- ---
> 5.rész Indextáblák
>> Oracle Database Concepts >> innen 3. Indexes
>> Oracle: SQL Language Reference >> innen CREATE INDEX példák

  DBA_INDEXES, DBA_IND_COLUMNS, DBA_IND_EXPRESSIONS

Feladatok:
5.01. 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 a
katalógusokból: DBA_INDEXES, DBA_IND_COLUMNS, DBA_IND_EXPRESSIONS

5.02. Adjuk meg azoknak a tábláknak a nevét, amelyeknek van csökkenő
sorrendben indexelt oszlopa.

5.03. 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).

5.04. Adjuk meg azon kétoszlopos indexek nevét és tulajdonosát, amelyeknek
legalább az egyik kifejezése függvény alapú.

--- --- --- ---
> 6.rész: Végrehajtási tervek előállítása
Végrehajtási tervek szöveges megjelenítése ("EXPLAIN PLAN OUTPUT"-ból)
1.lépés: Készítsük el az utasítások végrehajtási tervét:
      EXPLAIN PLAN FOR SELECT ... (ide írjuk be a SELECT utasítást)
2.lépés: A végrehajtási tervek megjelenítése a dbms_xplan 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
Először kulcs és integritási megszorítások nélkül hozzuk létre a tábláinkat,
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 (lásd javaslatok lentebb)
próbáljunk ki a táblákon, miközben nézzük meg a végrehajtási terveket,
ezeket mentsük is el egyszerű .txt szövegfájlba.
b.) 2.lépcső: Hozzuk létre a táblákat megszorításokkal, lásd create_dept_const.txt
és az előző lekérdezéseket újból futtatva nézzük meg a végrehajtási terveket.
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 megszorítások és
indexek hozzáadásával milyen változások történnek a végrehajtási terveken?

A fenti feladathoz adjunk meg különböző lekérdezéseket:
- 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 where
feltételeket, különböző összekapcsolásokat (például külső joint is), nézzük meg
az alkérdéseket (NOT IN és NOT EXISTS is), korrelált alkérdéseket, csoportosítást,
halmaz- és multihalmaz műveleteket, az inline nézeteket tartalmazó lekérdezéseket,
és állítsuk elő (mentsük le szöveges fájlba) a hozzájuk tartozó végrehajtási terveket.
Például olyan lekérdezések, mint
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 (inner-join és left/right/outer-join is)
A joint fejezzük ki IN (alkérdéssel), 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 a fizetése (SAL) az
1-es fizetési kategóriába esik (theta-join), és már legalább 20 éve munkában áll.
c.) Adjuk meg azoknak az osztályoknak az azonosítóját, nevét, az osztályon dolgozók
átlagfizetését, amely osztályoknak nincs 3-as fiz.kategóriájú dolgozója (anti-join).
Lásd még tervek.txt

--- --- --- ---
> 7.rész: Végrehajtási tervek megváltoztatása hintek segítségével
SELECT /*+ tipp lista */ ... lekérdezés (hintekkel együtt)

Segédanyagok:
>> Oracle Database Performance Tuning Guide => hintek.txt
a.) Vegyük rá az Oracle-t, hogy a meglévő indexek alapján érje el a tábla sorait.
b.) Vegyük rá, hogy az Oracle-t, hogy ne használja ezeket az indexeket.