IV. Témakör: Adattárolás. Indexstruktúrák.

1.gyak. (IX.10)
>> Előzmények - Előismeretek
>> 4.1. Bevezetés. Feladatok a táblák oszlopaival
>> 4.2. Feladatok adatbázis objektumokkal (DBA_OBJECTS)  
 
2.gyak. (IX.17)
>> 4.3A. Táblák, nézetek, indexek, szinonimák, szekvenciák, adatbázis-kapcsolók
                létrehozása és kezelése, tulajdonságaik megkeresése a katalógusban
>> EA1. Előadáshoz kapcsolódó ellenőrző kérdések
  
3.gyak. (IX.24)
>> 4.4. Feladatok adattárolással kapcsolatos fogalmakra
>> EA2. Előadáshoz kapcsolódó feladatok fájlszervezésre
   
4.gyak. (X.1)
>> 4.3B. (folyt.) Táblák (tárolási paraméterekkel), indexek, bitmap indexek
                létrehozása és kezelése, tulajdonságaik megkeresése a katalógusban
>> 4.5. Feladatok ROWID adattípusra
>> EA3. Előadáshoz kapcsolódó feladatok indexstruktúrákra 
   
5.gyak. (X.8)
>> 4.6. Feladatok indexekre, bitmap indexekre.
>> 4.7. Index szervezett (IOT) táblák létrehozása és kezelése
   
6.gyak. (X.15)
>> 4.8. Partícionált táblák létrehozása és kezelése. Feladatok partíciókra
>> 4.9. Klaszterek létrehozása és kezelése. Feladatok klaszterekre  
 
____________________________________________________________
Feladatok és segédanyagok:
- Nikovits Tibor: Információkezelés gyakorlatok feladatainak felhasználásával
- További ajánlott irodalom és hasznos linkek:
   Lásd 1.1 Tankönyvek az előadásokhoz
   Lásd 1.3 Tankönyvek a gyakorlatokhoz
   Lásd 4.1 Oracle Online Documentation
- Reference alapján az adatszótárnézetek leírása: Katalogus09s.html
   
Oracle adatbázis mintasémái  download.oracle.com ablinux.inf.elte.hu
Samples Schemas HTML PDF HTML PDF
1.félév:  IP-cATP anyagához download.oracle.com ablinux.inf.elte.hu
SQL Quick Reference
SQL Reference
PL/SQL User's Guide and Reference
PL/SQL Packages and Types Ref.
 HTML
HTML
HTML
HTML		 PDF
PDF
PDF
PDF		 HTML
HTML
HTML
HTML 		PDF
PDF
PDF
PDF
2.félév:  IP-cAMÜ anyagához download.oracle.com  ablinux.inf.elte.hu
Concepts
Reference
2 Day DBA
Administrator's Guide
2 Day + Performance Tuning Guide
Performance Tuning Guide		 HTML
HTML
HTML
HTML
HTML
HTML		 PDF
PDF
PDF
PDF
PDF
PDF		 HTML
HTML
HTML
HTML
HTML
HTML
 PDF
PDF
PDF
PDF
PDF
PDF 
Oracle környezethez javasolt doksik download.oracle.com ablinux.inf.elte.hu
SQL*Plus Quick Reference
SQL*Plus User's Guide and Reference
Oracle SQL Developer User's Guide
SQL Developer Installation Guide		 HTML
HTML
HTML
HTML		 PDF
PDF
PDF
PDF 		HTML
HTML
HTML
HTML		 PDF
PDF
PDF
PDF

_______________________________________________________
1. gyak. - Adatszótárnézetek lekérdezése
   
>> Előzmények - Előismeretek
>> 4.1. Bevezetés. Feladatok a táblák oszlopaival
>> 4.2. Feladatok adatbázis objektumokkal  

Előzmények - Előismeretek
>>  I.  Témakör: Lekérdezés: Rel.alg. SQL SELECT feladatok
>> II. Témakör: Relációs tervezés. SQL DDL, DML feladatok
>> III. Témakör: Adatbázis programozás. PL/SQL feladatok
 
Néhány apró specialitás, amikről érdemes egy-két szót ejteni:
>> A DUAL tábla
>> Külső join  művelet
és a Felső-N elemzés (pl. zh-n hasznos a felső 5 sor kiíratásához)
       SELECT  *  FROM 
       (SELECT... FROM ...WHERE... ORDER BY <oszlop> DESC)
       WHERE  ROWNUM  <= 5
 
4.1. Bevezetés. Feladatok a táblák oszlopaival
DBA_TAB_COLUMNS
 
A rendszerkatalógus (rendszertábla, adatszótárnézet) maga is egy adatbázisnak
tekinthető, amit lekérdezve egyre többet tudhatunk meg az Oracle adatbázisban
tárolt dolgokról és azok tulajdonságairól. A rendszerkatalógus elemei.
  Lásd 41_rendszerkatalogus.txt  
  Lásd Katalogus09s.html
Lásd  Oracle Doksi otn.oracle.com ablinux.inf.elte.hu
Concepts
Reference
 HTML
HTML		 PDF
PDF		 HTML
HTML
 PDF
PDF

Feladatok az adatszótárnézetek lekérdezésére
DBA_TAB_COLUMNS tábla lekérdezésével

