Gedimensioneerde gegevensmodellering -bij creatie databank, vooraf definiëren



Dovnload 62.77 Kb.
Datum20.08.2016
Grootte62.77 Kb.
1.4.1 Gedimensioneerde gegevensmodellering
-bij creatie databank, vooraf definiëren
-transactiedatabank, gebruikt ER-gegevensmodellering of onmiddellijke relationele model
-datawarehouse, gebruikt gedimensioneerde gegevensmodellering
-relationele model > minimale redundante gegevens > aanpassen van meestal slechts 1 rij in 1 tabel
-bij select > veel complexer > eindgebruikers kunnen niet hun eigen selects schrijven
-intuïtief model nodig > gedimensioneerde gegevensmodellering
-diverse tabellen voor in stervorm structuur > sterschema
-1 grote feiten tabel (petabytes geen uitzondering)
-en verzameling kleinere dimensietabellen
-eenvoudige sleutel (surrogaat sleutel, geen logische betekenis)
-feitentabel heeft meervoudige sleutel
-elk van de attributen verwijzen naar één van de dimensietabellen
-en één of meerdere kolommen met meetwaarden (meestal numerieke gegevens)
-feitentabel w altijd geraadpleegd via dimensietabellen (biedt restricties aan opvraging)
-eerst via dimensietabellen sleutels van rijen die voldoen opzoeken
-vervolgens sleutels construeren om gegevens uit feitentabel te halen
-selectiever > minder feiten in feitentabel
-sequentieel doorzoeken enorme feitentabel > geen optie (10tallen dimensietabellen)
-gegevens voldoen meestal niet aan 3NF
-meestal zoeken in slechts 1 dimensie (efficiënt > 1 dimensietabel)
-dimensietabellen zijn klein (wel veel redundante info)

3.5 implementatie van joins


-cartesische producten en joins meest invloed op verwerkingstijd (Ť) van query
-zware systeembelasting > vermeiden door denormaliseren gegevens:
1/ opslag kindtabel met beschrijvende attributen van oudertabel (bv: fis in ranking)
2/ opslag van kindtabel met naar grootouder verwijzende sleutel van oudertabel
-niveaus kortsluiten (bv: iso, lev1,lev2 en lev3 in cities)
3/ opslag doorsnede tabellen met attributen van de tabellen met een veel-op-veel relatie
4/ opslag van oudertabel met statistische info over kindtabellen en andere verwante tabellen
5/opslag tabel met resultaten uit meerdere auto-joins van tabel zelf (bv: pop, area,level)
-pre-joining of clustering (nogal extreem)
-verzamelen van rijen van verschillende tabellen die frequent gejoint worden in zelfde blokken
-beperkt aantal I/O operaties voor specifieke query
-andere query’s minder efficiënt
-verschillende implementaties van joins en keuze ertussen door query optimizer
-op basis van aantal heuristische en syntactische regels en gebruikte predicaten
-deels op basis van geschatte uitvoertijd en benodigde bronnen (aantal blokken en I/O operaties)
-slechts conceptueel
-verschillende methodes:
1/ Block nested loop join
-eenvoudig
-in 4 geneste lussen w elke rij van X met elke rij van Y vergelijken
-2 meest uitwendige lussen lezen blokken uit geheugen
-2 inwendige lussen vergelijken rijen uit blok uit ene tabel met rijen uit blok andere tabel
- Ť ~ βx + (βx . βy)
-βx zorgt dat meest uitwendige lus tabel met minst blokken kiest (meestal tabel links in join)

2/ index nested loop join


- als alle attributen in join ook geïndexeerd zijn
-3 geneste lussen
-meest uitwendige lus leest blokken in van niet geïndexeerd veronderstelde tabel X
-middelste lus loop door deze blokken
-meest inwendige lus zoekt via indices naar corresponderende rijen van Y
-kleinere verwerkinstijd: Ť ~ βx + #x . fy
-fy een functie die indexmethode gebruikt
-voor equi-joins veel efficiënter dan block nested loop
3/ sort-merge join
-tabellen X en Y gesorteerd op attributen van equi-jion > gemerged in lezen
-1 blok van tabel in geheugen en beschouwd met steeds 1 rij
-afhankelijk van relatieve volgorde schuift 1 tabel een rij op of beide
-overschrijding blok grens dan wordt er een nieuw blok ingelezen
-verwerkingstijd beperken tot: Ť ~ βy . ln2y)
4/ hash join
1/ tabellen onafhankelijk van elkaar ingelezen, elke rij eenvoudige hashfunctie toegepast
-predicaten voor equijoin leveren zelfde hashwaarde op
-dezelfde hashwaarden worden gegroepeerd in een partitie
-in blokken van partities weggeschreven
-1 partitie kan rijen met diverse waarden voor join-predicaat bevatten
2/ per partitie join uitvoeren
-overeenkomstige partities inlezen
-op kleinste partitie (build partitie) wordt opnieuw hashtabel gecreëerd
-op andere partitie (probe partitie) w rij-per-rij verwerkt
-op rij w 2de hashfunctie toegepast om beperkte verzameling rijen die voldoen
-minimale verwerkingstijd: Ť ~ 3 . (βx + βy)
-indien onvoldoende geheugen: Ť +=~ β . ln(β)
-tenzij 1 van de tabellen klein genoeg is dan kan algoritme in 1e fase geskipt worden

5.3 wijzigbare views / CTE’s


-restricties
-wijzigingen in slechts 1 tabel
-kolommen CTE moeten min 1 kandidaatsleutel v/d tabel bevatten (key preserved)
-gebruik van distinct, group by, statistische functies
-geen verband meer tussen CTE rijen en tabel rijen > niet meer wijzigbaar
-kolomwaarden mogen niet berekend w op basis van nadere kolommen of scalaire subqueries
-kolommen uit samengestelde select met set operatoren > ook niet meer wijzigbaar
-updates in CTE’s beperken tot kolommen die
- rijen doeltabel kunnen identificeren
-attributen bevatten die gewijzigd moeten worden (bovenstaand geldt ook)
-nieuwe waarden bevatten (geen restricties)

HS6 DDL ASPECTEN

Systeem catalogus / data dictionary
-tabellen (structuren w bepaald door RDBMS)
-constraints (beperkingen)
-view (perspectieven om een tabel te bekijken)
-indexen (snelkoppelingen naar data)
-stored procedures (opgeslagen en deels gecompileerde query’s)
-triggers (veranderingen veroorzaakt door een gebeurtenis)
-voor elk type zijn er metatabellen
-voor allerlei tabelkenmerken
-1 rij voor elke tabel
-metatabel met kolomkenmerken
-1 rij voor elke kolom van elke tabel in databank
-tabellen slaan info op voor sleutels, indexen, stored procedures
-info over tabellen opslaan in tabellen > efficiënt voor RDBMS
-vele RDBMS laten query’s toe op metadata (metadata query’s)
-veel info over structuur tabellen
-kan gebruikt w ter controle
-welke tabellen aanwezig?
-wat w verwijdert bij table drop?
-nauwlijks toegestaan dat veranderingen worden aangebracht aan meta tabellen
-dit zou ook niet veel extra mogelijkheden bieden tov DDL syntax

tabel toevoegen


CREATE TABLE naam(
date smalldatetime NOT NULL
naam varchar(22) NOT NULL
)

tabel wijzigen


ALTER TABLE naam
ALTER COLUMN date int

default waarden (indien null w automatisch defaultwaarde genomen)


-moet niet statisch zijn, bv: random float DEFAULT cast(RAND() + 0.5 as int)
-niet genoeg om een surrogaatsleutel te genereren
-kan via sequencers / identity kolommen (afh. van RDBMS)
-gegarandeerd uniek
kolomdefinitie wijzigen
-sterk afh. Van RDBMS
-desnoods:
-kolom toevoegen met nieuwe definitie
-met update gegevens kopiëren van oud naar nieuw£
-constraints die verwijzen naar oude tabel verwijderen
-verwijder oude tabel
-nieuwe constraints definiëren op nieuwe tabel
tabel verwijderen
-DROP TABLE naam
-verwijdert volledige tabel def + geg. Uit datacatalogs + corresponderende constraints, views, indices
-kan mislukken indien verwijzigen in oudertabel naar rijen in de te verwijderen tabel

constraints / integriteitsregels


-bewaken van consistentie en correctheid gegevens
-zoveel mogelijk overlaten aan DMS
-bij wijzigen gegevens w gecontroleerd of alle constraints voldaan zijn
-2 manieren tot defineren:
-inline, tijdens kolom definitie
-out-of-line via aparte constraints definitie
-best een prefix geven die constraint aanduid
1/ not null constraint
-enkel inline of via checkconstraint
-elke rij moet waarde hebben
2/ primary key constraint
-geef combinatie van kolomen die uniek moet zijn
ALTER TABLE naam
ADD CONSTRAINT pk_naam
PRIMARY KEY (rid,cid)
-PK over 1 kolom kan (niet aangeraden)
-meestal mogen PK kolommen niet null zijn (soms expliciet vermelding nodig)
3/ verwijzende sleutel constraint (foreign key)
-eist dat de combinatie van waarden die ook voorkomen in sleutel ook moeten voork. In ouder tabel
ALTER TABLE naam
ADD CONSTRAINT fk_naam_tabel2
FOREIGN KEY (cid,fis)
REFERENCES tabel2 (cis,fis)
ON UPDATE CASCADE
ON DELETE CASCADE
-foreign key verwijst naar originele tabel kolommen
-references verwijst naar de verwijzende tabel kolommen
-regel over wat moet gebeuren bij delete en update
-cascade > trapsgewijs acties doorvoeren bij rijen in de verwezen tabel
-indien andere regels protesteren > totale actie w afgebroken
-set null > zet verwijzing in kindrijen op null
-set default > zet verwijzingen in kindrijen op default waarde
-kunnen pas gedefinieerd w nadat tabel gedefinieerd is (via alter tabel)
4/ unique constraint
-unieke combo’s van kolommen die geen PK zijn
5/ check consraints
-meestal willekeurige bewerkingen op kolommen van rij zelf
-stopt uivoering enkel bij false niet bij true of unknown
ADD CONSTRAINT ck_elementen CHECK(elementen >=-10)
-not null kan ook als check constraint maar is minder efficiënt

6.3 VIEWS


