Index, nézet, trigger

Mer av detta

Adatbázis fizikai szerkezete, adatszótár és monitorozás

av Andrea Horváth

Adatbázis-rendszer választásának szempontjai

av Joci Csépányi

Elmetérkép

av Áron Rusák

Tranzakcionálisan konzisztens működés, zárak és működés naplózása

av Kinga Tajti

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

Készítette: Erdélyi Dániel

2017.03.21.-i Corvinusos Adatbázis-rendszerek előadás, azon készített jegyzeteim és külső források segítségével

Egyéb objektumok

Sorozat (sequence)

A behivatkozott Oracle dokumentációban vannak leírva a "sorozat" használati lehetőségei, előnyei, illetve általános felépítése. Ezeken kívül a legvégén példával demonstrálja a "sorozat" gyakorlati használatának lehetőségét.

Profil

A behivatkozott Oracle dokumentációban vannak leírva a "profil" használati lehetőségei, előnyei, illetve általános felépítése. Ezeken kívül a legvégén példával demonstrálja a "profil" gyakorlati használatának lehetőségét.

Könyvtár

A behivatkozott Oracle dokumentációban vannak leírva a "könyvtár" használati lehetőségei, előnyei, illetve általános felépítése. Ezeken kívül a legvégén példával demonstrálja a "könyvtár" gyakorlati használatának lehetőségét.

Jogosultság, szerepkör

A behivatkozott Oracle dokumentációban vannak leírva a "jogosultság" használati lehetőségei, előnyei, illetve általános felépítése. Ezeken kívül a legvégén példával demonstrálja a "jogosultság" gyakorlati használatának lehetőségét.

Kontextus

A behivatkozott Oracle dokumentációban vannak leírva a "kontextus" használati lehetőségei, előnyei, illetve általános felépítése. Ezeken kívül a legvégén példával demonstrálja a "kontextus" gyakorlati használatának lehetőségét.

Adatbázis link

A behivatkozott Oracle dokumentációban vannak leírva az "adatbázis link" használati lehetőségei, előnyei, illetve általános felépítése. Ezeken kívül a legvégén példával demonstrálja az "adatbázis link" gyakorlati használatának lehetőségét.

Szinoníma

A behivatkozott Oracle dokumentációban vannak leírva a "szinoníma" használati lehetőségei, előnyei, illetve általános felépítése. Ezeken kívül a legvégén példával demonstrálja a "szinoníma" gyakorlati használatának lehetőségét.

Trigger

Példa: ebben az esetben megadunk előre egy default (alapértelmezett) értéket az újonnan bevitt telefonszámokra, hogy egyezzen a munkatelefonszámmal, ha nincs kitöltve

CREATE TRIGGER Clients_Insert
BEFORE INSERT ON Clients
  REFERENCING NEW ROW AS New
FOR EACH ROW
  SET New.home_telephone =
      COALESCE(New.home_telephone,New.work_telephone);

forrás: https://mariadb.com/kb/en/sql-99/trigger-examples/

Hogyan hozhatunk létre trigger-t?

CREATE TRIGGER ... utasítással, programkód segítségével

Oracle dokumentációban ábrázolva: https://docs.oracle.com/database/121/SQLRF/img/create_trigger.gif

Procedurális kód

Az adatbázison belül tárolhatunk függvényeket, amelyek segítenek feldolgozni az adatokat.

Ezeket a metódusok programkódok, amiket oszlopokban (attribútumokban, az objektumorientált programozás szerint) tárolhatunk.

Egy procedurális kódhoz, szükség van egy programnyelvre is:

az SQL nem procedurális
Java
Oracle esetében: PL/SQL
eljárásokat, függvényeket, csomagokat (package), objektumorientált metódusokat és adatbázis triggereket írhatunk

Ennek hatására az Oracle adatbáziskezelő rendszer több procedurális nyelvet is érti, és képes futtatni.

Miért kell a trigger?

A kényszerek nem tudnak minden problémára megoldást nyújtani.

Például:

ugyanazon táblában lévő cellák összehasonlítása
jelenbeli dátum (sysdate) használata gondot okozhat
adatérvényesség biztosítása máshogy

Programkód segítségével lehet ezt megoldani. -->procedurális kód

Nézet

Materializált nézet

Az egyszerű nézet és a tábla tulajdonságait vegyíti
Egy mester tábla/nézet replikációja, amit egy adott időpontban hoztak létre
bármikor lehet frissíteni, szükség esetén
hosszú időt vehet igénybe
internethez nem mindig kapcsolódó, illetve offline adatbázisok esetében hasznos lehet az alkalmazásuk

