Data Definition Language (DDL)

CREATE TABLE

Sie können eine Tabelle auf unterschiedliche Art und Weise erstellen. Als erstes schauen wir uns die konservative Methode mit der CREATE TABLE Anweisung an.

create table tablename 
  (spalte 1 Datentyp, Spalte 2 Datentyp ... Spalte n Datentyp)
;

Folgende Regeln für die Namensvergabe sind zu beachten:

• Das erste Zeichen muss ein Buchstabe sein.
• Die Länge kann nicht 30 Zeichen überschreiten.
• Verwendbar sind die Zeichen A-Z, a-z, 0-9, _, $ und #
• Im gleichen Schema kann ein Name nur einmal vergeben werden.
• Ein Oracle-reserviertes Wort darf nicht verwendet werden.

create table produkt (Produktnummer number(10), Produktname char(30));

Es wird eine Tabelle namens Produkt erzeugt, welche aus zwei Spalten besteht.

Wichtige Datentypen:

Number    (Gesamtstellen,Nachkommastellen)
Varchar2  (Zeichenzahl)
Date

Eine Übersicht aller Datentypen befindet sich in [13].

Erstellen von Tabellen auf Basis einer Abfrage

Oftmals ist es notwendig, eine neue Tabelle zu erstellen, die auf Daten einer oder mehrerer bereits bestehender Tabellen aufbaut. Hierfür können Sie die neue Tabelle auf Basis einer Unterabfrage erstellen:

create table tablename as Unterabfrage;

create table emp_from_dept_10 as 
  select empno,ename from emp where deptno = 10
;

Es wird eine neue Tabelle erstellt, welche genau die Datensätze enthält, die der Abfrage entsprechen. Die Datentypen richten sich nach den Datentypen der Spalten in der oder den Originaltabellen.

select * from emp_from_dept_10
EMPNO  ENAME
---------------
 7782  CLARK
 7839  KING
 7934  MILLER

Es ist ebenfalls möglich, eine neue Tabelle auf Grundlagen einer Abfrage über mehrere Tabellen zu erstellen.

create table emp_from_dept_10 as 
  select e.empno, e.ename, d.dname from emp e, dept d 
    where e.deptno = d.deptno and d.deptno = 10
;

Es wird eine neue Tabelle, welche genau die Datensätze enthält, die der Abfrage entsprechen. Zusätzlich wird noch der Name der Abteilung angezeigt.

select * from emp_from_dept_10
EMPNO  ENAME    DNAME
---------------------------
 7782  CLARK    ACCOUNTING
 7839  KING     ACCOUNTING
 7934  MILLER   ACCOUNTING

DROP TABLE

DROP TABLE tabellenname;

Alle Daten und die Struktur der Tabelle werden gelöscht. Alle offenden Transaktionen werden committet (automatisches COMMIT). Alle zugehörigen Indizes werden gelöscht. Alle auf dieser Tabelle basierenden VIEWs und SYNONYMs werden nicht gelöscht. DROP kann nicht per ROLLBACK rückgängig gemacht werden.

TRUNCATE

Mit der Anweisung TRUNCATE können Sie alle Datensätze einer Tabelle sehr schnell löschen. Allerdings können diese Datensätze dann mit einem ROLLBACK nicht mehr zurückgerollt werden.

truncate table tabellenname

truncate table emp;

Alle Datensätze der Tabelle Mitarbeiter (emp) werden gelöscht.

ALTER TABLE

Mit der Anweisung ALTER TABLE ist es möglich, eine bestehende Tabellenstruktur zu verändern. Sie können bestehende Spalten verändern, neue Spalten hinzufügen und Spalten bei Bedarf auch löschen.

alter table emp add birthdate date;
DESC emp;
Name      Null?     Typ
----------------------------------------- 
EMPNO     NOT NULL  NUMBER(4)
ENAME               VARCHAR2(10)
JOB                 VARCHAR2(9)
MGR                 NUMBER(4)
HIREDATE            DATE
SAL                 NUMBER(7,2)
COMM                NUMBER(7,2)
DEPTNO              NUMBER(2)
BIRTHDATE           DATE