-select-opdrachten
-bewaart als objecten in datadictionary
-zelfde functionaliteit als CTE’s
-virtuele tabellen
-mag je overal gebruiken
-queryoptimizer beslist
-vooraf globaal uit te voeren (view materialization) volledige tabel opslaan
-selectief uitvoeren tijdens hoofdquery uitvoeren (view resolution)
-zo streng mogelijk in select en where (grootte beperken)
-gebruikt om complexe problemen stapsgewijs oplossen
-syntax beperkingen van SQL omzeilen
-view kan geraadpleegd worden door verschillende opdrachten zonder herdefiniëren (<> CTE)
-views meestal enkel privilege van DBA
-autorisatie middel
-tabellen naar de buitenwereld beperken (via select (kolommen) of via where (rij)
-niet zichtbaar van buitenaf
-tabel structuur kan veranderen zonder dat de view verandert (flexibel++ )
-kolom namen kan je herdefiniëren in view
-nesten is toegestaan
-aanpassen view
-drop view en heraanmaken
-verwijdert ook alle views die verwijzen naar deze view
-verwijderen basistabel > view wordt ook verwijdert
-kan computed column simuleren (kolommen die berekend zijn uit andere kolommen)
-structuur RDBMS komt meestal niet overeen met beeld dat gebruiker erover heeft
-opgenomen in tabellen van catalogus
-wijzigen van gegevens via view
-indien voldoet aan zelfde restricties als wijzigbare CTE’s
-view gedefinieerd met WITH CHECK OPTION
-iedere rij wordt gecheckt voor aanpassing of hij voldoet aan WHERE
-fungeert als filter bij in- en uitvoer
-alternatief voor gecorreleerde subquerys in andere tabellen of zelfs rijen bij
check-constraints

CREATE VIEW victories AS (


SELECT gender, date, resort, rank() over (…) number
FROM Races
JOIN Results ON Races.rid = Results.rid
)

6.4 INDEXERING



-interne, gesorteerde gegevensstructuren met verwijzingen naar rijen tabel
-vlug aflopen van rijen in bepaalde sorteer volgorde (versnellen van o.a. order by)
-handig bij joins bij verwijzingen naar sleutelwaarden andere tabel
-query optimizer bepaald of index gebruikt wordt
-opties sterk afhankelijk van RDBMS
-indexen op views meestal niet mogelijk
-indexeren op bewerkingen van zelfde rij (virtuele-kolom indexen)
-verwijderen door drop index
-sporadische inschakelen bij uitvoeren van bepaalde rapporten
-uitschakelen tijdens in bulk laden gegevens (erna index volledig laten herberekenen (minder overhead))
-primaire sleutel- en unique-constraints w automatisch indexen aangemaakt
-eenvoudig vermijden van duplicaten
-index op primaire sleutel sneller bij joins vanuit kindertabel
1/ bestandsorganisatie van gegevenstabellen
-afhankelijk van brontabel
1/ in heap bestanden rijen toevoegen
-optimale insert opdrachten (geen tijd nodig berekenen plaats rij)
-in blokken gesplitst (blok vol > nieuw blok)
-in bulk opladen ++
-rij zoeken > lineair doorzoeken bestand (weinig efficiënt)
-verwijderen rijen > gaten (worden gemarkeerd maar niet direct opnieuw gebruikt)
-periodiek reorganiseren vrije ruimte (defragmentatie)
-goed voor kleine tabellen (slechts enkele blokken data)
-goed als er optimaal gebruik opslagcapaciteit
-goed als je vaak querys laat lopen die alle rijen verwerken
2/ sequentiële bestanden, rijen gesorteerd opgeslagen
-sortering volgens waarde sorteer sleutel (één of meerde attributen)
-snel opzoeken van kolomwaarden die tot zoeksleutel behoren (binair zoeken)
-toevoegen rij
-indien voldoende plaats > enkel dat blok aanpassen
-indien geen plaats meer > volgende blokken ook aanpassen
-je kan ook werken met overflow of transactiebestanden
-niet sequentieel opslaan rijen
-indien binaire search geen resultaat > doorzoek overflow lineair
-positief effect op insert efficiëntie
-periodiek mergen van transactie met sequentiële bestanden
3/ hash bestand, adres van blok van rij berekenen op basis van meerdere attributen van rij
-hash waarde > beperkt aantal slots (1 slot kan 1 rij bevatten)
-evenwicht hashwaarde en aantal slots (grote hashwaarden <> trager zoeken)
-hashwaarde moet rijen zoveel mogelijk verdelen over blokken
-indien geen slots meer vrij zijn om rij op te slaan
-gelinkte lijsten van overloop gebieden
-hiërarchie van overloop gebieden (dynamische hashing)
-gemeenschappelijke overloopgebieden die men met 2de hashfunctie adresseert
-efficiënt voor zoekopdrachten met specifieke waarden van attributen
-niet efficiënt bij zoekopdrachten met like en between … and predicaten
-zoeken op deelverzameling attributen waarop hash is gebaseerd > lineair zoeken
-weinig efficiënt indien men 1 van attributen van hashfunctie vaak aanpast (verplaatsen rij)

2/ implementatie van indexen


-gegevensstructuur bestaan uit records die voor een combinatie van waarden voor attributen
een adres (record identifier) van een rij in tabel
-Null waarden niet in index opgeslagen
-gegevensstructuur zelf w opgeslagen in indexbestand
-afhankelijk van bestandorganisatie van gegevensstructuren: combo van volgende basistypen
1/ primaire / geclusterde indexen
-zijn gegevensbestand en indexbestand georganiseerd als sequentiële bestanden
-gesorteerd op zelfde combo’s van attributen (meestal PK tabel > primaire index)
-sequentiële gegevensbestanden meestal enkel gebruikt met primary index
-indien combinatie van attributen niet noodzakelijk uniek > geclusterde index
-max aantal: ( geclusterde || primary ) index = 1
-bestand gesorteerd > enkel index naar 1e rij blok (sparse indexing <> dense indexing)
-meer kans om index bestand in 1 keer in geheugen te laden
-efficiëntie van primaire en geclusterde indexen neemt af als tabel meer wijzigt
2/ secundaire indexen
-index bestand sequentieel
-gegevensbestanden niet sequentieel of in andere volgorde dan attributen index
-aantal onbeperkt
-geen unieke combo van attributen vereist, mogelijk door:
- meerdere records met enkelvoudige adresvelden voorzien
-adresvelden vervangen door pointers naar gelinkte lijsten
3/ hiërarchie van indexen
-vergelijkbaar met paginatabellen in virtueel geheugen laden
-index bestand beschouwen als gegevens bestand
-lineaire index structuur vervangen door hiërarchie van indexen (boomstructuur)
-ieder Blok bevat pointer naar knoop op lager niveau hiërarchie
-blokken op laagste niveau verwijzen op het einde naar volgende blok
-meestal dynamisch gebalanceerde boom (b+ tree) elke knoop > blok indexbestand
-elke blok voor minimum helft gevuld
-zorgt dat opzoekingen +- even lang duren (consistente opzoekingduur)
-performantie vermindert niet na herhaaldelijk wijzigen tabelgegevens
4/ Hash gegevens bestanden / hash indexen
-maken extra indexstructuren dikwijls overbodig
-hash indexen omdat net als indexen gericht op tabeltoegang via combo attributen
-hashfunctie bepaald direct blok waar rij zich moet bevinden
-rij niet aanwezig, dan bestaat ze niet
-snelste toegang voor beperkt aantal rijen
5/ bitmap indexen
-# mogelijke waarden voor attribuut relatief klein tov # rijen
- voor elke rij bewaart index een bitmapveld (vb: 4 verschillende waarden: 0100)
-soms minder geheugen gebruik (beter geschikt voor compressie)
-efficiënt voor
-opzoekingen met logische operatoren tss attributen
-opzoekingen op kolommen met veel duplicaten
-meest gebruikt in datawarehouses (tabellen w weinig aangepast)

6/ join index / Multi-tabel index


-meest gebruikt bij datawarehouses
-record voor elke combo van rijen met zelfde waarde voor bepaalde attribuut
-record bevat waarde attribuut + verwijzing naar in beide tabellen
-efficiënt voor querys die je vaak uitvoert en steeds dezelfde join gebruiken
-combo bitmap index en join index: bitmap join index
-gebruik van indexen
-performantie querys verhogen
-performantie verbetering <> overhead bij wijzigen gegevens
-bij aanpassingen moeten ook indexen geüpdate worden
-ook bij geindexte attributen
-indexen nemen geheugen in
-query optimizer moet uit meer alternatieven kiezen bij meer indexen
-indexen op kolomcombo’s
-enkel indien kolommen veel gebruikt worden in where, Group by, order by, distinct, join
-vaak indexen ook op verwijzende sleutels definiëren en niet alleen primaire (soms autom.)
-indexen kan je niet gebruiken in having-clausule
-kolommen die met vaak wijzigt werken indexen meestal vertragend (vermeiden in transactie omg.)
-indexen die query optimizer blijkt te negeren verwijderen
-attribuut combo waar je meest op select of joint > indexeren met primary of cluster index
-als primary key = sequentieel surrogate key > geen index (weinig kans dat er een select op gebeurd)
-performantie problemen (rijen worden achteraan toegevoegd (meerdere users > wachten)
-beter: enige cluster index gebruiken voor
-attributen met veel duplicaten
-attributen die veel voorkomen in between … and predicaten
-niet geclusterde indexen zijn hier immers minder efficiënt
-index w selectief genoemd als de selectiviteit hoog is
-selectiviteit = ( #unieke waarden door combo’s geïndexeerde attributen ) / ( # rijen tabel )
-niet geclusterde index met kleine selectiviteit weinig nut
-omdat teveel rijen corresponderen met zelfde indexwaarde
-beter hele tabel sequentieel doorlopen
-bij grote tabellen gebruik je dan best bitmap index
-selectief benaderen van enkele rijen is bitmap index dan weer slecht
-in samengestelde index > meest selectieve kolom best als eerste component
-tenzij frequentie van group by en order by anders insinueren
-in B+-tree vlugger afdalen tot laagste niveau
-kleine tabellen > indexen overbodig (kunnen vaak volledig in geheugen geladen worden)
-indexen op combo’s kolommen > voordelig
-kunnen gebruikt worden bij selectief die enkel 1e kolom of kolommen gebruiken
-volgorde kolommen bij index def. speelt van belang
-sommige querys reeds genoeg info uit index halen (geen raadplegen data)
-noemt men covered indexen of index-only plans
-gebruikt bij opvragen statistische info van kindtabellen
-argumenten group by en argumenten aggregaatfuncties in 1 index
-covered index benadert functionaliteit meerdere clusterd indexen per tabel

-1 grote index opsplitsen in meerdere enkelvoudige


-indien queryoptimizer ze apart raadpleegt en de doorsnede efficiënt bekomt
-soms: achter schermen equi-join van indexen met record ids in join predicaat
-wordt ook index join genoemd ≠ join index
-bied ook funct. van covered index zonder specifieke definitie samengestelde sleutels
-niet aantal attributen van index maar wel totale lengte index klein houden
-meer kans dat indexbestand in 1 keer in geheugen kan
-in B+-tree groter aantal records / blok maar geringer aantal niveaus in boom
-in datawarehouzes w elke component van primary key van feitentabel individueel geïndexeerd
-liefst met bitmap index
-elke dimensietabel w na filtering op selectiecriteria gejoined met corresp. index in feitentab.
-geeft record identifiers die in 1 dim voldoen aan query
-intersectie van resultaattabellen in ≠ dims levert adressen op van uiteindelijke result rows
-dan pas w feittabel benadert om result row op te halen
-weinig genormaliseerd maar ook overvloedig geïndexeerd ( in <> tot transactie omgeving)
-kan omdat deze tabellen weinig w aangepast

CREATE NONCLUSTERED INDEX gender_disipline_date


ON Races (gender ASC, disipline ASC, date ASC )
WITH (PAD_INDEX=OFF, SORT_IN_TEMPDB=OFF, DROP_EXISTING=OFF, IGNORE_DUP_KEY=OFF, ONLINE = OFF )

3GL ELEMENTEN

7.1 procedurele extensies

bijna ieder RDBMS bied een hybride programmeer omgeving aan


-standaard sql syntax aangevuld met procedurele extensies
-oracle: PL/SQL
-DB2: SQL PL
-SQL server: Transact-SQL
-SQL standaard: SQL/PSM (nog geen implementaties)
-handig om sql opdrachten te bundelen tot scripts (oracle: anonymous Block genoemd)
-inbedden van DML en DDL instructies
-starten van script:
-oracle (sql plus) : start
-sql server: sqlcmd –i
-Alle 3GL omgevingen bieden
-host-variabelen en constanten definiëren
-overal gebruiken in SQL-opdrachten waar men uitdrukkingen mag specificeren
-resultaat van select opslaan in host-variablele
-onderscheidt tussen 1 result row of meerdere
-via ingebouwde systeemvariabelen info bekomen over de uitvoering van inbedded SQL instructies
-bv: succesvol uitvoer command?, hoeveel rijen beïnvloed?
-foutopvang via exception handlers
-code copieren uit andere files ( met 1 opdracht )
-code modulair construeren door geneste blokken of stored procedures als subroutine gebruiken
-conditionele en iteratieve controlestructuren (bv: if, case, for, while, goto, …)

deze faciliteiten vermeiden door gebruik te maken van


-case-expressies
-recursieve CTE’s
-pivoteren
-leidt tot kleiner aantal SQL opdrachten
-leidt tot kleinere Round Trip Tijd bij client server toepassingen (zelfs als client server op 1 pc)
-query optimizer helpt bij optimalisatie (bij scripts werkt query optimizer niet over grenzen heen)
-werken efficiënter omdat ze gegevens zonder omwegen kunnen benaderen
-gegevens moeten niet in tussenliggende datastructuren geladen worden om bewerkingen te doen
-zoekmethodes en sorteeropdrachten optimaal uitgevoerd (rekening houdend
bestandsorganisatie van de gegevenstabellen, indices en gesofisticeerde geheugencaches
+ vaak ondersteuning voor meerdere threads
1/ enkel rekening houden met bewerkingen die brongegevens transformeren tot resultaattabel
-beperkt aantal SQL instructies (verpakken mbv 3GL taal tot script)
2/ niet met elke programmeerstap die atomair gegevens verwerkt
-procedureel programma, herhaaldelijk beroep doet op simpele SQL opdrachten
-en beroep doet op tussenliggende werktabellen (veel overbodige I/O operaties)
-er wordt teveel gebruik gemaakt van 3GL, gevolg:
-records worden benadert als bestanden (efficiëntie--)
-dure RDBMS faciliteiten nauwelijks gebruikt
-lagere performantie
-mag enkel gebruikt worden om:
-routinematige DBA taken te ontwikkelen, bv creatie van
-stored procedures
-triggers
-events

Transacties


-atomair, gebundelde reeks opeenvolgende SQL querys en wijzigingen
-waarbij op het eind ofwel
-commit de wijzigingen bevestigd
-rollback de wijzigingen ongedaan maakt tot aan start transactie
-kan aangeduid worden met begin transaction opdracht
-vooral handig bij wijzigingen die in meerdere tabellen wijzigingen aanbrengen
-constraints uitschakelen voor transactie, voor commit constraints testen (dan commit of rollback)
-zorgen voor overgang tussen 2 consistente toestanden van databank
-rollback na commit > geen effect
-deels ongedaan maken door savepoints: save transaction
-sommige RDBMS argument meegeven tot welke savepoint je wil commit of rollback doen
-instellingen mogelijk om na iedere commit, rollback nieuwe transactie te starten
-andere instelling: iedere opdracht verwacht commit (anders default rollback)
-alle opdrachten geven meestal impliciet een commit

1/ Transactielogbestanden


-log files om transacties ongedaan te maken indien nodig
-transactie eerst naar logfile dan uitvoeren
-chronologisch opgeslagen
-bevat ook before images: kopie van waarden voor wijziging
-wijzigingen ongedaan maken door before images opnieuw laden in databank
-belangrijke rol bij herstel (recovery) van slecht functionerende databank
-periodiek maken van backup (volledige kopie)+ bijhouden logs sinds laatste backup
-recovery via rollback
-wijzigingen logfiles ongedaan gemaakt in omgekeerd chronologische volgorde
-recovery via rollforward
-recovery door terugzetten backup en dan chronologisch log files toepassen (after images)
-backup: 1 keer per dag
-recovery via rollforward veel tijd
-beperken door checkpoints (tijdelijk syncen van logfiles en checkpoint plaatsen)
-nieuwe aanvragen worden tegengehouden en huidige worden afgewerkt
-geheugen buffers worden weggeschreven naar disk
-bij checkpoint w consistente toestand verondersteld
-enkel logfiles na checkpoint herstellen
2/ locking
-gelijktijdig gebruik van gegevens veroorzaakt problemen, bv:
-dirty write probleem: X wijzigt gegevens, Y wijzigt gegevens, één van beide voert rollback uit
niet duidelijk welke gegevens moeten terug gezet worden
-dirty read probleem: X wijzigt gegevens, Y vraagt deze gegevens, X voert rollback uit
Y gebruikt gegevens die nooit bestaan hebben
-X vraagt gegevens op, Y wijzigt gegevens of voegt rijen toe (commit). Als X dezelfde gegevens
opnieuw leest, w vastgesteld dat gegevens verandert zijn (non repeatable reads)
of dat nieuwe rijen toegevoegd zijn (phantom reads)
-lost update probleem: X vraagt gegevens, Y vraagt gegevens, X update op basis v zijn data (Y idem)
-vermeiden door transacties
-seriële verwerking > geen problemen
-maar gelijktijdig gebruik databank vermindert
-moeten zoveel mogelijk parallel verwerkt worden
-selectieve vergrendeling of locking
-enkel de geselecteerde gegevens worden gelocked
-bij opvragen gegevens in transactie, worden andere transacties geblokkeerd
-inhoud na parallelle uitvoering identiek alsof na seriële uitvoering (serialiseerbaarheid)
-door tweefase-lock-protocol
-groeifase: locks aanvragen
-vanaf eerste lock vrijgegeven > krimpfase: geen locks meer verkrijgen
-locks worden meestal impliciet gedaan door DBMS
-indirecte invloed door transactiegrenzen af te bakenen (best zo kort mogelijk)
-iteratieve controle structuren best vermeiden
-hoeveelheid data die geblocked wordt > granulariteit van een lock
-1 rij, blok waarin rijen zijn opgeslagen, volledige tabel, volledige databank
-kleine granulariteit > hoge graad parallelle uitvoering, minder congestieproblemen
-moeilijker beheer voor RDBMS
-kan men specifieren bij opstarten of tabelcreatie, of niet

-isolation level


-hoever mogen trans. die gelijktijdig worden uitgevoerd op dezelfde data elkaar beïnvloeden
-instellen voor alle transacties tijdens sessie of individuele transactie of per SQL opdracht
-op een van de volgende niveaus
-serializable
-maximale afscherming
-seriële verwerking (=~ meest pessimistische lockingstrategie)
-geen kans op conflicten
-repeatable read
-share of read lock
-select opdrachten worden niet geblokeerd
-tijdens schrijven wordt exclusive of write lock ingesteld (tijdens transactie)
-phantom reads mogelijk
-read commited of cursor stability
-share lock reads opgegeven na verwerking select opdracht
-non repeatable reads mogelijk
-opeenvolgende querys in transacties kunnen inconsistenties opleveren
-standaard in meeste RDBMS
-read uncommited
-ook exclusive lock w na wijziging opgegeven
-dirty read mogelijk
-kans op conflict voorgesteld als klein
-achteraf check of er conflicten zijn opgetreden (desnoods trans. herhalen)
-niveau parallelliteit van uitvoering maximaal
-deadlocks bij exclusive locks moet RDBMS direct detecteren en opgeven
-door 1 of meerdere trans. een rollback te forceren
-efficientie locking en goede query optimizer maken verschil tss kwaliteit RDBMS
-ACID
-Atomair
-trans. is atomair, wordt altijd als 1 geheel uitgevoerd (of in geheel niet)
-Consistent
-transacties zijn overgangen van de 1e consistente toestand naar de volgende
-Isolatie
-isolatie zorgen van meerdere instelbare niveaus
-Duurzaam
-alle wijzigingen van afgew. trans. moeten permanent zijn, desnoods via recovery

3/stored procedures


-SQL opdrachten aangevuld met procedurele extensies van specifiek RDBMS
-worden permanent in databank opgeslagen in systeem catalogus
-parameter lijst kan men default waarden meegeven
-define locale variabelen
-bij create
-worden aangesproken structuren gecontroleerd
-code w opgeslagen in systeemcatalogus
-om performantie te verhogen: code precompiled of gebind
-bepaald welke strategie zal gebruikt worden (kan uitgeschakeld worden (actuele keuze))
-opgeroepen expliciet uit toepassingsprogramma of
-recursief vanuit zelfde stored procedures
-vanuit trigger
-via execute opdracht (of iets dergelijks)
-invoer parameters initialiseren door positionele plaats of door interne naam
-resultaat kan soms omgeleid worden naar insert opdracht (kan ook via cursors)
-transacties die het zelf geïnitieerd heeft zelf ook afsluiten met rollback of commit
-voordelen:
-deel van programma centraal opslaan
-dwingt ogp af en vereenvoudigt onderhoud en zorgt voor minder kans op fouten
-scripts periodiek uitvoeren
-eenvoudig aan te roepen via task sceduler os
-definieren uitvoer bgij start RDBMS
-kunnen opgeroepen worden vanuit eender welke host-programeertaal, host-omgeving
-worden een heel pak standaard meegeleverd
-beheerstaken vereenvoudigt
-performantiemonitoring
-verkrijgen info over objecten in systeemcatalogus
-interactie externe diesnten (LDAP (files over tcp/ip) en mail servers)
-soms niet in databank maar in ddl’s of gecodeerd met SQL en 3GL extensies
-toegang vastleggen voor ≠ gebruikers
-functionaliteit aanbieden en taken delegeren (geen rechtstreekse toegang tot datastruct.)
-tijdens uitvoering > geen communicatie met RDBMS (minder traffic op netwerk)
-vooraf compileren > geen keuze meer nodig door query optimizer > sneller
-soms groeperen tot stored packages
-kan met 1 opdracht verwijdert worden
-globale variablelen
-overloading (meerdere procedures met zelfde naam maar andere signatuur
-bijzondere vorm: functie (invoer en uitvoer paramenters)

CREATE PROC getSmallest


@first INT OUT
AS
SET @first = ( SELECT MIN(r)
FROM ( SELECT cid, rank() OVER (ORDER BY cid) r
FROM Compeditors) x
WHERE cid <> r )
RETURN 0

4/ Triggers


-zijn een soort van stored procedures die enkel automatisch worden uitgevoerd bij bepaalde bewerkingen
-diverse typen: DML-triggers (reactie op insert,update of delete) en DDL-triggers (bij create, alter,en drop)
-DML-triggers
-create trigger en drop trigger
-tabel verwijderen > verwijdert ook triggers
-3 componenten (volgens Event-Condition-Action model (ECA)
-triggerevent bepaald bij welke SQL opdracht op welke tabel getriggert wordt
-opgeven before-, after-, instead of triggers van SQL instructie
-triggeractie bepaald wat trigger doet
-kan hetzelfde doen als stored procedure
-moet niet beperkt blijven tot trigger tabel
-men mag stored procedures oproepen
-triggers kunnen elkaar (eventueel recursief) triggeren
-triggerconditie beperkt triggeractie tot bepaalde rijen
-voorwaarde moet niet beperkt blijven tot triggertabel
-trigger wordt slechts 1 keer per SQL-opdracht getriggert
-zelf zoeken in triggercode welke rijen aangepast zijn (hulptabellen RDBMS ter beschikking)
-SQL-server
-pseudo tabellen inserted en deleted (kopieën van toegevoegde verwijderde rijen)
-functies update en columns_updated (welke kolommen beïnvloed door update)
-Oracle
-bij def trigger for each row: specificeert code die voor iedere rij moet w uitgevoerd
-worden rijtriggers genoemd
-old en new bevatten vorige en nieuwe waarden rij (new is aanpasbaar)
-handig bij before- en instead of triggers
1/ before triggers
-cardinaliteitsregels kunnen niet volledig gegarandeerd worden via check-constraints (geen joins)
-door before trigger kan eender welke integriteitsregel in databank w opgeslagen
-deel programma centraliseren
-controle of gewijzigde rijen voldoen aan integriteitsregels bij insert of update > rollback indien nodig
-constraints zijn veel efficiënter (verkiezen boven triggers)
-constraints hebben voorrang op triggers
2/ after triggers
-goed om redundante info in gedenormaliseerde tabellen automatisch te corrigeren
-nieuwe update veroorzaken of andere trigger triggeren
-hoeveelheid procedurele code beperken
-triggers zorgen voor overhead (kan juiste keuze zijn indien query frequentie laag is)
-triggers kunnen soms ook recursief zichzelf triggeren
-autoarchiveren van te verwijderen rijen is makkelijk te realiseren
-loggen van wie er welke bewerkingen heeft uitgevoerd (audits)
3/ instead of triggers
-goed voor versoepelen restricties van wijzigingen van views en CTE’s normaal onderworpen zijn
-wat moet gebeuren bij aanpassingen aan niet wijzigbare rijen
-meestal beperkt tot insert opdrachten
-kunnen ook reeks update- of zelfs delete opdrachten maken
-invoer van bepaalde tabellen omleiden naar andere tabellen of zelfs andere databankservers

5/ Cursors


-methode om resultaat met meerdere rijen te behandelen in 3GL talen
-creëren met declare opdracht, koppelen aan select met zo’n restrictief mogelijke where clausule
-deze opdracht wordt nog niet geopend maar wel bij open opdracht
-RDBMS houd dan resultaat rijen ter beschikking van applicatie
-fetch opdracht om rijen één voor één te doorlopen
-soort pointer op tabel
-na close verwijdert RDBMS resultaattabel en locks (best zo snel mogelijk, geheugen vasthouden op server)
-cursor kan hergebruikt worden
-deallocate verwijdert buffers op client niveau
-fetch aanvaard: next, prior, first, last om verschillende rijen op te vragen
-je kan ook absolute / relatieve positie opgeven
-verwijderen en updaten van rijen is mogelijk met speciale versie van insert en update (positioneel updaten)
-zelfde voorwaarden als bij wijzigen van CTE of view
-kan leiden tot Halloween probleem (5.3) of zelfs oneindige lussen
-manier van buffering afhankelijk van RDBMS
-beïnvloeden via opties bij declare
1/ externe manier: 1 rij bij expliciet vragen, efficiënt bij weinig rijen, hoge belasting op netwerk
2/ tegen gestelde: buffert volledige resultatentabel
3/ tussen oplossingen: beperken aantal rijen en laten client cachen om verkeert te beperken
-afhankelijk van methode zie je de gegevens in
-actuele toestand (dynamische cursors)
-zoals ze eruit zegen toe cursor geopend werd (statische cursor)
-soms wordt enkel primary key gebufferd en dan met deze wordt exact rij opgeslagen
-toevoegingen van andere applicaties zijn onzichtbaar, wijzigingen en verwijderingen wel
-negatieve invloed op verwerkingstijd en geheugengebruik, kan leiden tot globale performantieproblemen
-laten ontwikkelaars toe databanktabellen als sequentiële tabellen te misbruiken (last resort)
-gebruik:
-doorgegeven gegevens tss stored procedures en gebruikersprogramma (kleine tabellen)
- als programma resulataten slechts selectief en gespreid in tijd verwerkt
-komt veel voor bij interactieve programma’s waar gebruiker door resulttabel kan bladeren
-fetch opdracht pas wanneer nodig
-dynamische SQL, SQL wordt pas aangemaakt tijdens programma run
-niet goed bij veel rijen
-declare > [ open(up to date) > … > close(buffers server sluiten) ]+ > deallocate(delete cursor)

DECLARE kinderen CURSOR local


FOR
SELECT hasc,name
FROM regios
WHERE parent = @hasc
OPEN kinderen
FETCH NEXT FROM kinderen INTO @hasc, @name
WHILE @@fetch_status = 0
BEGIN
PRINT …
FETCH NEXT FROM kinderen INTO @hasc, @name
END
CLOSE kinderen
DEALLOCATE kinderen

Key preserved

Als er 1 kandidaat sleutel van een tabel in een CTE is

DML

Data Manipulation Language

RDBMS

Relationeel DataBase Managment Systeem

DBA

DataBank Administrator

DDL

Data Definition Language

Data Dictionary

Of systeemcatalogus: verzameling administratieve tabellen

Meta data query’s

Opvragen van gegevens over tabellen (w opgeslagen in meta tabellen)

Surrogaat sleutel

Custom gegenereerde sleutel bv: cid, rid (meestal nummer)

View materialization

View globaal uitvoeren en resultaat opslaan, dan volledig laden in hoofdquery

View resolution

Gelijktijdig uitvoeren met hoofdquery

Computed columns

Kolommen die berekend zijn op basis van andere kolommen

Virtuele kolom indices

Indices op bewerkingen tussen waarden van 1 rij

Transaction file

Of overflow, rijen die in apart bestand niet sequentieel w opgeslagen en lineair doorzocht moeten worden

Dynamische hashing

Hiërarchieën van overloopgebieden die de adressen van rijen waarvoor geen plaats meer is bevatten

Foreign key

Verwijzende sleutel

Record identifier

Adres van een rij is een indexrecord

Primary index

Index gesorteerd op primary index

Geclusterde index

Combinatie van attributen van index niet noodzakelijk uniek

Index join

Efficiënt doorsneden van resultaat tabellen bekomen door equi-jion van indexen met record identifiers in join predicaat

Join index

Index die records bijhoud van combinaties rijen en waarde voor attributen

B+ tree

Dynamisch gebalanceerde boomstructuur van hiërarchische indexen

Anonymous block

Bundel SQL opdrachten, SQL script

RTT

Round Trip Time: tijd nodig om van client naar server en terug te gaan

Save point

Tijdens transactie punt saven waar alles consistent is, mogelijkheid om commit of rollback te doen tot dit punt

Before images

Waarden in logfile van voor de wijziging

Recovery in rollback

Omgekeerd chronologisch wijzigingen in logfiles ongedaan maken

Recovery in rollforward

Backup restoren en dan in chronologische volgorde logfiles toepassen

Row trigger

In Oracle: trigger met ‘for each row’ clausule die code specificeert die voor elke rij die aangepast is moet worden uitgevoerd

Dynamische cursor

Er w niet gebufferd bij ophalen gegevens, elke rij wordt bij aanpassingen expliciet opgehaald

Statische cursor

Alles w gebufferd, gegevens gezien zoals ze waren toe cursor opende

Build partitie, probe partitie

Respectievelijk kleinste en grootste partitie bij een hash join




De database wordt beschermd door het auteursrecht ©opleid.info 2017
stuur bericht

    Hoofdpagina