41col_01
   - Hány oszlopa van a sila.emp táblának?

41col_02
   - Milyen típusú a sila.emp tábla 6.oszlopa? 

41col_03
   - Adjuk meg azoknak a tábláknak a tulajdonosát és nevét, amelyeknek
      van 'Z' betűvel kezdődő oszlopa. 

41col_04
   - Adjuk meg azoknak a tábláknak a nevét, amelyeknek legalább 8 darab
     dátum típusú oszlopa van. 

41col_05
   - Adjuk meg azoknak a tábláknak a nevét, amelyeknek 1. es 4. oszlopa is
     VARCHAR2 típusú.
 
4.2. Feladatok adatbázis objektumokkal
DBA_OBJECTS
 
Katalógus táblák megkeresése a katalógusban (DBA_OBJECTS). 
Katalógus táblák leírása, lásd  Katalogus09s.html
Egy tábla oszlopainak lehetséges típusai, DEFAULT értékek.
Mennyi helyet foglal el egy objektum, ha egyáltalán foglal?
DATA_OBJECT_ID oszlop jelentősége a DBA_OBJECTS katalógusban.
 
42obj_01
   - Kinek a tulajdonában van a DBA_TABLES nevű nézet (ill a DUAL nevű tábla)?

42obj_02
   - Kinek a tulajdonában van a DBA_TABLES nevű szinonima (ill a DUAL nevű)?
     (Az iménti két lekérdezés megmagyarázza, hogy miért tudjuk elérni őket.)

42obj_03
   - Milyen típusú objektumai vannak az orauser nevű felhasználónak az adatbázisban?

42obj_04
   - Hány különböző típusú objektum van nyilvántartva az adatbázisban?

42obj_05
   - Melyek ezek a típusok?
 
42obj_06
   - Kik azok a felhasználók, akiknek több mint 10 féle objektumuk van?

42obj_07
   - Kik azok a felhasználók, akiknek van triggere és nézete is?

42obj_08
   - Kik azok a felhasználók, akiknek van nézete, de nincs triggere?

42obj_09
   - Kik azok a felhasználók, akiknek több mint 40 táblájuk, de
      maximum 37 indexük van?
 
42obj_10
   - 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)

42obj_11
   - Melyek azok az objektum típusok, amelyek nem igényelnek tényleges tárolást,
      vagyis nem tartoznak hozzájuk adatblokkok?
      (Ezeknek csak a definíciója tárolódik adatszótárban)
      Az utóbbi két lekérdezés metszete nem üres. --> lásd később partícionálás

Az alábbi feladatot PL/SQL-ben oldd meg!
42obj_12
   - Írjunk meg egy plsql procedúrát, amelyik a paraméterül kapott karakterlánc
     alapján kiírja azoknak a tábláknak a nevét és tulajdonosát, amelyeknek neve
     az adott karakterlánccal kezdődik.
    (Ha a paraméter kisbetűs, akkor is működjön a procedúra!)
          PROCEDURE tabla_kiiro(p_kar VARCHAR2)
    A fenti procedúra segítségével írjuk ki a Z betűvel kezdődő táblák nevét
    és tulajdonosát.

_____________________________________________________
2. gyak.  - Séma objektumok
 
>> 4.3A. Táblák, nézetek, indexek, szinonimák, szekvenciák, adatbázis-kapcsolók
                létrehozása és kezelése, tulajdonságaik megkeresése a katalógusban
>> EA1. Előadáshoz kapcsolódó ellenőrző kérdések
   
4.3A. Táblák, nézetek, indexek, szinonimák, adatbázis-kapcsolók
létrehozása és kezelése, tulajdonságaik megkeresése a katalógusban

Táblák (és megszorítások), nézettáblák, szinonimák (publikus és nem publikus),
szekvenciák, adatbázis-kapcsolat. Létrehozásuk, használatuk és tulajdonságaik
megkeresése a megfelelő katalógusban. Katalógus táblák leírása és szerkezete,
lásd  Katalogus09s.html
 
Lásd 43_semaobjektumok.txt  (illetve az SQL Reference) CREATE példái, 
vagyis hozzunk létre táblákat, szinonimát, szekvenciát, adatbázis-kapcsolót!
A megfelelő CREATE szintaxisa, lásd  SQL Reference  HTML     PDF
 
Sémaobjektumok létrehozásához és használatához (jelszavas) segédanyagok:
Lásd  Tablak.pdf      EgyebObjektumok.pdf      Adatszotar.pdf  
 
Miután létrehoztuk, keressük meg a tulajdonságaikat a megfelelő katalógustáblákban: 
DBA_SYNONYMS, DBA_VIEWS, DBA_SEQUENCES, DBA_DB_LINKS
folyt köv. >> 4.3B. (folyt.) Táblák (tárolási paraméterekkel), indexek, stb...
 
43obj_01 Táblák és megszorítások létrehozása (create table) példák
 
43obj_02 Nézettáblák létrehozása és használata (create view) példák
 
43obj_03 (SQL Reference Doksi ->> create synonym)
   - Adjuk ki az alábbi utasítást:
     SELECT * FROM szinonima1;
     majd derítsük ki, hogy kinek melyik tábláját kérdeztük le.
     (Ha esetleg nézettel találkozunk, azt is fejtsük ki, hogy az mit kérdez le.)

