[PLSQL] Gibt es ein "Select oder Insert"- Befehl?

[HeZa]

Zitat von sirius:

Ich schreib halt immer wieder die selbe Befehlssequenz die da besteht aus

• Ermitteln ob der Datanesatz schon vorhanden ist
• Wenn ja, dann PK holen
• Wenn nein dann Insert probieren
• falls Insert fehl schlägt (Exception abfangen) dann PK von dem inzwischen eigentragenen Datensatz holen

...

Ich wollt hier nur einmal wissen, wie andere Datenbanknutzer in ihren Projekten damit umgegangen sind (ich bin ja hoffentlich nicht der erste) und ob es zufällig sogar einen Befehl gibt in Oracle.

...

Danke Euch allen für Eure Beiträge. Aber ich befürchte langsam, dass ich der einzige bin, der ständig über dieses Problem stolpert.

Du bist bestimmt nicht der erste der dieses Muster verwendet. Du scheinst es aber als eine Art Standard Muster zu verwenden und das ist es nicht. Wir können uns natürlich nur auf dieses eine Beispiel beziehen. Ich bin mir fast sicher, das kein anderer hier die Sache so wie du lösen würde.

Die Tabelle DBT_ZEIT macht einen kranken Eindruck. Sie hat einen eindeutigen Schlüssel (Datum, Stunde) und du gibst ihr noch einen künstlichen warum? Warum überhaupt eine Tabelle DBT_ZEIT? Trage Datum und Stunde in DBT_Traffic ein und nimm sie mit in den Primary Key auf. Warum ein Datumsfeld und ein Stundenfeld wenn die Stunde doch mit in das Datumsfeld passt?

Übernehme nur das Datumsfeld in DBT_TRAFFIC (lösche ID_ZEIT) und fülle es mit ROUND(<Datum>, 'HH'). Das würde deine Inserts erheblich beschleunigen und deine SP würde nur aus dem MERGE und dem Signal senden bestehen.

Ich weiß ja nichts über die Update-Häufigkeit und die Datenmenge. Mein erster Ansatz wäre erstmal jeden Traffic-Datensatz mit dem genauen Zeitpunkt zuspeichern. Verdichtung würde ich erst für die Auswertung.

Für mich sieht deine Lösung etwas exotisch aus. Bring doch mal eine der anderen Stellen wo dieses Muster verwendest. Vielleicht kann man ja auch dort aufzeigen wie es einfacher, sicherer und schneller geht.

[sirius]

Zitat von HeZa:

Für mich sieht deine Lösung etwas exotisch aus. Bring doch mal eine der anderen Stellen wo dieses Muster verwendest. Vielleicht kann man ja auch dort aufzeigen wie es einfacher, sicherer und schneller geht.

Ok.

SQL-Code:

--  Die Tabelle dbt_Channel führt verschiedene IDs (alles FK) aus anderen Tabellen zusammen zu einem neuen einzigen Key.
-- Diese FKs bilden einen unique index (in anderen Varianten gab es noch zusätzliche Spalten, welche außerhalb jedweden Indexes lagen)
-- der PK (Spaltenname: ID), der hier in dbt_Channel gebildet wird, wird in verschiedenen Messwerttabellen verwendet, welche mit Abstand dann auch die größten Tabellen sind.

    Select count(ID) into c from dbt_channel 
     where ID_Characteristic=CharacteristicID
     and ID_BaseIndicator=BaseIndicatorID
     and ID_Indicator=IndicatorID
     and ID_Phase=PhaseID
     and ID_Unit=UnitID
     and ID_Interval=IntervalID
     and Frequency=aFrequency;
    if c>0 then
      if aDoInsert then
        RaiseAlreadyExist('Channel');
      end if;
    else
      if not aDoInsert then
        RaiseNotExist('Channel');
      end if;
      begin
        insert into dbt_Channel (Id_Characteristic,Id_Phase,Id_Indicator,
                               id_Unit,Id_Interval,Frequency,Id_Baseindicator)
            values (CharacteristicID,
                    PhaseID,
                    IndicatorID,
                    UnitID,
                    IntervalID, 
                    aFrequency,
                    BaseIndicatorID)

            returning ID into ChannelID;    
        commit;
      exception
        when dup_val_on_index then
          c:=1;
        when others then
          raise;
      end;  
    end if;
    if c>0 then
      Select ID into ChannelID from dbt_channel 
       where ID_Characteristic=CharacteristicID
       and ID_BaseIndicator=BaseIndicatorID
       and ID_Indicator=IndicatorID
       and ID_Phase=PhaseID
       and ID_Unit=UnitID
       and ID_Interval=IntervalID
       and Frequency=aFrequency;
    end if;  
    return(ChannelID);


   -- Die Reaktion auf aDoInsert ist hier nochmal gesondert zu sehen und muss nicht zwingend hier beachtet werden

