by Anna Szalay 7 years ago
486
More like this
Az alábbi gondolattérkép a 8. heti előadás anyagát dolgozza fel.
Ahol nincs megjelölve forrás, az előadás diáit és az előadáson készített jegyzeteimet használtam forrásként.
Készítette:
Szalay Anna Dorina
Az adatbáziskezelő rendszerek általában még sokfajta egyéb objektumtípust támogatnak. De ezek erősen gyártófüggőek, és nem szabványosak.
Ezeket is az SQL valamiféle "kiterjesztésével" hozzuk létre (CREATE), dobjuk el (DROP), vagy módosítjuk (ALTER).
Ide sorolhatóak még például:
Dimenziók:
A dimenziók hierarchikus (gyermek/szülő) viszonyt határoznak meg oszloppárok vagy oszlophalmazok között. Minden gyermek pontosan egy szülőhöz van társítva. Mivel a dimenzió csak logikai kapcsolatok gyűjteménye, nincs tárolási terület hozzárendelve. A dimenziót létrehozó CREATE DIMENSION parancs meghatározza a hierarchia szinteket (LEVEL), a köztük lévő viszonyt (HIERARCHY), illetve opcionálisan az ATTRIBUTE ágban kiegészítő oszlopot vagy oszlop halmazt az adott szinthez. Az oszlopok származhatnak egy táblából (denormalized) vagy több táblából (normalized). Utóbbi esetén illesztést kell alkalmazni a HIERARCHY ágban.
Szekvencia Generátor:
A szekvencia generátor segítségével számok szekvenciális sorozatát állíthatjuk elő (pl. egyedi elsődleges kulcsoknak). Segítségével többfelhasználós rendszerek esetén elkerülhetünk felesleges zárolásokat (pl. egyszerre két felhasználó is tud adatokat bevinni egy táblába). Méretük maximum 38 számjegy lehet, definíciójukat az adatszótárban (data dictionary) tároljuk. A számok generálása független az egyes tábláktól, így egy szekvencia generátort akár több táblánál is használhatunk.
Forrás: http://oraoptimization.blogspot.hu/2008/03/architektra-part-4-sma-objektumok-22.html
A GRANT SQL utasítás segítségével adhatunk rendszer jogosultságot és szerepkört amennyiben:
ADMIN OPTION jogosultság
Ha megadjuk a WITH ADMIN OPTION záradékot akkor a jogosultság a következőkkel bővül:
Forrás: http://slideplayer.hu/slide/2171770/
A szinonima egy alternatív név bármely táblára, nézetre, materializált nézetre, szekvenciára, eljárásra, függvényre, csomagra, típusra, Java osztályra, felhasználó által definiált objektum-típusra vagy egy másik szinonimára. Mivel adatot a szinonima sem tárol, így számára sem szükséges területeket lefoglalni…a definícióját az adatszótárban (data dictionary) tároljuk. Használatának két fő célja a biztonság és a kényelem, valamint az, hogyha a master táblájának megváltozik a neve, akkor csak magát a szinonimát kell átírni, s utána a szinonimát használó alkalmazások probléma nélkül futnak majd. A szinonima lehet public vagy private. Előbbi minden felhasználó számára elérhető, míg utóbbi egy bizonyos felhasználó sémájában található, s mások számára a hozzáférhetőséget ő határozhatja meg.*
*http://oraoptimization.blogspot.hu/2008/03/architektra-part-4-sma-objektumok-22.html
A PL/SQL segítségével:
írhatunk.
Utasítás szintű triggerek szintaxisa a következő:
CREATE [OR REPLACE] TRIGGER <triggernév> [BEFORE|AFTER] [DELETE] [OR INSERT] [OR UPDATE [OF <oszlopnév>]]
ON <táblanév>
[DECLARE {<lokális változó>}]
BEGIN
<törzs>;
END;
"A REPLACE opciót akkor használjuk, ha a trigger már létezik. Az OF kulcsszó segítségével megadhatjuk, hogy melyik oszlop módosítására legyen érzékeny a trigger. A PL/SQL blokkot a DECLARE vagy a BEGIN kulcsszóval kezdjük, és az END kulcsszóval zárjuk."
"Ha olyan triggert hozunk létre, mely többféle DML utasítás hatására is működésbe lép, előfordulhat, hogy a trigger törzsében el kell döntenünk, hogy éppen melyik utasítás végrehajtása indította a triggert. Így egy trigger eltérő módon viselkedhet INSERT, DELETE vagy UPDATE utasítás esetén."
Forrás: http://softwareonline.animare.hu/cikk.aspx?id=2573
Konkrét példa
A kép forrása:
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS2004
A kép forrása:
https://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm
CREATE OR REPLACE FUNCION dolgozo_neve(p_szigszam varchar2)
RETURN varchar2 IS
megoldas varchar2;
BEGIN
SELECT nev INTO megoldas FROM dolgozok
WHERE szigszam=p_szigszam;
RETURN megoldas;
END dolgozo_neve;
A dolgozo táblában keressük azt a dolgozót (a nevét), akinek a személyi igazolvány száma megegyezik a megadott személyi számmal (p_szigszam -> p, mert ez egy paraméter).
Később így használhatjuk fel a függvényt:
SELECT szigszam, dolgozo_neve(szigszam)
FROM dolgozok;
(saját példa)
Ehhez kell egy procedurális nyelv (ilyen lehet a Java, vagy akár lehet saját nyelv is). Az Oracle adatbázis esetén ilyn nyelv a PL/SQL.
Gyakran lenne szükségünk a már korábban tárgyakt kényszerek mellett azoknál bonyolultabbakat is alkalmazni, vagy esetleg olyan kényszereket, amelyek nem tartósan állnak fenn, csupán az adatok bevitele pillanatában érvényesek. Ezeket kénytelenek leszünk valami más módon biztosítani. Ha nem lehet deklaratív szabályokkal, akkor programkóddal tesszük majd ezt. Erre igen alkalmasak egyes adatbáziskezelő rendszerekben az alkalmazás triggerek.
"Az adatbázis trigger az adatbázis egy táblájához kötődő névtelen PL/SQL blokk, mely a táblán végrehajtott DML utasítások hatására automatikus végrehajtódik."*1
"Egy triggernek két állapota lehet: engedélyezett és letiltott. A letiltott trigger nem indul el, ha a kiváltó esemény bekövetkezik. Az engedélyezett trigger esetén az Oracle automatikusan a következő tevékenységeket hajtja végre:
*1 http://softwareonline.animare.hu/cikk.aspx?id=2573
*2 http://www.tankonyvtar.hu/hu/tartalom/tamop425/0046_plsql_programozas/ch13s03.html
"A nézetek olyan virtuális táblák, melyek adataikat egy vagy több fizikai táblából, vagy más nézetekből veszik. Fő feladatuk tehát az adatoknak egy előre megszabott formában történő megjelenítése, s ezáltal bizonyos adatok elrejtése. Működésük és a rajtuk végrehajtható műveletek többé-kevésbé megegyeznek a tábláknál megszokottakkal."*
* http://oraoptimization.blogspot.hu/2008/03/architektra-part-4-sma-objektumok-13.html
A nézet és a tábla tulajdonságait ismerjük már. De mit kell tudni a materializált nézetről?
"A materializált nézetek használatának célja a teljesítmény növelése, de jelentős többletfeladattal jár a menedzselésük"*1
"A materializált nézeteket adatok összegzésére, számítására, replikázására és szétosztására használhatjuk. Ebből kifolyólag főként adattárházaknál (data warehouse), döntéstámogató rendszereknél és elosztott vagy mobil számításoknál használjuk őket. Az optimalizáló (optimizer) automatikusan felismeri, hogy mikor lehet egy kérést materializált nézet segítségével kielégíteni, s automatikusan behelyettesíti azt a lekérdezésbe. Így nem szükséges közvetlen a táblákból vagy nézetekből kinyerni a kívánt adatokat, amivel növelhetjük a teljesítményt."*2
"A materializált nézetet létrehozó SELECT utasítás meghatározza, hogy a materializált nézet milyen adatokat tartalmaz. Csak néhány megszorítás van, amit specifikálhatunk. Tetszőleges számú táblát összekapcsolhatunk. A táblák mellett nézeteket, inline nézeteket (allekérdezés a SELECT utasítás FROM utasításrészében), allekérdezéseket, materializált nézeteket használhatunk az összekapcsolásban. A definiáló lekérdezés SELECT listájában viszont nem lehet allekérdezés, de például a WHERE feltételben igen."*3
"Néhány materializált nézet csak összekapcsolásokat tartalmaz (és nem tartalmaz aggregálásokat). Ennek előnye, hogy a drága összekapcsolások előre kiszámíthatók.
Az ilyen típusú materializált nézetek gyors frissítése minden típusú DML esetén lehetséges. A frissítés lehet ON COMMIT vagy ON DEMAND. Ha a REFRESH FAST opciót adjuk meg, az Oracle további ellenőrzéseket végez a definíción, hogy a gyors frissítés végrehajtható-e az alaptáblák változása esetén."*4
*1 http://www.tankonyvtar.hu/hu/tartalom/tamop412A/2010-0011_oracle_adattarhaz/lecke4_lap2.scorml
*2 http://oraoptimization.blogspot.hu/2008/03/architektra-part-4-sma-objektumok-13.html
*3 http://www.tankonyvtar.hu/hu/tartalom/tamop412A/2010-0011_oracle_adattarhaz/lecke4_lap2.scorml
*4 http://people.inf.elte.hu/vzoli/Adattarhaz/Hatteranyag/Adatt%C3%A1rh%C3%A1z_8_Ea_Materializ%C3%A1lt_n%C3%A9zet_2_v3.html
Nézet létrehozása:
CREATE VIEW v_preferred_restaurants AS
SELECT *
FROM restaurants
WHERE restaurant_stars=5;
View created.
Tábla létrehozása:
CREATE TABLE preferred_restaurants AS
SELECT *
FROM restaurants
WHERE restaurant_stars=5;
Table created.
Materializált nézet létrehozása:
CREATE MATERIALIZED VIEW mv_preferred_restaurants AS
SELECT *
FROM restaurants
WHERE restaurant_stars=5;
Materialized view created.
Nézet:
Tábla:
Materializált nézet:
A materializált nézet információinak forrása: http://oraoptimization.blogspot.hu/2008/03/architektra-part-4-sma-objektumok-13.html
*http://www.tankonyvtar.hu/hu/tartalom/tamop412A/2010-0011_oracle_adattarhaz/lecke4_lap2.scorml
Nézet létrehozására egy példa:
CREATE VIEW v_preferred_restaurants AS
SELECT *
FROM restaurants
WHERE restaurant_stars=5;
View created.
Később az általunk létrehozott nézetből akár le is kérdezhetünk:
SELECT restaurant_name, restaurant_owner, restaurant_city
FROM v_preferred_restaurants
WHERE city='London';
17 rows selected.
Ahelyett, hogy állandóan bonyolult lekérdezéseket kellene írnunk a táblákból, létrehozhatunk egy nézetet (CREATE VIEW parancs segítségével), amelyben egy lekérdezést definiálunk és névvel látunk el.
Később ezekből a nézetekből tudunk majd lekérdezni, és így a komplex SELECT két részre bomlik, és ezzel egyszerűsödik a munkánk.
Nem írja elő szabvány, hogy milyenek léteznek.
ÉRDEKESSÉG
A vállalatok egymástól lopják az ötleteket, így az indexek az évek során egyre jobban hasonlítanak egymásra. A cégek még a bírósági perektől sem riadnak vissza.
Egyes szakterületeken, például hangfelvételek tárolásakor vagy képfeldolgozáskor, speciális formátumra van szükség az indexeléshez.
*http://mierdekel.hu/klwp/2015/03/27/adattarhaz-adatelemzesek-riportok-bitterkep-indexek-bitmap-indexes/
A csatolt képet nézve például:
Egy bittérképes indexben bitvektorok helyezkednek el (ez csupa egyesből és 1-ból áll), például ha az oszlop színeket tartalmaz akkor annyi index bejegyzés lesz, ahány szín van.
Minden egyes bitvektor annyi bitből áll, ahány sora van a táblának. Ha a tábla első sorában kék érték szerepel a szín oszlopban akkor a kék bitvektorban az első bit értéke 1-es lesz míg a többi szín bitvektorának első bitje 0-ás. Ha a második sorban piros szín szerepel, akkor a piros bitvektor első bitje 0, de második bitje 1-es lesz, míg a többi vektor második bitje nulla, stb.
Példa
"Létrehozhatunk indexeket olyan függvényekre és kifejezésekre is, melyek tartalmaznak legalább egy oszlopot egy indexelt táblából. Ezek a függvény-alapú indexek kiszámolják a függvény vagy kifejezés értékét, s azt tárolják az indexben. Típusát tekintve B-fa vagy bitmap indexek lehetnek. A függvény maga lehet aritmetikai kifejezés, vagy olyan kifejezés, ami tartalmaz PL/SQL függvényt, csomag függvényt, C hívást vagy SQL függvényt. Nem tartalmazhat azonban aggregátum függvényeket."*
*http://oraoptimization.blogspot.hu/2008/03/architektra-part-4-sma-objektumok-22.html
Tulajdonságai
A szerkezet ponthoz csatolt képen láthatunk egyes helyeken ...-okat. Az index valójában sokkal nagyobb, mint amit az ábra sugall.
A B-fa jellemzően sorszinten zárolódik, csakúgy, mint egy tábla. Ez a sorszintű lockolás teszi lehetővé, hogy OLTP rendszerek jól működhessenek.
Egy-egy INSERT utasításkor jellemzően nehezebb az index frissítése, mint a sor beszúrása a táblába. Különösen nehéz olyan UPDATE-et végrehajtani, amelyben az indexelt oszlop értéke változik.
Jelentése
A B-fa 'B' betűje nem binárist, hanem "balanszírozottat" jelent. A balanszírozottság azt jelenti, hogy minden irányban ugyanannyi lépés van a gyökértől a levélig. Tehát azonos magas minden irányban.
Szerkezete
Ahogyan a csatolt képen is látható, a B-fa gyökérből, tehát egy központi elemből, ágakból - közbülső csomópontokból - és levelekből áll. A levelekben találhatóak a kulcsértékek és a mutatók. A tábla ugyan "rendezetlen", de az index maga rendezett.
"A levélelemekben nem csak az index kulcsa tárolódik el, hanem az ún. ROWID vagyis egy sorazonosító is."* A ROWID az Oracle sajátja. Egy pointer, ami rámutat az adott sorra.
*http://mierdekel.hu/klwp/2015/03/27/adattarhaz-adatelemzesek-riportok-bitterkep-indexek-bitmap-indexes/
B-fa szerkezeti ábra
Ha az indexre nincs többé szükségünk, eldobhatjuk a DROP INDEX utasítás segítségével.
Például:
DROP INDEX lakohely_cim_egybe;
index LAKOHELY_CIM_EGYBE dropped.
Ha a kényszert eldobjuk (vagy csupán kikapcsoljuk), akkor az index automatikusan eldobódik.
Ha egy index automatikusan a kényszer létrehozásakor jött létre (tehát implicit módon), akkor jellemzően explicit módon (tehát DROP INDEX használatával) nem dobható el.
Amennyiben nem a PRIMARY KEY vagy a UNIQUE kényszer eredményeképpen jön létre, hanem explicit módon, akkor a CREATE INDEX utasítást használjuk.
Példa:
CREATE INDEX lakohely_cim_egybe
ON lakohely (utca, hsz);
index LAKOHELY_CIM_EGYBE created.
Az egyedi és a nem egyedi indexeknél is ugyanúgy létezik egy- és többoszlopos típus is.
"Fizikai tárolásban nincs különbség közöttük."*
*https://db.bme.hu/~gajdos/2012adatb2/12.%20eloadas%20Oracle%20indexeles%20ppt.pdf
Nem egyedi
A nem egyedi indexeket nem az adatbázis hozza létre, hanem a tervező. Ha ezt nem teszi meg, félő, hogy túlságosan lassúak lesznek a lekérdezések.
Később is hozzá lehet adni, de nehéz. Utólagosan a rendszergazdák vagy a fejlesztők szokták létrehozni. Fontos ellenőrizni, hogy a gyártó engedélyezte-e, hogy módosítsanak benne a terméktámogatás elvesztése nélkül. Ha nem, fel kell venni a gyártóval a kapcsolatot.
Egyedi
Általában nem a felhasználó explicit DDL parancsa hozza létre, hanem a felhasználó a táblának egy PRIMARY KEY vagy UNIQUE kényszerét hozza létre, és az index ilyenkor magától létrejön.
Elvileg létezik SQL szintaxis egyedi indexek explicit létrehozására is, de ez elkerülendő. Ilyenkor inkább kényszert kell deklarálni.
Többoszlopos
A többoszlopos kulcsok többoszlopos indexeket eredményeznek. Ezeket még konkatenált = összetett indexeknek is nevezzük.
Jobb mint több egyoszlopos index használata, mert:
• Gyorsabb lekérdezés
• Gyorsabb frissítés
lehetséges általa.
Alkalmazásuk során figyelni kell, hogy az oszlopokat milyen sorrendben tároljuk az indexben. A lekérdezések csak akkor tudják használni a kompozit indexeket, ha az első néhány oszlop szerepel az utasítás (select, update, delete) where feltételében.
Akár egyszerre több féle index is létezhet egy táblában, ha megfelelnek bizonyos követelményeknek (pl.: különböző típusú indexeknek kell lenniük), ld.: http://docs.oracle.com/database/121/CNCPT/indexiot.htm#CNCPT88833. Ennek a tulajdonságnak a kihasználásával akár egyedi és nem egyedi indexeket is kreálhatunk ugyanarra az oszlop csoportra.
Forrás: https://db.bme.hu/~gajdos/2012adatb2/12.%20eloadas%20Oracle%20indexeles%20ppt.pdf
Egyoszlopos
Ha a tábla kulcsa egyoszlopos, akkor az index is arra az egy oszlopra épül (vagyis azt indexeli).
"Automatikusan létrejön a Primary key és Unique oszlopokra."* Valamint fontos tudni, hogy a "null értékek nem kerülnek be az indexbe".*
Az egyoszlopos egyedi és nem egyedi indexekről többet olvashatunk a http://docs.oracle.com/database/121/CNCPT/indexiot.htm#CNCPT88837 weboldalon.
*https://db.bme.hu/~gajdos/2012adatb2/12.%20eloadas%20Oracle%20indexeles%20ppt.pdf
"Sok index létrehozása lassítja a rekordok létrehozását, törlését és módosítását, valamint jelentősen megnövelheti az adatbázis méretét, ezért általában csak ahhoz a mezőhöz érdemes indexet létrehozni, amelynek értékei alapján gyakran futtatunk lekérdezést, vagy gyakran végzünk sorba rendezést."*
*http://tudasbazis.sulinet.hu/hu/szakkepzes/informatika/adatbazis-kezeles/relaciok-kepzese-es-matematikai-hatteruk/index-fogalma-hasznalata
Az index célja:
Nehéz megállapítani az ilyen oszlopokba való beszúráskor, hogy létezik-e már ilyen elem. Az indexek segítségével ez a folyamat hatékonyabban megy végbe.
Milyen esetekben nem lesz gyorsabb? Erre a legjobb példa az előadáson elhangzott INSERT utasítás használata. 1ms a beszúrás időtartama, míg az ehhez tartozó index módosítása 2ms.
Definíció: Az index a táblához hozzárendelt, de attól rendszerint függetlenül tárolt "objektum" az adatbázisban.
(Fontos: Itt az objektum fogalma nem egyenlő az objektum orientáltság fogalmába foglalt objektum jelentésével. Ebben a definícióban inkább entitást, elemet jelent.)
Külön SQL utasítással hozzuk létre. Nincs előírva, hogy lennie kell az adatbázisban.
Egy fa szerkezet (lásd a csatolt képen). Minden ami fontos, a fa levelein helyezkedik el. Mivel a fa rendezett, fentről (a fa gyökeréből) kiindulva kezdjük a vizsgálódást.
A DML-ek ÁLTALÁBAN automatikusan módosítják (frissítik) az indexet is. Csak általában, mivel például az Oracle is csak a -es verzióban sajátította el ezt a tulajdonságot. (Előtte, ha beszúrtunk, újra kellett kreálni az indexeket, mert az eddigiek érvénytelenné váltak.)
"A relációkban tárolt információk visszakeresését az indexek nagymértékben meggyorsíthatják, így a tervezés során nagy hangsúlyt kell fektetni a helyes indexek kiválasztására, szem előtt tartva azt is, hogy az indexek számának növelésével az adatok beviteléhez illetve módosításához szükséges idő megnövekszik az indexek frissítése miatt. Csak azokat a mezőket válasszuk ki indexelésre, amik szerint a későbbiek során rendezni, vagy keresni szeretnénk."*
*http://tudasbazis.sulinet.hu/hu/szakkepzes/informatika/adatbazis-kezeles/relaciok-kepzese-es-matematikai-hatteruk/index-fogalma-hasznalata