43obj_04 (SQL Reference Doksi ->> create sequence)
   - Előkészítés: Az Oracle emp és dept alaptábláiból hozzuk létre (de üresen!)
      a dolg és oszt táblákat.
   - Hozzunk létre egy szekvenciát, amelyik az osztály azonosítókat fogja generálni
      a számunkra. Minden osztály azonosító a 10-nek többszöröse legyen.
     Vigyünk fel 3 új osztályt és osztályonként minimum 3 dolgozót a táblákba.
     Az osztály azonosítókat a szekvencia segítségével állítsuk elő, és ezt tegyük
     be a táblába. (Vagyis ne kézzel írjuk be a 10, 20, 30 ... stb. azonosítót.)
     A felvitel után módosítsuk a 10-es osztály azonosítóját a következő érvényes  
     generált osztály azonosítóra. (Itt is a szekvencia segítségével adjuk meg, hogy
     mi lesz a következő azonosító.) A 10-es osztály dolgozóinak az osztályazonosító
     értékét is módosítsuk az új értékre.

43obj_05 (SQL Reference Doksi ->> create database link)
  -  Hozzunk létre adatbázis-kapcsolót az egyik adatbázisban, amelyik a másik
     adatbázisra mutat. Ennek segítségével adjuk meg olyan lekérdezést, amely
     több adatbázis tábláit használja, például  
     dolg (emp) táblát az ABLINUX adatbázisból,
     oszt (dept) táblát ORADB adatbázisból.

43obj_06
   - Hozzatok létre adatbázis-kapcsolót (database link) az egyik adatbázisban,
     amelyik a másik adatbázisra mutat. Ennek segítségével adjátok meg a
     következő lekérdezéseket. A lekérdezések alapjául szolgáló táblák:

     NIKOVITS.VILAG_ORSZAGAI ABLINUX adatbázis
     NIKOVITS.FOLYOK ORADB adatbázis

     Az országok egyedi azonosítója a TLD (Top Level Domain) oszlop.
     Az ország hivatalos nyelveit vesszőkkel elválasztva a NYELV oszlop tartalmazza.
     A GDP (Gross Domestic Product -> hazai bruttó össztermék) dollárban van megadva.
 
     A folyók egyedi azonosítója a NEV oszlop.
     A folyók vízhozama m3/s-ban van megadva, a vízgyűjtő területük km2-ben.
     A folyó által érintett országok azonosítóit (TLD) a forrástól a torkolatig
     (megfelelő sorrendben vesszőkkel elválasztva) az ORSZAGOK oszlop tartalmazza.
     A FORRAS_ORSZAG és TORKOLAT_ORSZAG hasonló módon a megfelelő
     országok azonosítóit tartalmazza. (egy folyó torkolata országhatárra is eshet, pl. Duna)

     A lekérdezéseket lehetőleg egyetlen SELECT utasítással fogalmazzuk meg,
     PL/SQL használata nélkül.

-- Melyik a föld 4 leghosszabb folyója sorrendben, és mennyi a hosszuk?
-- Melyik Afrika 3. leghosszabb folyója?
-- Mely folyók érintik Csehországot?
-- Mely országokon folyik keresztül a Mekong?
-- Melyek azok a folyók, amelyeknek a forrása és torkolata ugyanabban az országban van,
    de a folyó más országot is érint?
-- Mely országokon folyik keresztül a Nílus? Az országokat a megfelelő sorrendben adjuk meg.
   
EA1 Előadáshoz kapcsolódó ellenőrző kérdések

Tankönyv: Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása, 2001.

ea1_01
   - Milyen típusú állományokból áll egy Oracle adatbázis?
      Melyiket mire használja a rendszer?

ea1_02
   - Mi a különbség a következő 3 adatszótár tartalmában? Melyik miket tartalmaz?
     DBA_TABLES, ALL_TABLES, USER_TABLES

ea1_03
   - Mire való a szekvencia? Milyen módon lehet használni SQL utasításból?
     Írjon le 2-3 SQL utasítást, amelyek egy SEQV1 nevű szekvenciát használnak.

ea1_04
   -  Milyen kapcsolatban vannak egymással az alábbi adattárolással kapcsolatos
       fogalmak? Táblatér, Szegmens, Adatfájl

ea1_05
   - Az alábbi objektumok közül melyik hány adatszegmenssel rendelkezhet?
     nem partícionált tábla, szekvencia, index-szervezett tábla, nézet, cluster,
     partícionált tábla, trigger, package, nem partícionált index, partícionált index
     Lehetséges válaszok:
     a: -> 0; b: -> 1; c: -> 1 vagy 2; d: -> 1 vagy több; e: -> 2 vagy több;

ea1_06
   - Mi a különbség a ritka és a sűrű index között?
     Az elsődleges illetve másodlagos indexek ritkák vagy sűrűk lehetnek?

ea1_07
   - Az Oracle által készített indexek ritkák vagy sűrűk?
      Milyen információkat tartalmaz a sorazonosító (ROWID) az Oracle-ben?
   
 ______________________________________________________
3. gyak.  - Adattárolás. Fájlszervezés
 