[HeZa]

Ok, ich habe es so gewollt .

Dann bitte aber auch noch die Beschreibung dieser Channel Tabelle und deren ungefähre Update-Häufigkeit (grob 10 pro Stunde oder tausende pro Minute oder gar Sekunde?).

[sirius]

SQL-Code:

prompt
prompt Creating table DBT_CHANNEL
prompt ==========================
prompt
create table DBT_CHANNEL
(
  ID                NUMBER not null,
  ID_BASINDICATOR   NUMBER not null,
  ID_INDICATOR      NUMBER not null,
  ID_PHASE          NUMBER not null,
  ID_UNIT           NUMBER not null,
  ID_INTERVAL       NUMBER not null,
  FREQUENCY         NUMBER(*,2),
  ID_CHARACTERISTIC NUMBER not null
)
;
alter table DBT_CHANNEL
  add constraint DBT_CHANNEL_PK primary key (ID);
alter table DBT_CHANNEL
  add constraint DBT_CHANNEL_UK1 unique (ID_BASINDICATOR, ID_INDICATOR, ID_PHASE, ID_UNIT, ID_INTERVAL, FREQUENCY, ID_CHARACTERISTIC);
alter table DBT_CHANNEL
  add constraint DBT_CHANNEL_DBT_BASEINDIC_FK1 foreign key (ID_BASINDICATOR)
  references DBT_BASEINDICATOR (ID) on delete cascade;
alter table DBT_CHANNEL
  add constraint DBT_CHANNEL_DBT_CHARACTER_FK1 foreign key (ID_CHARACTERISTIC)
  references DBT_CHARACTERISTIC (ID);
alter table DBT_CHANNEL
  add constraint DBT_CHANNEL_DBT_INDICATOR_FK1 foreign key (ID_INDICATOR)
  references DBT_INDICATOR (ID) on delete cascade;
alter table DBT_CHANNEL
  add constraint DBT_CHANNEL_DBT_INTERVAL_FK1 foreign key (ID_INTERVAL)
  references DBT_INTERVAL (ID) on delete cascade;
alter table DBT_CHANNEL
  add constraint DBT_CHANNEL_DBT_PHASE_FK1 foreign key (ID_PHASE)
  references DBT_PHASE (ID) on delete cascade;
alter table DBT_CHANNEL
  add constraint DBT_CHANNEL_DBT_UNIT_FK1 foreign key (ID_UNIT)
  references DBT_UNIT (ID) on delete cascade;

Auf dem PK ID liegt wieder eine Sequence und ein Trigger (wie bei dbt_Zeit)

Ein neuer Eintrag kommt recht unregelmäßig. Zeit spielt hier nicht die oberste Rolle.

Zitat von HeZa:

Ok, ich habe es so gewollt .

Ich hab das auch noch mit dynamischen SQL gelöst, welches mehrere solche Tabellen mit ähnlicher Tabellenstruktur (von den "wichtigen" Spalten her) bedient.

Und bitte wegrationalisiere mir meine ausnormalisierte Tabelle hier nicht weg
Es ist durchaus möglich, dass hier noch Spalten mit Zusatzinformationen ergänz werden

[HeZa]

Hallo Sirius,

hier ein Entwurf. Da mir der Context fehlt kann ich natürlich nichts testen. Was mir besser gefällt ist, das im Fall, wenn die Channel-Daten schon gespeichert wurden nur einmal die Tabelle dbt_channel abgefragt wird. Der Rest beruht eher darauf, das ich keine Sorgen bei der Verwendung von Exceptions habe, aber vielleicht fehlen mir da auch nur die schlechten Erfahrungen.

SQL-Code:

BEGIN
  LOOP
    BEGIN
      -- ChannelID suchen
      select ID into ChannelID from 
        dbt_channel
      where 
        ID_Characteristic=CharacteristicID
        and ID_BaseIndicator=BaseIndicatorID
        and ID_Indicator=IndicatorID
        and ID_Phase=PhaseID
        and ID_Unit=UnitID
        and ID_Interval=IntervalID
        and Frequency=aFrequency;
        
      if aDoInsert THEN
        -- existiert, sollte aber nicht
        RaiseAlreadyExist('Channel');
      end if;
     
      -- ansonsten sind wir fertig    
      RETURN ChannelID;  
    EXCEPTION
      WHEN NO_DATA_FOUND THEN    
        -- Channel-Daten noch nicht angelegt
        
        if not aDoInsert THEN
          -- sollten aber angelegt sein 
          RaiseNotExist('Channel');
        end if;
        
        BEGIN
          insert into dbt_Channel (
            Id_Characteristic, Id_Phase, Id_Indicator, id_Unit, Id_Interval, Frequency,  Id_Baseindicator
          ) values (
            CharacteristicID,  PhaseID,  IndicatorID,  UnitID,  IntervalID,  aFrequency, BaseIndicatorID
          ) returning ID into ChannelID;
          commit;
          
          RETURN ChannelID;
        EXCEPTION 
          when dup_val_on_index then
            NULL; -- den Datensatz gibt es doch schon, alles von vorne
        END;    
    END;
  END LOOP;
END;

Da ich nicht weiß wie du ChannelID verwendest, bleibt der Verdacht, dass auch diese Tabelle überflüssig seinen könnte. Diese Tabelle scheint mir wie die DBT_ZEIT Tabelle eine Gruppierung darzustellen, die man auch aus den Daten gewinnen könnte wenn man statt der ChannelID die sieben anderen Werte abspeichern würde. Aber ich weiß natürlich nicht wie viele Datensätze anderer Tabellen von der ChannelID abhängen oder wie teuer es ist, die Gruppierung live aus den von ChannelID abhängigen Daten zu ziehen.

[sirius]

Sorum habe ich das auch schon einmal aufgezogen. Dadurch wird halt im Normalfall nur ein Select gemacht, und in den meisten Fällen ist das auch ausreichend, denn der entsprechende Datensatz existiert ja nur einmal nicht, ab dann existiert er ja immer und wird auch recht häufig verlangt.

Ich hatte irgendwann dann auf die jetzige Variante umgestellt, weil...tja schon lang her. Exceptions brauchen auf jeden Fall viel Zeit, aber wie das im Vergleich zu einer vollständigen Abfrage ist, weiß ich nicht. Letztenendes spielt es hier keine wichtige Rolle, da in diesem Fall relativ selten drauf zugrgriffen wird (für Datenbankverhältnisse "selten").

Die ID_Channel wird in (aktuell) drei weiteren Datenbanktabellen verwendet. Diese Tabellen werden sehr groß. Derzeit sind dort die Spalten ID_Channel, ID_Measurement, UTCDate + TimeShift, Value (Messwert) enthalten. Man hat also einen Kosten/Nutzenfaktor von ca 1 zu 3 (wenn man mal die Größe der Spalten vernachlässigt). Wenn ich jetzt alle IDs aus dbt_Channel speichern müsste, würde dieser Faktor bedeutend schlechter (siehe auch Normalisierung von Datenbanktabellen). Hinzu kommt, dass ich zu den Channels noch Zusatzinformationen ergänzen werde (zukünftig). Aktuell würde ich sie einfach als neue Spalte in dbt_Channel legen.

Diese IF-Verzweigung mit aDoInsert kannst du weglassen. Das war jetzt nur noch hier mit drinn.


Edit (nochmal zu einem anderen Beitrag):

Zitat von HeZa:

Übernehme nur das Datumsfeld in DBT_TRAFFIC (lösche ID_ZEIT) und fülle es mit ROUND(<Datum>, 'HH').

Würde das nicht bedeuten, dass jedwede Art der Normalisierung nicht gemacht werden soll. Du löst ja meine untergeordneten Tabellen auf (auch ein Vorschlag bei dbt_Channel). Dann haben wir am Ende immer nur eine Tabelle mit allen Angaben.
Deswegen kann ich es nochmal auf den Punkt bringen: Alle PK-FK-Kombinationen müssen doch dieses Problem haben. Was macht man da?

[HeZa]

Zitat von sirius:

Edit (nochmal zu einem anderen Beitrag):

Zitat von HeZa:

Übernehme nur das Datumsfeld in DBT_TRAFFIC (lösche ID_ZEIT) und fülle es mit ROUND(<Datum>, 'HH').

Würde das nicht bedeuten, dass jedwede Art der Normalisierung nicht gemacht werden soll. Du löst ja meine untergeordneten Tabellen auf (auch ein Vorschlag bei dbt_Channel). Dann haben wir am Ende immer nur eine Tabelle mit allen Angaben.
Deswegen kann ich es nochmal auf den Punkt bringen: Alle PK-FK-Kombinationen müssen doch dieses Problem haben. Was macht man da?

Eine untergeordnete Tabelle einzuführen ist nicht zwangsläufig mit Normalisierung gleich zu setzen.

