Hallo,
ich habe folgendes Problem:
Ich habe eine Tabelle, in dem eine Spalte 2 mögliche Tabellen als Fremdschlüssel hat und ich weiß nicht, wie genau ich das umsetzen kann.
Beispiel:
Aktion:
ID | Name | Bla | Ziel
|
/ \
/ \
Gebäude Viertel
Also das Ziel kann ein Gebäude und ein Viertel sein, wie kann ich das Datenbankmäßig am besten umsetzen?
Danke!
Moin, Cojones,
erstmal pre-Tags verwenden, sonst sieht man nichts:
> ID | Name | Bla | Ziel
> |
> / \
> / \
> Gebäude Viertel
Direkt lässt sich das nicht machen, die Methode heißt Subtyping: Eine Tabelle aufbauen, die einen eigenen Schlüssel ZIEL_Id hat, auf den Du verweisen kannst, und zwei Fremdschlüssel, wobei immer nur einer belegt ist. Oder Du machst es gleich mit zwei FS in der obersten Tabelle - nicht ganz sauber, reicht aber immer dann, wenn der Supertyp ZIEL sonst nirgends gebraucht wird.
Gruß Ralf
Direkt lässt sich das nicht machen, die Methode heißt
Subtyping: Eine Tabelle aufbauen, die einen eigenen Schlüssel
ZIEL_Id hat, auf den Du verweisen kannst, und zwei
Fremdschlüssel, wobei immer nur einer belegt ist. Oder Du
machst es gleich mit zwei FS in der obersten Tabelle - nicht
ganz sauber, reicht aber immer dann, wenn der Supertyp ZIEL
sonst nirgends gebraucht wird.Gruß Ralf
Danke, wusste ich nicht mit den
-Tags. Also wäre Subtyping der saubere und beste Lösungsansatz?
Hi cojones,
Also wäre Subtyping der saubere und beste Lösungsansatz?
na ja, hängen wir die Wurscht mal ein wenig tiefer: Subtyping ist nur ein Modell. Die Umsetzung läuft immer auf 2 (oder auch mehr) Fremdschlüssel hinaus, bei denen der Programmierer dafür sorgen muss, dass nie mehr als einer belegt ist. Soweit ich weiß, wird das von keiner Datenbank unterstützt.
Gruß Ralf
Hallo Cojones, Hallo Ralf,
ID | Name | Bla | Ziel-ID
|
| Ziel-ID | Ziel-Nr | Ziel-Typ |
/ \
/ \
Gebäude Viertel
Etwas sauberer wäre es, erstmal auf eine Tabelle zu gehen, die differenziert, ob man einen Ziel-Typ Gebäude oder Viertel verwendet und die Ziel-Nr in den entsprechenden Beschreibungtabellen Gebäude und Viertel (wobei die Nummernkreis theoretisch (aber bitte nie praktisch) identisch sein könnten).
Vorteil des Konstruktes: Man kann leichter die Anzahl der Zieltypen erweitern.
MfG Georg V.
Hi Ralf,
Die Umsetzung läuft immer auf 2 (oder auch
mehr) Fremdschlüssel hinaus, bei denen der Programmierer dafür
sorgen muss, dass nie mehr als einer belegt ist. Soweit ich
weiß, wird das von keiner Datenbank unterstützt.
Kommt darauf an, wie viel Unterstützung Du von der DB haben willst.
CREATE TABLE myTable
(id NUMBER(9) PRIMARY KEY,
fk1 NUMBER(9) REFERENCES myTest1 (id),
fk2 NUMBER(9) REFERENCES myTest2 (id),
CONSTRAINT ccFk CHECK
((fk1 IS NOT NULL AND fk2 IS NULL) OR
(fk1 IS NULL AND fk2 IS NOT NULL))
);
trifft’s doch ziemlich gut, oder?
Gruß
Martin
Moin, Martin,
danke für den Hinweis - ich habe 15 Jahre QS in der Modellierung gemacht, und immer waren die Jungs am Heulen. Ist das Standard-SQL oder nur in PostgreSQL möglich?
Gruß Ralf
Moin, Georg V.,
das predige ich seit 20 Jahren: Kein Subtyping ohne Diskriminante, und seit 20 Jahren ernte ich Undank bis hin zu Beschimpfungen 
Gruß Ralf
Kommt darauf an, wie viel Unterstützung Du von der DB haben
willst.CREATE TABLE myTable
(id NUMBER(9) PRIMARY KEY,
fk1 NUMBER(9) REFERENCES myTest1 (id),
fk2 NUMBER(9) REFERENCES myTest2 (id),
CONSTRAINT ccFk CHECK
((fk1 IS NOT NULL AND fk2 IS NULL) OR
(fk1 IS NULL AND fk2 IS NOT NULL))
);trifft’s doch ziemlich gut, oder?
Gruß
Martin
Hallo Martin,
sehr guter Ansatz, werde es so mal versuchen.
Habt ihr euch schon mal die DB-Abfragen angeschaut, die durch eurer Datenmodell entstehen.
Da benötige ich doch tatsächlich 2 OUTERJOINS. Das mag bei 10 Datensätzen performant sein, aber bei 'ner Million, wartet man doch schon mal ein paar Sekunden.
Mag zwar sein, das euer Datenmodell das richtige ist… aber performant ist was anderes… Durch den Null-Value in dem einen Feld wird ein Full-Tablescan nötig. 
Was spricht dagegen die Gebäude und Viertel in einer Tabelle zu halten und nur durch einen Typ zu unterscheiden. Ggf falls kann man den Typ in den Primärschlüssel aufnehmen(dann auch FK).
Durch einen Selfjoin könne man die Beziehung zwischen Gebäude und Viertel erzeugen.
cu Frank
Hallo Ralf,
Ist das Standard-SQL oder nur in PostgreSQL möglich?
also ausprobiert habe ich das unter Oracle. Sollte aber eigentlich alles Standard sein. Wie weit andere DBs das unterstützen weiß ich leider nicht, aber eigentlich sind das schon eher Basics. Eventuell ist die Syntax anders, Table-Constraints gibt’s aber ziemlich sicher bei allen größeren Herstellern.
Gruß
Martin
Hallo Frank,
Habt ihr euch schon mal die DB-Abfragen angeschaut, die durch
eurer Datenmodell entstehen.
Natürlich habe ich das, ich verwende sowas ständig.
Da benötige ich doch tatsächlich 2 OUTERJOINS.
Outer Joins sind nicht böse.
Das mag bei 10
Datensätzen performant sein, aber bei 'ner Million, wartet man
doch schon mal ein paar Sekunden.
Nein.
Mag zwar sein, das euer Datenmodell das richtige ist… aber
performant ist was anderes…
dem einen Feld wird ein Full-Tablescan nötig.
Nein.
CREATE TABLE mar\_test1 AS (SELECT object\_id id FROM user\_objects);
DELETE FROM mar\_test1 WHERE id IS NULL;
ALTER TABLE mar\_test1 ADD CONSTRAINT pk1 PRIMARY KEY (id);
CREATE TABLE mar\_test2 AS (SELECT object\_id id FROM user\_objects);
DELETE FROM mar\_test2 WHERE id IS NULL;
ALTER TABLE mar\_test2 ADD CONSTRAINT pk2 PRIMARY KEY (id);
CREATE TABLE mar\_table
(id NUMBER(9) PRIMARY KEY,
fk1 NUMBER(9) REFERENCES mar\_test1 (id),
fk2 NUMBER(9) REFERENCES mar\_test2 (id),
CONSTRAINT ccFk CHECK
((fk1 IS NOT NULL AND fk2 IS NULL) OR
(fk1 IS NULL AND fk2 IS NOT NULL))
);
CREATE SEQUENCE mar\_seq;
INSERT INTO mar\_table
SELECT mar\_seq.NEXTVAL, id, NULL FROM mar\_test1;
INSERT INTO mar\_table
SELECT mar\_seq.NEXTVAL, NULL, id FROM mar\_test2;
ANALYZE TABLE mar\_table COMPUTE STATISTICS;
ANALYZE TABLE mar\_test1 COMPUTE STATISTICS;
ANALYZE TABLE mar\_test2 COMPUTE STATISTICS;
EXPLAIN PLAN FOR
SELECT \* FROM mar\_table a, mar\_test1 b, mar\_test2 c
WHERE a.fk1 = b.id(+) AND
a.fk2 = c.id(+) ORDER BY 1;
SELECT LPAD(' ',2\*(depth-1))||operation operation,
options,
object\_name
FROM plan\_table p
ORDER BY id;
Ergebnis:
OPERATION OPTIONS OBJECT\_NAME
SELECT STATEMENT
NESTED LOOPS OUTER
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID MAR\_TABLE
INDEX FULL SCAN SYS\_C0089477
INDEX UNIQUE SCAN PK1
INDEX UNIQUE SCAN PK2
Da ist genau ein FTS drin (in dem Fall ein FIS, aber das können wir hier vernachlässigen), und den muss er machen, weil mar_table nicht eingeschränkt wurde. Wenn ich das etwas einschränke, z.B. so:
EXPLAIN PLAN FOR
SELECT \* FROM mar\_table a, mar\_test1 b, mar\_test2 c
WHERE a.fk1 = b.id(+) AND
a.fk2 = c.id(+) AND
a.id BETWEEN 100 AND 250;
dann sehe ich gar keinen FTS/FIS mehr:
SELECT STATEMENT
NESTED LOOPS OUTER
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID MAR\_TABLE
INDEX RANGE SCAN SYS\_C0089477
INDEX UNIQUE SCAN PK1
INDEX UNIQUE SCAN PK2
Was spricht dagegen die Gebäude und Viertel in einer Tabelle
zu halten und nur durch einen Typ zu unterscheiden. Ggf falls
kann man den Typ in den Primärschlüssel aufnehmen(dann auch
FK).
Mir fallen noch ca. 50 Gründe ein, das nicht so zu machen, denke aber das reicht fürs erste.
Durch einen Selfjoin könne man die Beziehung zwischen Gebäude
und Viertel erzeugen.
Könnte man. Man könnte das aber auch richtig modellieren.
Gruß
Martin
Mahlzeit!
Da benötige ich doch tatsächlich 2 OUTERJOINS.
Outer Joins sind nicht böse.
Das mag bei 10
Datensätzen performant sein, aber bei 'ner Million, wartet man
doch schon mal ein paar Sekunden.Nein.
Mag zwar sein, das euer Datenmodell das richtige ist… aber
performant ist was anderes…
dem einen Feld wird ein Full-Tablescan nötig.Nein.
Ich frage mich, woher diese _Gerüchte_ bzgl. Outer-Joins und Null-Values kommen???
Bereits seit der 7er-DB von Oracle verwende ich diese beiden bösen Buben und bisher konnte ich - auch mithilfe des TKPROFS bzw. TOADS - die Statements zumindest so performant machen, dass man Online nicht merkte, dass da gerade ein paar hundert Millionen (keine 10 oder eine Million) Datensätze abgefragt wurden.
Jetzt muß nur noch das Gerücht bzgl. Null-Values in Indizes (zumindest unter Oracle) bereinigt werden …
Grüße,
Tomh - der gerade ein Select mit 7 Outer-Joins, ein paar NVLs und ein paar NULL-Abfragen zurechtgebogen hat …
Moin nochmal,
also das Gerücht mit den NULL-Abfragen hält sich sehr gut:
SQL\>explain plan for
select STATUS from XXXXXXXXX where OBJ\_ID is null;
EXPLAIN PLAN ausgef³hrt.
SQL\> select \* from table(dbms\_xplan.display());
PLAN\_TABLE\_OUTPUT
----------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 24 | 0 (0)| |
|\* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| XXXXXXXXX | 7653K| 175M| 7857 (2)| 00:01:51 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
SQL\> explain plan for
select STATUS from XXXXXXXXX where OBJ\_ID = '1234';
EXPLAIN PLAN ausgef³hrt.
SQL\> select \* from table(dbms\_xplan.display());
PLAN\_TABLE\_OUTPUT
----------------------------------------------------------------------
Plan hash value: 1694160547
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 24 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| XXXXXXXXX | 1 | 24 | 1 (0)| 00:00:01 |
|\* 2 | INDEX RANGE SCAN | XPKUTOBJ | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJ\_ID"='1234')
Zu den OUTERJOINS:
Klar sind die nicht generell „böse“, aber vermeiden ist einfach besser!
Bin überrascht von deinen 7 OUTERJOINS… da kann doch was nicht stimmen, entweder dein Datenmodell oder deine Abfrage.
Versuch das mal ohne OUTERJOINS und die performance geht hoch… ^^
Nochmal zum Tabellennamen:
WOHN_OBJEKT oder TCACC013 wären gute Beispiel
*grins*
cu Frank
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hi Tom,
Ich frage mich, woher diese _Gerüchte_ bzgl. Outer-Joins und
Null-Values kommen???
Am schlimmsten daran finde ich, dass diese Gerüchte immer ung’schaut als wahr übernommen werden, und sich offensichtlich kaum einer die zwei Minuten nimmt, um nachzuschauen, ob das überhaupt stimmt. Das gleiche gilt ja für die ach-so-bösen Full Table Scans, Nested Loops, … (die Liste kann man laaaange fortführen).
Die haben wohl alle ihren Tom Kyte nicht gelesen.
Gruß
Martin
Hallo Frank,
also das Gerücht mit den NULL-Abfragen hält sich sehr gut:
Natürlich zieht eine Abfrage auf IS NULL nach sich, dass der jeweilige Index nicht verwendet werden kann (weil NULLs nicht indiziert werden). Wenn Du aber auf IS NOT NULL abfragst, dann sieht das anders aus. Zeige mir bitte den unnötigen Full Table Scan in meinem Beispiel.
Zu den OUTERJOINS:
Klar sind die nicht generell „böse“, aber vermeiden ist
einfach besser!
Sag einmal hast Du meine Antwort überhaupt gelesen?
Bin überrascht von deinen 7 OUTERJOINS… da kann doch was
nicht stimmen, entweder dein Datenmodell oder deine Abfrage.
Noch nie ein Data Warehouse gesehen?
Versuch das mal ohne OUTERJOINS und die performance geht
hoch… ^^
Das ist wieder ein Gerücht, selbst mit Emoticons. Wahr aber ist, dass falsche Designs sowohl Programmfehler als auch schlechte Performance verursachen.
Gruß
Martin
Hallo Martin,
klar habe ich deine Nachricht gelesen, aber welchen Punkt genau soll ich nicht angeschaut haben? Welcher Punkt hat irgendwelche Fakten beinhaltet, die meine Meinung ändern könnte.
Ein Datawarehouse mit OUTERJOINS…das kann doch nicht war sein oder? Die Dataware-Houses die ich kenne, verwenden STARSCHEMAs und hierbei gibt es keinen OUTERJOIN, weil alle DIMENSIONEN passend zu den FAKTEN gefüllt werden. Ansonsten besteht die Gefahr, das bei Abfragen Daten verloren gehen und die Gesamtsummen der DIMENSIONEN nicht übereinstimmen.
Auch ich habe mal ein DWH betreut.
Ich habe grad noch mal ein „IS NOT NULL“ verwendet, hier gibts ebenfalls einen FULLTABLE-SCAN, wieso soll hier ein Index verwendet werden? Die Treffermenge liegt bei 100%, da macht ein FULLTABLE-SCAN Sinn(und ist gewünscht).
Um jetzt noch mal zum urspünglichen Thema zu kommen:
OUTERJOINS und NULL-Abfragen:
Meiner Meinung nach sollte diese vermieden werden und wenn das bei der Datenmodellierung schon einplant, läuft später was mit den Abfragen falsch. Mit einem solchen Datenmodell wirst du bei größeren Datenmengen nicht froh.
Du hast leider keine Fakten bringen können die belegen das OUTERJOINS super sind. Somit bleibe ich bei meine Meinung, dass diese sparsam verwendet werden sollten.
Je mehr ich von dir lese, desto weniger Stimme ich dir zu. Deine
Antwort „:Noch nie ein Data Warehouse gesehen?“ liefert keine Fakten, sonden einfach NIX.
cu Frank
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hallo Frank,
Zu 1. Erst einmal sehe ich im konkreten Fall wenig Sinn in einem SELECT Gebäude und Viertel zu mischen - in aller Regel werde ich nur das eine oder nur das andere haben wollen. Damit brauche ich dort schon einmal keinen outer join.
Selbst wenn ich aber beide brauche bist Du bisher den Beweis schuldig geblieben, dass der outer join tatsächlich langsam ist.
Und zu den paar Sekunden: Mit meinem Beispiel und 20 Mio Datensätzen (10 Mio in der Haupttabelle und je 5 in den beiden anderen) braucht ein Select mit beiden outer joins, der mir ca. 1000 Sätze liefert, gemessene 0,051 Sekunden.
Zu 2. Ich habe anhand eines Beispiels bewiesen, dass kein Full Table Scan gemacht werden muss. Wie Du es schaffst, diesbezüglich dennoch bei Deiner Behauptung zu bleiben, ist mir eigentlich ein Rätsel.
Und weil Dir Gründe fehlen, die gegen Dein Datenmodell sprechen:
Das hatten wir aber schon…
Gruß
Martin
Hi Martin!
Die haben wohl alle ihren Tom Kyte nicht gelesen.
Hab ich auch nicht gemacht - allerdings im Zuge von diversesten Performance-Verbesserungen auf so manche _unglaubliche_ Konstellation gestoßen.
Außerdem sind Optimizer-Hints sogar auf einer 10er noch ursuper 
Grüße,
Tomh
Hallo Martin,
ich habe deine Lösung getestet und dazu noch meine aufgebaut.
Es ist bei 147k Datensätzen ein Unterschied von 6/100Sek festzustellen.
Die Composite-Index-Lösung ist schneller.
Dein Problem mit dem Gleichartikeit(1) von Objekten ist Definitionssache
wenn ich definiere, das dass Wohnobjekt in einer Tabelle gespeichert wird ist das eben so(In einem Warenwirtschaftssystem würdest du doch auch Häuser und Kühlschränke im Artikelstamm aufnehmen, wenn diese verkauft werden, wo ist da die Gleichheit? Oder legst du für jede Artikelgruppe eine neue Tabelle an?).
Wenn ich dann auf die Idee komme, ein Wohnobjekt „Strasse“ zu erzeugen, brauche ich noch eine Tabelle und muss das Datenmodell ändern? Das läuft irgendwie aus dem Ruder… (so schreibt man sich unabkömmlich)…
Meiner Meinung nach gehört die Information in die Tabelle und nicht in die Struktur.
Grund 2,4 und 5 erledigen sich von alleine, da die Geschwindigkeit etwas anderes aussagt.
Bleibt nur noch Punkt 3, ich könnte dein System auch nicht intuitiv bedienen. Wie kann ich mir die Applikation vorstellen, die mit dem Datenmodell arbeitet, wie sieht die Erfassungsmaske aus und wie viel Programmierlogik steck da drin(kannst du mit APEX eine Enduser-Anwendung generieren oder muß du da noch Programmieraufwand reingestecken?
SQL\> SELECT count(\*) FROM mar\_table a, mar\_test1 b, mar\_test2 c
2 WHERE a.fk1 = b.id(+) AND
3 a.fk2 = c.id(+) ORDER BY 1;
COUNT(\*)
----------
147442
Abgelaufen: 00:00:00.35
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 59 (6)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | NESTED LOOPS OUTER | | 147K| 1871K| 59 (6)| 00:00:01 |
| 3 | NESTED LOOPS OUTER| | 147K| 1295K| 57 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL| MAR\_TABLE | 147K| 719K| 56 (4)| 00:00:01 |
|\* 5 | INDEX UNIQUE SCAN| PK1 | 1 | 4 | 1 (0)| 00:00:01 |
|\* 6 | INDEX UNIQUE SCAN | PK2 | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------
----------------------------------------------------
SQL\> SELECT count(\*) FROM my\_table a, mar\_test3 b
2 WHERE a.fk\_id = b.id
3 AND a.fk\_typ = b.typ;
COUNT(\*)
----------
147458
Abgelaufen: 00:00:00.29
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 63 (5)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | NESTED LOOPS | | 147K| 1440K| 63 (5)| 00:00:01 |
| 3 | TABLE ACCESS FULL| MY\_TABLE | 147K| 720K| 61 (4)| 00:00:01 |
|\* 4 | INDEX UNIQUE SCAN| PK\_MAR\_TEST3 | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------
cu Frank