1
~.. . lk
8. fejezet
Nézetek és indexek Ezt a fejezetet a nézettáblák ismertetésével kezdjük. A nézettábla olyan reláció, melyet más relációkra vonatkozó lekérdezésekkel definiálunk. A nézettáblák az adatbázisban nincsenek tárolva, de ugyanúgy lekérdezhetok, mintha létezo táblák lennének. A nézettáblára vonatkozó lekérdezés végrehajtásakor a lekérdezésfeldolgozó a nézettábla definícióját felhasználva látszólagosan eloállítja a nézettáblát. A nézettáblák tárolhatók is abban az értelemben, hogy az adatbázisból periodikusan eloállíthatók és tárolhatók is. A tárolt nézettáblákkal a lekérdezések végrehajtási sebessége növelheto. A tárolt nézet táblák nagyon fontos speciális típusa az index, ami olyan tárolt adatstruktúra, amelynek egyedüli célja a tárolt relációk meghatározott sorai elérésének felgyorsítása. E fejezetben be fogjuk mutatni az indexeket, és áttekintjük a tárolt táblákhoz a megfelelo index kiválasztásának legfontosabb kérdéseit.
8.1. Nézettáblák A CREATETABLEutasítással definiált táblák fizikailagléteznek az adatbázisban, azaz az adatbázisrendszer valamilyen fizikai struktúrában tárolja oket. Megtartják az állapotukat, azaz nem változnak addig, amíg valamilyen táblamódosító SQL-utasítás meg nem változtatja oket. Létezik az SQL-relációknak egy másik típusa, amit nézettáblának nevezünk, amelyek nem léteznek fizikailag az adatbázisban. A nézettáblákat a lekérdezéshez hasonló kifejezés segítségével definiáljuk. A nézettáblákat ugyanúgy lekérdezhetjük, mint a fizikailag létezo táblákat, és egyes esetekben még módosíthatjuk is oket.
362
8. Nézetek és indexe k
Relációk,
~
363 8.1. Nézet
táb lák
A nézettábla definíciója:
táblák és nézettáblák
CREATE VIEW FilmProducer AS SELECT Filmek.filmcím, név FROM Filmek, GyártásIrányító azonosító; WHERE producerAzon
Az SQL-programozók gyakran használják a "tábla" szót a ,,reláció" szó helyett. Az ok abban rejlik, hogy fontos különbséget tenni a tárolt relációk, azaz a "táblák" és a virtuális relációk, a ,,nézettáblák" között. Most, hogy már ismerjük a különbséget a tábla és a nézettábla között, a ,,reláció" megnevezést csak ott fogjuk használni, ahol a táblát és a nézettáblát is használhatnánk. Amikor hangsúlyozni akarjuk, hogy egy reláció tárolt, akkor az "alapreláció" vagy "alaptábla" kifejezéseket fogjuk használni. Létezik egy harmadik típusú reláció is, amely nem nézettábla és nincs fizikailag tárolva. Ezek az ideiglenes relációk, amelyek valamely lekérdezés eredményeképpen jöttek létre. Ezekrol is ,,relációként" fogunk említést tenni.
=
Fenti definíció két relációt kapcsol össze és az azonosítók egyezését követeli meg. A film címét és a producer nevét a megegyezo azonosítójú sorpárokból állítja e16. O
8.1.2. Nézettáblák
lekérdezése
A nézettáblák pontosan ugyanúgy kérdezhetok le, mint a ténylegesen tárolt alaptáblák.
A FROMzáradékban
8.1.1. Nézettáblák létrehozása A legegyszerubb mód egy nézettábla létrehozására a következo:
8.3. példa. Úgy kérdezhetjük le a ParamountFilmek relációt, mintha egy tárolt relációlenne:
CREATE VIEW
AS <definíció>;
A nézettábla definíciója egy SQL-lekérdezés.
SELECTfilmcím
8.1. példa.
FROM ParamountFilmek
Tételezzük fel, hogy egy olyan nézet táblát szeretnénk, amely a
WHERE
Filmek(filmcím,
év, hossz, múfaj,
stúdióNév,
CREATE VIEW ParamountFilmek
2) 3) 4)
a 2-4. sorok között található meg. 8.2. példa. lára.
A
filmdm
és év.
a Paramount
által 1979-ben gyártott
filmek címeit kapjuk
és alap-
SELECT DISTINCT színészNév FROM ParamountFilmek, SzerepelBenne
látható, hogy a nézettábla neve ParamountFilmek. a 2. sor tartalmazza:
1979;
8.4. példa. Írhatunk olyan lekérdezéseket is, amelyek nézettáblákat táblákat is tartalmaznak. Ilyen például a következo:
AS
WHERE
la attribútumait
=
Ezzel a lekérdezéssei meg. O
SELECTfilmcím,év FROM Filmek WHERE stúdióNév= 'Paramount';
Az 1.sorban
év
producerAzon)
reláció egy részét specifikálja, pontosabban a Paramount stúdió által gyártott filmek címét és gyártási évét. Ezt a nézettáblát a következoképpen definiálhatjuk: 1)
megadjuk a nézettábla nevét, és a nézettábla
definíciójáthasználva az adatbázisrendszerre bízzuk, hogy eloállítsa a lekérdezéshezszükségessorokat.
A
nézettábla
A nézettáb-
filmcím
= filmCím
AND
ParamountFilmek.év
= SzerepelBenne.filmÉv;
definíciója
O
Ez a lekérdezés a Paramount által gyártott filmekben szereplo összes színész nevét megadja. O
Nézzünk egy példát bonyolultabb lekérdezéssei definiált nézettáb-
célunk a FilmProducer
nézettábla
létrehozása,
amely a filmek címét
Azt, hogy mit is jelent a nézettáblák használata, a legegyszerubben úgy t~djuk megmutatni, hogyha a FROMzáradékban a nézettáblákat kicseréljük a nezettáblákat definiáló alkérdéssel. Az alkérdést követo sorváltozóval tudunk az
és a producereik nevét tartalmazza. Ezt a nézettáblát a következo két reláció felhasználásával tudjuk definiálni:
a~kérdéssellétrehozott tábla soraira hivatkozni. Például a 8.4. példa lekérdezésenek eredménye megegyezik a 8.1. ábrán látható lekérdezés eredményével.
Filmek(filmcím, év, hossz, múfaj, stúdióNév, producerAzon) GyártásIrányító(név, cím, azonosító, nettóBevétel)
a
364
8. Nézetek és indexek SELECT DISTINCT FROM
színészNév
(SELECT filmcím, FROM Filmek WHERE stúdióNév
8.1.2. feladat. A 8.1.1. feladat nézettábláit használva (alaptáblák használata nélkül) adjuk meg a következo lekérdezéseket:
év
=
aj Keressük meg azoknak a noknek a neveit, akik gyártásirányítók és színészek is.
'Paramount'
) Pm, SzerepelBenne WHERE
Pm.filmcím
8.1. ábra.
= filmCím
A nézettábla
8.1.3. Attribútumok
AND
Pm. év
használatának
=
SzerepelBenne.filmÉv;
alkérdéssel
! ej Keressük meg azon stúdióelnökök nevét, akik egyben színészek is és nettó bevételük legalább 50 000 000 $.
átnevezése
CREATE VIEW FilmProducer(filmCím, SELECT Filmek.filmcím, név WHERE
producerNév)
AS
GyártásIrányító
producerAzon
=
bJ Keressük meg azon gyártásirányítók neveit, akik egyben stúdióelnökök is és nettó bevételük legalább 10 000 000 $.
való megvalósítása
Néha szeretnénk más attribútumneveket használni a nézettábla definíciójában, mint amelyek a nézettáblát definiáló lekérdezésMI adódnak. A nézettábla attribútumait megadhatjuk egy zárójelek közé írt lista formájában, amelyet a CREATEVIEWutasításban a nézettábla neve után kell írni. Például a 8.2. példa nézettáblájának definícióját a következoképpen lehetne átírni:
FROM Filmek,
365 8.2. Adatok módosftása nézettáblákon keresztül
azonosító;
A nézettábla ugyanaz lesz, attól eltekintve, hogy az oszlopnevek nem f ilmdm és név, hanem filmCím és producerNév lesznek.
8.1.4. Feladatok 8.1.1. feladat. A következoalaptáblákból kiindulva: FilmSzínész (név , cím, nem, születésiDátum) GyártásIrányító(név, cím, azonosító, nettóBevétel) Stúdió (név, cím, elnökAzon)
Építsük fel a következo nézettáblákat: a) Egy GazdagProducer nézettáblát, amely a legalább 10 000 000 $ nettó bevételu gyártásirányítók nevét, címét, azonosító számát és bevételét adja meg. b) Egy StúdióElnök nézettáblát, amely azon gyártásirányítók nevét, címét és azonosító számát tartalmazza, akik stúdióelnökök is.
8.2. Adatok módosítása
keresztül
Korlátozott módon lehetséges beszúrni, törölni vagy változtatni az adatokat egy nézettáblán. Elso látásra teljesen értelmetlen ötletnek tunik, mivel a nézettábla nem létezik abban a formában, amelyben egy alaptábla (tárolt reláció) létezik. Mit jelent például egy új sor beszúrása egy nézettáblába? Hol tároljuk, és honnan emlékezzen majd arra az adatbázis-kezelo, hogy a sor a nézettáblába került? A legtöbb nézettábla esetén nem engedjük meg az ilyen beszúrást. Eléggé egyszeru nézettáblák esetén azonban a nézettábla módosítását átalakíthatjuk az alaptábla módosításává, amely ugyanolyan hatású lesz, mintha a nézettáblát módosítanánk. Az ilyen nézettáblákat módosítható nézettábláknak nevezzük. Az ,~nstead oP' (a kiváltó esemény helyett muködo) trigger is használható arra, hogy a nézettábla módosítását az alaptábla módosításával végezze el. Ezzel a programozó meg tudja határozni, hogy a nézettáblán keresztüli módosítás hogyan történjen.
8.2.1. Nézettábla megszüntetése A nézettábla különleges módosításának tekintheto a nézettábla megszüntetése. Ezt a ,~áltoztatást" akkor is végre lehet hajtani, ha a nézettábla nem módosítható. A megszüntetési utasítás: DROP VIEW ParamountFilmek;
Ez az utasítás kitörli a nézettábla definícióját, tehát utána már nem kérdezhetjük le és nem módosíthatjuk a nézettáblát. Ugyanakkor a nézettábla megszüntetése nincs kihatással az alaptábla soraira. Ellenben, a DROP
e) Egy ProdSzínész nézettáblát, amely azon személyek nevét, címét, nemét, születési dátumát, azonosító számát és nettó bevételét tartalmazza, akik egyben gyártásirányítók és színészek is.
nézettáblákon
TABLE Filmek
nemcsak a Filmek táblát szünteti meg, hanem a ParamountFilmek nézettáblát is használhatatlanná hivatkozik.
teszi, mivel az egy már nem létezo Filmek relációra
366
8. Nézetek és indexek
8.2.2. Módosítható
nézettáblák
Az SQL-szabvány formálisan leírja, mikor lehet egy nézettáblát módosítani és mikor nem. Az SQL-szabályok bonyolultak, de nagyjából azt engedik meg, hogy egy R reláción (amely szintén lehet módosítható nézettábla) definiált nézettáblát módosíthassunk, ha definíciójában a SELECTután DISTINCTnem szerepel. Két fontos kikötés van:
. .
A WHERE záradékban R nem szerepelhet egy alkérdésben sem.
.
semmilyen más reláció nem szerepelhet (a FROMzáradékban).
A FROMzáradékban csak R szerepelhet, csak egyszer fordulhat itt elo és A SELECTzáradék listája elég attribútumot kell tartalmazzon ahhoz, hogy egy beszúrás esetén a többi attribútumot nullértékkel, vagy az alapértelmezett értékkel tölthessük fel az alaptáblában. Például kötelezo az olyan attribútumérték megadása, mely NOTNULL-nakvan deklarálva és nincs alapértelmezett értéke.
A nézettáblába való beszúrás közvetlenül a nézettábla R alaptáblájába történik. Az egyetlen finom különbség az, hogy csak azon attribútumoknak tudunk így értéket adni, amelyek a nézettáblát definiáló SELECTzáradékban elofordulnak. 8.5. példa. Tételezzük fel, hogy 8.1. példában definiált ParamountFilmek nézettáblába szeretnénk egy sort beszúrni a következo módon: INSERT INTO ParamountFilmek
VALUES('Csillagok
háborúja',
1979);
A ParamountFilmek nézettábla megfelel az SQL módosíthatósági feltételeinek, mivel a következo alaptábla néhány attribútumára vonatkozik: Filmek(filmcím,
év, mufaj,
hossz,
stúdióNév,
producerAzon)
A ParamountFilmek nézettáblába való beszúrás a Filmektáblába való következo beszúrásként
kerül végrehajtásra:
367 8.2. Adatok módosftása nézettáblákon keresztül a 8.3. példa lekérdezése a most beszúrt sort ('Csillagok háborúja', 1979) nem fogja figyelembe venni. Ezen anomália elkerülése végett a nézettábla definiálásakor a SELECTzáradékban stúdióNév attribútumot is szerepeltetnünk kell: CREATE VIEW ParamountFilmek AS SELECT stúdióNév, filmcím, év FROM Filmek
= 'Paramount
WHEREstúdióNév
';
Ezután a ParamountFilmek nézettáblába beszúrjuk a kívánt sort:
INSERTINTOParamountFilmek VALUES ( 'Paramount " 'Csillagok háborúja', 1979); Ez a beszúrás a Filmektáblára ugyanúgy hat, mint a következo: INSERT INTO Filmek(stúdióNév, filmcím,év) VALUES('Paramount', 'Csillagokháborúja',1979); Megjegyezzük, hogy bár a létrehozott sor a nem említett attribútumokon NULL értéket kap, de a ParamountFilmek nézettáblában éppen a beszúrás által megkívánt sort fogja eredményezni. D A módosítható nézettáblákból törölhetünk is. A törlés, a beszúráshoz hasonlóan, az alaptáblában történik. Azért, hogy biztosítsuk azt, hogy csak azok a sorok törlodjenek, amelyek a nézettáblában láthatóak, a törlo utasítás WHERE záradékához "és" (AND)logikai muvelettel hozzá kell kapcsolnunk a nézettáblát definiáló utasítás WHERE záradékában szereplo feltételt. 8.6. példa. Tételezzük fel, hogy a módosítható ParamountFilmek nézettáblából szeretnénk kitörölni az összes olyan filmet, amelyek címe tartalmazza a ,,háború" szót. Ezt a következo utasítás segítségével érhetjük el: DELETEFROM ParamountFilmek WHERE filmcím LIKE '%háború%';
A törlés átalakul egy, a Filmek táblára vonatkozó törlésre azzal a különbségINSERT
INTO
Filmek(filmcím,
VALUES('Csillagok
háborúja',
év) 1979);
Megjegyezzük, hogy a nézettábla definíciója miatt ebben a beszúrásban csak a filmcímésév attribútumok kaphattak értéket, a Filmektábla többi attribútumainak nem tudtunk értéket adni. A Filmek táblába beszúrandó sor attribútumértékei tehát: a filmcímattri-
bútumértéke 'Csillagok
háborúja', az év attribútumértéke 1979,azösszes
többi attribútumértéke NULL.Minthogy a beszúrt sor stúdióNévattribútumának értéke NULL,és ez a ParamountFilmek nézettábla válogatási feltételének nem felel meg, így a beszúrt sornak a nézettáblára nincs hatása. Így például
gel, hogy a ParamountFilmek nézettáblát hozzáadódik a törlés WHEREfeltételéhez:
definiáló lekérdezés
DELETE FROM Filmek WHERE filmcím LIKE '%háború%' AND stúdióNév
a muvelet eredménye.
WHEREfeltétele
'Paramount ' ;
D
Hasonlóképpen, a módosítások is az alaptáblán keresztül történnek. A nézettábla módosításainak tehát az a hatása, hogy az alaptábla azon sorait módosítja, amelyek a nézettáblában sort eredményeznek.
368
8. Nézetek és indexek
Miért nem módosíthatók
egyes nézettáblák?
Tekintsük a 8.2. példában eloforduló FilmProducer nézettáblát, amely a filmcímeket párosítja a gyártásirányítókkal. Ez a nézettábla az SQL definíciója szerint nem módosítható, mivel két relációra vonatkozik: a Filmek és a GyártásIrányító relációkra. Tételezzük fel, hogy szeretnénk beszúrni a következo sort: ('Indiana
Jones és a halál templom',
'George Lucas')
A Filmek és a GyártásIrányító relációkba is be kellene szúrni egy-egy sort. Az olyan attribútumokra, mint például a hossz vagy a filmcím, használhatjuk az alapértelmezett értéket, de mit csináljunk a két egyenlové tett attribútummal, a producerAzon és az azonosító attribútumokkal? Használhatnánk a NULLértéket számukra. Így azonban nem tudnánk összekapcsolni a két relációt, mert az SQL két NULLértéket nem tekint egyenlonek (lásd 6.1.6. alfejezet). Tehát az 'Indiana Jones és a haláltemplom' és a ' George Lucas' nem kapcsolódna össze a FilmProducer nézettáblában, tehát a beszúrás nem lenne helyesen végrehajtva.
8.7. példa. A következo,nézettáblában történo módosítás: UPDATE ParamountFilmek SET év
=
1979
WHEREfilmcím
=
'Csillagok
háborúja
stúdióNév
'Csillagok 'Paramount
háborúja ' ;
film'
AND
D
8.2.3. Nézettáblákra vonatkozó típusú triggerek
által készített filmek nézettáblájának
CREATE VIEW ParamountFilmek AS SELECT filmcím, év FROM Filmek WHERE
stúdióNév
= , Paramount ' ;
Amint a 8.5. példában megmutattuk, ez a nézettábla módosítható, de nem kívánt következménnyeljár, ha ugyanis a ParamountFilmek nézettáblába beszúrunk egy sort, akkor a rendszer nem tudja kikövetkeztetni, hogy az alaptáblába történo beszúráskor a stúdióNév attribútum helyes értéke Paramount lesz-e, így a stúdióNév NULLértéket kap. Jobb megoldást érhetünk el, ha egy erre a táblára vonatkozó, a 8.2. ábrán látható, ,,instead-of' típusú triggert hozunk létre. A triggerben nincs semmi meglepo. A 2. sorban látjuk az INSTEADOF kulcsszót, amellyel azt érjük el, hogy a ParamountFilmek nézettáblába való beszúrás nem jön létre, helyette ez a trigger fog muködni. 1) 2) 3) 4) 5) 6)
CREATE TRIGGER ParamountInsert INSTEAD OF INSERT ON ParamountFilmek REFERENCING NEW ROW AS ÚjSor FOR EACH ROW INSERT INTO Filmek(filmcím, év, stúdióNév) VALUES(ÚjSor.filmcím, ÚjSor.év, 'Paramount'); 8.2. ábra.
UPDATEFilmek SET év = 1979
= =
8.8. példa. Idézzük fel a Paramount 8.1. példában szereplo definícióját:
369
film';
az alaptáblában ilyen alakban megy végre:
WHERE filmcím
8.2. Adatok módosítása nézettáblákon keresztül
,,helyette"
(instead-of)
Ha nézettáblára vonatkozó triggert definiálunk, akkor a BEFOREés AFTERhelyett az INSTEADOF-ot kell használnunk. Ha így járunk el, akkor ha egy esemény kiváltja a trigger muködését, akkor a triggerben megadott muveletek futnak le a trigger muködését kiváltó muvelet helyett. Így az instead-of típusú trigger elfogadja a nézettábla módosítási kísérletet, és helyette az adatbázis tervezoje által helyesnek gondolt muveletet hajtja végre. A következo egy tipikus példa erre.
A nézettáblába való beszúrást az alaptáblán történo beszúrásra cserélo trigger
A végrehajtani kívánt beszúrás helyett az 5. és 6. sorokban megadott akció következik be. Ez a Filmek táblába történo beszúrás, melyben a három ismert attribútumot adjuk meg. A filmcímés az év attril;>útumoka nézettáblába beszúrni kívánt sorból származnak; ezen értékekre az ÚjSor sorváltozó névvel hivatkoztunk, melyet a 3. sorban deklaráltunk azért, hogy meg tudjuk nevezni azt a sort, amelyet a nézettáblába kíséreltünk meg beszúrni. A stúdióNév attribútum értéke a 'Paramount ' konstans. Ez nem a nézettáblába beszúrni kísérelt sor része, hanem abból a feltételezésünkbol következik, hogy a beszúrás a ParamountFilmek nézeten keresztül érkezett. D
8.2.4. Feladatok 8.2.1. feladat.
Melyek módosíthatók a 8.1.1. feladat nézettáblái közül?
8.2.2. feladat.
Tegyük fel, hogy megkonstruáltuk a következo nézettáblát:
