Indexek, nézetek, adatbázisban tárolt programok, egyéb elemek a relációs adatbázisokban

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

Az index célja:A PRIMARY KEY és UNIQUE kényszerek biztosítása (az ilyen tulajdonságú oszlopokban egyedi értékeket tárolunk)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.A lekérdezések ESETLEGES felgyorsítása (a lekérdezések sebessége nem minden esetben válik gyorsabbá, de azért az esetek nagy százalékában ez a jellemző), akár 10, 100 vagy 1000-szeres gyorsítás is elérhető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. az adatintegritási szabály ellenőrzése hatékonyabb (gyorsabb) legyen

"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 egyedi és a nem egyedi indexeknél is ugyanúgy létezik egy- és többoszlopos típus is.

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

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éslehetsé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

"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

Á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.

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.

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_egybeON lakohely (utca, hsz);index LAKOHELY_CIM_EGYBE created.

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.

Nem írja elő szabvány, hogy milyenek léteznek.ÉRDEKESSÉGA 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.

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/

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.

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.

"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

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.

"A bittérképes index haszna igazán olyan oszlopoknál mutatkozik meg, amelyekben kevés a különböző érték, de ezekből sok fordul elő, és az értékek közel egyenletes eloszlást mutatnak."* Kis kardinalitású oszlopokra jobban beválik.Jellemzően nem sorszinten lockolódik.A folyamatos online karbantartása sokkal nehezebb.A gyártók többségénél a bitvektorok tömörítettek.OLTP rendszereknél szinte mindig tragikusan rossz a használatuk. Viszont az adattárházaknál a SELECT utasítást nagyon fel tudják gyorsítani. Különösen az OR feltételek esetén múlják felül a B-fa indexeket a SELECT felgyorsításában.Gyakran az ETL folyamat része az is, hogy a bittérképes indexeket a folyamat kezdetén eldobják, majd a végén újraépítik. Ez az újraindexelés.*http://mierdekel.hu/klwp/2015/03/27/adattarhaz-adatelemzesek-riportok-bitterkep-indexek-bitmap-indexes/

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.

"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

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 tárol adatot, csupán az eredményt létrehozandó lekérdezést, azaz a nézet definíciójátA nézet csupán meta-adatElsősorban megkönnyíti a lekérdező munkájátLehet jogosultságkezelési célja isA DML műveletek nem mindig megengedettek a nézeten keresztül

Nézet létrehozására egy példa:CREATE VIEW v_preferred_restaurants ASSELECT *FROM restaurantsWHERE 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_cityFROM v_preferred_restaurantsWHERE city='London';17 rows selected.

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:gyors, egyszerűsítnem tárol adatot, csupán a nézet definíciójátmegmarad a lekérdezés logikájaTábla:ténylegesen tárolja az adatotaz újonnan létrehozott tábla teljesen különálló lesz az eredetitől, tehát itt nincs meg a lekérdezés logikájaMaterializált nézet:nézet és a tábla kombinációjatárhelyet fogyasztanak.ha változnak az adatok a master táblájában, akkor frissíteni kell őket.lekérdezések behelyettesítése által növelik az SQL végrehajtások teljesítményét.felhasználók és alkalmazások szempontjából átlátszóakközvetlenül hozzáférhetünk egy SELECT utasítássaladattárház jellegű rendszerek kedvelt eleme"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"*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ása:CREATE VIEW v_preferred_restaurants ASSELECT *FROM restaurantsWHERE restaurant_stars=5;View created.Tábla létrehozása:CREATE TABLE preferred_restaurants ASSELECT *FROM restaurantsWHERE restaurant_stars=5;Table created.Materializált nézet létrehozása:CREATE MATERIALIZED VIEW mv_preferred_restaurants ASSELECT *FROM restaurantsWHERE restaurant_stars=5;Materialized view created.

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:Lefuttatja a triggert. Ha ugyanarra az utasításra több azonos típusú trigger van definiálva, akkor ezek sorrendje határozatlan.Ellenőrzi az integritási megszorításokat és biztosítja, hogy a trigger ne sértse meg azokat.Olvasási konzisztenciát biztosít a lekérdezésekhez.Kezeli a trigger és a sémaobjektumok közötti függőségeket.Osztott adatbázis esetén, ha a trigger távoli táblát módosított, kétfázisú véglegesítést alkalmaz."*2*1 http://softwareonline.animare.hu/cikk.aspx?id=2573*2 http://www.tankonyvtar.hu/hu/tartalom/tamop425/0046_plsql_programozas/ch13s03.html

ha csak adatok "perzisztálására" használjuk az adatbázist, és ott kódot nem tárolunk, akkor a teljes program-logika távol áll az adatoktól. Ez gyakran nem célszerű.praktikus és hatékony az adatokat még ott az adatbázisban feldolgozni, azok átmozgatása nélkülezt adatbázisban tárolt eljárásokkal és függvényekkel tehetjük megaz objektumorientált programozás szerint az objektum-típus attribútumokból és metódusokból álltábláinkban az oszlopok játsszák az attribútumok szerepét...de akkor a metódusok = programkódok -> ezeket is jó lenne tárolniEhhez 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.

A PL/SQL segítségével:eljárásokatfüggvényeketcsomagokat (package)objektumorientált metódusokatadatbázis triggereketírhatunk.

CREATE OR REPLACE FUNCION dolgozo_neve(p_szigszam varchar2)RETURN varchar2 ISmegoldas varchar2;BEGINSELECT nev INTO megoldas FROM dolgozokWHERE 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)

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

A kép forrása:https://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm

A kép forrása:https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS2004

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

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 GRANT SQL utasítás segítségével adhatunk rendszer jogosultságot és szerepkört amennyiben:Van ADMIN OPTION vagy ANY PRIVILEGE rendszer jogosultságunk Egy szerep kiosztásához ADMIN OPTION vagy ANY ROLE szerepkörre van szükségünk.ADMIN OPTION jogosultságHa megadjuk a WITH ADMIN OPTION záradékot akkor a jogosultság a következőkkel bővül: A kezdeményezett adhat vagy visszavonhat rendszer jogosultságot vagy szerepkört az adatbázisban. A felhasználók önmaguktól nem vonhatják meg a jogosultságot A kezdeményezett kiadhatja a rendszer szintű jogosultságot vagy szerepet az ADMIN OPTION segítségévelA kezdeményezett módosíthatja vagy eldobhatja a szerepkört Pl.: GRANT new_dba TO michael WITH ADMIN OPTION;Forrás: http://slideplayer.hu/slide/2171770/

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