>> 4.4. Feladatok adattárolással kapcsolatos fogalmakra   
>> EA2. Előadáshoz kapcsolódó feladatok fájlszervezésre    
 
4.4. Feladatok adattárolással kapcsolatos fogalmakra

Adattárolással kapcsolatos fogalmak: Blokk, Extens, Szegmens, Adatfájl,
Táblatér. Ezek egymáshoz való viszonya.
Szegmensek típusai: Tábla, Index, Cluster, Partíció, Rollback, Temporális stb.
A tábla által lefoglalt hely és a táblabeli adatok által ténylegesen foglalt blokkok
vizsgálata és a kettő közötti különbség.

Adattárolással kapcsolatos fogalmakra vonatkozó lekérdezések a katalógusból.
Katalógus táblák leírása és szerkezete, lásd  Katalogus09s.html
DBA_TABLES, DBA_DATA_FILES, DBA_TEMP_FILES,
DBA_TABLESPACES, DBA_SEGMENTS, DBA_EXTENTS,
DBA_FREE_SPACE
   
44tab_01
   - Adjuk meg az adatbázishoz tartozó adatfájlok és temporális fájlok nevét és
      méretét méret szerint csökkenő sorrendben

44tab_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.

44tab_03
    - Mekkora a blokkok mérete a USERS táblatéren?

44tab_04
    - Melyik a legnagyobb méretű tábla szegmens az adatbázisban (a tulajdonost is
       adjuk meg) és hány extensből áll? (A partícionált táblakat ne vegyük figyelembe)

44tab_05
   - Melyik a legnagyobb méretű index szegmens az adatbázisban és
      hány blokkból áll? (A partícionált indexeket most se vegyük figyelembe)

44tab_06
   - Adjuk meg adatfájlonként, hogy az egyes adatfájlokban mennyi a foglalt hely
      összesen, írassuk ki a fájlok méretét is.

44tab_07
   - Melyik két felhasználó objektumai foglalnak összesen a legtöbb helyet
      az adatbázisban?

44tab_8
   - Melyik táblatéren van az ORAUSER felhasználó dolgozo táblája?

44tab_9
  - Írjunk meg egy plsql procedúrát, amelyik a paraméterül kapott táblára kiírja  
    az őt létrehozó CREATE TABLE utasítást.
        PROCEDURE cr_tab(p_owner VARCHAR2, p_tabla VARCHAR2)
   Elég ha az oszlopok típusát és DEFAULT értékeit kíírja, és elég ha a következő
   típusú oszlopokra működik: CHAR, VARCHAR2, NCHAR, NVARCHAR2,
   BLOB, CLOB, NCLOB, NUMBER, FLOAT, BINARY_FLOAT,
   DATE, ROWID

  Teszteljétek a procedúrát az alábbi táblával.
  CREATE TABLE tipus_proba(
       c10 CHAR(10) DEFAULT 'bubu',
       vc20 VARCHAR2(20),
       nc10 NCHAR(10),
       nvc15 NVARCHAR2(15),
       blo BLOB,
       clo CLOB,
       nclo NCLOB,
       num NUMBER,
       num10_2 NUMBER(10,2),
       num10 NUMBER(10) DEFAULT 100,
       flo FLOAT, bin_flo binary_float DEFAULT '2e+38',
       bin_doub binary_double DEFAULT 2e+40,
       dat DATE DEFAULT TO_DATE('2007.01.01', 'yyyy.mm.dd'),
       rid ROWID);

EA2 Előadáshoz kapcsolódó elméleti feladatok a "zöldkönyvből" 

Indexstruktúrák - az előadáshoz kapcsolódó fájlszervezési feladatok.
Feladatok - Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása
                    a "zöld könyv" 4.1, 4.2, 4.3 fejezetei alapján
A fogalmak: Oracle dokumentációkból - a "Concepts"  itt:
                    Part II. Oracle Database Architecture - 5. Schema Objects
                   
Molina-Ullman 4.1. fejezete: Indexek szekvenciális fájlokon:

ea2_01
   - Minden blokkba 3 rekord, vagy 10 indexrekord (érték-mutató pár) fér.
     Összesen n rekordunk van. Hány blokkos az adatfájl, a sűrű index és
     a ritka index? 

ea2_02
   - Minden blokkba 30 rekord, vagy 200 indexrekord (érték-mutató pár) fér.
     Összesen n rekordunk van. Egyik blokk telítettsége sem lehet több, mint 80%.
     Hány blokkos az adatfájl, a sűrű index és a ritka index?

ea2_03
   - Minden blokkba 3 rekord, vagy 10 indexrekord (érték-mutató pár) fér.
     Összesen n rekordunk van. Többszintű indexünk legfelső szintje csak
     1 blokkból áll. Hány blokkos az indexfájl, ha az első szinten sűrű az index,
     és ha az első szinten ritka az index? 

ea2_04 
   - Minden blokkba 3 rekord, vagy 10 indexrekord (érték-mutató pár) fér.
      Hány adatblokkot kell átlagosan beolvasni az összes adott értékű rekord
      eléréséhez, ha rendezett állományunk van, egy érték 1,2 vagy 3-szor
      szerepelhet 1/3, 1/3, 1/3 valószínűségekkel, és olyan sűrű indexünk van,
      amelyben csak az első előforduláshoz tartozik indexrekord?
    - Feltesszük, hogy tudjuk előre, hogy mennyi előfordulásunk van
      az adott értékből.

