Oracle: Table
Tabellendefinition
BearbeitenEs gibt drei Arten von Tabellen:
- Relationale Tabellen
- Objekttabellen
- XML Tabellen
Die relationale Tabelle ist die Grundstruktur, um Daten zu halten. Sie ist in der Form von Spalten (Columns) und Zeilen (Rows) aufgebaut.
- Syntax
CREATE TABLE <schema.table>(
<relational properties>
);
- Beispielscript
CREATE TABLE reltable(
id NUMBER,
username VARCHAR2(50),
email VARCHAR2(50),
job VARCHAR2(50)
);
RELTABLE
ID | USERNAME | JOB | |
---|---|---|---|
1 | BBLOCKSBERG | bb@besen.hex | Hexe |
2 | BBLÜMCHEN | bb@z.oo | Dickhäuter |
Objekttabellen erweitern relationale Tabellen um die Möglichkeit der Nutzung von Objekttypen. Mindestens eine Spalte der Tabelle beinhaltet die Definition eines Objekttyps.
OBJTABLE
ID | USERINFO |
---|---|
1 | (Objectdata: BBLOCKSBERG bb@besen.hex Hexe) |
2 | (Objectdata: BBLÜMCHEN bb@z.oo Dickhäuter) |
OBJTYPE
USERNAME | JOB |
---|
. . .
Check-Constraints
BearbeitenBereits bei der Definition einer Tabelle, kann man festlegen, welche Werte oder Wertebereiche für einen Spaltenwert erlaubt oder verboten sind. Dadurch wird die Integrität der Datenbank erhöht, da nur Werte in die Datenbank eingepflegt werden können, die diesen Regeln entsprechen. Beispiel:
CREATE TABLE TEST (
T1 VARCHAR(10) NOT NULL,
T2 NUMBER(2) DEFAULT 10,
T3 NUMBER(3,2) CHECK T3 >=5
) TABLESPACE T;
NOT NULL | Bestimmt, dass der Spalte im Datensatz ein Wert zugeordnet werden muss. |
DEFAULT | Setzt einen Vorgabewert. |
CHECK | Ist eine Boolesche Regel, die wahr sein muss, damit der Datensatz akzeptiert wird. |
Primärschlüssel
BearbeitenALTER TABLE reltable ADD CONSTRAINT reltable_pk PRIMARY KEY (id);
ALTER TABLE reltable ADD PRIMARY KEY (id) DISABLE;
Fremdschlüssel
BearbeitenALTER TABLE reltable
ADD CONSTRAINT fk_reltable2
FOREIGN KEY (reltable2_id)
REFERENCES reltable2(id);
Einfügereihenfolge ermitteln
BearbeitenWenn viele Tabellen existieren, die mit Fremdschlüsseln miteinander verknüpft sind, dann kann man sich die Einfüge-Reihenfolge der Tabellen aus den Informationen im Datenbank-Distionary generieren lassen.
Beispiel für Oracle:
WITH rel AS
(
-- rel start
SELECT
child_c.table_name child
, parent_c.table_name parent
FROM user_constraints child_c, user_constraints parent_c
WHERE child_c.constraint_type = 'R'
AND child_c.r_constraint_name = parent_c.constraint_name
-- rel ende
)
, ebenen AS
(
-- ebenen start
SELECT level+1 ebene, child tabelle
FROM rel
CONNECT BY PRIOR child = parent
START WITH parent IN
(
SELECT parent
FROM rel
WHERE parent NOT IN (SELECT child FROM rel)
)
-- Ebene 1 hinzufügen
UNION
SELECT 1, parent
FROM rel
WHERE parent NOT IN (SELECT child FROM rel)
-- ebenen ende
)
-- Ermitteln und ausgeben der einzelnen Ebenen
SELECT MAX(ebene) ebene, tabelle
FROM ebenen
GROUP BY tabelle
Diese Query verwendet Informationen aus der Dictionary-View USER_CONSTRAINTS. Durch mehrere inline-Views und einem rekursivem SQL-Statement werden die gesuchten Informationen ermittelt. Die Formulierung 'CONNECT BY PRIOR' ist in Oracle eine Möglichkeit, rekursiv vorzugehen. Es werden durch 'START WITH parent IN' zuerst alle Tabellen gesucht, die keine eigenen Fremdschlüssel haben, die aber Detail-Tabellen haben (das ist die Ebene 1). Dann werden alle Tabellen gesucht, die Fremdschlüssel zu den bereits gefundenen Tabellen haben (das ist die Ebene 2). Dann werden alle neu gefundenen Tabellen untersucht, ob sie wiederum Detail-Tabellen haben. Genau das ist die Rekursion. Sie wird so lange weiter fortgesetzt, bis alle Detail-Tabellen gefunden wurden. (Ebenen 3, 4, 5, u.s.w) Die Pseudo-Spalte LEVEL gibt in Oracle bei rekursiven SQL-Statements an, wie viele Rekursions-Schritte bereits ausgeführt wurden.
Bei der oben beschriebenen Query werden Tabellen ohne Fremdschlüssel nicht ausgegeben. Falls es Ring-Verkettungen gibt, dann werden die daran beteiligten Tabellen auch nicht ausgegeben, da sie bei der 'START WITH' Klausel nicht gefunden werden. Wenn Ring-Verkettungen vorhanden sind, dann kann man diese mit der folgenden Query ausgeben. Sie verwendet auch wieder die Inline-Views REL und EBENEN und sucht schließlich nach Tabellen in USER_TABLES, die zwar in REL enthalten sind (die also Fremdschlüssel haben), die aber nicht in der View EBENEN enthalten sind. Falls eine Ring-Verkettung nur eine einzige Tabelle betrifft, also die Tabelle einen Fremdschlüssel hat, der auf die eigene Tabelle verweist, dann ist dafür noch ein weiterer Schritt erforderlich. Solche Tabellen werden gefunden mit dem Zugriff auf REL mit der Bedingung 'WHERE parent = child'.
WITH rel AS
(
-- rel start
SELECT
child_c.table_name child
, parent_c.table_name parent
FROM user_constraints child_c, user_constraints parent_c
WHERE child_c.constraint_type = 'R'
AND child_c.r_constraint_name = parent_c.constraint_name
-- rel ende
)
, ebenen AS
(
-- ebenen start
SELECT level+1 ebene, child tabelle
FROM rel
CONNECT BY PRIOR child = parent
START WITH parent IN
(
SELECT parent
FROM rel
WHERE parent NOT IN (SELECT child FROM rel)
)
-- Ebene 1 hinzufügen
UNION
SELECT 1, parent
FROM rel
WHERE parent NOT IN (SELECT child FROM rel)
-- ebenen ende
)
SELECT table_name tabelle
FROM user_tables
WHERE table_name NOT IN (SELECT tabelle FROM ebenen)
AND table_name IN (SELECT parent FROM rel)
-- Und noch alle Cycle mit nur einem Element hinzufügen
UNION
SELECT parent tabelle
FROM rel
WHERE parent = child
Die REL-View kann man auch dazu verwenden, um die dritte Gruppe von Tabellen zu finden, nämlich diejenigen, die mit Fremdschlüsseln nichts zu tun haben. Diese Tabellen haben keine eigenen Fremdschlüssel und werden auch von keiner anderen Tabelle als als Master-Tabelle referenziert. Dafür sucht man nach allen Tabellen in USER_TABLES, die weder als Parent, noch als Child in der REL-View vorkommen.
WITH rel AS
(
-- rel start
SELECT
child_c.table_name child
, parent_c.table_name parent
FROM user_constraints child_c, user_constraints parent_c
WHERE child_c.constraint_type = 'R'
AND child_c.r_constraint_name = parent_c.constraint_name
-- rel ende
)
SELECT table_name tabelle
FROM user_tables
WHERE table_name NOT IN (SELECT parent FROM rel
UNION SELECT child FROM rel)
Partitionierung
BearbeitenPartitionierung nennt man den Vorgang, eine große Tabelle zwecks Performanzoptimierung in kleinere Teiltabellen zu zerlegen.
Es gibt verschiedene Arten der Partitionierung:
- Range-Partitionierung
- Hash-Partitionierung
- List-Partitionierung
- Interval-Partitionierung
Ferner kann man Partitionen noch weiter in Sub-Partitionen unterteilen.
Range-Partitionierung
BearbeitenBeispiel
CREATE TABLE t_range
( t1 VARCHAR2(10) NOT NULL,
t2 NUMBER NOT NULL,
t3 NUMBER
)
PARTITION BY RANGE (t2)
( PARTITION part1 VALUES LESS THAN (1),
PARTITION part2 VALUES LESS THAN (11),
PARTITION part3 VALUES LESS THAN (MAXVALUE)
);
Eine Partition mit MAXVALUE muss nicht angegeben werden. Dann allerdings können keine Werte größer oder gleich 11 für die Spalte t2 eingefügt werden.
Die Partitionierung kann auch von mehreren Spalten abhängig gemacht werden:
CREATE TABLE t_range2
( jahr NUMBER NOT NULL,
monat NUMBER NOT NULL,
tag NUMBER NOT NULL,
zeitpunkt DATE NOT NULL,
umsatz NUMBER(14,2)
)
PARTITION BY RANGE (jahr, monat, tag)
( PARTITION p_2013 VALUES LESS THAN (2013, MAXVALUE, MAXVALUE),
PARTITION p_2014_01 VALUES LESS THAN (2014, 1, MAXVALUE),
PARTITION p_2014_02 VALUES LESS THAN (2014, 2, MAXVALUE),
PARTITION p_2014_03 VALUES LESS THAN (2014, 3, MAXVALUE),
PARTITION p_2014_04_01 VALUES LESS THAN (2014, 4, 2),
PARTITION p_2014_04_02 VALUES LESS THAN (2014, 4, 3),
PARTITION p_2014_04_03 VALUES LESS THAN (2014, 4, 4),
PARTITION p_2014_04_04 VALUES LESS THAN (2014, 4, 5),
PARTITION p_max VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE)
);
In einer Query kann man sich auf die einzelnen Partitionen beziehen, indem man entweder den Namen der Partition angibt, oder die Werte, die die Partitionierung beeinflussen. Beispiel:
select * from t_range2 partition( p_2013 );
select * from t_range2 partition for ( 2013, 12, 1 );
Hash-Partitionierung
BearbeitenBeispiel
CREATE TABLE t_hash
( t1 VARCHAR2(10) NOT NULL,
t2 NUMBER NOT NULL,
t3 NUMBER
PARTITION BY HASH (t2)
PARTITIONS 4
;
List-Partitionierung
BearbeitenBeispiel
CREATE TABLE t_list
( ort VARCHAR2(30) NOT NULL,
t2 NUMBER,
t3 NUMBER
)
PARTITION BY LIST(ort)
( PARTITION part_nord VALUES IN ('Hamburg','Berlin'),
PARTITION part_sued VALUES IN ('Muenchen', 'Nuernberg'),
PARTITION part_west VALUES IN ('Koeln','Duesseldorf'),
PARTITION part_ost VALUES IN ('Halle'),
PARTITION part_def VALUES (DEFAULT)
);
Wenn keine Default-Partition angelegt wird, dann können für die Spalte ort nur die angegebenen Werte eingetragen werden.
Interval-Partitionierung
BearbeitenIntervall-Partitionierung ist eine besondere Form der Range-Partitionierung, wobei die Spalte, von der die Partitionierung abhängt, den Datentyp DATE haben muss.
Beispiel
CREATE TABLE t_interval
( buchungs_datum DATE NOT NULL,
buchungs_text VARCHAR2(100),
betrag NUMBER(10,2)
)
PARTITION BY RANGE (buchungs_datum)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p_historie VALUES LESS THAN (TO_DATE('2014.01.01', 'YYYY.MM.DD')),
PARTITION p_2014_01 VALUES LESS THAN (TO_DATE('2014.02.01', 'YYYY.MM.DD')),
PARTITION p_2014_02 VALUES LESS THAN (TO_DATE('2014.03.01', 'YYYY.MM.DD'))
);
Bei dieser Tabelle werden initial die drei angegebenen Partitionen angelegt. Sobald in die Tabelle Sätze eingefügt werden, bei denen der Wert für buchungs_datum nicht in die bestehenden Partitionen passt, werden automatisch weitere Partitionen erzeugt. Dabei wird pro Monat eine neue Partition erzeugt.
Man kann die Bildung neuer Intervalle jederzeit ändern. Die Änderung beeinflusst nicht die bereits existierenden Partitionen. Nur wenn neue Partitionen nach der letzten existierenden Partition gebraucht werden, kommt die Intervall-Angabe zur Anwendung.
Beispiel zur Definition, dass neue Partitionen nur im Rhythmus von jeweils 2 Jahren angelegt werden sollen:
ALTER TABLE t_interval
SET INTERVAL (NUMTOYMINTERVAL(2,'YEAR'))
;
Beispiel zur Definition, dass neue Partitionen nur im Rhythmus von jeweils 5 Tagen angelegt werden sollen:
ALTER TABLE t_interval
SET INTERVAL (NUMTODSINTERVAL(5,'DAY'))
;
temporäre Tabellen
Bearbeiten...
externe Tabellen
Bearbeiten...
LOBs
Bearbeiten...
Datadictionary-Views für Tabellen
BearbeitenIn Oracle gibt es fast alle Dictionary-Views in dreifacher Ausführung:
- Views mit dem Präfix USER_ zeigen die eigenen Objekte an, also die Objekte, die im eigenen Schema erstellt sind.
- Views mit dem Präfix ALL_ zeigen alle Objekte an, für die man eine Zugriffsberechtigung hat. Das sind die Objekte im eigenen Schema und auch Objekte in anderen Schemata, für die man durch den GRANT-Befehl eine Zugriffsberechtigung erhalten hat.
- Auf Views mit dem Präfix DBA_ kann man nur zugreifen, wenn man das Administrations-Recht hat. In dieser View werden alle Objekte der gesamten Datenbank angezeigt, also auch die, auf die man keine Zugriffsrechte besitzt.
Alle Oracle Dictionary-Views sind in dem Manual Reference (nicht: SQL-Reference) beschrieben.
Einige Beispiele
Alle eigenen Tabellen einer Datenbank anzeigen:
SELECT TABLE_NAME FROM USER_TABLES
Alle Tabellen anzeigen, auf die man zugriffsberechtigt ist:
SELECT TABLE_NAME FROM ALL_TABLES
Alle Tabellen, die es in der gesamten Datenbank gibt, anzeigen:
SELECT TABLE_NAME FROM DBA_TABLES
Alle Spaltennamen anzeigen mit den dazugehörigen Datentypen einer Tabelle:
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,
DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'Tabellenname'
ORDER BY COLUMN_ID
Seit der Version 11 gibt es zusätzlich die View ALL_TAB_COLS. Der Unterschied zu ALL_TAB_COLUMNS besteht darin, dass versteckte Spalten in dieser View nicht ausgefiltert werden:
SELECT * FROM ALL_TAB_COLS
Alle Fremdschlüssel-Beziehungen und anderen Constraints anzeigen. Fremdschlüssel-Beziehungen haben den Typ 'R'. Bei DELETE_RULE = 'CASCADE' handelt es sich um eine Beziehung mit Löschweitergabe und bei 'NO ACTION' um eine Beziehung mit Lösch-Restriktion.
SELECT CONSTRAINT_NAME, TABLE_NAME, R_CONSTRAINT_NAME REFERENCED_TABLE, DELETE_RULE
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R'
Tabellen-Kommentare werden in einer eigenen View bereitgestellt:
SELECT TABLE_NAME, TABLE_TYPE, COMMENT
FROM ALL_TAB_COMMENTS
Ab der Version 11g gibt es eine eigene View zur Ausgabe von Statistik-Daten:
SELECT * FROM ALL_TAB_STATISTICS
Zusätzliche Informationen über partitionierte Tabellen. Jede partitionierte Tabelle hat hier einen Eintrag:
SELECT * FROM ALL_PART_TABLES
Informationen über Partitionen und Sub-Partitionen. Jede Partition bzw. Sub-Partition hat hier eine eigene Zeile:
SELECT * FROM ALL_TAB_PARTITIONS
SELECT * FROM ALL_TAB_SUBPARTITIONS
Tabellen verkleinern
BearbeitenWenn man Sätze aus einer Tabelle löscht, dann wird der Speicherplatz im Tablespace trotzdem nicht freigegeben. Alle Datenblöcke, die einmal für die Speicherung von Daten für eine bestimmte Tabelle verwendet wurden, bleiben dieser Tabelle zugeordnet und können nicht zur Speicherung von Daten in anderen Tabellen benutzt werden. Wenn man eine Tabelle nur vorübergehend mit einem großen Datenvolumen gefüllt wurde und der Speicherplatz danach für andere Tabellen zur Verfügung gestellt werden soll, dann gibt es verschiedene Möglichkeiten:
- ALTER TABLE MOVE TABLESPACE
- EXPORT / drop Table / create Table / IMPORT
- ALTER TABLE SHRINK SPACE
Die Varianten 1 und 2 haben den Nachteil, dass es eine Ausfallzeit gibt, in der andere Programme, die auf diese Tabelle zugreifen wollen, eine Fehlermeldung bekommen.
Die Variante 3 gibt es seit der Version 10g. Sie hat den Vorteil, dass die Tabelle nur durch einen Lock gesperrt wird. Andere Programme müssen so lange warten, bis die Verkleinerung fertig ist, sie bekommen aber keine Fehlermeldung.
Der Befehl ALTER TABLE SHRINK SPACE ist an bestimmte Bedingungen geknüpft:
Verkleinern kann man damit
- Tabellen
- Indizes
- indexorganisierte Tabellen
- Partitionen
- Subpartitionen
- LOB Segmente (ab Version 10.2)
- Materialized Views
Voraussetzungen
- Oracle RDBMS ab Version 10g.
- Der Tablespace muss mit der Option SEGMENT MANAGEMENT AUTO angelegt sein.
- Bei der Tabelle, die verkleinert werden soll, muss ROW MOVEMENT aktiviert sein.
Einschränkungen
- Die Tabelle darf nicht komprimiert sein.
- Die Tabelle darf keine FUNCTION BASED Indizes besitzen.
- Die Mastertabelle einer ON COMMIT MATERIALIZED VIEW kann nicht verkleinert werden.
- ROWID MATERIALIZED VIEWS müssen nach dem Verkleinern neu aufgebaut werden.
- Tabelle darf keine LOB oder LONG Spalten besitzen (nur in Version 10.1).
- Der Befehl SHRINK SPACE ist eine Art der Reorganisation. Dadurch ändern sich die ROWIDs.
alter table test ENABLE ROW MOVEMENT;
alter table test SHRINK SPACE;