Einleitung

DOAG: Beitrag
Trivadis GmbH, Peter Jensch
Juni 2004
Betrifft
Primary-Key-Constraints und Reihenfolge von Tabellen-Attributen
Art der Info
Technische Background Info
Quelle
Aus unserer Schulungs- und Beratungstätigkeit
Einleitung
Applikationen leben von Veränderungen und Erweiterungen. Dies gilt selbstverständlich
auch für Applikationen im Oracle-Umfeld.
Durch notwendige Erweiterungen kommt es bei der nachfolgenden Erstellung von
Datenbank-Constraints gelegentlich zu kleinen Interpretationsproblemen. Werden später
erstellte Attribute für die Erstellung der Primary-Key-Constraints verwendet, kann ggf. nicht
mehr das erste oder die ersten Attribute der Basis-Tabelle verwendet werden. Eine
mühsame Korrektur der Tabellen-Definition ist die Folge.
Eine Bereinigung der Struktur-Definition der Tabelle sollte aber in jedem Fall durchgeführt
werden, da bereits mit dem SQL*Plus-Tool unter Verwendung des DESCRIBE-Kommandos
der Entwickler leicht in die Irre geführt werden kann. Sicherlich wird eine genaue Analyse
der Constraints schnell erkennen lassen, dass das DESCRIBE-Kommando nicht oder nur
eingeschränkt für eine Constraint-Interpretation verwendet werden kann.
Beispiel:
SQL> desc dept_sekt
Name
-------------------------------------DEPTNO
DNAME
LOC
DEPT_SECTION
Null?
Type
-------- -------------------------NOT NULL NUMBER(2)
VARCHAR2(14)
VARCHAR2(13)
NOT NULL VARCHAR2(1)
Die Tabelle dept_sekt wurde mit Hilfe des ALTER TABLE Befehls um das Attribut
dept_section erweitert. Wird ein neues Attribut einer Tabelle hinzugefügt, so wird dieses
als letztes der Tabelle in der Attributliste angehängt. Unabhängig davon wurde dieses
Attribut in unserem Beispiel in die Primary-Key-Constraint-Definition aufgenommen.
Üblicherweise geht man davon aus, dass das erste Attribut bzw. die ersten Attribute für die
Definition des Primary-Key-Constraint verwendet werden. Mit der Definition des PKConstraints wird automatisch ein NOT NULL Constraint auf die betroffenen Attribute
erstellt.
Diese NOT NULL-Constraints, insbesondere am Tabellen-Anfang, führen oft zum Schluss,
diese Attribute gehören zur PK-Constraint-Definition. Dies ist nicht natürlich automatisch
richtig!
Bei einer genaueren Analyse des Beispiels unter Verwendung der Data-Dictionary-Views
user_constraints und user_cons_columns erkennt man schnell, dass in diesem Fall der
Primary-Key aus den Attributen deptno und dept_sektion aufgebaut ist:
SQL> SELECT constraint_name, constraint_type, table_name
2
FROM user_constraints
3
WHERE table_name = 'DEPT_SEKT';
CONSTRAINT_NAME
C TABLE_NAME
------------------------------ - -----------------------------PK_DEPT_SEKT
P DEPT_SEKT
SQL> col table_name format a15
SQL> col column_name format a15
SQL> SELECT constraint_name, table_name, column_name, position
2
FROM user_cons_columns
3
WHERE table_name = 'DEPT_SEKT';
CONSTRAINT_NAME
-----------------------------PK_DEPT_SEKT
PK_DEPT_SEKT
TABLE_NAME
--------------DEPT_SEKT
DEPT_SEKT
COLUMN_NAME
POSITION
--------------- ---------DEPTNO
1
DEPT_SEKTION
2
Die NOT NULL-Definition aus dem DESCRIBE-Kommando wird automatisch über den
Primary-Constraint erstellt und kann aus der View user_tab_columns im Attribut
NULLABLE eingesehen werden:
SQL> SELECT table_name, column_name, nullable
2
FROM user_tab_columns
3
WHERE table_name = 'DEPT_SEKT';
TABLE_NAME
--------------DEPT_SEKT
DEPT_SEKT
DEPT_SEKT
DEPT_SEKT
COLUMN_NAME
--------------DEPTNO
DNAME
LOC
DEPT_SEKTION
N
N
Y
Y
N
Eine weitere Möglichkeit die Informationen aus der Datenbank zu lesen:
SQL> col tablename
format a15
SQL> col constraint_name format a15
SQL> SELECT c.table_name
2
, c.constraint_name
3
, cc.column_name
4
, tc.column_id
5
, cc.position
6
FROM user_constraints c
7
, user_cons_columns cc
8
, user_tab_columns tc
Tablename
Constraint_Name
Column_Name
Tab_Position
Con_Position
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WHERE
AND
AND
AND
AND
tc.table_name
= c.table_name
c.constraint_name
= cc.constraint_name
tc.column_name
= cc.column_name
c.constraint_type
= 'P'
c.table_name IN (SELECT c1.table_name
FROM user_constraints c1
, user_cons_columns cc1
, user_tab_columns tc1
WHERE tc1.table_name
=
AND c1.constraint_name =
AND tc1.column_name
=
AND cc1.position
!=
AND c1.constraint_type =
ORDER BY c.owner, c.table_name, cc.position
c1.table_name
cc1.constraint_name
cc1.column_name
tc1.column_id
'P')
/
TABLENAME
--------------DEPT_SEKT
DEPT_SEKT
CONSTRAINT_NAME
--------------PK_DEPT_SEKT
PK_DEPT_SEKT
COLUMN_NAME
TAB_POSITION CON_POSITION
--------------- ------------ -----------DEPTNO
1
1
DEPT_SEKTION
4
2
Jetzt erkennt man schnell, dass das zweite Constraint-Attribut das 4-te Attribut in der
Tabellen-Definition ist.
Für eine Reorganisation bietet sich ein CREATE TABLE AS SELECT (CTAS) an. Die Attribute
werden in neuer Reihenfolge in der Select-Liste angegeben. Die „alte“ Tabelle muss
gelöscht und die neue Tabelle umbenannt werden. Bei dieser Methode werden die
Constraints der referenziellen Integrität, Trigger, Grants usw. nicht kopiert und müssen von
Hand neu erstellt werden.
Für die optimale Erstellung von Tabellen werden häufig folgende Richtlinien zu Grunde
gelegt:
-
Primary-Key-Constraint-Attribute sind die ersten Attribute einer Tabelle
NOT NULL Attribute sollten so häufig wie möglich gesetzt werden. Diese Attribute
sind die ersten Spalten einer Tabelle
Je häufiger ein Attribut einer Tabelle verwendet wird, umso weiter „vorne“ in der
Attributliste sollte dieses Attribut in der Tabellendefinition verwendet werden
Darüberhinaus kann eine Strukturanpassung der Tabellen, die einen Neuaufbau notwendig
machen, den Blockfüllungsgrad und die Performance verbessern. Ein nicht zu verachtender
Nebeneffekt! Das folgende Beispiel zeigt den Neuaufbau einer Tabelle mit dem „Create
Table As Select“ – Befehl.
SQL> create table dept_sekt_neu
2
as select deptno, dept_sektion, dname, loc
3
from dept_sekt
4
order by deptno;
Table created.
SQL> alter table dept_sekt_neu add (constraint pk_dept_sekt_neu
2
primary key ( deptno, dept_sektion ) validate;
Table altered.
SQL> desc dept_sekt_neu
Name
-------------------------------------DEPTNO
DEPT_SEKTION
DNAME
LOC
Null?
-------NOT NULL
NOT NULL
Type
-------------------------NUMBER(2)
VARCHAR2(1)
VARCHAR2(14)
VARCHAR2(13)
Weitere Möglichkeiten, wie beispielsweise eine Online-Reorganisation, ergeben sich ab
der Oracle-Version 9i unter Verwendung des Standard-Package DBMS_REDEFINITION
oder der Verwendung des Trivadis Reorganisation Tools TabReorg.
Eine jederzeit geordnete und interessante Referenz zu diesem, oder auch zu anderen
Themen, wie Online-Reorganisation, New Features, Tipps und Tricks bietet die Trivadis
Webseite. Oder Sie besuchen einen unserer Kurse ☺
Peter Jensch
Trivadis GmbH
Peter Jensch
Max – Lang – Str. 59
70771 Leinfelden - Echterdingen
Internet: http://www.trivadis.com
Mail: [email protected]
Tel: +49 +711 90 36 32 30
Fax: +49 +711 90 36 32 59