ea2_05 
   - Minden blokkba 3 rekord, vagy 10 indexrekord (érték-mutató pár) fér.
      Hány blokkot kell átlagosan beolvasni az összes adott értékű rekord
      eléréséhez, ha egy érték 1,2 vagy 3-szor szerepelhet 1/3, 1/3, 1/3
      valószínűségekkel, és olyan sűrű indexünk van, amelyben minden
      előforduláshoz tartozik indexrekord?
    - Feltesszük, hogy már kiszámoltuk, hogy melyik indexblokkban van
      az első keresett indexérték, és tudjuk, hogy hány van belőle.

Molina-Ullman 4.2. fejezete: Másodlagos indexek

ea2_06 
   - Az adatfájlba történő beszúrás és törlés esetén a másodlagos indexfájlnak
      változnia kell. Javasoljunk néhány módszert arra, hogy lehet naprakészen
      tartani a másodlagos indexet az adatfájlok változásaival szinkronban.

ea2_07
   - Minden blokkba 3 rekord, vagy 10 indexrekord (érték-mutató pár), vagy
     50 mutató fér. Tegyük fel, hogy átlagosan 10-szer szerepel minden indexérték.
     Összesen 3000 rekordunk van. Másodlagos indexet készítünk, úgy, hogy az
     egy indexértékhez tartozó mutatókat kosarak blokkjaiban tároljuk. Mekkora
     az állomány mérete összesen, beleértve az adatokat, indexeket és mutatókat
     tartalmazó blokkokat?  

Molina-Ullman 4.3. fejezete: B-fák

ea2_08
   - Legyen a rekordok száma 1 000 000, és az indexelt oszlopban minden érték
      különböző. Sűrű indexre készítünk B-fát. Egy blokkba 10 rekord vagy
      (99 kulcs és 100 mutató) fér. Legyen a telítettség 70%, azaz legalább
      69 kulcs és 70 mutató szerepel az indexblokkban. Mekkora az adatfájl és
      az index együttes mérete? Mennyi a keresés blokkolvasási költsége?
 
_______________________________________________________
4. gyak.  - Indexstruktúrák

>> 4.3B. (folyt.) Táblák (tárolási paraméterekkel), indexek, bitmap indexek
                létrehozása és kezelése, tulajdonságaik megkeresése a katalógusban
>> 4.5. Feladatok ROWID adattípusra
>> EA3. Előadáshoz kapcsolódó feladatok indexstruktúrákra 
     
4.3B. (folyt.) Táblák (tárolási paraméterekkel), indexek, bitmap
indexek létrehozása, tulajdonságaik megkeresése a katalógusban

Adattárolással kapcsolatos paraméterek megadása egy tábla létrehozásakor:
INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, PCTUSED,
PCTFREE … stb. További hely lefoglalása manuálisan a tábla számára.
Az adatblokk szerkezete (fejléc, sorok, mezők, túlcsordulási blokkok).
Lásd  43_tabla_tarolas.txt CREATE TABLE példáit próbáld ki!

Az indexek felépítése és szerkezete. Indexek létrehozása Oracle-ben.
Egyoszlopos, többoszlopos, bitmap, függvény alapú indexek létrehozása
és ezek tulajdonságainak megkeresése az adatszótárban. 
Lásd 43_indexek.txt CREATE INDEX és CREATE BITMAP INDEX
példáit (illetve az SQL Reference CREATE INDEX példáit) próbáld ki!

43obj_07
   - Hozzunk létre táblákat a tárolási paraméterek megadásával!
     Nézzük meg a katalógusból a tulajdonságait!
 
43obj_08
   - 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, stb.
folyt.köv.: Állapítsuk meg az iménti indexeknek mindenféle tulajdonságait
        a katalógusokból. >> 4.6. Feladatok indexekre, bitmap indexekre
       
4.5. Feladatok ROWID adattípusra

A sorazonosító (ROWID) adattípus és pszeudo oszlop.
A Rowid részei. A DBMS_ROWID package legfontosabb függvényei.
DATA_OBJECT_ID, RELATIVE_FNO kinyerése a sorazonosítóból.
Lásd 45_rowid.txt  (ROWID adattípus formátuma és jelentése)
 
45row_01
   - Az SH felhasználó SALES táblája (sh.sales) hány blokkot foglal le az
     adatbázisban? (Vagyis 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á?)

45row_02
   - Az SH felhasználó SALES 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ő.

45row_03
   - Az egyes blokkokban hány sor van?

45row_04
   - Hozunk létre egy táblát az EXAMPLE táblatéren, amelynek szerkezete azonos
      az sh.sales tábláéval és pontosan 128 KB helyet foglal az adatbázisban.
      Foglaljunk le manuálisan egy újabb 128 KB-os extenst a táblához.
      Vigyünk fel sorokat addig, amig az első blokk tele nem  lesz, és 1 további sora lesz
      még a táblának a második blokkban. (A felvitelt plsql programmal végezzük!)