Es wird zur Tabelle emp eine neue Spalte namens birthdate und dem Datentyp date hinzugefügt.

alter table emp drop column birthdate;

Es wird die Spalte birthdate einschließlich der Inhalte gelöscht.

DESC emp;
Name       Null?     Typ
------------------------------------ 
EMPNO      NOT NULL  NUMBER(4)
ENAME                VARCHAR2(10)
JOB                  VARCHAR2(9)
MGR                  NUMBER(4)
HIREDATE             DATE
SAL                  NUMBER(7,2)
COMM                 NUMBER(7,2)
DEPTNO               NUMBER(2)

ALTER TABLE emp MODIFY job NOT NULL;
DESC emp;
Name       Null?     Typ
---------------------------------- 
EMPNO      NOT NULL  NUMBER(4)
ENAME                VARCHAR2(10)
JOB        NOT NULL  VARCHAR2(9)
MGR                  NUMBER(4)
HIREDATE             DATE
SAL                  NUMBER(7,2)
COMM                 NUMBER(7,2)
DEPTNO               NUMBER(2)

Die Spalte job wird so verändert, dass NULL-Werte in dieser Spalte nicht mehr erlaubt sind.

alter table emp modify job varchar2(20);
DESC emp;
Name        Null?    Typ
----------------------------------------- 
EMPNO       NOT NULL NUMBER(4)
ENAME                VARCHAR2(10)
JOB                  VARCHAR2(20)
MGR                  NUMBER(4)
HIREDATE             DATE
SAL                  NUMBER(7,2)
COMM                 NUMBER(7,2)
DEPTNO               NUMBER(2)

Es wird der Datentyp (genauer der Wertebereich) der Spalte job verändert

Zusammenfassung:

• Die Größe einer Spalte kann immer vergrößert werden.
• Eine NULL-Spalte kann immer hinzugefügt werden.
• Eine Spalte, die noch keine Werte hat, kann verkleinert werden und der Datentyp kann ebenfalls verändert werden.
• Eine NOT NULL Spalte kann hinzugefügt werden, wenn die gesamte Tabelle leer ist.
• Besitzt eine Spalte in jedem Datensatz einen Wert, kann diese Spalte in eine NOT NULL Spalte umgewandelt werden.

CREATE VIEW

Eine Sicht ist genau genommen nichts weiter als eine gespeicherte Abfrage. Diese gespeicherte Abfrage wird wie eine Tabelle behandelt. Sie kann genau wie eine Tabelle abgefragt werden. Selbst Einfüge-, Änderungs- oder Löschoperationen sind unter bestimmten Umständen möglich. Dies hängt von der gespeicherten Abfrage ab.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias] ...)]
  AS subquery
  WITH CHECK OPTION [CONSTRAINT constraint]]
  WITH READ ONLY;

OR REPLACE - Erzeugt die View neu wenn diese bereits existiert.
FORCE - Erzeugt die View, auch wenn die Tabelle nicht existiert.
WITH CHECK OPTION - Nur die Datensätze, die in der View angezeigt werden, können verändert werden.
WITH READ ONLY - Kein DML-Befehl kann auf diese View angewendet werden.

Der eigentliche Nutzen einer Sicht ist das Verstecken einer recht komplizierten und umfangreichen Abfrage. So ist es einfacher, alle Datensätze der Sicht research_mitarbeiter abzufragen als sich die Abfrage, welche alle Research-Mitarbeiter ermittelt, zu schreiben und anschließend auszuführen.

Ein weiterer Vorteil einer Sicht ist die Möglichkeit, Berechtigungen auf Sichtebene anzugeben. Es ist durch Sichten demnach möglich, z.B. dem Datenbankbenutzer anna die Berechtigung zu geben, sich alle Datensätze der Mitarbeiter (emp) anzeigen zu lassen, außer die Datensätze der Mitarbeiter der Research-Abteilung. Hierfür brauchen Sie nur eine kleine Sicht, die alle Mitarbeiter mit Ausnahmen der Research-Mitarbeiter auswählt. Nun geben Sie Anna die notwendigen Berechtigungen auf die Sicht.