További információ a materializált nézetről:

http://docs.oracle.com/cd/B10500_01/server.920/a96567/repmview.htm

Hogyan hozunk létre nézetet?

explicit módon: (példa)

CREATE VIEW nézetnév AS

SELECT oszlop1, oszlop2

FROM oszlopok

WHERE oszlop1 is not null;

Oracle dokumentációban ábrázolva: https://docs.oracle.com/database/121/SQLRF/img/create_view.gif

Később lekérdezhetünk a nézetből: (példa)

SELECT oszlop1, oszlop2, oszlop3

FROM nézetnév

WHERE oszlop2 is null;

Alapvető tulajdonságok

A nézet nem tárol adatot! Csak az eredményt létrehozandó lekérdezést (definíciót).
A nézet egy meta-adat (olyan adat, ami más adatról ad információt)

forrás: https://techterms.com/definition/metadata

Elsősorban a lekérdező munkáját könnyíti meg
Lehet jogosultságkezelési célja
Nincs nagy helyigénye (fizikailag)
A DML műveletek nem mindig megengedettek nézeteken keresztül

Miért kell a nézet?

A lekérdezések megírásának felgyorsítására használják, a lekérdező munkáját könnyíti meg.

Bonyolult lekérdezések helyett, eltárolunk kisebb lekérdezéseket, névvel látjuk el, és később behivatkozhatjuk, így megkönnyítve annak a szerkesztését, illetve kiszámítását.

Index

speciális, belső adatszerkezet
gyakran használt objektum a relációs adatbázisban
általában a táblához van hozzárendelve, de attól függetlenül van tárolva (külön is létre lehet hozni)
független --> több index létrehozása is lehetséges 1 adattáblához
a "fizikai adatbázis része"
fizikai adatbázis felépítése: https://technet.microsoft.com/en-us/library/ms179276(v=sql.90).aspx
logikailag összefügg a táblákkal, de fizikailag külön tárolódik

Milyen típusai vannak?

Egyéb megoldások

Különböző szakterületeken, különböző megoldások születhetnek.

Bittérképes

Olyan rendszerek esetében hasznos, ahol egyszerre kevés felhasználónak van jogosultsága a szerkesztéshez. Színek tárolására is alkalmas.

Eredeti gyártó: teradata, mások továbbvették

Egy összehasonlítása a B-fa típusú indexeléssel: http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html

Képes színeket tárolni, bináris kód segítségével
minden cellára megvan határozva egy 0-1 érték, hogy a fő színek közül van-e benne olyan
a sorok számától függ a hossza
ezenkívül rendelkezik egy rowid értékkel, ami mindig megegyezik
pl. gépjárművek színének tárolása egy rendőrségi adatbázisban, max. 100 szín ---> 100 bitvektor
pl. térképészet, térinformatika
nem sorszinten záródik
adattárházakban használhatóbb - felgyorsítja a SELECT utasítást
nehéz karbantartani folyamatosan
bitvektorok tömörítettek
OR feltételek hasznosak
újraindexelés lehetséges

Függvényalapú

Olyan függvényeredményeket tárolunk az indexben, amik tartalmaznak legalább egy oszlopot az indexelt táblából.

"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, DETERMINISTIC-usnak kell lennie, s nem vonatkozhat LOB típusú, REF vagy beágyazott tábla oszlopára sem."

forrás: http://oraoptimization.blogspot.hu/2008/03/architektra-part-4-sma-objektumok-22.html

B-fa+variációi

Különböző variációi léteznek, a fentebb említett szabványok hiánya miatt. Ilyen például a B* és a B+. Legfőképp az elsődleges kulcsok indexeinél használják, illetve OLTP típusú rendszerekben.

forrás: https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721

Alapvető tulajdonságai

B = balanced = minden irányban ugyanannyi lépés van a gyökértől a levélig

az index szerkezete szerteágazó, terebélyes
sorszinten záródik = több ember is tud egyszerre szerkeszteni
emiatt is használható jól OLTP rendszerekben, ahol ez egy fontos szempont a több felhasználó miatt
nehéz updatelni az indexelt oszlopokat
különösen, ha az oszlopértéke változik
az INSERT utasítás alatt is az index frissítése a hosszadalmasabb folyamat, nem az új adat bevitele

Szerkezete

Részei:

gyökér blokk: az alsóbb rétegbe mutatnak az indexadatok
közbülső csomópont: több réteg is lehet, ami alapján kapjuk meg a B-fa magasságát (n+2, ahol n a csomópontok száma)
levél blokk: itt vannak tárolva az indexértékek
kulcsértékek
mutatók: Oracle-ben a rowid - egyedi sorazonosító