45row_05
   - Próbáljuk ki az előzőt ismét, de most a PCTFREE értéket állítsuk 40-re.
     Mindkét esetben ellenőrizzük is, hogy a sorok tényleg két blokkban vannak,
     és a másodikban csak egyetlen sor van.

45row_06
   - Állapítsuk meg, hogy a sh.sales táblának a következő adatokkal azonosított sora
     (time_id='1999.04.10', prod_id=2860, cust_id=37280) melyik adatfájlban van,
     azon belül melyik blokkban, és a blokkon belül hányadik a sor?

45row_07 (HF)
  - Írjunk egy olyan  plsql függvényt, amely a ROWID részeit értelmezi,
    lásd 45_rowid.txt (ROWID adattípus formátuma és jelentése) vagyis
    amely az itt szereplő 64-es kódolásnak megfelelő számot adja vissza.
  - A függvény paramétere egy karakterlánc, eredménye pedig a kódolt
    numerikus érték legyen. (Elég ha a függvény max 6 hosszú, helyesen
    kódolt karakterláncokra működik, hosszabb karakterláncra vagy
    rosszul kódolt paraméterre adjon vissza -1-et.)
  - Ennek a fv-nek a segítségével adjuk meg egy táblabeli sor pontos
     fizikai elhelyezkedését. (Melyik fájl, melyik blokk, melyik sora)
     Példaul az  emp tábla azon sorára, ahol a dolgozó neve 'KING'.

  - Ennek a kurzusnak az egyik célkitűzése az, hogy az Oracle Database
     Online Documentation könyvtárban otthonosan mozogjunk, lásd pl.
     >> PL/SQL Packages and Types Reference >>  HTML     PDF 
     "90 DBMS_ROWID" fejezetét, mégis most gyakorlásként írjuk meg
     a fenti plsql függvényt (HF). A megírt függvényünk működése könnyen
     ellenőrizhető az DBMS_ROWID csomagban szereplő függvényekkel:     
    SELECT rowid, 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 emp
   WHERE ename='KING'

45row_08 (További példák a ROWID használatára)
   - Hozzatok létre egy EXCEPTIONS nevű táblát az utlexcpt.sql nevű
     script alapján, majd egy olyan constraintet, amelyet a táblában levő
     sorok közül néhány megsért. (Emiatt a constraint létrehozása hibaüzenetet
     kell, hogy eredményezzen.) Állapítsátok meg az EXCEPTIONS nevű
     tábla segítségével, hogy mely sorok sértik meg az iménti constraintet.
  - Az utlexcpt.sql nevű script a következő utasítást tartalmazza:
     CREATE TABLE exceptions(row_id rowid, owner varchar2(30),
                      table_name varchar2(30), constraint varchar2(30));
 
 
EA3. Előadáshoz kapcsolódó feladatok indexstruktúrákra

Feladatok - Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása
                    a "zöld könyv" 4.3, 4.4 és 5.4 fejezetei alapján
A fogalmakhoz jól használható Oracle dokumentáció: "Concepts"  HTML  PDF
                    ebben Part II. Oracle Database Architecture - 5. Schema Objects
 
Az indexek felépítése és szerkezete. A B-fa index levél és nem levél csúcsok
szerkezete. A Bitmap index szerkezete. B-fa és Bitmap indexek készítése papíron.
Bitvektorok tömörítése szakaszhossz kódolással. Indexek létrehozása Oracle-ben.
Különböző logikai műveletek elvégzése bitmap indexek segítségével.
                  
ea3_01 - B-fa
   - Az alábbi táblára készítsen B-fa indexet a dolgozó tábla DKOD oszlopára.
      Tegyük fel, hogy egy blokkba 3 bejegyzés fér el. Rajzolja fel (kézzel) a fát.

DKOD DNEV   FIZETES  FOGLALKOZAS  BELEPES  OAZON
---------------------------------------------------
1    SMITH     800   CLERK        1980     20  
2    ALLEN    1600   SALESMAN     1981     30      
3    WARD     1250   SALESMAN     1981     30      
4    JONES    2975   MANAGER      1981     20      
5    MARTIN   1250   SALESMAN     1981     30      
6    BLAKE    2850   MANAGER      1981     30      
7    CLARK    2450   MANAGER      1981     10      
8    SCOTT    3000   ANALYST      1982     20      
9    KING     5000   PRESIDENT    1981     10      
10   TURNER   1500   SALESMAN     1981     30      
11   ADAMS    1100   CLERK        1983     20      
12   JAMES     950   CLERK        1981     30      
13   FORD     3000   ANALYST      1981     20      
14   MILLER   1300   CLERK        1982     10     

