Категории: Все - adatbázis

по Erdélyi Dániel 7 лет назад

434

Index, nézet, trigger

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.

Index, nézet, trigger

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:

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:

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

Nézet

Materializált nézet


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

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

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

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

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


Szerkezete

Részei:


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

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



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.



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

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
Egyoszlopos/többoszlopos
Miért kell az index?

Hatékonyságnövelés
Adatintegritási szabály ellenőrzése

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


  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