create view Research_Mitarbeiter as 
  select * from emp 
    where deptno=
     (select deptno from dept where dname = 'RESEARCH')
;

Es wird ein View erstellt, der alle Spalten der Tabelle Mitarbeiter (emp) selektiert, die aus der Abteilung RESEARCH kommen.

Ändern einer View

Das Ändern einer View erfolgt mit der Option OR REPLACE:

create or replace view Research_Mitarbeiter as 
  select * from emp 
    where deptno=
     (select deptno from dept where dname = 'SALES')
;

DROP VIEW

drop view Research_Mitarbeiter;

CREATE [PUBLIC] SYNONYM

Ein SYNONYM ist ein alternativer Name für eine Tabelle, einen VIEW oder gar für ein anderes SYNONYM. Synonyme duplizieren - genauso wie Views - keine Daten, sondern stellen nur eine zusätzliche Form bereit, um dieselben Daten darzustellen.

Ein PUBLIC-SYNONYM (öffentliches Synonym) kann von jedem benutzt werden.

create public synonym d for scott.dept;

DROP SYNONYM

drop synonym d;

CREATE SEQUENCE

Oftmals ist es hilfreich, einen Zähler in einer Tabelle zu verwenden. Zuerst muss die Sequence erzeugt werden. Danach kann auf die Sequence in der oder den Tabellen zugegriffen werden.

Mit der Pseudocoloumn sequencename.nextval können Sie den nächsten Wert der Sequence erzeugen und in die Tabelle einfügen. Nehmen wir an, Sie haben folgende leere Tabelle erzeugt:

create sequence s1 start with 1 maxvalue 5 increment by 1 nocycle;

Es wird eine Sequence namens s1 erzeugt. Der Startwert dieser Sequence ist 1 (start with 1) und der Maximalwert beträgt 5 (maxvalue=5). Erhöht wird jeweils um 1 (increment by 1). Wird der Maximalwert erreicht, so wird nicht von vorn begonnen (nocycle).

insert into produkt(produktnummer,produktname) 
             values(s1.nextval,'Milch');
insert into produkt(produktnummer,produktname) 
             values(s1.nextval,'Butter');
insert into produkt(produktnummer,produktname) 
             values(s1.nextval,'Brot')
;
select * from produkt;
PRODUKTNUMMER  PRODUKTNAME
---------------------------
            1  Milch
            2  Butter
            3  Brot

Es wurden drei Produkte hinzugefügt und eine neue Produktnummer jeweils über s1.nextval erzeugt. Dies geht bei der oben erstellten Sequence s1 bis zur Zahl 5, danach muss beim Einfügen von Datensätzen wieder ein Wert eingegeben werden.

create sequence product_seq start with 1 increment by 1;
insert into t_emp values (product_seq.nexval, 'Joe Black');

Mit der Funktion sequencename.currval können Sie sich den aktuellen Wert der Sequence anschauen, nachdem mindestens einmal nextval angefordert wurde.

select s1.currval from dual;
CURRVAL
-------
      3

Es wird der aktuelle Wert der Sequence s1 ermittelt und zurückgegeben

Wichtige Optionen der Create Sequence Anweisung

increment by n		Wert, um den jeweils erhöht wird
		(Standard ist 1).
start with n		erste zu generierende Sequencenummer
maxvalue		Höchstwert der Sequence.
minvalue		Mindestwert der Sequence.
cycle sequence		fängt nach Erreichen des Maximalwertes von vorn an
		(ist nicht Standard).
nocycle sequence		fängt nach Erreichen des Maximalwertes nicht von vorn an.
		(Ist Standard - vergleiche mit Cycle)
cache n		Es werden n Sequencewerte im Speicher gehalten.
nocache		Es werden keine Sequencewerte im Speicher gehalten.

Informationen über Sequences mit Hilfe des Data Dictionary

select sequence_name,min_value,max_value,increment_by,last_number 
  from user_sequences
;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
----------------------------------------------------------
S1                    1         5            1           6

Mit dieser Sicht des Data Dictionary werden die Optionen der Sequences ermittelt. last_number ist der nächste verfügbare Sequencewert.

