Zum Inhalt springen
Frage 17. Oktober 2008 · 23 Antworten

Fremdschlüsselproblem

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!

Diese Frage stammt aus dem Jahr 2008. Weißt du es heute besser?

Aktuelle Antwort ergänzen

23 Antworten

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

0 Hilfreich
Antwort auf drambeldierzum Beitrag ↑

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 ei …

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?
0 Hilfreich
Antwort auf Cojoneszum Beitrag ↑

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. Ode …

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

0 Hilfreich
Antwort auf drambeldierzum Beitrag ↑

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 ei …

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.

0 Hilfreich
Antwort auf drambeldierzum Beitrag ↑

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 hinau …

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

1 Hilfreich
Antwort auf TheBeastzum Beitrag ↑

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üt …

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

0 Hilfreich
Antwort auf Georg_Vzum Beitrag ↑

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 …

Moin, Georg V.,

das predige ich seit 20 Jahren: Kein Subtyping ohne Diskriminante, und seit 20 Jahren ernte ich Undank bis hin zu Beschimpfungen :smile:

Gruß Ralf

0 Hilfreich
Antwort auf TheBeastzum Beitrag ↑

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üt …

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.

0 Hilfreich
Antwort auf drambeldierzum Beitrag ↑

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

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… :smile: Durch den Null-Value in dem einen Feld wird ein Full-Tablescan nötig. :frowning:

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

0 Hilfreich
Antwort auf drambeldierzum Beitrag ↑

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

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

0 Hilfreich
Antwort auf FrankPzum Beitrag ↑

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 …

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… :smile: Durch den Null-Value in
dem einen Feld wird ein Full-Tablescan nötig. :frowning:

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).

  1. Weil es falsch ist. Gebäude und Viertel sind nicht das gleiche.
  2. Du musst Deinen Typ dann in allen Subtabellen mitführen, sonst kannst Du keine Foreign Keys erstellen.
  3. Weil es nicht intuitiv erfassbar ist. Jeder, der so ein Schema übernimmt braucht Wochen um zu verstehen, was das soll. Für den Anfang: Nenne mir einmal einen vernünftigen Namen für die Tabelle.
  4. Weil Composite Keys, die ein j/n Feld enthalten keinem Optimizer eine Freude machen.
  5. Weil es keine Vorteile hat.

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

0 Hilfreich
Antwort auf TheBeastzum Beitrag ↑

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 …

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… :smile: Durch den Null-Value in
dem einen Feld wird ein Full-Tablescan nötig. :frowning:

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 …

0 Hilfreich
Antwort auf Tomhzum Beitrag ↑

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, d …

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]

0 Hilfreich
Antwort auf Tomhzum Beitrag ↑

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, d …

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

0 Hilfreich
Antwort auf FrankPzum Beitrag ↑

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() …

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

0 Hilfreich
Antwort auf TheBeastzum Beitrag ↑

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 D …

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]

0 Hilfreich
Antwort auf FrankPzum Beitrag ↑

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 OUTE …

Hallo Frank,

  1. Du schriebst das Datenmodell wie von mir vorgeschlagen wurde zwei Outer Joins nach sich ziehen, und die wären böse. Wörtlich: „Das mag bei 10“.
    Datensätzen performant sein, aber bei 'ner Million, wartet man
    doch schon mal ein paar Sekunden.
  2. Du schriebst durch die NULLs in den Foreign Key Tabellen müsste das DBMS zwangsläufig Full Table Scans durchführen.

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:

  1. Weil es falsch ist. Gebäude und Viertel sind nicht das gleiche.
  2. Du musst Deinen Typ dann in allen Subtabellen mitführen, sonst kannst Du keine Foreign Keys erstellen.
  3. Weil es nicht intuitiv erfassbar ist. Jeder, der so ein Schema übernimmt braucht Wochen um zu verstehen, was das soll. Für den Anfang: Nenne mir einmal einen vernünftigen Namen für die Tabelle.
  4. Weil Composite Keys, die ein j/n Feld enthalten keinem Optimizer eine Freude machen.
  5. Weil es keine Vorteile hat.

Das hatten wir aber schon…

Gruß
Martin

1 Hilfreich
Antwort auf TheBeastzum Beitrag ↑

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 ein …

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 :wink:

Grüße,
Tomh

0 Hilfreich
Antwort auf TheBeastzum Beitrag ↑

Hallo Frank, Du schriebst das Datenmodell wie von mir vorgeschlagen wurde zwei Outer Joins nach sich ziehen, und die wären böse. Wörtlich: „Das mag bei 10“. Datensätzen performant sein, aber bei 'ner Million, wartet man …

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

0 Hilfreich
Antwort auf FrankPzum Beitrag ↑

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 Gleichart …

Hallo Frank,

hat ein wenig gedauert, aber ich lasse das natürlich nicht auf mir sitzen :wink:

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.

Das noch als objectiv schneller zu bezeichnen müsste Dir aber schon auch selbst schwer fallen, oder?

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?).

Nein, ich lege natürlich nicht für jede Artikelgruppe eine eigene Tabelle an. Dass allerdings Häuser und Viertel das Gleiche sind hast Du definiert, nicht der OP. Ohne also mehr über das eigentliche Datenmodell zu wissen tun wir uns da schwer.

Spätestens wenn die zwei Tabellen so aussehen finde ich Deinen Ansatz wirklich uneingeschränkt falsch:

GEBAEUDE
 id
 adresse1
 adresse2
 viertel\_id
VIERTEL
 id
 bezeichnung
 plz

Das einzige, was in Deiner Mischtabelle dann immer gefüllt wäre ist der Primary Key - ein mehr als deutlicher Hinweis, dass da was schief läuft. Der sähe dann ja so aus:

GEBAEUDE\_VIERTEL
 typ
 id
 adresse1
 adresse2
 viertel\_id
 bezeichnung
 plz

Wie Du da verhinderst, dass jemand ein Viertel löscht an dem noch Gebäude hängen (oder ein Gebäude einfügt, das auf ein nicht existentes Viertel verweist) wäre übrigens auch interessant.

Bleibt nur noch Punkt 3, ich könnte dein System auch nicht
intuitiv bedienen.

Na schön. Uns fehlt noch die Tabelle, die sich auf Gebäude/Viertel bezieht. So sähe die bei mir aus:

PERSON
 id
 vorname
 nachname
 viertel\_id
 gebaeude\_id

und bei Dir

PERSON
 id
 vorname
 nachname
 gebviertel\_id
 fk\_typ

Wenn Du mir mit diesen Informationen für dein Modell den SELECT hinschreiben kannst, der alle Personen im Gebäude 4711 zurückliefert, dann bist Du ziemlich gut. Ich könnte es nicht. In meinem Modell ist das eher trivial, finde ich zumindest.

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?

Also ich schätze mal grob: Bei mir leichter als bei Dir.

Gruß
Martin

0 Hilfreich
Antwort auf TheBeastzum Beitrag ↑

Hallo Frank, hat ein wenig gedauert, aber ich lasse das natürlich nicht auf mir sitzen :wink: ich habe deine Lösung getestet und dazu noch meine aufgebaut. Es ist bei 147k Datensätzen ein Unterschied von 6/100Sek festzus …

Hallo Martin,

zum Datenmodell alles Definitionssache. Es kommt einfach auf die Anforderung an. Auf jeden Fall würde ich versuchen Outerjoins zu verhindern. Dein Datenmodell hat mind. einen Fehler, die PLZ kann nicht zum Viertel gehören, da(in Deutschland) nicht mal Strassen die gleiche PLZ haben müssen.

Zum Thema Geschwindigkeit. 6/100 bei einem Durchlauf, lass das mal 100.000 mal passieren, da sind das schon 1,6 Stunden(Bei ner Million Druchläufe = 16h).
Ich habe am Anfang von Sekunden gesprochen, nicht von Stunden… ^^
Wenn das auf einer Produktionsumgebung laufen muss und 100 Leute nicht arbeiten können ergibt das 1600Std. = 1PersonenJahr. Ups…, da sind deine 6/100Sek objektiv sehr teuer(bei 50Euro/h = 80.000Euro).

Dazu kommt noch das deine Applikation viel komplizierter zu programmieren ist(Datenpflege). Und Anforderungen, wie neue Wohnobjekte(Strasse), sind nur durch Datenmodelländerung machbar.

