ich möchte gerne Metadatentabellen sortieren, welche von anderen Tabellen als Fremdschlüssel referenziert werden.
Als Beispiel:
Tabelle: people
id name
1 Paul
2 Jack
3 Heinz
Tabelle: people_money
id nid money
1 3 1000
2 1 800
3 2 900
Wobei die Spalten ‘id’ in beiden Tabelle Primärschlüssel sind und ‘nid’ in der Tabelle ‘people_money’ ein Fremdschlüssel ist, der auf ‘id’ aus der Tabelle ‘people’ referenziert. Dabei gelten die Constraints ON UPDATE - CASCADE und ON DELETE - RESTRICT.
Mein Frage: ich möchte, dass die Tabelle ‘people’ aufsteigend nach der ‘name’ Spalte sortiert wird und so die Namen dann auch die aufsteigenden IDs je nach ihrer neuen Position in der Sortierung erhalten. Durch das ON UPDATE - CASCADE sollten sich dabei die ID’s in der ‘people_money’ Tabelle mit ändern.
Wie kann man das machen und funktioniert das auch wirklich so durch die Constraints?
Aber welche Auswirkung hat denn dann das ON UPDATE - CASCADE? Oder besser, in welchem Fall hat dieses eine Auswirkung in der Tabelle, welche den Fremdschlüssel besitzt?
Okay … bei einem Update … wie in dem Schema oben zu erkennen, referenziert die ‘nid’ auf die ‘id’ Spalte aus der ‘people’ Tabelle. Wenn ich nun zwei Einträge in der ‘people’ Tabelle ändere, also z.B. den Namen, der unter ‘id’ 1 steht in die Zelle schreibe, welche ‘id’ 2 hat und umgekehrt, dann muss sich ja auch die ‘id’ in der referenzierten Tabelle ändern, da ja der eigentliche Name nun unter einer anderen ‘id’ steht. Das wäre ein Update.
Ich wiederhole meine Frage und stelle eine weitere: was willst Du machen, wenn ein neuer Eintrag dazu kommt, ich sage mal ‘Andreas’? Willst Du den dann wieder ‘vorne’ einsortieren und alle Keys ändern?
Oder um es kurz zu sagen: Datenbanken funktionieren anders. Buche doch ein Kurs bei ads
Also … das ist ein DWH (Data Warehouse) System, welches Millionen von Einträge enthält … nun funktionieren die Analysen so, dass die ID’s der Metadaten zurückgegeben werden und das Frontend holt sich über eine spezielle Methode dann die eigentlichen Namen der Metadaten, welche zu den IDs gehören … Da man nun also die eigentlichen Name nicht sortieren kann, sollen sie schon im DWH in den Metadatentabellen sortiert vorliegen … und JA … diese Prozedur wird jedesmal neu ausgeführt, wenn neue Daten dazukommen … dafür sind die DWH Server ausgelegt … wie DB funktionieren ist in unserer Firma bekannt, doch kann ich den Umstand, dass Millionen von Daten nicht an das Frontend zurückgegen werden sollen nicht ändern … durch die Rückgabe der IDs wird erhebliche Bandbreite beim Schicken der Daten aus dem DWH zum Frontend eingesparrt und es werden nur die Namen zu den IDs angefordert, welche auch wirklich angezeigt werden … da die Analysen intern mit den IDs arbeiten, werden so die korrekten Sortierungen zurückgegeben, wenn die Namen hinter den IDs schon in der korrekten Sortierung vorliegen …
Es geht hier nicht darum, das Schema der DB zu ändern … Wenn man mit einem System arbeitet, welches nun einmal so konzipiert worden ist, dann muss man auch mal versuchen, aus den gegebenen Tatsachen das Beste zu machen. So läufts nun einmal in der freien Wirtschaft. Und das ich und die meisten hier nun einmal in der Uni was anderes gelernt haben, okay, das ist nun mal so. Aber erzähle das mal dem, der deinen Scheck bezahlt.
Das ordnet die Rows in einer Tabelle physisch nach der Reihenfolge eines Index. Das ist aber eine “einmal” Aktion, und muss letztendlich nach jedem Laden bzw. Ändern der Daten wiederholt werden.
Ob das praktikabel ist in eurer Umgebung musst Du selber rausfinden.
Übrigens: was sollen eigentlich die ganzen … in Deinem Posting? Das verwirrt mich etwas.
Normalerweise nimmt man das her um zu zeigen, dass man Text weggelassen hat
CLUSTER hilft dabei gar nichts. Das ordnet zwar die Indexdaten um, aber:
a) ist diese Sortierung nicht permanent sondern verflüchtigt sich bei nachfolgenden Änderungen wieder
und
b) betrifft das nur die physikalische Anordnung der Daten auf der Festplatte. Das hat mit der eigentlichen Ausgabe überhaupt nichts zu tun.
So, zurück zum Problem:
Sortieren tust du deine Daten bei der Ausgabe. Updaten tust du sie beim Aktualisieren. Ich sehe jetzt nicht ganz, wo es da ein Problem gibt - außer, dass ein SELECT nun mal kein UPDATE ist.
Wenn du sagst, dass DB-Erfahrung bei euch in der Firma vorhanden ist, dann zählt so etwas aber zu den absoluten Grundlagen. Und ein Data Warehouse System erfordert einige Vorarbeiten in einer relationalen Datenbank. Wenn man wie ihr optimieren möchte, gibt es sicher einige wege, aber das tut man im Detail analysieren und sich passende Lösungen dafür schreiben.
b) betrifft das nur die physikalische Anordnung der Daten auf der Festplatte. Das hat mit der eigentlichen Ausgabe überhaupt nichts zu tun.
Würde das nicht dazu führen, dass ein SELECT ohne ORDER BY (seq scan) die Datensätze in der “physischen” Reihenfolge zurückliefert und damit in der Reihenfolge des Index?
So hatte ich zumindest die (etwas diffuse) Eingangsfrage verstanden.
Nein. Nur unter ganz - mittlerweile muss man “obskur” sagen - Bedingungen.
Die Daten werden ohne ORDER BY in der Reihenfolge ausgegeben, in der sie gelesen werden. Nun ändert aber jedes UPDATE unter Umständen die Position des Eintrags in der Tabelle.
Außerdem gibt es seit einer Weile paralelle Tabellenscans: wenn ein Prozess eine Tabelle liest und ein zweiter will auch etwas aus dieser Tabelle, dann fängt der zweite keineswegs von vorne an. Statt dessen klinkt er sich in den ersten Prozess ein und bekommt die Daten ab dort, wo der erste Prozess gerade ist. Wenn der erste fertig ist, liest der zweite nur noch das Stück (am Anfang), dass ihm noch fehlt.
Die Ausgabe dieser Daten ist dann aber komplett unabhängig von der Reihenfolge auf der Festplatte.
Allgemein bleibt festzuhalten: per Definition ist die Anordnung der Daten bei einem fehlenden ORDER BY nicht deterministisch.
Deswegen meinte ich ja auch, dass das CLUSTER nach jedem Laden der Tabelle (bzw. nach jeder Änderung) neu ausgeführt werden muss.
Außerdem gibt es seit einer Weile paralelle Tabellenscans: wenn ein Prozess eine Tabelle liest und ein zweiter will auch etwas aus dieser Tabelle, dann fängt der zweite keineswegs von vorne an. Statt dessen klinkt er sich in den ersten Prozess ein und bekommt die Daten ab dort, wo der erste Prozess gerade ist. Wenn der erste fertig ist, liest der zweite nur noch das Stück (am Anfang), dass ihm noch fehlt.
Ah, das mit den parallelen Scans wusste ich noch nicht. Wie lange gibt es das schon?
Auf einem DB2 “new features” Seminar wurde das dort auch als neues Feature vorgestellt…
Irgendwie haben doch alle die gleichen Ideen
Allgemein bleibt festzuhalten: per Definition ist die Anordnung der Daten bei einem fehlenden ORDER BY nicht deterministisch.
Das CLUSTER würde dann unter Umständen (!) das ORDER BY beschleunigen, oder?
Nein, definitiv nicht, keine gute Idee. Um es kurz zu machen: CLUSTER ist hier kein Mittel, dass auch nur ansatzweise zu einer Lösung führt.
Ah, das mit den parallelen Scans wusste ich noch nicht. Wie lange gibt es das schon?
8.3, glaube ich.
Das CLUSTER würde dann unter Umständen (!) das ORDER BY beschleunigen, oder?
Nicht wirklich.
Es würde dafür sorgen, dass die Daten im Speicher schon (fast oder ganz) richtig sortiert vorliegen, wenn sie im besten Fall passend eingelesen wurden. Sprich: es sind weniger Sortierzyklen notwendig. Aber die sind im Gegensatz zu den I/O-Operationen so billig, dass sich das nicht nennenswert auswirkt.
Außerdem bleibt das Problem der parallelen Scans: die verwerfen dann wieder alles und es muß alles neu sortiert werden.
8.3. Seit 8.4 nutzt PG z.B. Hasches, um einen UNION zu beschleunigen. Beispiel:
test=# create table a (i int);
CREATE TABLE
test=*# create table b (i int);
CREATE TABLE
test=*# insert into a values (1);
INSERT 0 1
test=*# insert into a values (2);
INSERT 0 1
test=*# insert into a values (3);
INSERT 0 1
test=*# insert into b values (1);
INSERT 0 1
test=*# insert into b values (2);
INSERT 0 1
test=*# commit;
COMMIT
test=# select * from a union select * from b;
So, was erwartest Du als Resultat? Wird es sortiert sein? Probier es mal mit etwas kleinerem als 8.4, denn in 8.4 passiert dies:
test=# select * from a union select * from b;
i
---
3
1
2
(3 rows)
Merke: man kann und darf sich nicht auf eine Reihenfolge verlassen, Datenbanken sind Mengenorientiert und diese nicht sortiert.