Löschen einer Sequence

drop sequence Sequencename ;

INDEX

Nutzen eines Index

Einen Index können Sie sich wie ein Telefonbuch vorstellen. Wenn Sie einen Namen suchen, schlagen Sie das Telefonbuch zuerst in etwa in der Mitte auf und vergleichen den Namen, den Sie dort vorfinden, mit dem von Ihnen gesuchten Namen. Ist der von Ihnen gesuchte Name größer (weiter hinten im Alphabet), so können Sie die vordere Hälfte getrost vergessen - denn der Name muss sich in der hinteren Hälfte befinden. Nun schlagen Sie die Hälfte der hinteren Hälfte auf u.s.w.

Haben Sie dann endlich die Seite gefunden, auf der sich der von Ihnen gesuchte Name befindet, lesen Sie die Seite von Anfang bis Ende - bis Sie den Namen gefunden haben. Nun können Sie beruhigt telefonieren. Wäre das Telefonbuch nicht alphabetisch sortiert, so müssten Sie das ganze Buch durchblättern, um die entsprechende Telefonnummer herauszubekommen.

Ein Index arbeitet ähnlich dem Telefonbuchprinzip. Zuerst wird die indizierte Spalte sortiert und anschließend als Baumstruktur gespeichert. Auf die unterschiedlichen Speicherformen von Indizes soll an dieser Stelle verzichtet werden.

CREATE INDEX

create index indexname on tabelle(spalte1, spalte2, ... spalte n);

create index id1 on emp(ename);

Es wird ein Index namens id1 erstellt, der sich über die Spalte ename der Tabelle emp erstreckt. Hierdurch wird das Suchen nach ename beschleunigt. Doppelt vorkommende ename sind erlaubt.

create unique index id1 on emp(ename);

Es wird ein Index namens id1 erstellt, der sich über die Spalte Ename der Tabelle emp erstreckt. Doppelt vorkommende ename sind nicht erlaubt.

Automatische INDEX-Erstellung

Bei den Constraints PRIMARY KEY und UNIQUE wird automatisch ein Index erstellt.

DROP INDEX

drop index indexname

drop index id1

Der Index namens id1 wird gelöscht.

Informationen über bestehende Indizes

Mit Hilfe bestimmter Data Dictionary Tables und Views erhalten Sie eine Übersicht über die bestehenden Indizes und die beteiligten Tabellen. In der Data Dictionary Sicht user_indexes sind alle Indizes und die Eindeutigkeit gespeichert. In der Data Dictionary Sicht user_ind_columns stehen die Tabellennamen und die entsprechenden beteiligten Spalten.

SELECT i1.index_name, i2.column_name 
  FROM user_indexes i1, user_ind_columns i2 
  WHERE i1.index_name = i2.index_name 
    AND i2.table_name = 'EMP'
;
Index_name  Column_name
------------------------
PK_EMP      EMPNO
ID1         ENAME

Es werden alle Indizes ermittelt, die sich auf die Tabelle emp beziehen. Ausgegeben wird der Indexname und die beteiligte Spalte bzw. die beteiligten Spalten

Richtlinien für das Erstellen eines Index

Indizes machen nur Sinn, wenn diese richtig und maßvoll eingesetzt werden. Ein Index dient primär dazu, die Suchoperationen zu beschleunigen. Allerdings muss ein Indexeintrag auch immer mitgepflegt werden. So viel Indizes wie nötig - nicht mehr!

Wann sollten Sie einen Index erstellen?

• Die Spalte wird häufig als Join-Kriterium benutzt.
• Die meisten Abfragen ermitteln als Ergebnismenge weniger als 5% der Gesamtmenge.
• Die Tabelle enthält einen sehr selektiven Wertebereich, dass heißt, es kommen verhältnismäßig wenig doppelte Spaltenwerte vor.

Wann sollten Sie keinen Index erstellen?

• Die Tabelle ist eher klein.
• Die Tabelle wird häufig aktualisiert.
• Die entsprechende Spalte wird selten in Suchfunktionen benutzt.
• Die Spalte ist nur wenig selektiv (z.B. Anrede o. ä.).