Es muss 'ne bessere Lösung geben, vielleicht ist meine nicht optimal, aber doch schneller und einfacher zu programmieren. Massenänderungen sind viel schneller. Egal wie schön dein Datenmodell ist, es ist zu teuer.

cu Frank

0 Hilfreich
Antwort auf FrankPzum Beitrag ↑

Hallo Martin, zum Datenmodell alles Definitionssache. Es kommt einfach auf die Anforderung an. Auf jeden Fall würde ich versuchen Outerjoins zu verhindern. Dein Datenmodell hat mind. einen Fehler, die PLZ kann nicht zum …

Hallo Frank,

zum Datenmodell alles Definitionssache. Es kommt einfach auf
die Anforderung an.

Ja, die kennen wir aber beide nicht, deswegen lässt es sich ja auch so schön theoretisieren.

Auf jeden Fall würde ich versuchen
Outerjoins zu verhindern.

Das wiederholst Du zwar gebetsmühlenartig, richtig wird es dadurch aber immer noch nicht.

Dein Datenmodell hat mind. einen
Fehler, die PLZ kann nicht zum Viertel gehören, da(in
Deutschland) nicht mal Strassen die gleiche PLZ haben müssen.

Mein Datenmodell war der Versuch, da sinnvolle Spalten zu verwenden anstatt col1 bis coln. Damit merkt man dann nämlich auch schon, dass das Deinige - vorsichtig formuliert - seltsam ist.

Zum Thema Geschwindigkeit. 6/100 bei einem
Durchlauf, lass das mal 100.000 mal passieren, da sind das
schon 1,6 Stunden(Bei ner Million Druchläufe = 16h).

Bei einer Million durchläufe braucht dein Datenmodell
0,29s x 1.000.000 = 290.000s = 80h 33’ 20",
das meine braucht
0,35s * 1.000.000 = 350.000s = 97h 13’ 20".
Der Unterschied beträgt also 16h 40 Minuten. Angenommen das sind deine 100 Anwender, und jeder von ihnen macht die (ganz nebenbei so völlig sinnlose) Abfrage täglich zehn Mal, dann brauchen wir für diese Million Abfragen 1000 Arbeitstage, das sind rund 5 Jahre. Wir verlieren also in 5 Jahren 2 Manntage. Ich hoffe nur von den 100 Mannen ist nie einer krank, sonst ist Deine Zeitersparnis beim Teufel…

Ich habe am Anfang von Sekunden gesprochen, nicht von
Stunden… ^^
Wenn das auf einer Produktionsumgebung laufen muss und 100
Leute nicht arbeiten können ergibt das 1600Std. =
1PersonenJahr. Ups…, da sind deine 6/100Sek objektiv
sehr teuer(bei 50Euro/h = 80.000Euro).

Erstens ist - wie soeben gezeigt - dein Ansatz falsch , zweitens dein Ergebnis und drittens ist dein Beispiel-SELECT Blödsinn.

Um auf die 2 Manntage zurückzukommen: Demgegenüber steht ein Angestellter, der das warten muss. Wie schon einmal angefragt: Schreib mir den SELECT hin, der dir alle Personen in Gebäude 4711 liefert. Meiner sieht so aus:

SELECT \* FROM person WHERE gebaeude\_id=4711;

und Deiner? Ich hoffe Du kannst garantieren, dass die Wartung des Programms diese 2 Tage nicht wieder verschlingt. Immerhin arbeitet ja an einem Programm, in dem 100 Anwender werken, normalerweise mehr als ein Programmierer.

Dazu kommt noch das deine Applikation viel komplizierter zu
programmieren ist(Datenpflege). Und Anforderungen, wie neue
Wohnobjekte(Strasse), sind nur durch Datenmodelländerung
machbar.

Noch einmal: Die Zusammenlegung von Viertel und Gebäude stammt von Dir, nicht vom OP. Mit dem gleichen Recht könnte ich also behaupten, es wäre wesentlich sinnvoller, Personen auch noch in die Tabelle reinzunehmen, immerhin brauche ich dann gar keinen Join mehr, was nicht nur schneller als ein Outer Join, sondern zweifelsohne auch schneller als ein Inner Join ist.

