Oracle9i für den DBA - *ISBN 3-8273-1559

Uwe Herrmann, Dierk Lenz,
Günter Unbescheid, Johannes Ahrends
Oracle9i
für den DBA
Effizient konfigurieren, optimieren
und verwalten
An imprint of Pearson Education
München • Boston • San Francisco • Harlow, England
Don Mills, Ontario • Sydney • Mexico City
Madrid • Amsterdam
3
Datenbankdesign
Beim Aufbau jeder Datenbank und jedes Datenbankschemas sind eine Fülle von
Designentscheidungen zu treffen. Diese Entscheidungen stehen auf der einen Seite
im Zusammenhang mit konkreten Anforderungen an die Funktionalität und Performance der betreffenden Installation und sind auf der anderen Seite abhängig
von den gegebenen technischen Möglichkeiten des eingesetzten Datenbanksystems – in diesem Falle Oracle9i. Oftmals stehen mehrere Alternativen für eine
Implementierung zur Verfügung. Das nachfolgende Kapitel möchte an dieser Stelle
behilflich sein, um im „Wald“ der technischen Merkmale optimale Entscheidungen für die Implementierung von Schema- und Datenbankobjekten treffen zu können.
3.1
Namenskonventionen
Beim Umgang mit einer Datenbank werden auf den verschiedensten Ebenen Dinge
benannt: Datenbanken, Instanzen, Dateien, Tablespaces, Tabellen, Views, Indizes
usw. Neben den allgemeingültigen Regeln für die Benennung von Datenbankobjekten, die z.B. die maximale Länge und die erlaubten Zeichen festlegen und vom
Datenbanksystem vorgegeben werden, ist es empfehlenswert, eigene Richtlinien
für die Namensbildung zu schaffen.
Warum denken wir an dieser Stelle über diese Namenskonventionen nach?
Einige der in Frage kommenden Beweggründe sind:
1. Wenn wir darüber nachdenken, nachdem die Datenbank konfiguriert und die
Daten geladen sind, ist es zu spät.
2. Eine Oracle-Datenbank wird auf verschiedenen Ebenen mit diversen Namen
identifiziert (u.a. Datenbankname, Instanzname, Netzwerkdienstname sowie
Dienstname). Sie können alle verschieden sein, müssen aber nicht.
3. Es gibt immer mehr Situationen, bei denen die im Unternehmen vorhandenen
Datenbanken global betrachtet werden – der Enterprise Manager oder ein
Storage-Subsystem bzw. SAN sind nur zwei Beispiele. Wenn dann fünf von acht
Datenbanken ORACLE heißen, ist die Übersichtlichkeit dahin.
4. Konventionen können die Erschliessung des Kontextes der betreffenden Objekte erleichtern: pk$tabellenname kann dem Betrachter signalisieren, dass es
sich um den Primaärschlüssel der Tabelle tabellenname handelt.
Im Folgenden wird zunächst über Datenbanken und deren Namensgebung diskutiert; im Anschluss dann über die Namensgebung bei Datenbankobjekten.
130
3.1.1
Datenbankdesign
Datenbanken
Bei der Bennung von Datenbanken ist zu unterscheiden zwischen dem per Parameter vergebenen Datenbanknamen (db_name), dem Domänennamen der Datenbank
(db_domain) und dem globalen Datenbanknamen, der aus den beiden genannten
Parameter zusammengesetzt wird, jedoch auch – unabhängig davon – explizit
gesetzt werden kann. Die folgenden Abschnitte erläutern die Zusammenhänge.
Domänen
Bei der Vergabe von Namen für eine Oracle-Datenbank gibt es zwei Stellen, an
denen eine Domäne auftauchen kann:
:
:
Die db_domain wird zusammen mit db_name (beides Initialisierungsparameter)
interner Bestandteil des Datenbanknamens; zusammen ergeben diese Namen
auch den Standardwert für den Initialisierungsparameter service_names, der ab
Oracle8i zur Adressauflösung bei den Netzwerkdiensten genutzt werden kann.1
Netzwerkdienstnamen, früher auch mit SQL*Net Alias oder Net8-Name bezeichnet, können ebenfalls mit einer Domäne versehen werden. Einen hohen
Bekanntheitsgrad unter langjährigen Oracle-DBAs dürfte die Standarddomäne
früherer Releases, world, haben.
Die beiden genannten Domänen haben eigentlich nichts miteinander zu tun.
Sobald man allerdings mit dem Enterprise Manager arbeitet, entsteht plötzlich
doch eine Verknüpfung: Bei der automatischen Erkennung von Netzwerkdiensten
liefert der Oracle Agent dem Management-Server alle vorhandenen Instanzen und
trägt sie sowohl in den Navigationsbaum als auch optional in die tnsnames.oraDatei ein – natürlich in der Form db_name.db_domain.
Die Empfehlung an dieser Stelle lautet also, die Domänen in beiden Fällen gezielt
zu nutzen und identisch zu halten.
Mittels eines weiteren Initialisierungsparameters können die Domänen zusammen
mit Datenbanknamen auf Datenbank-Links übertragen werden: Sobald
global_names = true
steht, dürfen Datenbank-Links nur so benannt werden wie die Zieldatenbank.
Diese Forderung ist gerade in gewachsenen Datenbanken schwer einzuhalten, da der
funktionale Verlust, der durch das Deaktivieren eines Datenbank-Links entsteht,
oft schwer zu überblicken ist. Fängt man jedoch mit einem neuen System bei Null
an, so ist diese Einstellung unbedingt zu empfehlen.
Einfache Empfehlungen für Standarddomänen sind firma.de, firma.com oder einfach firma. Da die im Oracle-Namensmodell verwendeten Domänen nicht identisch zu den (meist jedoch ähnlichen) Netzwerkdomänen sein müssen, können mit
1.
In der CONNECT_DATA-Klausel wird hierzu statt dem Parameter (SID=XXX) der Parameter
(SERVICE_NAME=XXX[.DOM]) angegeben. Eine Verbindung wird hergestellt, wenn der angegebene
SERVICE_NAME einem der in service_names angegebenen Namen entspricht.
Namenskonventionen
131
Hilfe der Domänen zusätzliche Informationen hinterlegt werden; z.B. können alle
produktiven Datenbanken in die Domäne prod.firma.de gelegt werden.
Datenbanknamen
Ausgehend davon, dass Domänen verwendet werden, kann sich der Datenbankname auf die Funktion der Datenbank fokussieren. Datenbanknamen wie oradb
sind wenig sinvoll. Es sollten eher Namen wie dwh für Data Warehouse, pps für die
Datenbank eines PPS oder xyz für die Datenbank unter der Anwendung XYZ ausgewählt werden.
Der Datenbankname kann insgesamt acht Zeichen lang sein. Im Hinblick auf die
nächste Migration kann es sinnvoll sein, dem Datenbanknamen eine Nummer
hinzuzufügen, also z.B. pps01.
Der Datenbankname wird als Initialisierungsparameter db_name hinterlegt. Er kann
beim CREATE DATABASE-Kommando angegeben werden. Es wird grundsätzlich empfohlen, dies zu tun – damit hat man die Sicherheit, mit den richtigen Initialisierungsparametern zu arbeiten. Arbeitet man z.B. mit der falschen Initialisierungsparameterdatei, so geht das CREATE DATABASE-Kommando schief, und das ist gut so.
Der Datenbankname, zusammengesetzt aus db_name und db_domain, wird beim
Anlegen der Datenbank u.a. dazu verwendet, den globalen Datenbanknamen abzuleiten. Dieser kann über die View global_name abgefragt werden. Der globale
Datenbankname wird dazu verwendet, die Namen von Datenbank-Links zu
erzwingen. Mit gesetztem Initialisierungsparameter
globale_names = true
muss – wie bereits erwähnt – ein Datenbank-Link immer genauso heißen wie der
globale Datenbankname der Zieldatenbank. Unabhängig vom eigentlichen Datenbanknamen kann der globale Datenbankname mit dem Kommando
ALTER DATABASE RENAME GLOBAL_NAME TO globdb.firma.de;
geändert werden.
Der Datenbankname selbst – Parameter db_name – kann ausschließlich über ein
CREATE CONTROLFILE-Kommando geändert werden.
Instanznamen
In der Oracle-Architektur sind Datenbank und Instanz getrennt, und somit können
auch verschiedene Namen vergeben werden. Der Instanzname verfügt nicht über
eine Domäne; er muss allerdings auf jedem Knoten eindeutig sein.
Auch wenn es nicht zwingend erforderlich ist – der Instanzname sollte gleich oder
zumindest ähnlich dem Datenbanknamen sein. Auf Grund der fehlenden Domänen kann er aus Übersichtlichkeitsgründen leicht abgewandelt werden, z.B. kann
die Instanz zur Datenbank dwh05.prod.firma.de den Namen pdwh05 tragen, um
die Eigenschaft Produktivsystem auch im Instanznamen zu verdeutlichen.
132
Datenbankdesign
Die maximale Länge des Instanznamens, hinterlegt im Umgebungsparameter
oracle_sid, ist plattformspezifisch. Acht Stellen werden bei den getesteten Plattformen problemlos akzeptiert; mehr als acht Stellen werden generell nicht empfohlen.
Datenbanken im Netzwerk
Sobald man auf eine Datenbank im Netzwerk zugreifen will, kommt zusätzlich zu
den bereits diskutierten Namen ein Netzwerkdienstname ins Spiel. Hiermit wird
die Datenbank im Netzwerk identifiziert.
Netzwerkdienstnamen können mit Domänen versehen werden. Sie sehen daher den
globalen Datenbanknamen sehr ähnlich und werden oft mit diesen verwechselt.
Hat man sich bei den Datenbanknamen Gedanken gemacht, so kann man natürlich die Netzwerkdienstnamen identisch zu den Datenbanknamen inklusive
Domäne wählen. Diese Wahl trifft z.B. auch der Enterprise Manager, wenn er eine
über den Agenten entdeckte Datenbank in sein Repository einträgt.
Oft gibt es für eine Datenbank mehrere Netzwerkdienstnamen. Oft wird für eine
Anwendung ein spezieller Netzwerkdienstname verwendet, so dass die Anwendungsdaten bei Bedarf zwischen verschiedenen Datenbanken verschoben werden
können.
Dieses Konzept findet sich auch bei den Dienstnamen als Instanzparameter wieder:
Hiermit kann jeder Instanz eine Liste von Diensten (service_names) mitgegeben
werden. Der Parameter wird standardmäßig auf den Wert db_name.db_domain
gesetzt. Beim Verbindungsaufbau über die Oracle Net-Schnittstelle wird ein
service_name mitgegeben, der mindestens einem Wert aus der Liste der
service_names der Instanz entsprechen muss.
3.1.2
Objektnamen
Grundregeln
Folgende Regeln existieren für die Namensgebung von Objekten in der Datenbank:
1. Namen sind bis zu 30 Byte lang (Ausnahme: Datenbankname bis zu 8 Byte; Datenbank-Links bis zu 128 Byte).
2. Namen dürfen keine Anführungsstriche enthalten.
3. Groß-/Kleinschreibung wird bei Namen nicht unterschieden.
4. Namen müssen mit einem alphabetischen Zeichen des Datenbankzeichensatzes
anfangen.
5. Namen können ausschließlich alphanumerische Zeichen, _, $ und # enthalten;
Namen von Datenbank-Links zusätzlich . und @ .
6. Reservierte Worte dürfen nicht verwendet werden.
7. dual darf nicht verwendet werden.
8.
Auch nicht reservierte Worte, die aber eine Bedeutung im Oracle-Umfeld haben (z.B. DIMENSION oder SEGMENT), dürfen nicht verwendet werden.
Namenskonventionen
9.
133
Zwei Objekte im gleichen Namensraum (d.h. Schema) dürfen nicht den gleichen Namen haben.
10. Die Spalten einer Tabelle bzw. einer View müssen alle unterschiedliche Namen
haben. Spalten unterschiedlicher Tabellen oder Views können gleich lautende
Namen haben.
11. Prozeduren oder Funktionen innerhalb eines Packages können gleich lautende
Namen haben, falls sie eine unterschiedliche Anzahl von Argumenten oder Argumente verschiedenen Typs haben.
12. Namen können in doppelten Anführungszeichen geschrieben werden. In diesem Fall treffen die Regeln 3 bis 7 nicht zu.
Allgemeine Empfehlungen
Neben den eigentlichen Regeln sind aber folgende Überlegungen wichtig, um
Namen für Datenbankobjekte zu finden:
:
:
:
Namen sollten lesbar und ausführlich sein. Die 30 Zeichen können ruhigen Gewissens ausgenutzt werden. Aussagekräftige Namen erhöhen die Lesbarkeit von
SQL-Befehlen und Programmen erheblich.
Passend zu den Voreinstellungen vieler Design Werkzeuge sollten Tabellennamen im Plural definiert werden. (Entitäten werden meist im Singular definiert.)
Spaltennamen für Spalten mit identischer Bedeutung sollten im gesamten Schema konsistent benannt werden. Somit können sowohl Beziehungen zwischen
Tabellen als auch Sachverhalte klar umschrieben werden.
Eine Spalte, die den Preis eines Produktes in Euro angibt, sollte ruhig
preis_in_euro genannt werden. Eine solche Spalte lediglich preis zu nennen,
kann verwirrend sein (man denke nur an die Euro-Umstellung). Sollte die Währung in einer eigenen Spalte waehrung_id gekennzeichnet sein (wobei dies natürlich einen Fremdschlüssel in einer Tabelle waehrungen repräsentiert), so kann der
Spaltenname z.B. preis_in_waehrung sein.
An dieser Stelle empfehlen wir, für deutsche Namen mit Umlauten ae, oe und ue
einzusetzen, obwohl auch Namen wie währungen möglich sind (dies auch ohne
doppelte Anführungszeichen). Solche Namen können aber Probleme bereiten, z.B.
wenn Sie bei der Programmierung in Oracle-fremden Produkten verwendet werden.
3.1.3
Systemobjekte
Auch für Systemobjekte wie Tablespaces, Datendateien, Rollback-Segmente usw.
müssen Namen gefunden werden. Hierbei ist auf jeden Fall Kreativität gefragt,
denn Systemobjekte sind für die Anwendung nicht unmittelbar erforderlich und
spielen somit auch keine Rolle im System, die a priori benannt werden kann.
Daher ist es wichtig, sich einige Grundregeln zu überlegen, um den Überblick im
Datenbankdschungel nicht zu verlieren.
Tablespaces für Systemobjekte wie Rollback- und Temporärsegmente sollten immer
gleich genannt werden, z.B. rbs und temp. Bei Tablespaces für Anwendungsdaten
134
Datenbankdesign
bringt die Anwendung oft einen Vorschlag mit. Ansonsten nimmt man für eine
Anwendung X gerne x_data für Tabellen, x_index für Indizes usw.
Bei den Datendateien sollte gewährleistet sein, dass man am vollqualifizierten
Dateinamen folgende Eigenschaften erkennen kann:
:
:
:
:
Diese Datei gehört zu einer Oracle-Datenbank.
Den Datenbanknamen (ohne Domäne)
Den Tablespace-Namen
Eine laufende Nummer für die n-te Datei des Tablespaces
Bei Rollback-Segmenten ist die Standardstrategie eine Nummerierung, z.B. rbs01,
rbs02 usw. Lediglich Rollback-Segmente für spezielle Aufgaben sollten auch entsprechend genannt werden, z.B. rbs_batch.
3.2
Physische Implementierung von Entitäten
Die Analyse der Anforderungen und die daran anschließende Entwicklung eines
konzeptionellen Datenmodells ist bekanntlich systemneutral, d.h. unabhängig
von dem eingesetzten Datenbanksystem. Anders verhält es sich dagegen bei der
Umsetzung des konzeptionellen Modells in das physische Datenmodell. Hierbei
sind die technischen Möglichkeiten des Zielsystems – in unserem Fall des Datenbanksystems Oracle8i oder Oracle9i – mit den funktionalen und operationalen
Anforderungen der betreffenden Anwendung in Einklang zu bringen.
Die folgenden Abschnitte behandeln vor diesem Hintergrund die verschiedenen
technischen Möglichkeiten, Entitäten in einer Oracle-Datenbank zu implementieren, und die mit diesen Möglichkeiten verbundenen Auswirkungen im Hinblick
auf die Zugriffe und die Verwaltbarkeit der betreffenden Objekte. Ziel ist es dabei,
die zur Verfügung stehenden Organisationsformen von Tabellen geschickt für die
Optimierung von Zugriffen und Verwaltungsoperationen zu nutzen.
3.2.1
Überblick
Die Zeiten, in denen Entitäten einzig in Form von Heap-Tabellen oder Clustern
implementiert werden konnten, sind lange vorbei. Mittlerweile existieren weitergehende Möglichkeiten der physischen Organisation, die – bei sinnvoller Nutzung
– wesentlich zur Optimierung von Anwendungen und zur Vereinfachung der
Administration beitragen können. Diese Organisationsformen sind im Einzelnen:
:
:
:
die „klassischen“ in Heap-Form organisierten Tabellen, die Daten unsortiert
speichern,
im Cluster abgelegte Tabellen, deren Daten in Abhängigkeit von Cluster-Schlüsseln physisch gespeichert werden,
Index-organisierte Tabellen, deren Daten über B*Baumstrukturen organisiert
sind,
Physische Implementierung von Entitäten
:
:
:
:
135
partitionierte Tabellen, die physisch unabhängige Partitionen zu einer „logischen“ Tabelle zusammenfassen,
externe Tabellen, die auf externe, in Dateiform abgelegte Daten zugreifen,
temporäre Tabellen, die Daten für die Dauer einer Transaktion oder Session
speichern,
Objekttabellen, deren Datensätze über Objektbezeichner referenzierbar sind,
und die die Einbettung von geschachtelten Tabellen (nested tables) erlauben.
Diese Organisationsformen werden mit ihren Besonderheiten in den folgenden
Abschnitten im Detail besprochen.
3.2.2
Heap-organisierte Tabellen
Heap-organisierte Tabellen sind die „klassischen“ Datenbanktabellen von OracleSystemen. Sie speichern ihnen zugewiesene Datensätze unsortiert in einem beliebigen, für Insert-Operationen zur Verfügung stehenden Oracle-Block. Bei einer neu
angelegten, leeren Tabelle bedeutet dies, dass die Daten in der Reihenfolge der
Insert-Operationen nacheinander – im Anschluss an den Header-Block – in die Blöcke des betreffenden Datensegmentes geschrieben werden. Im fortgeschrittenen
Zustand der Tabelle, nachdem auch DELETE- und UPDATE-Operationen die Tabelle
verändert haben, werden Datenblöcke, die für INSERT-Operationen zur Verfügung
stehen, über so genannte Freilisten verwaltet. Aufeinander folgende INSERT-Operationen werden dadurch nicht zwangsläufig in physisch aufeinander folgende Blöcke geschrieben, sondern können beliebig verstreut werden. Der jeweilige „Höchststand“ der Füllung wird durch die so genannte Hochwassermarke (high water mark)
gekennzeichnet. Blöcke, die sich jenseits der Hochwassermarke befinden, haben
demnach noch nie Datensätze für die betreffende Tabelle enthalten.
Der Zugriff auf Heap-organisierte Tabellen erfolgt entweder durch das vollständige
Lesen der Tabelle (full table scan) oder über eine vorgegebene Satzadresse. Beim vollständigen Lesen werden alle Blöcke vom Beginn des Segmentes bis zur Hochwassermarke gelesen. Bei dynamischen Tabellen mit einem hohen Anteil an DELETEOperationen kann dies bedeuten, dass entsprechend viele Leerblöcke gelesen werden müssen – ein typischer Fall für eine Reorganisation der betreffenden Tabelle.
Der Zugriff über Satzadressen (rowids) erfolgt dagegen gezielt auf bestimmte Blöcke
in bestimmten, über Dateinummern identifizierten Dateien. Die Hochwassermarke
und die „Datenlöcher“ spielen in diesen Fällen keine Rolle. Die Satzadressen selbst
werden in der Regel über entsprechende Indexzugriffe vom System ermittelt. Die
Indizes werden in separaten Segmenten aufgebaut. Für aufwändig indizierte Tabellen kann der für Indizes benötigte Speicherplatz dem der Tabelle nahe kommen
oder ihn gar überschreiten.
Heap-organisierte Tabellen sind gut geeignet für Entitäten, die keine besonderen
Anforderungen an die Menge der Daten, deren physische Speichersequenz oder
deren Indizierung stellen. Heap-Tabellen sind darüber hinaus eine gute Standardlösung für den Fall, dass die genauen Anforderungen an die Speicherung nicht
bekannt sind.
136
Datenbankdesign
Im folgenden Beispiel wird die Heap-Tabelle kunde mit den Attributen kdnr
(Schlüssel), kname und kadresse erstellt. Die organization-Klausel ist dabei optional:
CREATE TABLE kunde (kdnr NUMBER CONSTRAINT kunde_pk PRIMARY KEY,
kname VARCHAR2(100), kadresse VARCHAR2(500))
ORGANIZATION HEAP;
3.2.3
Cluster
In Clustern abgelegte Tabellen speichern Daten in Abhängigkeit von ClusterSchlüsseln in für diese Cluster-Schlüssel ausgewiesenen Datenblöcken. Teilen mehrere Tabellen einen Cluster-Schlüssel, können sie gemeinsam in einem Cluster
angelegt werden. Entsprechend werden Datensätze mit gleichen Cluster-Schlüsseln
gemeinschaftlich gespeichert.
Die Zuordnung von Cluster-Schlüsseln zu Datenblöcken und entsprechend der
Zugriff auf diese Sätze wird über zwei unterschiedliche Verfahren – durch Indizierung und Hash-Algorithmen – geregelt. Dementsprechend sprechen wir von
Index-Clustern oder Hash-Clustern.
Index-Cluster
Bei Index-Clustern übernimmt ein B*-Index, der so genannte Cluster-Index, die
Reservierung einzelner Blöcke für bestimmte Cluster-Schlüssel. Da der Index im
Kontext der Insert-Operationen gefüllt wird, erfolgt die Reservierung der Blöcke in
der Reihenfolge, wie die Insert-Operationen der Cluster-Schlüssel von den Programmen durchgeführt werden. Die Hochwassermarke wird bei leeren Index-Clustern –
wie auch bei Heap-Tabellen – synchron zu den Insert-Operationen verschoben.
Im folgenden Beispiel wird der Index-Cluster testcl mit den Tabellen kunde und
auftrag angelegt:
CREATE CLUSTER testcl (kdnr NUMBER) INDEX;
CREATE INDEX i$testcl ON CLUSTER testcl;
CREATE TABLE kunde (kdnr NUMBER, kname VARCHAR2(100),
kadresse VARCHAR2(500))CLUSTER testcl(kdnr);
CREATE TABLE auftrag (aufnr NUMBER, kdnr NUMBER, aufdat DATE)
CLUSTER testcl(kdnr);
Listing 3.1: Anlegen Index-Cluster
Hash-Cluster
Beim Hash-Cluster ist die Reihenfolge der Insert-Operationen nicht relevant für die
Zuordnung der Datenblöcke zu den Cluster-Schlüsseln. Hier bestimmt eine HashFunktion den Block, in welchem ein Cluster-Schlüssel zu speichern ist. Die HashFunktion gibt in der Regel den Blockversatz relativ zum Beginn des betreffenden
Physische Implementierung von Entitäten
137
Segmentes an. Damit nicht mehrere Cluster-Schlüssel einen Datenblock teilen,
muss beim Anlegen eines Hash-Clusters die erwartete maximale Anzahl von Cluster-Schlüsseln möglichst präzise angegeben werden. Diese Schlüsselanzahl
bestimmt direkt die Anzahl der Blöcke, die unterhalb der Hochwassermarke bereitgehalten werden müssen – unabhängig davon, ob Daten eingefügt wurden oder
nicht. Insert-Operationen bewegen daher nur dann die Hochwassermarke, wenn
ein Blocküberlauf stattfindet.
Im folgenden Beispiel wird der Hash-Cluster testclh mit den Tabellen kundeh und
auftragh angelegt. Es werden 1.000 Cluster-Schlüssel erwartet. Entsprechend werden 1.000 Blöcke initialisiert. Die Hash-Funktion arbeitet – spezifiziert durch die
hash is-Klausel – auf Basis der numerischen Spalte kdnr:
CREATE CLUSTER testclh (kdnr NUMBER(10,0)) HASHKEYS 1000 HASH IS kdnr;
CREATE TABLE kundeh (kdnr NUMBER(10,0), kname VARCHAR2(100),
kadresse VARCHAR2(500))
CLUSTER testclh(kdnr);
CREATE TABLE auftragh (aufnr NUMBER, kdnr NUMBER(10,0), aufdat DATE)
CLUSTER testclh(kdnr);
Listing 3.2: Anlegen Hash-Cluster
Cluster-Implementierungen sind keine Standardlösungen, sie sollten sorgfältig
geplant werden. Generell können sie die folgenden Vorteile bieten:
:
:
:
„physische Joins“ durch die Speicherung mehrerer Tabellen in einem Cluster,
sofern der Cluster-Schlüssel identisch mit dem Join-Schlüssel ist
Adressierung der Datensätze im Hash-Cluster ohne Vorhandensein eines Indexes
„sortierte“ Speicherung der Datensätze
Diesen Vorteilen stehen jedoch eine Reihe von Nachteilen gegenüber:
:
:
:
:
:
:
Für den Fall, dass nur eine der im Cluster abgelegten Tabellen gelesen wird,
kommt es zu erhöhten Blockzugriffen.
bei nur partiell gefüllten Hash-Clustern erhöhte Anzahl von Blockzugriffen
Ungenügende Füllung von Cluster-Blöcken bei „geringem“ Datenvolumen pro
Cluster-Schlüssel; umgekehrt Verkettung von Cluster-Blöcken (Überlauf) bei
„großem“ Datenvolumen pro Cluster-Schlüssel; nicht einschätzbare Wirkungen
bei „schwankendem“ Datenvolumen pro Cluster-Schlüssel
Eindeutigkeit kann nur über zusätzliche Indizes sichergestellt werden, die zusätzlichen Speicherplatz kosten.
ineffiziente Segmentgrößen bei Hash-Clustern, wenn die Anzahl der ClusterSchlüssel nicht bekannt ist oder falsch eingeschätzt wurde
Die Hash-Funktion kann nur für Gleichheitsoperatoren genutzt werden.
138
:
Datenbankdesign
Cluster können nicht repliziert und partitioniert werden.
Die vorangehenden Abschnitte machen deutlich, dass Cluster-Implementierungen
nur selten zum Einsatz kommen dürften.
3.2.4
Index-organisierte Tabellen (IOT)
Index-organisierte Tabellen speichern ihre Daten – sortiert nach dem Primärschlüssel – in Form eines B*-Index-Baumes. Im Gegensatz zu normalen B*-Index-Bäumen
enthalten die B*-Bäume von IOT-Tabellen jedoch auch Spalten, die nicht zu dem
Tabellenschlüssel gehören. Es ist darüber hinaus möglich, bestimmte Spalten, die
nicht zum Schlüssel gehören, „auszulagern“, d.h. außerhalb des B*-Baumes in
einem separaten Segment – dem Überlaufbereich – zu speichern. Auf diese Weise
können die Datensätze in den Blättern des Baumes kurz gehalten und dadurch der
Baum kompakter aufgebaut werden. Beginnend mit Oracle8i lassen sich zusätzliche Sekundärindizes für IOT-Tabellen definieren, um Zugriffe, die nicht über die
Primärschlüsselspalten erfolgen, zu optimieren. Diese Sekundärindizes enthalten
logische Satzadressen für den Zugriff auf den Index-Baum. Da Einträge in IndexBäumen durch INSERT- und UPDATE-Operationen und damit verbundenen SplitOperationen auf den Index-Blättern möglicherweise verschoben werden, können
Zugriffe über logische Satzadressen fehlschlagen. In diesen Fällen ist dann eine
zusätzliche Scan-Operation über den Primärschlüssel notwendig, um den betreffenden Datensatz zu lesen.
Im folgenden Beispiel wird die Index-organisierte Tabelle artikelpreis mit den
Schlüsselspalten artnr, von_datum und bis_datum angelegt. Die Spalten kommentar
und rabattliste werden dabei – sofern der Schwellenwert von 10 % pro Block und
Datensatz überschritten wird – in einen Überlaufbereich ausgelagert, der sich in der
Tablespace ts_over befindet:
CREATE TABLE artikelpreis
(artnr VARCHAR2(20), von_datum DATE, bis_datum DATE,
preis_euro number(6,2), kommentar VARCHAR2(2000),
rabattliste number(4),
CONSTRAINT pk_artikelpreis PRIMARY KEY(artnr, von_datum, bis_datum)
)
ORGANIZATION INDEX TABLESPACE ts_data
PCTTHRESHOLD 10 INCLUDING preis_euro OVERFLOW TABLESPACE ts_over;
Listing 3.3: Index-organisierte Tabelle
Im nächsten Beispiel wird der Sekundärindex i$artpreis_rab für die Spalte
rabattliste angelegt:
CREATE INDEX i$artpreis_rab ON artikelpreis(rabattliste);
Wie normale Indizes, so lassen sich auch Index-organisierte Tabellen zusätzlich
komprimieren. Bei der Komprimierung werden die Spalten des Primärschlüssels in
Präfix- und Suffixbereiche aufgeteilt und die wiederholt auftretenden Werte der
Präfixe innerhalb eines Blockes unterdrückt. Durch die Komprimierung wird eine
Physische Implementierung von Entitäten
139
Verkürzung der Datensätze in den Blättern des Index-Baumes erreicht, die den
Index kompakter und damit für I/O-Operationen effizienter machen.
Im folgenden Beispiel wird die oben beschriebene Tabelle artikelpreis mit einer
Komprimierung auf der ersten Schlüsselspalte – artnr – angelegt. Dementsprechend werden redundante Artikelnummern innerhalb eines Index-Blattes unterdrückt:
CREATE TABLE artikelpreis
(artnr VARCHAR2(20), von_datum DATE, bis_datum DATE,
preis_euro NUMBER(6,2), kommentar VARCHAR2(2000),
rabattliste NUMBER(4),
CONSTRAINT pk_artikelpreis PRIMARY KEY(artnr, von_datum, bis_datum)
)
ORGANIZATION INDEX COMPRESS 1 TABLESPACE ts_data
PCTTHRESHOLD 10 INCLUDING preis_euro OVERFLOW TABLESPACE ts_over;
Listing 3.4: Index-organisierte Tabelle (komprimiert)
Index-organisierte Tabellen sind sehr gut geeignet für die Umsetzung aller Entitäten, die einen großen Teil ihres Informationsgehaltes aus den Schlüsselspalten
beziehen. Hierbei schlägt vor allem die Einsparung von Speicherplatz zu Buche, da
keine separaten Segmente für Tabellen und Indizes angelegt werden müssen. Mögliche Kandidaten sind z.B.
:
:
so genannte Intersection-Tabellen, die n:m-Relationen 2 des Datenmodells auflösen und
große Referenztabellen, die nur wenige Spalten enthalten, die nicht zum
Schlüssel gehören.
3.2.5
Partitionierte Tabellen
Partitionierte Tabellen bieten die Möglichkeit, Datensätze nach vorgegebenen Verteilungskriterien physikalisch unabhängig, d.h. in separaten Tablespaces mit unterschiedlich großen Extents, zu speichern und diese Partitionen separat zu verwalten. Aus Sicht der Anwendung sind alle Daten über den Namen der partitionierten
Tabellen les- und schreibbar, der Optimizer dagegen kennt die physikalische Aufteilung der Tabelle und ist in der Lage, entsprechende Zugriffspfade zu generieren.
Die zu einer partitionierten Tabelle gehörenden Indizes lassen sich ebenso partitionieren – nach eigenständigen oder identischen Kriterien wie die Tabelle selbst. Für
die Festlegung der Verteilungskriterien stehen drei unterschiedliche Strategien zur
Verfügung:
:
:
2.
Die Range-Partitionierung verteilt die Daten nach festen, in der Syntax vorgegebenen Wertebereichen.
Die Hash-Partitionierung regelt die Verteilung über eine Hash-Funktion.
Jedes A bezieht sich auf ein oder mehrere B und umgekehrt kann sich jedes B auf ein oder mehrere A beziehen.
140
:
:
Datenbankdesign
Die Composite-Partitionierung kombiniert die beiden oben erwähnten Methoden, indem für jede Range-Partition mehrere Hash-Subpartitionen angelegt
werden.
Ab Oracle9i steht mit der List-Partitionierung ein weiteres Verteilungskriterium
zur Verfügung, wodurch die Zuteilungen zu Partitionen über beliebige, disjunkte Wertelisten erfolgen.
Durch die Aufteilung der Daten in „kleinere“ Partitions-Segmente kann grundsätzlich eine bessere Verwaltbarkeit und ein effizienterer Aufbau der Indizes erreicht
werden. Für wirklich große Tabellen, d.h. Tabellen im Gigabyte-Bereich, sollte die
Partitionierung auf jeden Fall in Erwägung gezogen werden. In Kapitel 3.4 werden
die Details dieses Themas ausführlich behandelt.
3.2.6
Externe Tabellen
Ab der Version 9i ist es möglich, externe, im Dateisystem abgelegte Daten mit Hilfe
von „externen“ Tabellen so in die Datenbank zu integrieren, dass ihr lesender
Zugriff über normale SQL-Befehle durchgeführt werden kann. UPDATE-, INSERTund DELETE-Operationen sowie das Anlegen von Indizes sind dagegen in diesem
Kontext nicht möglich. Die externen Daten können jedoch mit Hilfe von Funktionen und Ausdrücken beliebig abgefragt, in Views integriert, über Synonyme referenziert oder nach Spalten sortiert werden.
Externe Tabellen werden im Data Dictionary hinsichtlich ihrer Spaltenstruktur, der
Lokation ihrer externen Daten sowie der für das Lesen der Daten notwendigen Formatmaske definiert. Der Zugriff auf die externen Daten erfolgt schließlich über
einen in die Datenbank integrierten Treiber.
Um mit externen Tabellen arbeiten zu können, muss zunächst einmal ein Datenbankverzeichnis angelegt werden. Dem im Data Dictionary definierten Aliasnamen wird dabei ein externes Verzeichnis auf dem Datenbankserver zugeordnet.
Im folgenden Beispiel wird dem Datenbankverzeichnis dat_dir das externe Verzeichnis /home/oracle zugeordnet:
CREATE OR REPLACE DIRECTORY dat_dir AS '/home/oracle';
Der Benutzer, der die externe Tabelle anlegen will, muss als Nächstes in der Datenbank Leserechte für dieses neue Datenbankverzeichnis erhalten:
GRANT READ ON DIRECTORY dat_dir TO scott;
Sollen die Zugriffe auf die externen Daten in einer Log-Tabelle protokolliert oder
Formatverletzungen in eine Bad-Datei geschrieben werden, sind darüber hinaus
Schreibrechte für das Verzeichnis zu vergeben:
GRANT WRITE ON DIRECTORY dat_dir TO scott;
Im nächsten Schritt kann dann die externe Tabelle – in diesem Fall exttab – definiert werden:
Physische Implementierung von Entitäten
141
CREATE TABLE scott.exttab
(empno NUMBER(4),
ename VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY dat_dir
ACCESS PARAMETERS
(records delimited by newline
badfile dat_dir:'exttab%p.bad'
logfile dat_dir:'exttab%p.log'
fields terminated by ','
missing field values are null
(empno,ename)
)
LOCATION ('exttab.txt')
)
PARALLEL
REJECT LIMIT UNLIMITED;
Listing 3.5: Externe Tabelle
In diesem Beispiel wird die externe Tabelle exttab mit den Spalten empno und ename
angelegt. Die zugrunde liegende Datei wird in der LOCATION-Klausel angegeben. Die
Zeilen der Tabelle sollen sich aus den Zeilen dieser Datei ergeben (NEWLINE-Klausel);
die Felder sind entsprechend durch Kommata getrennt gespeichert. Auf die Datei
wird über den Treiber Oracle_Loader – siehe die TYPE-Klausel – zugegriffen. Die Datei
Exttab.txt kann durchaus mehr als die hier angegebenen „Spalten“ enthalten, entsprechende, weiterführende Einträge werden ignoriert. Für den Fall, dass die „Spaltenwerte“ der Datei sich nicht im Rahmen der definierten Datentypen und
längen – hier NUMBER(4) und VARCHAR2(10) – darstellen lassen, wird eine Fehlerdatei
geschrieben – siehe die BADFILE-Klausel. In diesem Beispiel wird der Name der Datei
aus den Elementen exttab, der Prozessnummer des betreffenden Serverprozesses
und der Dateierweiterung txt gebildet. Jeder Zugriff wird darüber hinaus in einer
Log-Datei – LOGFILE-Klausel – protokolliert. Zugriffe werden über die gewohnte SQLSyntax generiert, die Selektion von Rowids ist jedoch – aus nahe liegenden Gründen
– nicht möglich. Die PARALLEL-Klausel sorgt für eine Parallelisierung der Zugriffe, was
jedoch nur für große Datenmengen Laufzeitvorteile mit sich bringt.
Externe Tabellen sind vor allem dort interessant, wo externe Daten konsolidiert
und geladen werden müssen. Die Dateien können auf diese Weise direkt aus der
Datenbank heraus – z.B. im Kontext von PL/SQL-Prozeduren – selektiert, die Daten
über SQL-Funktionen entsprechend verdichtet und in interne Tabellen übernommen werden. Der aufwändige Aufruf von separaten Werkzeugen, wie z.B. SQL*Loader, ist damit in vielen Fällen nicht mehr notwendig. Ebenso ist es möglich, die der
externen Tabelle zugeordneten Datendateien durch gleichnamige zu ersetzen.
Dadurch kann der Datenbestand komplett ausgetauscht werden, ohne die Definition der externen Tabelle in der Datenbank ändern zu müssen.
142
3.2.7
Datenbankdesign
Temporäre Tabellen
Der Terminus „temporäre Tabellen“ (temporary tables) ist etwas irreführend: Was an
temporären Tabellen „flüchtig“ ist, sind ihre Daten, keineswegs jedoch ihre im
Data Dictionary definierte Struktur. Einmal angelegt sind sie – entsprechende Privilegien vorausgesetzt – für alle Benutzer sichtbar und benutzbar. Die Daten, die sie
speichern, werden jedoch am Ende jeder Transaktion oder am Ende der betreffenden Session automatisch gelöscht. Sie sind grundsätzlich – auch nach einer COMMITOperation – nur für den einfügenden Benutzer sichtbar.
Physisch speichern temporäre Tabellen ihre Daten in der temporären Tablespace
des anlegenden Benutzers, und dies auch erst zum Zeitpunkt der betreffenden
Insert-Operation. Aus diesem Grunde führt die Verwendung der TABLESPACE-Klausel bei temporären Tabellen zu Fehlern. Sie können beliebig indiziert, über Views –
auch zusammen mit regulären Tabellen – referenziert und mit Triggern belegt werden. DML-Operationen auf ihren Daten erzeugen nur für die Rollback-Informationen Redo Log-Einträge, nicht jedoch für die geänderten Datenblöcke.
Im folgenden Beispiel wird die temporäre Tabelle load erzeugt, deren Daten bis
zum Ende jeder Session erhalten bleiben sollen.
CREATE GLOBAL TEMPORARY TABLE load
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT PRESERVE ROWS;
Listing 3.6: Temporäre Tabelle
Zum Einfügen und Selektieren von Daten werden herkömmliche SELECT- und
INSERT-Befehle benutzt. Indizes werden über den CREATE INDEX-Befehl – ohne
TABLESPACE- und STORAGE-Klausel – erzeugt, setzen allerdings voraus, das die betreffende Tabelle vollständig leer ist.
CREATE INDEX t$load ON load(class)
*
FEHLER in Zeile 1:
ORA-14452: Versuch, einen Index auf einer schon verwendeten temporären
Tabelle zu erstellen, zu ändern oder zu löschen
Temporäre Tabellen sind sehr gut für die Zwischenspeicherung und spätere Konsolidierung von Daten geeignet. Beliebig viele Anwendungen mit gleichen Anforderungen an temporäre Speicherstrukturen können sich jeweils eine temporäre
Tabelle teilen, ohne Bezeichner für die Identifizierung der eigenen Daten mitzuführen. Die Daten können dabei intern entstehen und intern weiterverarbeitet werden
oder in einem ersten Schritt von externen Ressourcen geladen und dann intern
konsolidiert werden. Durch das reduzierte Redo Log-Volumen können temporäre
Tabellen im Kontext großer Datenmengen verwendet werden. Ein weiterer Vorteil
ist dadurch gegeben, dass sie ihren Speicherplatz nicht permanent in Form eines
Physische Implementierung von Entitäten
143
eigenen Segmentes anlegen, sondern diesen – beim Vorhandensein von Daten –
aus dem Speichervolumen des temporären Tablespaces beanspruchen.
Temporäre Tabellen haben unter Oracle9i durch das Aufkommen von externen
Tabellen für das Laden externer Daten an Bedeutung verloren, sind hier aber
immer noch interessant für die interne Zwischenspeicherung.
Leider ist es nicht möglich, parallele Abfragen oder parallele DML-Operationen auf
Temporär-Tabellen auszuführen, außerdem können sie nicht partitioniert werden.
3.2.8
Objekttypen und Kollektionen
Seit der Version 8.0 des Oracle-Datenbanksystems stehen eine Reihe objektrelationaler Merkmale zur Verfügung. Die Liste dieser Merkmale wurde unter Oracle8i
und – vor allem – unter Oracle9i erweitert, so dass nun eine beachtliche Bandbreite
objektorientierter Techniken – inklusive der Vererbung und Polymorphie – für
Designimplementierungen zur Verfügung stehen. Eine ausführliche Besprechung
dieser Merkmale liegt außerhalb der Ziele dieser Publikation. Auf den folgenden
Seiten soll jedoch ein kurzer Überblick über die Möglichkeiten gegeben werden,
konzeptionelle Vorgaben in Oracle8i und Oracle9i umzusetzen. Weitergehende
Informationen zu diesem Thema finden sich in Kapitel 10.
Objekttypen
Objekttypen sind die Grundbausteine der objektorientierten Welt. Sie werden im
Kontext von Oracle auch als „benutzerdefinierte Datentypen“ (user-defined datatypes) bezeichnet. Objekttypen bestehen ihrerseits aus Attributen und Methoden.
Attribute sind entweder skalare Datentypen, wie z.B. NUMBER und VARCHAR2, Referenzen oder andere benutzerdefinierte Datentypen. Methoden (member functions)
sind Prozeduren oder Funktionen, die relevante Abläufe für die Objekte des betreffenden Typs abbilden. Methoden können in PL/SQL oder Java codiert und als solche in der Datenbank gespeichert werden. Methoden, die in anderen Sprachen –
z.B. C – realisiert wurden, werden dagegen extern gehalten, sind aber über gespeicherte Prozeduren ansprechbar. Neben den vom Benutzer frei definierbaren
Methoden gibt es vordefinierte Methoden: Konstruktoren (constructor methods) dienen dem Anlegen und Ändern von Objekten eines Typs und heißen so wie der
Objekttyp selbst.
Objekttypen sind sehr gut geeignet, wiederverwendbare Attributgruppen mit für
sie relevanten Prozedurabläufen zu definieren und in unterschiedlichen – relationalen wie objektrelationalen – Schemakontexten einzusetzen.
Im folgenden Beispiel wird der Objekttyp adresse angelegt, der aus den Attributen
strasse, postfach, plz , ort und land besteht.
144
Datenbankdesign
CREATE OR REPLACE TYPE adresse AS OBJECT(
strasse VARCHAR2(50),
postfach VARCHAR2(20),
plz
VARCHAR2(20),
ort
VARCHAR2(50),
land
VARCHAR2(50))
/
Listing 3.7: Anlegen Objekttyp adresse
Einmal definiert, lassen sich Objekttypen als Spaltenobjekte (column object) oder
Zeilenobjekte (row object) verwenden.
Spaltenobjekte
Objekttypen, die als Attribute in anderen Objekttypen oder in relationalen TupelTabellen verwendet werden, bezeichnet man als Spaltenobjekte. Auf diese Weise
lassen sich Objekttypen als „Attributgruppen“ beliebig oft einsetzen. Im folgenden
Beispiel wird die relationale Tabelle firma angelegt, die – neben zwei skalaren Attributen – den Objekttyp adresse verwendet:
CREATE TABLE
id
name
anschrift
/
firma(
INTEGER CONSTRAINT pk_firma PRIMARY KEY,
VARCHAR2(100),
adresse)
Listing 3.8: Anlegen Tabelle
INSERT-Operationen für diese Tabelle müssen den – mit dem Objekttyp automatisch vordefinierten – Konstruktor adresse verwenden:
INSERT INTO firma
VALUES (1,'DATABASE Consult',
adresse(NULL,NULL,'83676','Jachenau',NULL)
);
Für den Zugriff auf einzelne skalare Attribute des Objekttyps adresse ist der Attributname der implementierenden Tabelle und der des Objekttyps anzugeben. Im
folgenden Beispiel werden entsprechend nur der Firmenname und Ort selektiert:
SELECT f.name, f.anschrift.ort FROM firma f;
Die – für relationale Begriffe – ungewohnte Syntax lässt sich nötigenfalls durch
eine View kaschieren:
CREATE VIEW firma_view AS
SELECT f.name name , f.anschrift.ort ort FROM firma f;
SELECT name, ort FROM firma_view;
Listing 3.9: Benutzung von Views
Physische Implementierung von Entitäten
145
Spaltenobjekte liegen auch dann vor, wenn Objekttypen in anderen Objekttypen
verwendet werden. Der Objekttyp person verfährt so:
CREATE OR REPLACE TYPE person AS OBJECT(
vorname VARCHAR2(30),
nachname VARCHAR2(30),
anschrift adresse)
/
Listing 3.10: Anlegen von Objekttyp person
Objekttabellen und Zeilenobjekte
Objekttypen, die vollständige Zeilen in Objekttabellen definieren, werden als Zeilenobjekte bezeichnet. Zeilenobjekte lassen sich aus Objekttypen heraus referenzieren. Im folgenden Beispiel wird die Objekttabelle personen auf der Basis des
Objekttyps person angelegt:
CREATE TABLE personen OF PERSON
OBJECT IDENTIFIER IS SYSTEM GENERATED;
Die in Objekttabellen gespeicherten Objekte werden über Objektbezeichner (object
identifier) referenziert. Standardmäßig legt Oracle – wie in diesem Beispiel
( ...IDENTIFIER IS SYSTEM GENERATED...) – eine interne OID-Spalte in der Länge
von 16 Byte für die interne Referenzierung der Objekte an. Alternativ lässt sich
auch der Primärschlüssel einer Tabelle hierzu einsetzen (…IDENTIFIER IS PRIMARY
KEY...), der jedoch nicht – wie der generierte Bezeichner – eine globale Eindeutigkeit
garantiert und ggf. auch mehr Speicherplatz benötigt.
Auch im Falle der Objekttabellen werden die Attributwerte über Konstruktoren eingefügt:
INSERT INTO personen
VALUES ('Otto','Müller',
adresse(NULL,NULL,'PLZ','Ort',NULL)
);
Zeilenobjekte lassen sich über ihre Objektbezeichner referenzieren. Im nächsten
Beispiel wird zunächst der Objekttyp auftrag angelegt, dessen Attribut auftraggeber den Objekttyp person referenziert:
CREATE OR REPLACE TYPE auftrag AS OBJECT
(
aufnr INTEGER,
datum DATE,
auftraggeber REF person ,
kommentar VARCHAR2(500)
)
/
Listing 3.11: Anlegen von Objekttyp auftrag
146
Datenbankdesign
Im zweiten Schritt wird nun die Objekttabelle auftraege auf der Basis des Typs
auftrag angelegt:
CREATE TABLE auftraege OF auftrag;
Damit das Attribut auftraggeber der Objekttabelle auftraege nicht alle möglichen Tabellenimplementierungen des Objekttyps person referenziert, wird der
Gültigkeitsbereich dieses Attributes in der Tabelle auftraege im folgenden Beispiel
auf die Objekte der Objekttabelle personen beschränkt. Mit anderen Worten: Das
Attribut auftraggeber der Tabelle auftraege referenziert nur Datensätze aus der
Tabelle personen:
ALTER TABLE auftraege ADD(SCOPE FOR (auftraggeber) IS personen);
Durch die Einschränkung des Gültigkeitsbereiches über die SCOPE-Klausel wird
auch die interne Speicherung des betreffenden Attributes optimiert! Einschränkungen sollten daher nach Möglichkeit durchgeführt werden.
Für das Einfügen von Personenreferenz in die neue Tabelle ist der REF-Operator
zuständig, der, wie das Beispiel zeigt, über einen eindeutigen (!) Zugriff auf die
Tabelle personen den Objektbezeichner zuordnet:
INSERT INTO auftraege
SELECT 12345, SYSDATE, REF(p), 'Kommentar'
FROM personen p
WHERE p.nachname = 'Müller'
AND p.vorname = 'Otto';
Listing 3.12: Einfügen mit REF
Beim Selektieren der Auftragsdaten ist die Syntax – dank der Möglichkeit der impliziten Dereferenzierung – einfacher. Für diese Dereferenzierung muss auf jeden Fall
ein Aliasname angegeben werden:
SELECT a.aufnr, a.auftraggeber.nachname, a.auftraggeber.vorname
FROM auftraege a
/
Kollektionen
Kollektionen lassen sich als Behältnis für mehrere gleichartige Objekte begreifen.
Es stehen zwei unterschiedliche Typen von Kollektionen bereit:
:
:
Varrays – sind geordnete Kollektionen von Elementen gleichen Datentyps, deren
maximale Anzahl bei der Definition festgelegt wird und nicht überschritten werden kann. Einzelne Elemente von Varray-Kollektionen können darüber hinaus
nicht beliebig gelöscht und eingefügt werden. Varrays können als Datentypen
für Attribute relationaler Tabellen oder für Attribute von Objekttypen verwendet werden.
Nested Tables – sind ungeordnete Kollektionen von Elementen gleichen Datentyps, die nicht in ihrer Maximalzahl festgelegt werden und aus nur einem Attri-
Physische Implementierung von Entitäten
147
but bestehen. Das Attribut einer geschachtelten Tabelle ist entweder ein skalarer
Datentyp oder ein Objekttyp. Geschachtelte Tabellen werden – wie auch Varrays – als Datentypen für Attribute relationaler Tabellen oder für Attribute von
Objekttypen verwendet.
Im folgenden Beispiel wird das Varray telnummern angelegt, das die Speicherung
von maximal zehn Telefonnummern pro Objektinstanz gestattet:
CREATE OR REPLACE TYPE telnummern AS VARRAY(10) OF VARCHAR2(20)
/
Dieses Varray kann nun in dem Objekttyp pers_tel verwendet werden:
CREATE OR REPLACE TYPE pers_tel AS OBJECT (
personenangabe person,
telefon
telnummern)
/
Listing 3.13: Objekttyp mit VARRAY
pers_tel kann dann – wie oben beschrieben – weiterverwendet werden. Zum Ein-
fügen von Varray-Attributen stehen ebenfalls vordefinierte Konstruktoren bereit:
in diesem Fall z.B. telnummern('nr1', 'nr2').
Im nächsten Beispiel wird der Objekttyp auftragsposition angelegt, der dann im
Folgenden für die geschachtelte Tabelle auftragspositionen benutzt wird.
CREATE OR REPLACE TYPE auftragsposition AS OBJECT
(posnr INTEGER, anzahl INTEGER, prodid VARCHAR2(20));
CREATE OR REPLACE TYPE auftragspositionen
AS TABLE OF auftragsposition
/
Listing 3.14: Objekttyp mit geschachtelter Tabelle
Der tabellarische Objekttyp auftragspositionen kann nun in einen weiteren
Objekttyp auftraege_pos integriert werden:
CREATE OR REPLACE TYPE auftraege_pos AS OBJECT
(auftragkopf auftrag,
pos_tab
auftragspositionen);
Schließlich wird dieser Objekttyp dazu benutzt, die endgültige Objekttabelle
auftraege_positionen mit der geschachtelten Tabelle pos_tab_table anzulegen:
CREATE TABLE auftraege_positionen OF auftraege_pos
NESTED TABLE pos_tab STORE AS pos_tab_table (
(PRIMARY KEY(NESTED_TABLE_ID,posnr))
ORGANIZATION INDEX COMPRESS)
RETURN AS LOCATOR;
Listing 3.15: Anlegen einer geschachtelten Tabelle
148
Datenbankdesign
Die Koppelung der Haupttabelle mit der geschachtelten Tabelle wird über die
interne Spalte nested_table_id gewährleistet. Für den Fall, dass die geschachtelte
Tabelle – wie in diesem Fall – einen Primärschlüssel enthält, kann sie als IOTTabelle angelegt und der Index-Baum zusätzlich komprimiert werden. Die Speicherung als Index hat die Sortierung der Daten (clustering) mit der nested_table_id als
Präfix zur Folge. Durch die Komprimierung wird die Wiederholung gleicher Werte
von nested_table_id unterdrückt und die Tabelle dadurch kompakter. Statt der
gesamten Daten wird ferner nur ein Locator für den Zugriff zurückgegeben, was
besonders bei geschachtelten Tabellen mit vielen Elementen empfehlenswert ist.
Das Füllen der Objekttabelle auftraege_positionen geschieht in zwei Phasen:
Zunächst wird ein Auftragskopfsatz über Konstruktoren mit einer leeren Referenz
in die geschachtelte Tabelle eingefügt:
INSERT INTO auftraege_positionen
VALUES(auftrag(123,SYSDATE,NULL,NULL),auftragspositionen());
In der zweiten Phase wird mit Hilfe des TABLE-Ausdrucks die geschachtelte Tabelle
für den Auftrag 123 mit einem Element gefüllt:
INSERT INTO
TABLE(SELECT a.pos_tab FROM auftraege_positionen a
WHERE a.auftragkopf.aufnr = 123)
VALUES(1,3,222)
/
Listing 3.16: Einfügen von Sätzen in eine geschachtelte Tabelle
3.3
Indexstrukturen
Indizes sind für relationale Datenbanksysteme aus verschiedenen Gründen unerlässlich: Auf der einen Seite helfen sie – richtig aufgebaut – bei der Optimierung
von Zugriffen, auf der anderen Seite können sie – als eindeutige Indizes – eindeutige Wertemengen garantieren. Für den Datenbankverwalter wie auch den Applikationsentwickler gruppieren sich unterschiedliche Aufgabenbereiche um die Welt
der Indizes:
1. Im Zuge der Datenmodellierung gilt es, geeignete Attribute für die Indizierung
auszusuchen.
2. Wird das physikalische Modell aufgesetzt, müssen den Indexkandidaten konkrete Indizes bzw. Indextypen zugewiesen werden.
3. Die Indizes des physikalischen Modells werden dann mit geeigneten StorageKlauseln angelegt.
4. Im laufenden Betrieb schließlich müssen die Indizes überwacht und bei Bedarf
reorganisiert werden. Gleichfalls kann es notwendig sein, konkrete Zugriffspläne einzelner SQL-Befehle zu generieren, hinsichtlich ihrer Indexnutzung zu
analysieren und – falls notwendig – die Indexstrukturen zu optimieren.