Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
1616 lines (1350 sloc) 48.6 KB
%Pakete;
%A4, Report, 12pt
\documentclass[ngerman,a4paper,12pt]{scrreprt}
\usepackage[a4paper, right=20mm, left=20mm,top=30mm, bottom=30mm, marginparsep=5mm, marginparwidth=5mm, headheight=7mm, headsep=15mm,footskip=15mm]{geometry}
%Papierausrichtungen
\usepackage{pdflscape}
\usepackage{lscape}
%Deutsche Umlaute, Schriftart, Deutsche Bezeichnungen
\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}
\usepackage[ngerman]{babel}
%quellcode
\usepackage{listings}
\usepackage{verbatim}
\usepackage{moreverb}
\let\verbatiminput=\verbatimtabinput
\def\verbatimtabsize{4\relax}
%tabellen
\usepackage{tabularx}
%listen und aufzählungen
\usepackage{paralist}
%farben
\usepackage[svgnames,table,hyperref]{xcolor}
%symbole
\usepackage{latexsym,textcomp}
\usepackage{amssymb}
%font
\usepackage{helvet}
\renewcommand{\familydefault}{\sfdefault}
%durch- und unterstreichen
\usepackage{ulem}
%Abkürzungsverzeichnisse
\usepackage[printonlyused]{acronym}
%Bilder
\usepackage{graphicx} %Bilder
\usepackage{float} %"Floating" Objects, Bilder, Tabellen...
\usepackage[space]{grffile} %Leerzechen Problem bei includegraphics
\usepackage{wallpaper} %Seitenhintergrund setzen
\usepackage{transparent} %Transparenz
%Tikz, Mindmaps, Trees
\usepackage{tikz}
\usetikzlibrary{mindmap,trees}
\usepackage{verbatim}
%for
\usepackage{forloop}
\usepackage{ifthen}
%Dokumenteigenschaften
\title{Summary Dbs2}
\author{Tobias Blaser}
\date{\today{}, Uster}
%Kopf- /Fusszeile
\usepackage{fancyhdr}
\usepackage{lastpage}
\pagestyle{fancy}
\fancyhf{} %alle Kopf- und Fußzeilenfelder bereinigen
\renewcommand{\headrulewidth}{0pt} %obere Trennlinie
\fancyfoot[L]{\jobname} %Fusszeile links
\fancyfoot[C]{Seite \thepage/\pageref{LastPage}} %Fusszeile mitte
\fancyfoot[R]{\today{}} %Fusszeile rechts
\renewcommand{\footrulewidth}{0.4pt} %untere Trennlinie
%Kopf-/ Fusszeile auf chapter page
\fancypagestyle{plain} {
\fancyhf{} %alle Kopf- und Fußzeilenfelder bereinigen
\renewcommand{\headrulewidth}{0pt} %obere Trennlinie
\fancyfoot[L]{\jobname} %Fusszeile links
\fancyfoot[C]{Seite \thepage/\pageref{LastPage}} %Fusszeile mitte
\fancyfoot[R]{\today{}} %Fusszeile rechts
\renewcommand{\footrulewidth}{0.4pt} %untere Trennlinie
}
\usepackage{changepage}
% Abkürzungen für Kapitel, Titel und Listen
\input{commands/shortcutsListAndChapter}
\input{commands/TextStructuringBoxes}
%links, verlinktes Inhaltsverzeichnis, PDF Inhaltsverzeichnis
\usepackage[bookmarks=true,
bookmarksopen=true,
bookmarksnumbered=true,
breaklinks=true,
colorlinks=true,
linkcolor=black,
anchorcolor=black,
citecolor=black,
filecolor=black,
menucolor=black,
pagecolor=black,
urlcolor=black
]{hyperref} % Paket muss unbedingt als letzes eingebunden werden!
\usepackage{graphicx}
\begin{document}
% Inhaltsverzeichnis
\tableofcontents
\clearpage
\ch{Repetition Dbs1}
\exam{'group by' wichtig}
\definition{OLTP}{Online-Transaction-Processing}
\se{ANSI Modell}
\img{img/v1.1.jpg}{ANSI 3-Ebenen Modell}{1}{}
\se{ACID}
\ul
\li Atomizität: Eine Transaktion ist atomare Verarbeitungseinheit, die entweder vollständig oder gar
nicht ausgeführt wird.
\li Consistency (Konsistenz): konsistenzerhaltend
\li Isolation: Eine T. soll so ausgeführt werden, als sei sie
isoliert von anderen
\li Durability (Dauerhaftigkeit): Änderungen einer T.
sind dauerhaft, sie dürfen nicht aufgrund von Fehlern
verloren gehen
\ulE
\ch{Oracle's PL/SQL}
\expl{PL/SQL}{SQL ist deklarativ und beschreibt nur was, nicht wie. \textbf{Procedural Language} ermöglicht das wie.}
\img{img/v1.2.jpg}{}{0.75}{}
\ul
\li wird kompiliert
\li wird nahe bei den Daten im DD abgelegt
\li Innerhalb von PL/SQL darf SQL ausgeführt werden, Resultate landen in Variablen
\ulE
\img{img/v1.3.jpg}{}{0.75}{}
\ul
\li 'DECLARE'-Block optional
\li Server soll Output zurückschicken: 'SQL> SET SERVEROUTPUT ON'
\li Letzter Fehler anzeigen:
\li PL/SQL Blöcke müssen mit Slash abgeschlossen werden:
\begin{verbatim}
SQL> BEGIN
dbms_output.put_line('Welcome to PL/SQL');
END;
/
\end{verbatim}
\li SQL> EXEC 'welcome' führt die Funktion welcome aus:
\begin{verbatim}
CREATE OR REPLACE PROCEDURE welcome
IS
user_name VARCHAR2(8) := user;
BEGIN -- 'BEGIN'
dbms_output.put_line('Welcome to
PL/SQL, ' || user_name || '!');
END;
/
\end{verbatim}
\li Blöcke dürfen verschachtelt werden: im BEGIN Block dürfen wiederum DECLARE, BEGIN, End, ... Blöcke stehen.
\li Verschachtelte Blöcke schränken Sichtbarkeit ein
\li Resultate werden durch 'INTO varname' in Variablen gespeichert
\li Datentyp von Tabelle übernehmen: ' Tabelle.Spalte\%TYPE;'
\li Systemvariablen: USER, ...
\li Exceptions:
\begin{verbatim}
WHEN NO_DATA_FOUND THEN
/*System Exception:
SELECT INTO liefert keinen Wert*/
RAISE;
WHEN TOO_MANY_ROWS THEN
/*SELECT INTO* liefert mehr als einen Wert*/
RAISE;
WHEN DUP_VAL_ON_INDEX THEN
/*Projektzuteilung existiert bereits*/
\end{verbatim}
\ulE
\img{img/v1.4.jpg}{PL/SQL Block mit DECLARE, BEGIN EXCEPTION und END Block}{0.75}{}
\se{Exceptions}
Wie in Java.
\img{img/v1.5.jpg}{}{0.75}{}
\img{img/v1.6.jpg}{Benannte Benutzer AUsnahmen}{0.75}{}
\se{Stored Procedures}
\expl{SP}{DBS Subroutine}
\ul
\li Objekte wie Tabellen, die Rechte und User zugeteilt werden
\ulE
\img{img/v1.7.jpg}{}{0.75}{}
\se{Funktionen}
\expl{Unterschied Procedure und Funktionen}{Funktionen können im SQL verwendet werden (Bps. AVG), Procedures können nur alleine ausgeführt werden.}
\important{Procedure kann nur im PL/SQL aufgerufen werden, Funktion sowohl im SQL wie PL/SQL}
\img{img/v1.8.jpg}{Vergleich}{0.75}{}
\ch{Stored Procedure in PL/SQL, Java und Python}
\ul
\li SP werden als Block in der Datenbank abgelegt.
\li Im Gegensatz zum anon. Block wird die P beim Aufruf geparst und in der DB gespeichert.
\li Vorteile: Performance, Security (Zentrale Überprüfungen, Zugriffsschutz)
\li Nachteil: SP herstellerspezifisch, Probleme mit Optimierer
\ulE
\examp{Security}{Security: zum Lesen: Views, zum Schreiben: brauchen keine schreibrechte, nur ausführungsrechte auf Procedure. Procedure definiert genau, was übergeben werden soll und erlaubt nur dies.}
\exam{Prüfungsrelevant: \\
1. Variablen (DECLARE), Blöcke (BEGIN...END), Konst.
(CONSTANT) \\
2. Typen (SQL; \%TYPE; NUMBER, SUBTYPE) \\
3. Conditional Control Statements (IF...END IF; ) \\
4. Sequential Control Statements (GOTO, NULL) \\
5. Loops (LOOP, FOR...LOOP, WHILE...LOOP) \\
6. Exception Handlers (EXCEPTION, RAISE, \\
7. Built-In Functions: Character; Date; Numeric, LOB, and
Miscellaneous Fns., Conversion Fns. \\
8. Prozeduren und Funktionen (CREATE PROCEDURE...AS) \\
9. Packages (CREATE PACKAGE; CREATE PACKAGE...BODY ) \\
10. Cursors (FOR record\_index IN cursor\_name LOOP) \\
11. Record Types (TYPE first\_rectype IS RECORD (var1
VARCHAR2(100)), \%ROWTYPE)
}
\ch{IO}
\ul
\li SET ist nicht Teil von SQL, sondern SQL plus (pakage).
\li Output: DBMS\_OUTPUT.PUT\_LINE (Putline ist wie eine Pipe, hat nichts mit der Konsole zu tun \ra schreibt in Buffer) \\ Wichtig: SET SERVEROUTPUT ON
\li Input: DBMS\_OUTPUT.GET\_LINE(:buffer, :status); \\ Wichtig: set echo on;
\ulE
\ch{Packages}
\ul
\li Gruppieren PL/SQL Typen und Unterprogramme
\li Können nicht aufgerufen, parameterized, oder verschachtelt werden
\li Bsp:dbms\_output, user\_lock
\ulE
\se{Package Deploying}
\img{img/v2.1.jpg}{}{0.75}{}
\img{img/v2.2.jpg}{}{0.75}{}
\sse{Bsp1}
\begin{verbatim}
CREATE OR REPLACE PACKAGE AngestelltenVerwaltung AS
...
FUNCTION AngEinstellen (Name VARCHAR2,..
..
END AngestelltenVerwaltung;
\end{verbatim}
\sse{Bsp2}
\begin{verbatim}
CREATE OR REPLACE PACKAGE BODY AngestelltenVerwaltung AS
...
PROCEDURE GetAngOfAbt ( AbtNo IN NUMBER,
..
IS
BEGIN
...
END GetAngOfAbt;
...
END AngestelltenVerwaltung;
\end{verbatim}
\ch{Cursor}
\img{img/v2.3.jpg}{Cursor Verarbeitung}{0.75}{}
\img{img/v2.4.jpg}{Cursor Prinzip}{0.75}{}
\img{img/v2.5.jpg}{Cursor Variable}{0.75}{}
\sse{Deklaration Bsp}
\begin{verbatim}
DECLARE
CurrAbtNr integer := 1;
CURSOR AngCursor IS
SELECT Salaer, PersNr FROM Angestellter
WHERE Angestellter.AbtNr=CurrAbtNr;
SalSumme NUMBER (8, 2) := 0;
AngSalaer Angestellter.Salaer%TYPE;
AngPersNr Angestellter.PersNr%TYPE;
\end{verbatim}
\sse{Cursor Verarbeitung}
\begin{verbatim}
BEGIN
OPEN AngCursor; /*SQL-Abfrage starten und Resultat in Puffer
speichern*/
LOOP /*Iteration ueber Resulatmenge*/
FETCH AngCursor INTO AngSalaer, AngPersNr;
EXIT WHEN AngCursor%NOTFOUND OR AngCursor%ROWCOUNT>10
SalSumme := SalSumme + AngSalaer;
dbms_output.put_line('Angstellter PersNr: ' || AngPersNr ||
' Salaer: ' || AngSalaer);
END LOOP;
CLOSE AngCursor;
dbms_output.put_line('Salaersumme: ' || SalSumme);
END;
\end{verbatim}
\important{String werden in 'einfach' Hochkommas geschrieben!}
\sse{Cursor mit Parametern}
\begin{verbatim}
DECLARE
CurrAbtNr integer;
CURSOR AngCursor (AbtId IN Abteilung.AbtNr%TYPE) IS
SELECT Salaer, PersNr FROM Angestellter
WHERE Angestellter.AbtNr=AbtId;
SalSumme NUMBER (8, 2) := 0;
AngRec
AngCursor%ROWTYPE;
\end{verbatim}
\sse{Cursor for Update}
Cursor muss für Update ``angekündigt'' werden.
\img{img/v2.6.jpg}{}{0.75}{}
\sse{Cursor Attribute}
\img{img/v2.7.jpg}{}{0.75}{}
\ch{Constraints}
\definition{Constraint}{Konsistenzbedinung, Regel der die Daten immer genügen müssen}
\expl{Implizite Constraints}{Werden durch das Schema abgedeckt, z.B. Schlüssel, Wertebereiche, Kardinalität, ...}
\expl{Primäre/Sekundäre Constraint}{Primäre ist sofort geprüft/gültig: z.B. Wertebereich, Sekundäre wird erst später geprüft: z.B. Summe von Salären, die nach dem Einfügen einer neuen Zeile erst berechnet und in Tabelle abgelegt werden muss.}
\expl{starke/schwache Constraints}{Während (stark) oder nach der Transaktion (schwach) geprüft}
\sse{Postgresql}
\img{img/v3.1.jpg}{Add Constraint}{0.75}{}
\img{img/v3.2.jpg}{Delete Constraint}{0.75}{}
\img{img/v3.3.jpg}{Enable/Disable Constraint}{0.75}{}
\img{img/v3.4.jpg}{View Constraints}{0.75}{}
\ch{Triggers}
\ul
\li Sicherstellung komplexer Konsistenzbedingungen
\li Werte von abgeleiteten Attributen berechnen
\li Speichern von materialisierten Views
\li Zugriffsschutz
\li Kann nicht direkt aufgerufen werden, gehört immer zu einer Tabelle
\li Wird automatisch beim Eintreten des Trigger-Events aufgerufen
\ulE
\definition{Before-Trigger}{Überprüft Vorbedinungen}
\definition{After-Trigger}{Prüft Nachbedingungen}
\definition{Row-Trigger}{Wird für jede betroffene Zeile aufgerufen}
\definition{Statement-Trigger}{Wird pro SQL-Anweisung/Ereignis aufgerufen}
\sse{Beispiel INSERT mit Bedingung}
\img{img/v3.6.jpg}{}{0.5}{}
\img{img/v3.5.jpg}{}{0.75}{}
\sse{:old, :new}
\ul
\li bei INSERT gibt es nur :new.{object}
\li beim DELETE gibt es nur :old.{object}
\li bei UPDATE sind beide vorhanden
\li referenzieren die neuen und die Alten Daten bei einer Datenbankänderung
\ulE
\important{Es gibt keinen SELECT Trigger!}
\sse{Ereignisabfrage}
\begin{verbatim}
...
IF DELETING
...
IF UPDATING
...
ELSE
...
END IF
...
\end{verbatim}
\sse{Ausführungsreihenfolge}
\img{img/v3.7.jpg}{}{0.75}{}
\sse{Login/Logoff Trigger}
Hat Zugriff auf userid und username, wird beim erfolgreichen an-/abmelden ausgelöst.
\sse{Instead-of-Trigger}
\ul
\li Ersetzt INSERT / UPDATE / DELETE
\li Materialisierte Views
\li Daten statt löschen nur als gelöscht markieren
\li Das eigentliche Originalstatement wird NICHT mehr ausgeführt
\ulE
\img{img/v3.8.jpg}{Beispiel View}{0.75}{}
\img{img/v3.9.jpg}{Beispiel View}{0.75}{}
\img{img/v3.10.jpg}{Instead-of-Trigger zur View}{0.75}{}
\se{Updatable Views / Materialized Views}
\expl{Unterschied View / Temporäre Tabelle}{Temoräre Tabelle lebt nur während Transaktion oder Session, Aktualität der temp. Table unbekannt. Biew ist immer aktuell.}
\expl{Warum kann man keinen Index auf eine View anlegen?}{Tabellendaten bringen bereits einen Index mit \ra macht keinen Sinn}
\definition{Materialisierte View}{Zwischengespeicherte View, wird nicht automatisch upgedatet}
\expl{Virtuelle Tabelle}{Foreign Data Wrappers. Bei SQLIte: Schnittstelle zu externem Storage, z.B: csv}
\exam{\\
\examp{For each Rows}{Tabelle A mit 10 Tuppel, Hälfte von Änderung betroffen, Für wie viele Rows wird der Trigger ausgelöst? \ra 5 Mal}
\expl{Trigger \& Locking}{Oracle lockt während dem Trigger die entsprechende Tabelle \ra Select Statement während Trigger nicht möglich}
\expl{Trigger \& Transaktionen}{Ein Trigger läuft als Transaktion, kann in einem Trigger eine weitere Transaktion geöffnet werden? Nein.}
\expl{Triggerreihenfolge}{Implementationsspezifisch}
}
\ch{Spezielle Datenstrukturen}
\se{Arrays}
\ul
\li Alle Elemente besitzen den gleichen Datenty
\li
\ulE
\sse{create}
\begin{verbatim}
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
\end{verbatim}
\sse{insert}
\begin{verbatim}
INSERT INTO sal_emp VALUES (
'Bill',
ARRAY[10000, 10000, 10000, 10000],
ARRAY[['meeting', 'lunch'], ['training','presentation']]
);
\end{verbatim}
\sse{select}
\begin{verbatim}
SELECT ARRAY[1,2,3+4];
// returns: {1,2,7}
SELECT ARRAY( SELECT 1 + (random()*5)::int
FROM generate_series(1,6) ORDER BY 1 );
// returns e.g. : {1,2,3,4,5,5}
\end{verbatim}
\sse{subscription, slice}
\begin{verbatim}
SELECT schedule[1:2][1:1]
FROM sal_emp WHERE name = 'Bill';
//returns: {{meeting},{training}}
SELECT * FROM sal_emp
WHERE 10000 = ANY (pay_by_quarter);
// any prüft jedes Array Element, ob 10000 enthalten ist
\end{verbatim}
\sse{Array Operatoren}
\begin{verbatim}
// containing (array ist untermenge):
SELECT ARRAY[2,7] <@ ARRAY[1,7,4,2,6]; // return true
gleichheit & casting:
SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]; // return true
// gemeinsames Element:
SELECT ARRAY[1,4,3] && ARRAY[2,1]; // return true
\end{verbatim}
\exam{Operationen nicht lesen aber schreiben können}
\ul
\li Arrays lassen sich mit negativen Indexes anlegen (z.B: von -5 bis +5)
\li Conversions, z.B. array to text (string\_(agg), array to rows
\li Es ist nur möglich an den Enden des Arrays anzufügen! Kein Einfügen an Stelle 7, wenn es keine 6 gibt
\li Array Update Varianten: Hinten anhängen / best. Wert ersetzen, mit Slice ganze Bereiche ersetzen, Array komplett ersetzen
\li Vorsicht mit Nullelementen in Arrays
\li Mit Bytearray können eigenen Datenstrukturen gebaut werden \ra eigener Konstruktor und eigene Lesefunktion erforderlich
\li Zwei Konstruktoren: '{1,2,3,4}' oder ARRAY[1,2,3,4]
\li Arrays dürfen mehrdimensional sein (integer[] darf weiteres integer[] array enthalten)
\ulE
\exam{Typische Prüfungsfrage: Resultatset von Subscript Query angeben \\
SELECT id, arr[4:6] FROM table; // liefert Element 4,5 und 6 von den Arrays, die eines besitzen, ansonsten werden nur die vorhandenen Elementen oder einen leere Menge geliefert.}
\se{Graphen}
\definition{Graph}{Knoten (Nodes/Vertices V, verbunden mit Kanten (Edges E). Graphen können gerichtet sein, Zyklen oder Pfade enthalten}
\examp{G = (V,E)}{\\ V = {A, B, C, D, E, F}\\
E = {(A, B), (A, C), (A, D), (C,D), (C, E), (D, E), (E, F)}
\img{img/v4.1.jpg}{}{0.25}{}
}
\se{Dictionaries}
\definition{Dictionaries}{Key-Value Pairs KVP}
\ul
\li Postgres: hstore ( 'a=>1, a=>2'::hstore )
\li Hstore wird als String abgelegt
\li hstore to array liefert {a,1,b,2}
\ulE
\begin{verbatim}
// List all keys
SELECT akeys(mykvpfield) FROM ...
// Get all key-value pairs
SELECT each(mykvpfield) FROM ...
// Get key value
SELECT mykvpfield->'name' FROM ...
// Test if key exists :
... WHERE mykvpfield ? '<value>';
// Abfragen
WHERE tags @> hstore('tourism', 'zoo')
\end{verbatim}
\se{Trees}
\definition{Trees}{ sind Specialformen von Graphen. Häufigstes Nutzungsverhalten: Write once, Read any times.}
\examp{Trees}{Kataloge, Stücklisten, Tags, DMOZ, Bibliothek}
\sse{Traversierungen}
\ul
\li Pre-order: Elternelement vor Kindelement besuchen
\li In-order: Elternelement zwischen Kindelementen besuchen
\li Post-Order: Elternelement nach Kindern besuchen
\li Depth-First: Zweig für Zweig bis ganz nach unten besuchen
\li Breigth-First: Ebene für Ebene besuchen
\ulE
\sse{Adjazenzliste}
\examp{}{Tabelle mit CTE}
\ul
\li Columns: ID, ParentID
\li Schnelle crud-ops
\li Level und Pfad bestimmen aufwendig
\li CTE zum Traversieren notwendig
\ulE
\sss{CTE}
\begin{verbatim}
id,desc, parent
---------------
2,'Computing', NULL
3, 'Programming', 2
4, 'C++', 3
5, 'Java', 3
6, Design', 2
\end{verbatim}
\sss{tree}
\begin{verbatim}
2. Computing
3. Programming
4. C++
5. Java
6. Design
\end{verbatim}
\sse{Nested Set Modell}
\examp{Nested Set Modell}{Tabelle der XML}
\definition{Nested Set Model}{Ähnlich wie Preorder Tree Traversal}
\examp{}{Nested Set Modell ist normale Baumstruktur, enthält allerdings als Knoteneigenschaften den kleinsten und höchsten direkten Nachfolger (left, right) als Wert. }
\ul
\li columns: Left, Right
\li Einfügen: sehr Aufwendig, weil Knoten umsortiert werden müssen und left/right angepasst werden müssen
\ulE
\img{img/v5.1.jpg}{}{0.75}{}
\sss{SQL}
\begin{verbatim}
-- fields: node, name, left, right, parent
-------------------------------------------------------
insert into skills values (2, 'Computing', 2, 29, NULL);
insert into skills values (3, 'Programming', 3, 10, 2);
insert into skills values (4, 'C++', 4, 5, 3);
\end{verbatim}
\sse{Materialisierter Pfad}
\examp{Materialisierter Pfad}{Ltree Datentyp, Modul}
\ul
\li Hirarchische Struktur von Bezeichnungen/Zahlen
\li Seppariert mit Punkt: Top.Countries.Europe.Switzerland
\li ltree ist eine Postgres Erweiterung
\ulE
\begin{verbatim}
CREATE TABLE skills (id INTEGER, oth ltree);
\end{verbatim}
\sss{Abfragebeispiele}
\begin{verbatim}
SELECT 'Welt.Europa.Schweiz.SG.Rapperswil.HSR'::ltree <@ 'Welt.Europa.Schweiz::ltree
// liefert true, weil HSR ein Nachfolger von Welt.Europa.Schweiz ist
// nächster gemeinsamer Vorgänger:
SELECT lca('Welt.Europa.Schweiz.SG.Rapperswil_Jona'::ltree, 'Welt.Europa.Schweiz.Zürich.Zürich'::ltree)
// liefert 'Welt.Europa.Schweiz'
// Alle Knoten, die 'School' heissen, egal wo in der Struktur
SELECT ... WHERE path ~ '*.School.*';
\end{verbatim}
\sse{XML}
\ul
\li XPATH (Postgres)
\li XML export:
\begin{verbatim}SELECT query_to_xml('SELECT * FROM mytable',TRUE,FALSE,''); \end{verbatim}
\ulE
\se{Repetition}
\uli{
\li hstore ist ein Key/Value Store in Postgres
\li XML Datentyp in Postgres kennt keine XQUERY
\li Select table\_to\_xml gibt eine Tabelle als XML aus
}
\exam{CTE Interpretieren können}
% PDF mit Repetitionsfragen
\ch{ORDBMS}
\definition{ORDBMS}{Objektrelationale Datenbanksysteme}
\expl{Unterschied Relationale Datenbanken / Objektorientierte Datenbanken}{Relationale Datenbanken haben es immer zum Ziel, als Resultat eine Tabelle zu liefern.}
\se{Objekttypen}
\uli{
\li Sind benutzerdefinierte Typen
\li Wird benannt durch den Namen des Typs, die Attribute (Name, Typ), die Methoden
}
\begin{verbatim}
CREATE OR REPLACE TYPE NameTyp AS OBJECT (
NStr VARCHAR2(20)
);/
CREATE OR REPLACE TYPE AdresseTyp AS OBJECT (
WOHNORT VARCHAR2(20),
PLZ VARCHAR2(10),
Land CHAR(2)
);/
CREATE OR REPLACE TYPE PersonenTyp AS OBJECT (
NName NameTyp,
VName VARCHAR2(20),
GebDatum DATE,
Adr AdresseTyp
);/
-- Verwendung
CREATE TABLE Angestellter (
PersNr INTEGER PRIMARY KEY,
PersInfo PersonenTyp,
Salaer NUMBER NOT NULL
);
\end{verbatim}
\important{In Typ-Deklarationen können keine Constraints und keine Defaultwerte definiert werden!}
\sse{Einfügen (Konstruktor)}
\begin{verbatim}
INSERT INTO Angestellter VALUES (
100,
PersonenTyp(
NameTyp('Graf'),
'Michael',
AdresseTyp (
'Thalwil', '8810', 'CH'
)
,To_Date('22.01.78', 'DD.MM.YY')
)
,8888
);
INSERT INTO Person VALUES (
NameTyp ( 'Mueller' ),
'Hans',
TO_DATE( '12.01.78', 'DD.MM.YY' ),
AdresseTyp ( 'Rapperswil', '8640', 'CH' )
);
INSERT INTO Person (NName, VName, GebDatum) VALUES (
NameTyp ( 'Meier' ),
'Vreni',
TO_DATE( '09.02.62', 'DD.MM.YY' )
);
\end{verbatim}
\sse{Abfragen}
\uli{
\li Traversieren der Objekthirarchie mittels des Punkt Operators: \\
SELECT t.NName, t.ADR.PLZ FROM Person t;
\li Objekte können verglichen werden: \\
SELECT * FROM Person WHERE adr = AdresseTyp('Thalwil', '8810', 'CH');
\li Order By ist nicht definiert. Muss selbst definiert werden
}
\begin{verbatim}
SELECT * FROM Angestellter;
SELECT persnr,a.persinfo.nname,a.persinfo.GibAlter() FROM Angestellter a;
\end{verbatim}
\sse{Vererbung}
\uli{
\li FINAL/NOT FINAL erlaubt/verbietet Vererbung (NOT FINAL ist default)
\li INSTANTIABLE / NOT INSTANTIABLE definiert abstrakte Klassen
}
\begin{verbatim}
CREATE TABLE Person OF PersonenTyp (
NName NOT NULL,
Adr DEFAULT AdresseTyp( NULL, NULL, 'CH' ),
GebDatum NOT NULL,
CONSTRAINT Name_UNIQUE UNIQUE( NName.NStr, VName, Adr.PLZ )
);
CREATE OR REPLACE TYPE PersonenTyp AS OBJECT (
name VARCHAR(40),
adresse VARCHAR(100),
geb_dat DATE,
MEMBER PROCEDURE druckeAdresse
) NOT FINAL;
-- Overriding
CREATE OR REPLACE TYPE StudentTyp UNDER PersonenTyp(
matrikelnr CHAR(7),
stud_adresse VARCHAR(100),
OVERRIDING MEMBER PROCEDURE druckeAdresse
);
\end{verbatim}
\uli{
\li Bereits beerbte Typen können nicht mehr geändert werden
}
\sse{Methoden}
\begin{verbatim}
CREATE OR REPLACE TYPE PersonenTyp AS OBJECT (
NName VARCHAR2(20),
VName VARCHAR2(20),
Adr AdresseTyp,
GebDatum DATE,
MEMBER FUNCTION gibAlter RETURN NUMBER,
ORDER MEMBER FUNCTION PersonenOrdnung (pers IN PersonenTyp) RETURN INTEGER
);
/
-- Implementation in der separat übersetzbaren Typdefinition
CREATE OR REPLACE TYPE BODY PersonenTyp AS
MEMBER FUNCTION gibAlter RETURN NUMBER IS
BEGIN
RETURN (SYSDATE - SELF.GebDatum) / 365;
END;
-- ...
END;
-- Aufruf
SELECT p.NName,p.GibAlter() FROM Person p;
\end{verbatim}
\sse{Vergleichsmethoden: MAP und ORDER}
\img{img/v6.1.jpg}{}{0.75}{}
\img{img/v6.2.jpg}{}{0.75}{}
\sse{Object Identifiers / Referenzen}
\uli{
\li Row Objects werden mit eine vom system generierten 16byte OID eindeutig identifiziert
\li Alternativ können die klassischen Primärschlüssel als OIDs
verwendet werden. Diese OIDs sind aber nicht systemweit
eindeutig, benötigen dafür weniger Platz!
}
\begin{verbatim}
CREATE TYPE TeilTyp AS OBJECT (
TName VARCHAR2(20)
);
CREATE TYPE TeileStrukturTyp AS OBJECT (
OberTeil REF TeilTyp,
UnterTeil REF TeilTyp,
Menge integer
);
\end{verbatim}
\img{img/v6.3.jpg}{Definieren von Object Tabellen}{0.75}{}
\img{img/v6.4.jpg}{Einfügen von Referenzen}{0.75}{}
\img{img/v6.5.jpg}{}{0.75}{}
Oracle überprüft 'Dangling' References nicht, man kann aber mit IS[NOT]DANGLING dies prüfen
\se{VARRAYS}
\uli{
\li Geordnete Menge von Datenelemente, max. Anzahl Elemente
\li Normalerweise „in line“ im Tupel gespeichert, ab einer gewissen
Grösse wird der VARRAY als BLOB ausserhalb des Tupels
gespeichert
\li Oracle kann weder Update, Delete oder Index auf Teil des VARRAYS anwenden. Es muss immer das ganze erneuert werden. Postgres kann es mit \[\]
}
\img{img/v6.6.jpg}{}{0.75}{}
\se{Nested Table}
\uli{
\li Tabelle in der Tabelle
\li lässt sich auch als sepparate Tabelle speichern, sodass von aussen darauf zu gegriffen werden kan
}
\begin{verbatim}
CREATE TYPE TelNummerTabTyp AS TABLE OF TelNummerTyp;
\end{verbatim}
\img{img/v6.7.jpg}{}{0.75}{}
\sse{VARRAY oder Nested Table?}
\img{img/v6.8.jpg}{}{0.75}{}
\sse{Associative Arrays}
\uli{
\li Ist etwas wie eine Hash Table.
\li Über den Key erhalte ich den Index.
\li Auch Index by Table genannt.
}
\exam{Index by Table: Nur wissen, dass es sowas gibt}
\ch{ODBMS}
\uli{
\li Objekte sollen bis zu beliebigen Tiefen persistiert werden können, ohne irgend ein Mapping
\li oo Datenbanken sind sehr eng an die jeweilige Sprache angebunden
\li 2-Tier Architekturen:
\uli{
\li Fat Client
\uli{
\li Applikationscode
\li DB Code (Persistenzimplementation, Queries)
}
\li DB Server
\uli{
\li Datenspeicherung
\li Locking
}
}
}
\sse{ODBS Anforderungen}
\img{img/v7.1.jpg}{}{0.75}{}
\sse{Warum keine Speicherung durch Serialisierung?}
\uli{
\li Unvollständig
\uli{
\li Klassendefinition wird nicht mitgespeichert
\li Probleme mit Versionierung / Schema Evolution
}
\li Nicht orthogonal (Serialisierbare Klassen müssen interface implementieren)
\li Nicht persistent (Object identity geht verloren)
\li Nicht skalierbar (Objekt-Graphen als Ganzes (de-)serialisiert)
\li Keine Transaktionen
\li Weder wiederherstellbar noch ‘concurrent’
}
\se{Persistenzstrategien}
\sse{Persistence by Reachability}
\uli{
\li Geht davon aus, dass es ein Wurzel Objekt gibt, von dem aus persistiert werden kann
\li Es werden nur von dort aus erreichbare Objekte persistiert
}
\img{img/v7.2.jpg}{}{0.25}{}
\sse{Persistenzserver}
\definition{Page Server}{Versteht nichts von Objekten, liefert einfach Pages an den Client aus}
\img{img/v7.3.jpg}{}{0.75}{}
\definition{Object Server}{Versteht Objekt und liefert anhand von Parametern Objekt an den Client zurück}
\img{img/v7.4.jpg}{}{0.75}{}
\sse{OID}
\uli{
\li oo DB's ersetzen OID Referenzen mit in-Memory Referenzen\\
\ra viell effizienter\\
\ra Pointer Swizzling
\li Logische OID: dem Programmierer bekannte OID, muss auf Memory-Referenz übersetzt werden
\li Physische OID: Memory Adresse \ra keine Übersetzung
}
\img{img/v7.5.jpg}{Pointer Swizzling}{0.75}{}
\expl{Objekterhaltende Abfrage}{Objekte werden nicht verändern}
\expl{Nicht Objekterhaltdende Abfrage}{z.B. Join. Aus bestehenden Daten werden neue Objekte generiert}
\expl{Objekterzeugende Anfrage}{Neue Obekte müssen zur Laufzeit aus bestehenden erzeugt werden}
\se{ODMG Standard}
\uli{
\li Attribute und Beziehungen wie bei oo Sprachen
\li Mehrfachvererbung bei Schnittstellen und EInfachvererbung bei Klassen
\li Objekte besitzen einen lebenslangen OID
\li Persistence by Reachability
\li ACID genügende Transaktionen
\li Lockprotokolle
}
\sse{ODL Object definition Language}
\img{img/v7.6.jpg}{1:1 Relation}{0.75}{}
\img{img/v7.7.jpg}{}{0.75}{}
\img{img/v7.8.jpg}{1:N Relation}{0.75}{}
\img{img/v7.9.jpg}{N:M Relation}{0.75}{}
\img{img/v7.10.jpg}{Rekursiv N:M}{0.75}{}
\img{img/v7.11.jpg}{Extent hat nichst mit 'extends' zu tun. extent bezeichnet die Wurzel!}{0.75}{}
\img{img/v7.12.jpg}{ANgPro Beispiel ODL}{0.75}{}
\sse{OQL Objective Query Language}
\uli{
\li Deskriptive Abfragespraches
\li
}
\img{img/v7.13.jpg}{Pfadorientierte Beispielabfrage mit OQL}{0.75}{}
\se{db4o}
\img{img/v7.14.jpg}{db4o Architektur}{1}{}
\img{img/v7.15.jpg}{db4o Modi}{0.75}{}
\ch{NoSQL}
\sse{Eigenschaften}
\expl{RDBMS Nachteile}{besitzen ``Single Point of Failure'' und kennen nur ``Scaling out'', kein ``Scaling up''}
\uli{
\li Nicht relational
\li Schemalos
\li Consistency keine Bedingung mehr (eventually consistent \ra BASE)
\li Hochverfügbarkeit
\li verteilte Datenbanken, verteilte Schreibzugriffe (läuft oft auf Clusters)
\li grosse Datenmengen
\li Open Source
\li Produkt des 21. Jhd
\li Einfache Datenreplikation
\li Keine genormte Abfragesprache, JSON jedoch weit verbreitet
}
\sse{NoSQL Kategorien}
\uli{
\li Key/Value-Datenmodelle bzw. -Datenbanken
\uli{
\li Value ist opak für DB (unsichtbar)
\li Besitzen Meta-Daten
\li bsp. hStore
}
\li Dokument-Datenmodelle bzw. -Datenbanken \ra geschachtelte Strukturen, typischerweise JSON
\uli {
\li Values sind strukturiert und dokumentiert im Unterschied zu Key-/Value DB
}
\li Column-Family Stores \ra Columnen zusammenfassen zu Supercolumnen
\img{img/v8.1.jpg}{Column Family}{0.75}{}
\li Graph-Datenbanken
\uli{
\li optimiert für Objekte mit Beziehungen
}
\li Andere: Objektdatenbanken und XML-Datenbanken
}
\expl{Schemalose DB}{
\uli{
\li nichtuniforme Daten \ra\ Bsp. Openstreetmap kennt keine Festlegung auf Strassen, Gebäude, etc.
\li implizite Schemen \ra\ Schema und Konsistenz-Infos innerhalb der Applikation
}
}
\se{Aggregations Datenmodelle}
z.B:
\uli{
\li Arrays
\li Dictionaries
\li geschachtelte Strukturen
}
\exam{Was passiert bei einer geclusterten DB mit der Performance, wenn ein weiterer Node hinzugefügt wird? \\ Zuerst geht sie runter, bis die reorganisation abgeschlossen ist, anschliessend wieder hoch.}
\se{Verteilte Datenbank-Modelle}
\sse{Arten des Verteilens}
\uli{
\li sharding
\li Replikation
}
\expl{sharding}{horizontales Partitionieren \ra\ z.B. Kunden a-c in einen Node ablegen, c-e in einen andern, ...}
\expl{Replikation}{
\uli{
\li Master-Slave: Schreib-ops nur auf Master, leseoperationen auf Replikate (Slaves)
\li Master-Master: (P2P), schreiben auf alle Knoten
}
}
\se{Konsistenz}
\expl{CAP}{Es können nur zwei Bedingungen garantiert werden, auf die dritte muss verzichtet werden \\ \ra\ bsp. relationale Datenbanken: auf Availability muss verzichtet werden während Index oder Sorting neu gebildet werden muss. \\
\ra\ Twitter: Au Consistency wird verzichtet. Wenn ein Tweet gelöscht wird, so geht es noch einige Zeit, bis er auf allen Knoten gelöscht ist.}
\img{img/v8.2.jpg}{CAP}{0.5}{}
\img{img/v8.3.jpg}{Bei NoSQL DB's ist nur ``Inkonsistenz'' und kein ``Fail'' erwünscht}{0.75}{}
\sse{BASE}
\uli{
\li \textbf{B}asically \textbf{A}vailable: permanent availablity
\li \textbf{S}oft State: consistency is no solid state
\li \textbf{E}ventual Consistency: data is consistent sometime
}
\expl{CAP und BASE}{sind schlecht definierbar/abgrenzbar\\
\ra\ Konsistenz kann (in verteilten DBs) durch mehr Knoten erhöht werden – zum Preis einer erhöhten Latenz\\
\ra\ Durability kann z.T. zugunsten der Performance geopfert werden\\
\ra\ Quorum: Mindestanzahl von Knoten, die Ack senden muss, damit das System als konsistenz gilt }
\se{Map Reduce}
\img{img/v8.4.jpg}{\textbf{Map} Reduce Beispiel anhand einer Shopping Card Statistik}{0.75}{}
\img{img/v8.5.jpg}{Map \textbf{Reduce} Beispiel anhand einer Shopping Card Statistik}{0.75}{}
\uli{
\li Eigent sich gut zur Berechung von Aggregationsfunktionen
\li Funktion:
\oli{
\li orderItems werden zerlegt in einzelne Key/Value Paare
\li Output von Map wird mit anderen Outputs zusammengeführt
\li Endresultat wird berechnet
}
}
\expl{Materialized View}{Gespeichertes Map Reduce, inkrementell aktualisiert}
\ch{Backup \& Recovery}
\expl{Transaktionen Backup}{Write ahead Log. Relationale Datenbanken schreiben die SQL Statements (insert, delete \ra nur Ändernde Opertionen) ins Log, bevor die Operationen ausgeführt werden}
\sse{Backup Alternativen?}
\uli{
\li Replizieren
\uli{
\li Irgendwann wird trotzdem ein Backup gebraucht, um einen bestimmten Zustand wiederherzustellen
\li Dump kein Problem, weil in dieser Zeit die Andern Nodes daten liefern können
}
\li geclustert
\uli{
\li Zentrales Backup trotzdem notwendig
}
\li cloud
\uli{
\li Lokales Backup bevorzugt
}
\li SAN Storage Area Network
\uli{
\li Backup unumgänglich für Speicherung auf einem langfristigen Backupmedium
}
}
\se{Backup Architektur}
\stdImg{v9.1}{Beteiligte Systemkomponenten}
\uli{
\li Log sollte physisch auf anderer Maschine als DB untergebracht sein
\li Wird das Memory knapp, werden noch nicht commitete Daten in die DB ausgelagert \ra heikel wegen Wiederherstellung nach Absturz
}
\se{Recovery}
\sse{Lokale Fehler einer Transaktion}
\uli{
\li Führt zu Deadlocks
\li wird mit Rollback oder lokale undo behoben
}
\sse{Hauptspeicherverlust}
\uli{
\li Stromausfall
\li Inkonsistenz von Daten die noch nicht auf Disk geschrieben wurden
\li wird mit globalem Undo behoben
\li Undo: alle aktiven Transaktionen werden zurückgesetzt
\li Redo: abgeschlossenen Trasaktionen werden wiederholt
}
\sse{Fehler mit Hintergrundspeicher}
\uli{
\li Hardwarefehler, Disk crash
\li Auf Disk gespeicherte Daten unbrauchbar
\li Komplettes Backup neu einspielen
\li Mit Hilfe von Log Files werden die Daten bis zum letzten konsistenten Zustand wiederholt
}
\expl{WAL}{Write Ahead Log: Vor den Op's werden alle Op's als Log EInträge festgehalten für Undo}
\expl{Checkpoint}{Blockt neue Transaktionen, bis alte abgeschlossen wird. Checkpoint ist somit Konsistenter Zustand. \ra\ zu viele Checkpoints bremsen DB aus.}
\stdImg{v9.2}{Überlappende Transaktionen sind verloren bei Absturz}
\stdImg{v9.3}{Checkpoint blockt T3 bis T2 abgeschlossen. Damit ist nach Absturz nur T3 und T4 verloren. Es kann versucht werden, T3 mittels der Logs wiederherzustellen.}
\expl{Checkpoint und Savepoint}{Savepoint ist Rücksprungpunkt innerhalb Transaktion, Checkpoint ist globalen Konsistenzpunkt}
\se{Backup}
\stdImg{v9.4}{}
\uli{
\li Variante 1:
\uli{
\li Keine Transaktionen auf DB während Sicherung aktiv =
Offline-Backup (consistent backup)
\li Vorteil: DB-Kopie ist in transaktionskonsistentem Zustand!
\li Nachteil: während Sicherung darf keine (Schreib-
)Transaktion auf DB aktiv sein! (vielfach nicht akzeptabel,
z.B. 24h Betrieb)
}
\li Variante 2:
\uli{
\li Transaktionen können auf DB während der Sicherung
aktiv sein = Online-Backup (inconsistent backup)
\li Vorteil: DB-Betrieb nicht (oder kaum) beeinträchtigt
\li Nachteil: DB-Kopie nicht in transaktionskonsistentem
Zustand
\li Wiederherstellung (Recovery) aufwändiger
}
}
\definition{PITR}{Point in Time Recovery: Wiederherstellung der DB bis zu einem definierten
Zeitpunkt vor dem Fehler („Simulation“ eines
Systemfehlers zu diesem Zeitpunkt) = PITR \\
\ra\ Besser als Full Backup + Inkrementelles Backup
}
\sse{Backup Planung}
\uli{
\li Was soll gesichert werden? Sys-DB+User-DB
\li Wie lange zurück archivieren?
\li Maximale Ausfallzeit, d.h. Zeit für Recovery? Anzahl und Grösse der Änderungen sowie Zeitintervall
\li Wie soll gesichert werden
\li Recovery Tests
}
\expl{Stammdaten}{Daten, die nicht mehr verändert werden.}
\ch{Interne Ebene}
\se{Heap}
\definition{Heap}{Collection von Data Pages}
\uli{
\li Daten werden nicht sortiert gespeichert
}
\stdImg{v10.1}{Data Pages}
\definition{Table Scan}{Scannen aller Pages einer Tabelle}
\se{Index}
\definition{Index}{Ein Index ist eine Datenstruktur für den effizienten Zugriff auf die Daten}
\uli{
\li Bei jedem Einfügen wird z.B. der PK in den Index gelegt
\li Index kann auch manuell für beliebige Felder erstellt werden
\li Indexes dienen der beschleunigung der Suche
\li Index bedeutet Overhead
}
\stdImg{v10.2}{}
\sse{Queries}
\stdImg{v10.3}{Arten von Queries}
\stdImg{v10.4}{Artn von Queries}
\sss{Relevante Operationen}
\stdImg{v10.5}{Relevante Operationen}
\se{Indexstrukturen}
\sse{ISAM}
\expl{ISAM}{Index Sequential Access Method}
\uli{
\li einfügen und suchen schnell
\li aktualisieren schlecht, weil alle Files reorganisiert werden
}
\sse{B-Bäume}
\uli{
\li Range- und Equal Search
\li Balancierte Mehrweg-Bäume
\li Die Knotengrösse entspricht der Seitengrösse
\li Der Verzweigungsgrad des Baums hängt davon ab, wie viele Einträge auf einer Seite Platz haben
\li Durch entsprechende Algorithmen für das Einfügen und Löschen von
Daten garantiert, dass der Baum immer ausbalanciert ist =>
Logarithmische Zugriffszeiten
\li Der Baum wächst von unten nach oben
\li Ist ein Knoten voll, so wird beim Einfügen der Knoten gesplitted und das mittlere Element wandert einen Ebene nach oben
\li Ein Knoten muss immer mindestens zwei Elemente beinhalten
\li Beim Löschen wird bei zu leeren Knoten der Baum reorganisiert
}
\stdImg{v10.6}{B-Baum}
\stdImg{v10.7}{B-Baum}
\stdImg{v10.8}{B-Baum}
\sse{B+ Baum}
\uli{
\li Daten sind in den Blättern
}
\sse{Clustered Index}
\stdImg{v10.9}{clustered Index}
\uli{
\li Pro Tabelle nur ein clustered Index, weil die Tuppels sortiert werden
}
\sse{Non Clustered Index}
\stdImg{v10.10}{non-clustered Index}
\stdImg{v10.11}{}
\uli{
\li Non-Clustered/Clustered Index gemixt braucht zwei Zugriffe
\li für jeden Zugriff muss eine Page geladen werden
}
\sse{Hash Index}
\uli{
\li Ordnet Keys zu Records {0,...,N-1}, konkret Schlüssel zu Blöcken zu
\li Probleme: Hash Überlauf \ra\ von Zeit zu Zeit Reorganisation nötig
\li Für Equi Join gut geeignet
}
\stdImg{v10.12}{Hash Index}
\sse{Bitmap Index}
\expl{Bitmap Index}{Matrix mit \#Rows*\#Otionen Felder. Für jede Kombination Feld/Option wird ein Bit 0 oder 1 gesetzt. Darum eignet sich dieser Index nur für Felder mit einer begrenzten Anzahl Optionen, z.B. Männlich/Weiblich oder Enum}
\uli{
\li Ordnet Attributwerte als Bitmuster
\li Geeignet für Attribute mit wenigen diskreten Werten, d.h. Wertebereiche mit
geringer Kardinalität (ca. 1\% der Datenmenge)
\li Vor allem für DataWarehouse geeignet
}
\stdImg{v10.13}{}
\sse{Mehrdimensionale, räumliche Indexe}
\uli{
\li near-queries
\li Räumliche Ordnungsstruktur: Was räumlich nahe beisammen ist, soll auch auf dem
Speicher nahe beisammen liegen
\li Hierarchien: Anpassung an ungleich verteilte Daten. schneller Ausschluss von ganzen
Bereichen bei der Suche
}
\stdImg{v10.14}{}
\se{Index erstellen}
\stdImg{v10.15}{}
\sse{Kosten Modell}
\img{img/v10.16.jpg}{Definitionen}{0.5}{}
\img{img/v10.17.jpg}{}{0.25}{}
\sse{Kosten bei Clustered Index}
\stdImg{v10.18}{}
\stdImg{v10.19}{}
\sse{Kosten bei unclustered Index}
\stdImg{v10.20}{}
\stdImg{v10.21}{}
\stdImg{v10.22}{}
\sse{Vergleich}
\stdImg{v10.23}{Vergleich für grosse Tabelle}
\stdImg{v10.24}{Vergleich für kleine Tabelle}
\uli{
\li Index bringt nur bei grossen Tabellen etwas
}
\se{Query Processing}
\stdImg{v10.25}{Select}
\oli{
\li Syntax check
\li Check, ob Tabellen, Felder etc. überhaupt vorhanden
\li Umwandlung in AST
\li Query wird anhand von Statistiken optimiert
\li Variable Binding, Code Generation
\li Execution
}
\exam{Prüfungsfrage: Unterschied Range Query und Multipoint Query}
\examp{}{Geg: p = 10000 Pages, r = 20 Records / Page\\
Ges: Elemente mit dem Attributewert Rot
\uli{
\li TableScan: 200000 IOs
\li Mit Index:
\uli{
\li 100 rote Elemente: 2 (Indexzugriffe)+100 = 102
\li 10000 rote Elemente: 2500+20000+200 = 22700
}
}
}
\important{Sollen mehr als 10\% Der Tupel gefunden werden, ist der Table Scan billiger als ein Index. Auch bei kleinen Tabellen lohnt sich ein Index nicht.}
\se{Join}
\sse{Hash Join}
\uli{
\li Von jedem Rechten Hash bilden und in Hash Store ablegen
\li Durch alle linken durchgehen und alle Tupel, die zu dem Hash gehören aus dem Hash Store (Assoziativ Store) auslesen.
}
\stdImg{v11.1}{Hash Join}
\expl{Tabelle passt nicht in Speicher}{Ist die zu joinende Tabelle zu gross, um sie in den Speicher zu laden, muss sie partitioniert werden \ra\ vorsortierung}
\definition{Hash Join}{$3*(M+N)$\\
M: Relation R mit M Pages\\
N: Relation S mit N Pages\\
}
\examp{Hash Join}{L: 1000 Pages, R: 200 Pages, Buffer: 52\\
$3*(M+N) = 3*(1000+200)=3600$
}
\sse{Nested Loop Join}
\uli{
\li Nur für kleine Tabellen
\li Für jeden linken jedes Rechte durchgehen und prüfen ob matched
}
\stdImg{v11.2}{Nested Loop Join}
\sss{Nested Loop Block Join}
\uli{
\li Jeder Block der äusseren Relation wird mit jedem Blcok der inneren Relation verglichen
\li Wenn man häufig einen Fremdschlüssel hat, auf dem bereits ein Index besteht
}
\stdImg{v11.3}{}
\examp{Nested Loop Block Join}{
\oli{
\li So viele linke Blöcke in Memory laden, wie Platz haben (Blockset)
\li Einen rechten Block ins Memory laden (Input Buffer)
\li Vergleichen
\li Für jede Kombination von Blocksets und rechten Blöcken muss ein einzelner Vergeich gemacht werden.
}
}
\definition{Nested Loop Block Join}{$M + N*(M/(B-2))$\\
M: Relation R mit M Pages\\
N: Relation S mit N Pages\\
B: Buffergrösse\\
(B-2 weil noch je ein Block für Input/Output)
}
\examp{Nested Block Join Kosten}{Left Table: 1000 Pages, Right Table: 200 Pages, Memory: 50 Pages + Input/Output Block\\
$\#IO's = M + N*(M/(B-2)) = 1000+200*(1000/50) = 5000$
}
\sss{Index Nested Loop}
\uli{
\li Wird nur eingesetzt, wenn alle Daten im Memory Platz haben
\li Alle Daten werden ins Memory geladen, anschliessend wird mittels dem Index zu jedem Tupel seine Join-Partner gefunden.
}
\oli{
\li Block laden
\li Für jedes Tupel über den Index die Fremdtupel Joinen
\li $\# TupelsLeft * IndexDepth$ (z.B: 1000*100 * 3)
}
\sse{Merge Join}
\uli{
\li Durch linke durchgehen und zu jedem das/die rechte(n) zuordnen
\li rechte müssen nicht durchsucht werden, weil sie sortiert sind
\li Sehr effizient, bedingt aber, dass die Tupels sortiert vorliegen
}
\stdImg{v11.4}{}
\definition{Merge Join}{$M+N$, Sortierung: $O(N*log(N))$\\
M: Relation R mit M Pages\\
N: Relation S mit N Pages\\
}
\examp{Merge Join}{L: 1000 Pages, R: 200 Pages, Buffer: 52\\
$M+N = 1200$
}
\expl{Index}{\uli{
\li Indexe erhöhen den Overhead
\li Indexe verlangsamen Update Operationen
\li Sind vor allem lesende Zugriff vorhanden, lohnen sich viele Indexe
\li Indexe sollten so optimiert sein, dass möglichst viele Anfragen beschleunigt werden
\li Index-Only Abfragen sind am schnellsten
\li Clustered Indexe beschleunigen Abfragen zusätzlich (nur eine pro Tabelle)
\li Ziel Der Indexe ist immer eine Minimierung von IO Operationen
\li Reihenfolge der Felder in zusammengesetzen Indexes müssen beachtet werden
}}
\se{DB Optimierung}
\uli{
\li DB Roundtripps minimieren: Batch-Transaktion, Alle Daten auf's Mal an die DB schicken, mit PlSQL Script verarbeiten
\li Denormalisierung: Redundanzen mit Triggers aktualisieren
}
\sse{Logische Optimierung}
\uli{
\li Selektionen so früh wie möglich ausführen
\li Basiert auf Termersetzung von Termen der Relationenalgebra anhand von Algebraäquivalenzen
\li Äquivalenzen als Ersetzungsregel eingesetzt, um anhand von heuristischen Vorgaben die Anfrage in die gewünschte Form zu bringen
}
\sse{Physische Optimierung}
Regel- (alt) oder Kostenbasierte Optimizer (modern)
\uli{
\li Einbezug von Indexes
\li Verbesserung der Analyse und Statistiken
\li Einsatz von Heuristiken (Daumenregeln) und Kosten
}
\definition{Selektivität}{ Selektivität: prozentueller Anteil der Tupels in einer
Tabelle, die von einer Query geliefert werden.}
\definition{Dichte}{Dichte=durchschnittlicher prozentueller Anteil von
Duplikaten.}
\ch{Verteilte DBMS}
\expl{VDBMS}{Besteht aus versch. Knoten. Knoten sind autonom. Jeder Knoten hat Teil an einer globalen Applikation. \ra\ Transaktionen und Queries sollen ohne Angabe von Konkreten Knoten ablaufen.}
\se{Homogene VDBMS}
\uli{
\li Alle Knoten haben identische Software
\li Alle Knoten wissen voneinander und arbeiten zusammen um
Benutzeranfragen zu verarbeiten.
\li Erscheint gegenüber dem Benutzer als ein System
}
\stdImg{v12.1}{Homogenes VDBMS benötigt ein globales Schema}
\sse{eng gekoppelte Multi Database System}
\stdImg{v12.2}{}
\sse{lose gekoppelte MDBMS}
\stdImg{v12.3}{}
\se{Heterogene VDBMS}
\uli{
\li Knoten können unterschiedliche SW und Schemas aufweisen
\li Schemaunterschiede \ra\ Hauptproblem für die Auführung verteilter
Queries
\li Softwareunterschiede \ra Hauptproblem für die Ausführung verteilter
Transaktionen.
\li Knoten wissen evtl nichts voneinander und können nur beschränkte
Funktionalitäten für die Kooperation anbieten
}
\stdImg{v12.4}{Oracle DB + über Gateway angebundenes Non-Oracle DBMS}
\expl{Gateway}{Bindet fremde Datenbank an bestehende DB an. Queries und Responses werden jeweils konvertiert.}
\se{Verteilte Datenhaltung}
\expl{Datenverteilung}{Benutzer soll keine Kenntnisse über Verteilung benötigen}
\expl{Transparenz}{
\uli{
\li Der Benutzer sieht eine globale Sicht, er kennt die Replikation
der Fragmente nicht
\li Queries werden auf der Relation definiert, nicht auf den
Fragmenten
}
\stdImg{v12.5}{}
}
\sse{Fragmentierung und Allokation}
\stdImg{v12.6}{}
\expl{Fragmentierung}{Unterteilung einer Relation in Fragmente. Fragmente enthalten genügend Information, um die Relation r zu rekonstruieren.}
\sse{Horizontale Fragmentierung}
Jedes Tupel wird auf ein oder mehrere Fragmente aufgeteilt.
\stdImg{v12.7}{Beispiel Horizontale Fragmentierung}
\sse{Vertikale Fragmentierung}
Das Schema einer Relation ist in mehrere kleinere Schemata aufgeteilt (Spalten werden in Fragmente aufgeteilt). Jedes Subschemata muss den Primärschlüssel enthalten!
\stdImg{v12.8}{Beispiel Vertikale Fragmentierung}
\sse{Replikation}
\definition{Replikation}{ Eine Relation (oder eine Fragement einer Relation) ist repliziert, wenn sie (es) redundant in mehrern Knoten gespeicher ist.
}
\sss{Vorteile}
\uli{
\li Verfügbarkeit – auch bei Ausfall eines Reolikas
\li Parallelität der Ausführung von Queries.
\li Reduzierter Datentransfer beim lokalen Zugriff auf die
replizierten Daten
}
\sss{Nachteile}
\uli{
\li Höhere Update Kosten \ra\ jedes Replikat muss aktualisiert werden
\li Komplexere Synchronisation \ra\ Aenderungen können zu temporär inkonsistenten Daten führen.
}
\se{Verteilte Transaktionen}
\uli{
\li Transaktion muss über alle Knoten gehen.
\li Benötigt wird immer ein globaler Transaktionsmanager
}
\stdImg{v12.9}{}
\stdImg{v12.10}{}
\sss{2PC}
\definition{2PC - Two Face Commit}{Mehrere Transaktionen zu einer Gesammttransaktion zusammenfassen.}
\uli{
\li Transaction Manager: koordiniert Transaktionen über mehrere Resource Managers
\li Resource Manager (z.B. DBMS, Message Queue System): unterstützt lokale Transaktionen
\li Applikation: steuert Transaktionen
}
\stdImg{v12.11}{2PC}
\uli{
\li Sowohl der Transaction Manager wie die Resourcen führen ein WAL.
\li Wenn einer kein Ready sendet, wird die Transaktion abgebrochen.
\li Bei Fehler: Rollback an alle
\li TM stellt sicher, dass alle Knoten mit Commit oder Rollback abschliessen
}
\stdImg{v12.12}{Zustandsübergang 2 PC : TM}
\stdImg{v12.13}{Zustandsübergang 2PC : RM}
\sss{X/Open XA (DTM)}
\stdImg{v12.14}{}
\uli{
\li standardisiert Schnittstellen zwischen Komponenten in verteilten Systemen
\li wird von praktisch allen kommerziellen Systemen unterstützt
\li TransactionManager steuert Resourcen über XA Interface
\li Applikation darf nicht mehr direkt Resourcen ansprechen
}
\stdImg{v12.15}{JTA: Java Transaction API ist eine Schnittstelle basierend auf XA}
\sse{Serialisierbarkeit}
2PL wird über alle Knoten realisiert.
\sse{Locking}
\uli{
\li Globaler Lockmanager ist unabdingbar
\li Deadlock-Detektion schwierig: Lock-Manager kooperieren in einem verteilten Protokoll für die Detektion
\li Performance kann stark beeinträchtigt werden
}
\se{Synchronisation replizierter Daten}
\sse{Read One, Write All}
\uli{
\li lesen schnell
\li schreiben langsam
}
\sse{Majority Protocol}
\uli{
\li Lesen oder Schreiben eines Objektes verlangt Zugriff
auf Mehrheit der Replikate \ra\ dadurch kann kein Anderer lesen/schreiben.
\li jedes Replikat kann gleichzeitig von mehreren
Transaktionen gelesen, jedoch nur von einer
Transaktion geändert werden
\li Daten können unter Umständen inkonsisten sein \ra\ Versionsnummer benötigt
\li lesen ist langsamer
\li Geht auch noch, wenn Knoten ausfällt
\li Hohe Kommunikationsausfall
\li Geht nicht für 2 Knoten
\li Deadlock auch bei nur einem Datenobjekt möglich (jeder hat 1/3 der Knoten gesperrt.
}
\sse{Quorum Consensus Protocol}
\stdImg{v12.16}{}
\sse{Primary Copy}
\stdImg{v12.17}{}
\se{Deadlocks}
\sse{Timeout}
\uli{
\li Auflösen nach Timeout
\li Zu Kurz: Deadlocks, die keine sind, werden aufgehoben
\li Zu Lang: Bad Performance
}
\sse{Wait-for Graph}
\stdImg{v12.18}{}
\se{Replikation}
\sse{Warum Replikation?}
\uli{
\li Höhere Verfügbarkeit
\li Niedriger Trafic
\li Bessere Performance durch lokalen Zugriff
\li Vorbeugung gegen Datenverlust
}
\sse{Anwendungen}
\uli{
\li Load-Balancing (High Performance Cluster, hochverfügbar skalierbar, Master-Master asynchron oder synchron, Sehr schnelle Verbindungen)
\stdImg{v14.1}{}
\li Remote Servers
\stdImg{v14.2}{}
\li Real-Time OLAP
\stdImg{v14.3}{}
\stdImg{v14.4}{}
\li Hochverfügbarkeit (Warm Standby)
\stdImg{v14.5}{}
\li Mobile Datenbanken
\stdImg{v14.6}{}
}
\sse{Konsistente Replikation}
\stdImg{v14.7}{Streng konsistente Replikation}
\stdImg{v14.8}{Schwach konsistente Replikation}
\sse{Synchrone Replikation}
\stdImg{v14.9}{}
\sse{Asynchrone Replikation}
\stdImg{v14.10}{}
\end{document}