Es muss 'ne bessere Lösung geben, vielleicht ist meine nicht
optimal, aber doch schneller und einfacher zu programmieren.

Auch das wieder eine Behauptung, die Du nicht müde wirst zu wiederholen, ohne dass das dadurch richtiger wird.

Massenänderungen sind viel schneller. Egal wie schön dein
Datenmodell ist, es ist zu teuer.

Egal wie schnell Dein Datenmodell ist, es ist falsch und nicht wartbar (und damit zu teuer).

Gruß
Martin

0 Hilfreich
Antwort auf TheBeastzum Beitrag ↑

Hallo Frank, zum Datenmodell alles Definitionssache. Es kommt einfach auf die Anforderung an. Ja, die kennen wir aber beide nicht, deswegen lässt es sich ja auch so schön theoretisieren. Auf jeden Fall würde ich versuche …

Hallo Martin,
das ich mich wiederhole liegt einfach da dran, dass du mir nicht das Gegenteil beweisen kannst und du einfach nur falsch liegst.

Bei dem 1Mio updates habe ich eine Massendatenänderung(z.B. während einer Migration) gemeint und nicht den Tagesbetrieb.
Aber auch im Tagesbetrieb sind diese 6/100 nicht zu verachten, wenn du Rechnenleistungs zu verschenken hast, schön(ich nicht).

Mein select würde auch nicht anders aussehen, wieso kommst du auf eine solche Idee?
SELECT * FROM person WHERE wohn_id=4711 (ggf. noch and wohn_typ=‚G‘)
Hier liegst du falsch. Aus meiner Zeit als DatawareHouse-Admin kenne ich fast nur solche Abfragen:

"select person,name,… FROM person p, wohnobject w where p.wohn_id = w.id and w.material in (‚Beton‘,‚Kalksand‘); "

Dort ist niemand auf die Idee gekommen nach ID’s zu fragen.

Wie sieht das Statement bei dir aus? Auf jedenfall wird dein Statement nicht schneller sein als meins, das haben wir mit den 6/100Sek schon bewiesen.
…

Klar im OP ist das Datenmodell vorgegeben worden, das heist aber nicht, das dieses unumstößlich ist (man kann auch mal über den Tellerrand schauen/auch Kunden irren). Also was passiert mit den Strassen? Und wie sehen deine SQL-Statements dann aus? Sind diese dann immer noch 6/100 langsamer, oder sind das schon mehreren Zentel.

Mein Datenmodell besteht aus zwei Tabellen, deins aus drei (mit Strassen 4, mit … 5,…6,…7…8). Wo ist da die Wartbarkeit schlechter?
Du stellst eine Behaubtung auf(die nicht stimmt) ohne diese zu beweisen(weil nicht beweisbar).

Noch mal die Strassen:
Bei mir gibts ein neues Kennzeichen (ggf in einer Kennzeichentabelle) für Strassen und schon können diese erfasst werden.
(da ich von vornherrein schon mit einem Self-Join arbeiten wollte, brauch ich nicht mal Druckprogramme anpassen, diese arbeiten rekursiv ^^)

Bei dir gibts erst mal 'ne neue Tabelle,

  • dann müssen alle SQL-Statements angepasst werden(die das alte Datenmodell verwenden),
  • dann die Erfassungs- und Abfragemasken für Strassen generiert (und in das Anwendermenü eingebaut werden),
  • dann müss die PersonenPflege angepasst werden (neue LOV , neues Feld, neue Label, …)
  • hoffentlich müssen wir die Daten nicht im DatawareHouse abfragen, weil wir dann auch noch Schnittstellen anpassen müssen und
  • im DWH dann auch noch die Mappings und das Starschema, sowie im Abfrage- und Admintool die Joins.
  • boah 5 Personentage?

Deine Behauptung, das dein Datenmodell einfacher(zu warten) ist stimmt nicht.
Es ist der falsche Weg…, aber das werde ich noch viele Male wiederholen müssen bis du das einsiehst. *grins*

cu Frank

0 Hilfreich

Deine Antwort

Zum Antworten bitte anmelden oder kostenlos registrieren.