Index, nézet, trigger

Więcej takich

Az adatbázis fizikai szerkezete, az adatszótár, és az adatbáziskezelő rendszer monitorozása 4

przez Richárd Kele

4. előadás gondolattérképe

przez Anna Szalay

5.EA_Gondolatterkep

przez Zoltán Szabó

A számítógépes munka összetevői

przez Beáta Hintz

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

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