Oracle: Table

Zurück zu " ORACLE Datenbank-Objekte " | Hoch zu " Inhaltsverzeichnis " | Vor zu " View "

Tabellendefinition

Es 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	EMAIL	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	EMAIL	JOB

. . .

Check-Constraints

Bereits 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

ALTER TABLE reltable ADD CONSTRAINT reltable_pk PRIMARY KEY (id);

ALTER TABLE reltable ADD PRIMARY KEY (id) DISABLE;

Fremdschlüssel

ALTER TABLE reltable
ADD CONSTRAINT fk_reltable2
  FOREIGN KEY (reltable2_id)
  REFERENCES reltable2(id);

Einfügereihenfolge ermitteln

Wenn 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

Partitionierung 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

Beispiel

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

Beispiel

CREATE TABLE t_hash 
( t1      VARCHAR2(10) NOT NULL,
  t2      NUMBER       NOT NULL,
  t3      NUMBER  
PARTITION BY HASH (t2)
PARTITIONS 4
;

List-Partitionierung

Beispiel

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

Intervall-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

...

externe Tabellen

...

LOBs

...

Datadictionary-Views für Tabellen

In 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

Wenn 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;

Zurück zu " ORACLE Datenbank-Objekte " | Hoch zu " Inhaltsverzeichnis " | Vor zu " View "