Forrás az Oracle dokumentációból, amiben a B-fa indexeléssel kapcsolatban olvashatunk magyarázatot illetve, példát: https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1170

Példa

Nincsenek szabványok

Az adatbáziskezelő rendszereket fejlesztő cégek egymástól veszik át az ötleteket (~ lopás)

--> másik cég perel

--> peren kívüli megegyezés ("stratégiai együttműködés")

Főképp a praktikusság miatt léteznek hasonló típusok!

Hogyan hozunk létre indexet?

Index létrehozásra példa:

https://www.tutorialspoint.com/sql/sql-indexes.htm

Explicit

ha explicit (sql utasítással) hozzuk létre:

CREATE [UNIQUE] [CLUSTERED] [NONCLUSTERED]
  INDEX indexnév ON {táblanév | nézet} (oszlopnév [ASC | DESC] [, . . .n])
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = feltöltési_tényező]
[[,] IGNORE_DUP_KEY]
[[,] SORTED_DATA]
[[,] SORTED_DATA_REORG]
 [[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
[ON fájlcsoport]

Oracle dokumentációban ábrázolva: https://docs.oracle.com/database/121/SQLRF/img/create_index.gif

forrás: http://softwareonline.animare.hu/cikk.aspx?id=3638

ha explicit töröljük:

DROP INDEX indexnév;

Oracle dokumentációban ábrázolva:

https://docs.oracle.com/database/121/SQLRF/img/drop_index.gif

Automatikusan

Az egyedi indexek jönnek létre automatikusan, amikor primary key vagy unique kényszert hozunk létre. Jellemzően ez a folyamat azonnal megtörténik.

DML utasítások automatikusan módosíthatják
nem mindig! - adatbáziskezelők a korai fázisukban nem feltétlenül implementálják ezt a funkciót

Törlése: automatikusan törlődik, ha a kényszert eldobjuk/kikapcsoljuk

külön nem is engedi a rendszer, hogy eldobjuk explicit módon

Milyen csoportosításai vannak?

Ez a két csoportosítás típus nem zárja ki egymást, tehát mind a 4 fajta index létrehozható:

egyoszlopos és egyedi

egyoszlopos, de nem-egyedi

többoszlopos és egyedi

többoszlopos, de nem-egyedi

Egyedi/nem-egyedi

egyedi index: olyan index, ami a primary key vagy unique kényszer hatására jön létre automatikusan
elvileg létrehozható kényszer nélkül is, de nem ajánlott!
nem-egyedi index:
nem a felhasználói hatáskör része általában
az adatmodell készítője tervezi meg, hozza létre
(vagy rendszergazda, fejlesztő)
((ha késztermékről van szó))

Egyoszlopos/többoszlopos

ha a tábla kulcsa egyoszlopos: 1 oszlopra épül az index is (egyoszlopos)
ha a tábla kulcsa többoszlopos: több mezőt is meg lehet adni (többoszlopos index)
másképp: "konkatenált" vagy összetett index

Miért kell az index?

Hatékonyságnövelés

lekérdezések felgyorsítása
index mentén keresés
az adott kulcson keresztül tudjuk megtalálni az adatot
gyorsabb, mintha minden rekordon végigmegyünk
+ nem kell rendezni
nem mindig lehetséges
fizikailag a lemez más sorrendben olvassa be a sorokat, mint ami logikailag jobb döntés lenne
az index módosítás 3x-osan lassíthat egy beszúrást (1 sor: 1ms helyett 3ms)
nem-egyedi index használata
lehet az index csak lassító tényező --> ignorálható

Adatintegritási szabály ellenőrzése

adatintegritásra példa:
nem lehet két azonos érték egy azonosító oszlop 2 különböző mezőjében
ha megadott értékek szerepelhetnek csak az adott oszlopban, nem lehet azokon kívül mást beleírni

Forrás: https://technet.microsoft.com/en-us/library/ms184276(v=sql.105).aspx

primary key/unique kényszer biztosítása, kikényszerítése
CREATE... utasításnál index is létrejön (jellemzően azonnal)
példa: van egy táblánk 1millió sorral és 1 új sort szeretnénk hozzáadni

az elsődleges kulcs értéke nem egyezhet semelyik sorban
minél gyorsabban le kell ellenőrizni az összes sort, hogy az adatbázis gyorsabban frissüljön

Az előadás az adatbázis-rendszerek különböző objektumainak használatát és előnyeit taglalja. Fő témaként az Oracle adatbázisokban található sorozatok, jogosultságok, profilok, kontextusok, könyvtárak és szinonímák részletes ismertetését mutatja be.