Erzeugung von Sichten, Sequenzen und Indizees

Datenbank - Objekte
Views erzeugen
Objekt
Objekt Beschreibung
Tabelle
Basiseinheit zum Speichern; besteht aus
View
Zeilen und Spalten;
Logische Repräsentation; kann Teilmengen
von einer oder mehreren Tabellen enthalten
Sequence
Index
Synonym
Copyright © Oracle Corporation, 1998. All rights reserved.
12-2
Was ist eine View?
Generiert Primärschlüsselwerte
Erhöht die Performanz von Anfragen
Erstellt alternative Namen für Objekte
Copyright © Oracle Corporation, 1998. All rights reserved.
Wozu braucht man Views?
EMP Table
EMPNO ENAME
JOB
MGR HIREDATE
SAL
COMM
DEPTNO
----- ------- --------- ----- --------- ----- ----- -------
12-3
7839 KING
PRESIDENT
17-NOV-81
5000
10
7698 BLAKE
7782 CLARK
MANAGER
7839 01-MAY-81
2850
30
MANAGER
7839 09-JUN-81
2450
10
7566 JONES
MANAGER
7839 02-APR-81
2975
7654 MARTIN
SALESMAN
7698 28-SEP-81
1250
1400
20
30
7499 ALLEN
SALESMAN
7698 20-FEB-81
1600
300
30
7844 TURNER
SALESMAN
7698 08-SEP-81
1500
0
30
7900 JAMES
CLERK
7698 03-DEC-81
950
7521 WARD
SALESMAN
7698 22-FEB-81
1250
7902 FORD
ANALYST
7566 03-DEC-81
3000
20
7369 SMITH
CLERK
7902 17-DEC-80
800
20
7788 SCOTT
ANALYST
7566 09-DEC-82
3000
20
7876 ADAMS
CLERK
7788 12-JAN-83
1100
20
7934 MILLER
CLERK
7782 23-JAN-82
1300
10
Copyright © Oracle Corporation, 1998. All rights reserved.
30
500
30
• Für Zugriffsbeschränkungen auf die
Datenbank
• Um komplexe Anfragen einfacher zu
machen
• Um unterschiedliche Sichten auf die
gleichen Daten zu ermöglichen
12-4
Copyright © Oracle Corporation, 1998. All rights reserved.
Einfache Views
und Komplexe Views
Erzeugung einer View
• Eine Unterabfrage wird in die Definition des
CREATE VIEW Statements eingebettet.
Einfache Views Komplexe Views
Anzahl Tabellen
eine
eine oder mehrere
Mit Funktionen
Nein
Ja
Mit Datengruppen
Nein
Ja
DML durch View
Ja
Nein
12-5
Copyright © Oracle Corporation, 1998. All rights reserved.
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
• Die Unterabfrage kann ein komplexes SELECTStatement sein.
• Die Unterabfrage darf kein ORDER BY enthalten.
12-6
Copyright © Oracle Corporation, 1998. All rights reserved.
Beispiel
Beispiel-View
• Erzeuge eine View EMPVU10, die Details
von Angstellten der Abteilung 10 enthält.
• Gebrauch von ALIAS-Namen in der
Unterabfrage
SQL>
2
3
4
View
SQL>
2
3
4
5
View
CREATE VIEW
AS SELECT
FROM
WHERE
created.
empvu10
empno, ename, job
emp
deptno = 10;
• Beschreiben der Struktur der View mit
dem SQL*Plus Kommando DESCRIBE
CREATE VIEW
AS SELECT
FROM
WHERE
created.
salvu30
empno EMPLOYEE_NUMBER, ename NAME,
sal SALARY
emp
deptno = 30;
• Die Spalten werden mit den ALIASNamen versehen
SQL> DESCRIBE empvu10
12-7
Copyright © Oracle Corporation, 1998. All rights reserved.
12-8
Copyright © Oracle Corporation, 1998. All rights reserved.
Daten in der View
SQL>
2
SELECT *
FROM
salvu30;
EMPLOYEE_NUMBER
--------------7698
7654
7499
7844
7900
7521
NAME
SALARY
---------- --------BLAKE
2850
MARTIN
1250
ALLEN
1600
TURNER
1500
JAMES
950
WARD
1250
6 rows selected.
12-9
Anfragen mit der View
Copyright © Oracle Corporation, 1998. All rights reserved.
SQL*Plus
7839
7782
7934
12-10
SQL>
2
3
4
5
View
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT
empno, ename, job
FROM
emp
WHERE
deptno = 10;
created.
• Spaltenaliases in der CREATE VIEW
Klausel werden in derselben Anordnung
angegeben wie die Spalten in der Unterabfrage.
12-11
Copyright © Oracle Corporation, 1998. All rights reserved.
EMPVU10
SELECT
FROM
WHERE
KING PRESIDENT
CLARK MANAGER
MILLER CLERK
empno, ename, job
emp
deptno = 10;
EMP
Copyright © Oracle Corporation, 1998. All rights reserved.
Änderung einer View
• Ändere die View EMPVU10 mit der
CREATE OR REPLACE VIEW Klausel.
Erzeuge ein ALIAS für jeden Spaltennamen.
USER_VIEWS
SELECT *
FROM
empvu10;
Komplexe View
Erzeugen einer komplexen View, die
Gruppenfunktionen enthält und Werte aus
zwei Tabellen.
SQL>
2
3
4
5
6
7
View
12-12
CREATE VIEW
AS SELECT
FROM
WHERE
GROUP BY
created.
dept_sum_vu
(name, minsal, maxsal, avgsal)
d.dname, MIN(e.sal), MAX(e.sal),
AVG(e.sal)
emp e, dept d
e.deptno = d.deptno
d.dname;
Copyright © Oracle Corporation, 1998. All rights reserved.
Regeln zur Ausführung von
DML Operationen in einer View
• DML Operationen können in einfachen
Views durchgeführt werden.
• Ein Tupel kann in einer View nicht
verändert werden wenn sie folgendes
hat:
• Eine View kann nicht geändert werden, wenn sie
folgendes enthält:
– Eine der Bedingungen aus der vorherigen Folie
– Spalten, die durch Ausdrücke definiert werden
– Die ROWNUM Pseudosplte
• Daten können nicht zugefügt werden wenn:
– Gruppenfunktionen
– Die View eine der Bedingungen aus der
– Eine GROUP BY Klausel
vorherigen Folie enthält
– NOT NULL Spalten in der Basistabelle sind, die
nicht von der View ausgewählt werden.
– Das DISTINCT Schlüsselwort
12-13
Copyright © Oracle Corporation, 1998. All rights reserved.
Verwendung der WITH CHECK
OPTION Klausel
SQL>
2
3
4
5
View
CREATE OR REPLACE VIEW empvu20
AS SELECT
*
FROM
emp
WHERE
deptno = 20
WITH CHECK OPTION;
created.
• WITH CHECK OPTION garantiert, dass
Einfügungen und Änderungen nur in Tupeln
durchgeführt werden, die durch die View-Query
selektiert werden können .
12-15
Regeln zur Ausführung von
DML Operationen in einer View
Copyright © Oracle Corporation, 1998. All rights reserved.
12-14
Copyright © Oracle Corporation, 1998. All rights reserved.
Verweigern von DML Operationen
• Mit der Option WITH READ ONLY kann
man sichern, dass keine DML Operationen auf der VIEW durchgeführt
werden.
SQL>
2
3
4
5
6
View
12-16
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT
empno, ename, job
FROM
emp
WHERE
deptno = 10
WITH READ ONLY;
created.
Copyright © Oracle Corporation, 1998. All rights reserved.
View löschen
Wird eine View gelöscht, so gehen keine
Daten verloren.
Sequenzen, Indizees,
Synonyme
DROP VIEW view;
SQL> DROP VIEW empvu10;
View dropped.
12-17
Copyright © Oracle Corporation, 1998. All rights reserved.
Copyright © Oracle Corporation, 1998. All rights reserved.
Datenbank - Objekte
Objekt
Tabelle
Beschreibung
Basiseinheit zum Speichern; besteht aus
Was ist eine Sequenz ?
• Automatisch generierte Abfolge von
Zahlen
Zeilen und Spalten;
View
Logische Repräsentation; kann Teilmengen
von einer oder mehreren Tabellen enthalten
Sequenz
Generiert Primärschlüsselwerte
Index
Erhöht die Performanz von Anfragen
Synonym
Erstellt alternative Namen für Objekte
12-19
Copyright © Oracle Corporation, 1998. All rights reserved.
• Ist ein Objekt, das von mehreren
anderen Objekten benutzt werden kann
• Eine typische Anwendung ist die
Erzeugung eines Primärschlüssels
• Verbessert die Effizienz
12-20
Copyright © Oracle Corporation, 1998. All rights reserved.
Das CREATE SEQUENCE
Statement
Definiere eine Sequenz zur automatischen
Generierung sequentieller Zahlen
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
Copyright © Oracle Corporation, 1998. All rights reserved.
12-21
Beispiel
• Erzeuge eine Sequenz DEPT_DEPTNO die als
Primärschlüssel in der DEPT Tabelle verwendet
wird.
• Ohne CYCLE option.
SQL> CREATE SEQUENCE dept_deptno
2
INCREMENT BY 1
3
START WITH 91
4
MAXVALUE 100
5
NOCACHE
6
NOCYCLE;
Sequence created.
12-22
Überprüfung von Sequenzen
• In der Tabelle des Data Dictionary
USER_SEQUENCES.
SQL> SELECT
2
3 FROM
sequence_name, min_value, max_value,
increment_by, last_number
user_sequences;
• Die Spalte LAST_NUMBER column gibt
die nächst mögliche Sequenznummer
an.
12-23
Copyright © Oracle Corporation, 1998. All rights reserved.
Copyright © Oracle Corporation, 1998. All rights reserved.
Die Pseudospalten NEXTVAL
und CURRVAL
• NEXTVAL gibt den nächsten möglichen
Sequenzwert zurück
CURRVAL gibt den gerade verwendeten
Sequenzwert an
NEXTVAL muss den Wert liefern, bevor
CURRVAL einen Wert enthält
12-24
Copyright © Oracle Corporation, 1998. All rights reserved.
Beispiel
• Gib eine neue Abteilung “MARKETING”
in San Diego ein.
SQL> INSERT INTO
2 VALUES
3
1 row created.
dept(deptno, dname, loc)
(dept_deptno.NEXTVAL,
'MARKETING', 'SAN DIEGO');
Verwendung von Sequenzen
• Durch Caching der Sequenzwerte im
Speicher wird eine schnellere Verarbeitung
erreicht.
• Lücken in den Sequenzwerten können
entstehen wenn:
– ein Rollback ausgeführt wird
• Zeige den verwendeten Wert aus der
DEPT_DEPTNO Sequenz.
SQL> SELECT
2 FROM
12-25
– das System abstürzt
– Eine Sequenz in einer anderen Tabelle
dept_deptno.CURRVAL
dual;
Copyright © Oracle Corporation, 1998. All rights reserved.
Änderung einer Sequenz
•Änderung des Inkrement-Wertes,
verwendet wird
12-26
Copyright © Oracle Corporation, 1998. All rights reserved.
Anweisung zur Änderung einer
Sequenz
•maximum-Wertes,
•minimum Wertes,
•Zyklus –Option oder
• Cache Option.
SQL> ALTER SEQUENCE dept_deptno
2
INCREMENT BY 1
3
MAXVALUE 999999
4
NOCACHE
5
NOCYCLE;
Sequence altered.
12-27
Copyright © Oracle Corporation, 1998. All rights reserved.
• Man muss Besitzer der Sequenz sein
und das ALTER-Privilege für die
Sequenz haben.
• Nur künftige Sequenz-Werte können
erzeugt werden.
• Die Sequenz muss gelöscht und neu
erzeugt werden, wenn man neue
Nummerierung wünscht
12-28
Copyright © Oracle Corporation, 1998. All rights reserved.
Löschen einer Sequenz
• Mit dem DROP SEQUENCE Statement.
SQL> DROP SEQUENCE dept_deptno;
Sequence dropped.
Was ist ein Index?
• Ein Pointer auf Tupel, um den Zugriff
schneller zu machen
• Der Index ist unabhängig von der
Tabelle
• Automatisches Anlegen und Benutzen
des Indexes bei Oracle
12-29
Copyright © Oracle Corporation, 1998. All rights reserved.
12-30
Wie wird ein Index angelegt?
Copyright © Oracle Corporation, 1998. All rights reserved.
Anlegen eines Indexes
• Für eine oder mehrere Spalten
• Automatisch
– Ein eindeutiger Index wird
automatisch bei der Definition der
PRIMARY KEY und der UNIQUE
Constraint angelegt.
• Manuell
– Der Benutzer kann einen nicht-
eindeutigen Index anlegen, um die
Ausführungszeit zu verringern.
12-31
Copyright © Oracle Corporation, 1998. All rights reserved.
CREATE INDEX index
ON table (column[, column]...);
• Verbessert die Geschwindigkeit der
Ausführung der ENAME Spalte in der
EMP Tabelle.
SQL> CREATE INDEX
2 ON
Index created.
12-32
emp_ename_idx
emp(ename);
Copyright © Oracle Corporation, 1998. All rights reserved.
Wann sollte ein Index angelegt werden
• Die Spalte wird häufig in der WHERE-Klausel
oder in der Join-Bedingung gebraucht.
• Die Werte in der Spalte weichen stark
voeinander ab.
• Die Spalten beinhalten eine große Zahl von
NULL-Werten.
• Zwei oder mehr Spalten werden häfig
zusammen in der WHERE-Klausel oder der JoinBedingung gebraucht.
Wann sollte kein Index angelegt
werden?
• Die Tabelle ist klein
• Die Spalten werden nicht oft in den
Anfragen gebraucht
• Die meisten Anfragen bringen mehr als
2–4% der Tupel
• Die Tabelle wird oft geändert
• Die Tabelle ist gross und die meisten Anfragen
werden nicht mehr als 2–4% der Tupel bringen.
Copyright © Oracle Corporation, 1998. All rights reserved.
12-33
12-34
Copyright © Oracle Corporation, 1998. All rights reserved.
Überprüfen des Index
Löschen des Index
• Die Data Dictionary View USER_INDEXES
enthält den Namen des Index und seine
Eindeutigkeit
• Löschen des Index aus dem Data Dictionary.
• Die View USER_IND_COLUMNS enthält
den Indexnamen, den Tabellennamen
und den Spaltennamen.
SQL> DROP INDEX index;
• Beispiel
SQL> DROP INDEX emp_ename_idx;
Index dropped.
SQL>
2
3
4
5
12-35
SELECT
FROM
WHERE
AND
ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
user_indexes ix, user_ind_columns ic
ic.index_name = ix.index_name
ic.table_name = 'EMP';
Copyright © Oracle Corporation, 1998. All rights reserved.
• Man muss Besitzer des Index sein oder
das DROP ANY INDEX Privileg haben.
12-36
Copyright © Oracle Corporation, 1998. All rights reserved.
Erzeugung und Löschen eines
Synonyms
Synonyme
Einfacher Zugriff auf Objekte durch
Erzeugung eines anderen Namens für ein
Objekt.
• Verweis auf eine Tabelle eines Besitzers
durch einen anderen Nutzer
• Verkürzung langer Namen
CREATE [PUBLIC] SYNONYM synonym
FOR
object;
12-37
Copyright © Oracle Corporation, 1998. All rights reserved.
• Erzeugen eines verkürzten Namens für
die View DEPT_SUM_VU.
SQL> CREATE SYNONYM d_sum
2 FOR
dept_sum_vu;
Synonym Created.
• Löschen des Synonyms.
SQL> DROP SYNONYM d_sum;
Synonym dropped.
12-38
Copyright © Oracle Corporation, 1998. All rights reserved.