ea3_02 - B-fa
   - Az alábbi feladatban a tankönyben szereplő algoritmussal építsünk fel egy B-fát!
     Tegyük fel, hogy egy B-fa blokkjaiba 3 kulcs fér el plusz 4 mutató. A kulcsok
     különbözőek. Szúrjuk be a B-fába az alábbi kulcsértékeket a megadott sorrendben:
     39,  15,  50,  70,  79,  83,  72,  43,  75,  45
     Adjuk meg a B-fa minden olyan állapotát, amikor egy csomópont kettéosztására
     volt szükség. Például, az első kettéosztás utáni állapot:
                                50
                   15 | 39         50 | 70

    - [Egy kis segítség:]
      -------------------
      Levél csúcs kettéosztásakor minden kulcsot megőrzünk a régi és az új (szomszédos)
      csúcsban. 1 új kulcs-mutató párt küldünk felfelé a szülő csúcsba, amit ott kell elhelyezni.
       
      Belső csúcs kettéosztásakor (N,M csúcsra) a mutatók első fele az N-be kerül,
      a második az M-be. A kulcsok első fele az N-be kerül a második fele az M-be,
     de középen kimarad egy kulcs, ami az M-en keresztül (első gyermekén keresztül)
     elérhető legkisebb kulcsot tartalmazza. Ez nem kerül sem N-be, sem M-be, hanem
     ez megy fölfelé N és M közös szülőjébe az M-re mutató mutatóval együtt.
 
ea3_03 - Bitmap
   - Tegyük fel, hogy fenti táblához a FOGLALKOZAS, a BELEPES és az OAZON
     oszlopokra létezik bitmap index (3 index). Készítsük el az alábbi lekérdezésekhez
     szükséges bitvektorokat, majd végezzük el rajtuk a szükséges műveleteket, és
     adjuk meg azt az előállt bitvektort, ami alapján a végeredmény sorok megkaphatók.
     Ellenőrzésképpen adjuk meg a lekérdezést SQL-ben is!
 
a. ) Adjuk meg azoknak a dolgozóknak a nevét, akik 1981-ben léptek be és a foglalkozásuk
      hivatalnok (CLERK), vagy a 20-as osztályon dolgoznak és a foglalkozásuk MANAGER.

b.) Adjuk meg azoknak a dolgozóknak a nevét, akik nem 1981-ben léptek be és a 10-es
     vagy a 30-as osztályon dolgoznak.

ea3_04 - Bitmap
   - Tömörítse az előző feladatban kapott bitvektorokat a szakaszhossz kódolással.

ea3_05 - Bitmap
   - Fejtsük vissza a következő, szakaszhossz kódolással tömörített bitvektort:
     11101101001011

ea3_06 - Hash
   - Mutassuk meg, hogy mi történik az alábbi ábra kosaraival a következő beszúrások
     és törlések bekövetkeztével
          
     0   d                              
             
     1   e    
     c    
     2    b    
             
     3   a    
     f    
    
    a.) A g, h, i, j rekordok beszúrása sorban a 0, 1, 2, 3 kosarakba
    b.) Az a és b rekordok törlése
    c.) A k, l, m, n rekordok beszúrása sorban a 0, 1, 2, 3 kosarakba
    d.) A c és d rekordok törlése
 
_______________________________________________________
5. gyak.  - Indexstruktúrák. Index szervezett táblák
 
>> 4.6. Feladatok indexekre, bitmap indexekre
>> 4.7. Index szervezett (IOT) táblák létrehozása és kezelése
  
4.6. Feladatok indexekre és bitmap indexekre

Lásd 43_indexek.txt CREATE INDEX és CREATE BITMAP INDEX
példái (illetve az SQL Reference CREATE INDEX példái) már voltak
korábban >> 4.3B. Indexek, bitmap indexek létrehozása és használata
Létrehoztunk egy vagy több táblához több különböző indexet, például
több oszlopos, csökkenő sorrendű, függvény alapú, bitmap index, stb.  

Most állapítsuk meg ezeknek az indexeknek a különböző tulajdonságait
a katalógusokból. Katalógus táblák leírása, lásd  Katalogus09s.html
DBA_INDEXES, DBA_IND_COLUMNS, DBA_IND_EXPRESSIONS

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

46_ind_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.)

46_ind_03
   - Adjuk meg az SH.SALES táblára létrehozott bitmap indexek nevét.

46_ind_04
   - a.) Adjuk meg azon kétoszlopos indexek nevét és tulajdonosát, amelyeknek
      legalább az egyik kifejezése függvény alapú .
   - b.) Adjuk meg az egyikükre, pl. 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.)

4.7. Feladatok index szervezett (IOT) táblákra

Index szervezett táblák. Objektumok azonosítója és adatobjektum azonosítója.
IOT részei (index, tábla) és ezek felderítése a katalógusban.
Katalógus táblák leírása és szerkezete, lásd  Katalogus09s.html
Lásd 47_IOT.txt CREATE TABLE ... ORGANIZATION INDEX  példákat!
 
47_iot_01
   - Hozzuk létre a 47_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 (IOT) 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ó?)

47_iot_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?

47_iot_03
   - Keressük meg a szegmensek között az előző táblákat illetve indexeket,
      és adjuk meg a méretüket.

47_iot_04
   - 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
     (DATA_OBJECT_ID).

47_iot_05
   - Adjuk meg a fenti táblák túlcsordulási részeit (ha van).

47_iot_06
   - Keressük meg a túlcsordulási részeket a szegmensek között és adjuk meg
     a méretüket.

47_iot_07
   - Keressük meg az objektum azonosítóikat és az adatobjektum azonosítóikat is.

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

47_iot_09
    - Írjunk meg egy plsql procedúrát, amelyik a paraméterül kapott index szervezett
      tábláról kiírja a tábla méretét.
      PROCEDURE iot_meret(p_owner VARCHAR2, p_tabla VARCHAR2)
      Vigyázzunk, mert a táblának lehet index és túlcsordulási szegmense is.
 
 _______________________________________________________