DBT_CHANNEL und DBT_ZEIT bestehen nur aus dem Primarschlüssel und haben damit eigentlich keine Daseinsberechtigung. Es gibt keine Spalten in diesen Tabellen die von diesen Daten abhängen. Dies verschleierst du ein wenig in dem du für den natürlichen zusammengesetzten Schlüssel, einen weiteren künstlichen vergibst.

Bei DBT_ZEIT ist das besonders deutlich zu sehen. Datum und Stunde sind der eigentliche Schlüssel der Tabelle. ID_ZEIT wird noch dazu erfunden. Datum und Stunde lassen sich mit Round(<DateTime>, 'HH') einfach in einem Date-Feld speichern. Du hast also eine Tabelle mit 2 Feldern die beide Unique sind und das eine Feld ID_Zeit wird nur gebraucht um auf den anderen Wert zu verweisen.

Speicherst du den gerundeten DateTime-Wert gleich in der Tabelle DBT_TRAFFIC wird alles einfacher.

statt

SQL-Code:

select * from 
  dbt_traffic t
  inner join dbt_zeit z on 
    z.id_zeit = t.id_zeit and z.datum between date '2010-01-01' and date '2010-03-31'

schreibst du dann

SQL-Code:

select * from 
  dbt_traffic t
where 
  t.DatumStd between date '2010-01-01' and date '2010-03-31 23:23:59'

und bei einem insert in die DBT_TRAFFIC Tabelle musst du DatumStd auf Round(sysdate, 'HH') setzen.

Du würdest doch auch nicht

SQL-Code:

create table LEUTE
  Nachname varchar(30),
  Vorname varchar(30)
);

in die folgenden Tabellen aufteilen

SQL-Code:

create table LEUTE
  Nachname varchar(30),
  VornameID number(10)
);

create table VORNAMEN
  VornameID number(10),
  Vorname varchar(30)
);

Oder?

Für DBT_CHANNEL mag es praktische Gründe geben, z.B. ist es immer nervig wenn man, um zwei Tabellen zu joinen immer 7 Felder angeben musst, aber das kann ich gar nicht beurteilen, da ich nicht ersehen kann was mit der ChannelID angestellt wird.

[sirius]

Was du sagst ist ja richtig, und für dbt_Zeit im jetzigen Zustand auch i.O. Aber stell dir mal vor, wenn ich demnächst zu einer bestimmten Stunde noch einen Eintrag machen will, bspw. ein externes Ereignis, irgendetwas beschreibendes. Dies wäre bei deiner gekürzten Variante nicht möglich bzw. würde IMHO die 2NF verletzen. (das war übrigens ein kleines Projekt nur für meinen Rechner, welches mir nur kurz Informationen über bestimmte Verbindungen sammeln soll; wann welche Verbindung wie viel Traffic pro Stunde erzeugt; das Programm ist quasi im Fließen und eine Erweiterung zu gewissen Informationen je Stunde war mir schon im Kopf)

Bei dbt_Channel wäre deine Variante sehr umständlich (zumal auch wieder das Prblem der Erweiterung steht). Wie gesagt, die ID aus dbt_Channel wird als FK in derzeit 3 weiteren Tabellen verwendet. Dies sieht folgendermaßen aus

• ID_Channel (FK)
• OrtsID (FK)
• Zeitangabe
• Messwert
• ... je nach Tabelle

Und diese Tabelle wird sehr groß. Zu etwa 30 verschiedenen (gleichzeitig verwendeten) OrtsID, gibt es ca. 200 verschiedene (und gleichzeitig verwendete) ID_Channel. Zu jeder Kombination werden mindestens jede Minute und zusätzlich noch aller 10 Minuten und, bei einer geringen Teilmenge an ID_Channel, jede Sekunde Messwerte abgespeichert.
Du siehst, ich habe ca. 30 Messgeräte welche ca. 200 verschiedene Messwerte ausspucken (grob geschätzt). Die Messwertbezeichnungen (ID_Channel) können im Laufe der Projektlaufzeit (Jahre) sich immer wieder ändern.
Jetzt alle IDs in die MEsswerttabellen zu legen würde ja die Datenbank sprengen.
Zumal sich das Problem ja nur verschiebt, denn die dbt_Channel fasst ja wieder nur andere IDS zusammen (deren Erstellung das selbe Problem haben). Es wird dann also quasi rekursiv noch größer.


Fazit: Meine Ursprungsidee für den Thread, war es herauszufinden, ob es einen einzigen Befehl für mein Problem gibt (ähnlich Merge into). Dies scheint nicht der Fall zu sein.