6. gyak.  - Partícionálás. Klaszterek
 
>> 4.8. Partícionált táblák létrehozása és kezelése. Feladatok partíciókra
>> 4.9. Klaszterek létrehozása és kezelése. Feladatok klaszterekre  
 
4.8. Partícionált táblák létrehozása és kezelése. Feladatok partíciókra
 
Táblák partícionálása. Range, hash és lista alapú partícionálás.
Összetett partícionálás, alpartíciók. Információk felderítése a katalógusban.
Lásd 48_particionalas.txt  CREATE TABLE... PARTITION
   BY RANGE/HASH/LIST stb... példákat próbáljuk ki!
Oracle dokumentációkból >> Concepts >> Part III. Features >>
   >> 18. Partitioned Tables, Partitioning Methods, Partitioned Indexes

További feladatok az adatszótárnézetek lekérdezésére
 Katalógus táblák leírása és szerkezete, lásd  Katalogus09s.html
DBA_PART_TABLES, DBA_PART_INDEXES,
DBA_TAB_PARTITIONS, DBA_IND_PARTITIONS,
DBA_TAB_SUBPARTITIONS, DBA_IND_SUBPARTITIONS,
DBA_PART_KEY_COLUMNS

48_prt_01
   - Adjuk meg az SH felhasználó tulajdonában levő partícionált táblák nevét  
      és a particionálás típusát.

48_prt_02
   - Soroljuk fel az SH.COSTS tábla partícióit valamint, hogy hány blokkot
      foglalnak az egyes partíciók.
      (Vigyázat! Különböző értéket kaphatunk a különböző adatszótárakban.
        Ennek magyarázatát lásd később az ANALYZE parancsnál)

48_prt_03
   - Adjuk meg, hogy az SH.COSTS tábla mely oszlop(ok) szerint van particionálva.

48_prt_04
   - Adjuk meg, hogy a NIKOVITS.ELADASOK3 illetve az SH.COSTS táblák
      második partíciójában milyen értékek szerepelhetnek.

48_prt_05
   - Adjuk meg egy partícionált tábla logikai és fizikai részeit! 
     (pl. NIKOVITS.ELADASOK).
     Maga a tábla most is logikai objektum, a partíciói vannak fizikailag tárolva.
     Nézzük meg az objektumok és a szegmensek között is.
   - Illetve ha alpartíciói is vannak (pl. NIKOVITS.ELADASOK4),
     akkor csak az alpartíciók vannak tárolva.
     Nézzük meg ezt is az objektumok és a szegmensek között is.

48_prt_06
   - Melyik a legnagyobb méretű partícionált tábla az adatbázisban a partíciók
     összméretét tekintve? (az alpartícióval rendelkező táblákat is vegyük figyelembe)
 
4.9. Klaszterek létrehozása és kezelése. Feladatok klaszterekre

 A klaszterek használata. Több tábla fizikailag egy helyen tárolva.
Mi a közös a táblákban? -> klaszter kulcs. Több oszlopos klaszterkulcs,
több tábla a klaszteren. Index klaszter és hash klaszter.
Lásd 49_klaszterek.txt  CREATE CLUSTER példáit próbáljuk ki!
Oracle dokumentációkból >> Concepts >> Part II. Architecture
           >> 5. Schema Objects >> Overview of Clusters / Hash Clusters

További feladatok az adatszótárnézetek lekérdezésére
Katalógus táblák leírása és szerkezete, lásd  Katalogus09s.html
DBA_CLUSTERS, DBA_CLU_COLUMNS, DBA_TABLES,
DBA_CLUSTER_HASH_EXPRESSIONS

49_cls_01
   - Hozzunk létre egy DOLGOZO(dazon, nev, beosztas, fonoke, fizetes, oazon)
     és egy OSZTALY(oazon, nev, telephely) nevű táblát.
    A két táblának az osztály azonosítója (oazon) lesz a közös oszlopa. A két táblát
    egy CLUSTEREN hozzuk létre. (Előbb persze létre kell hozni a clustert is.)

49_cls_02
   - Adjunk meg egy olyan clustert az adatbázisban (ha van ilyen), amelyen még
     nincs egy tábla sem.

49_cls_03
   - Adjunk meg egy olyant, amelyiken legalább 6 darab tábla van.

49_cls_04
   - Adjunk meg egy olyan clustert, amelynek a cluster kulcsa 3 oszlopból áll.
     (Vigyázat!!! Több tábla is lehet rajta)

49_cls_05
   - Hány olyan hash cluster van az adatbázisban, amely nem az oracle
     alapértelmezés szerinti hash függvényén alapul?
 
49_cls_06
   - Hozzunk létre egy hash clustert és rajta két táblát, majd szúrjunk be a
     táblákba sorokat úgy, hogy a két táblának 2-2 sora ugyanabba a blokkba
     kerüljön. Ellenőrizzük is egy lekérdezéssel, hogy a 4 sor valóban
     ugyanabban a blokkban van-e. (A ROWID lekérdezésével)
     (A sorok elhelyezését befolyásolni tudjuk a HASH IS megadásával.)