Datenbankdesign: Ersetze eine boolesche Spalte durch eine Zeitstempelspalte?

Es gibt eine richtige Möglichkeit, Nullwerte zu indizieren, und es wird kein FBI verwendet. Oracle wird Nullwerte indexieren, aber es werden keine LEAF-Werte in der Struktur indexiert. Also, Sie könnten die Spalte is_finished eliminieren und den Index so erstellen.

Wenn Sie dann den EXPLAIN-Plan für diese Abfrage überprüfen:

Möglicherweise wird der Index verwendet (wenn der Optimierer zufrieden ist).

Zurück zur ursprünglichen Frage: Wenn ich mir die verschiedenen Antworten hier ansehe, denke ich, dass es keine richtige Antwort gibt. Ich mag eine persönliche Vorliebe haben, eine Spalte zu entfernen, wenn es unnötig ist, aber ich mag es auch nicht, die Bedeutung von Spalten zu überladen. Es gibt zwei Konzepte hier:

1. Die Aufzeichnung ist beendet. %Code%
2. Der Datensatz wurde an einem bestimmten Datum beendet. %Code%

Vielleicht müssen Sie diese getrennt halten, vielleicht nicht. Wenn ich daran denke, die Spalte is_finished zu entfernen, stört mich das. Auf der Straße kann die Situation entstehen, wo der Rekord beendet ist, aber Sie wissen nicht genau wann. Vielleicht müssen Sie Daten aus einer anderen Quelle importieren und das Datum ist unbekannt. Sicher, das ist jetzt nicht in den Geschäftsanforderungen, aber die Dinge ändern sich. Was machst du dann? Nun, Sie müssen einen Dummy-Wert in die Spalte date_finished setzen, und jetzt haben Sie die Daten ein wenig kompromittiert. Nicht fürchterlich, aber da ist etwas los. Die kleine Stimme in meinem Kopf schreit DU TUT ES FALSCH wenn ich solche Dinge mache.

Mein Rat, halte es getrennt. Du sprichst von einer winzigen Säule und einem sehr dünnen Index. Speicher sollte hier kein Problem sein.

  

Regel der Repräsentation: Falten Sie Wissen   in Daten so kann Programmlogik sein   dumm und robust.

     

-Eric S. Raymond

  

Ist es eine gute oder schlechte Idee?

Gute Idee.

Sie haben den von einer redundanten Spalte belegten Speicherplatz eliminiert. Die Spalte DATUM dient der doppelten Aufgabe - Sie wissen, dass die Arbeit abgeschlossen wurde, und wenn .

  

Ich habe gehört, dass Sie keinen Index für eine Spalte mit NULL-Werten haben können. "where data_finished is not null" wird also bei großen Tabellen sehr langsam sein.

Das ist falsch. Oracle-Indizes ignorieren NULL-Werte.

Sie können einen funktionsbasierten Index erstellen, um zu vermeiden, dass die NULL-Werte nicht indexiert werden , aber am häufigsten DBAs, denen ich begegnet bin wirklich mag sie nicht, also sei auf einen Kampf vorbereitet.

  

Ist es eine gute oder schlechte Idee? Ich habe gehört, dass Sie keinen Index für eine Spalte mit NULL-Werten haben können, so dass "where data_finished is not null" bei großen Tabellen sehr langsam ist.

Oracle indiziert Nullwertfelder , indexiert jedoch nicht NULL -Werte

Dies bedeutet, dass Sie einen Index für ein Feld erstellen können, das mit NULL markiert ist, aber die Datensätze, die NULL in diesem Feld enthalten, werden nicht in den Index aufgenommen.

Dies wiederum bedeutet, dass wenn Sie date_finished NULL angeben, der Index weniger groß ist, da die NULL -Werte nicht im Index gespeichert werden.

Die Abfragen, die die Suche nach Gleichheitsbereichen auf date_finished betreffen, werden in der Tat besser abschneiden.

Der Nachteil dieser Lösung besteht natürlich darin, dass die Abfragen, die die NULL -Werte von date_finished enthalten, auf den vollständigen Tabellenscan zurückgesetzt werden müssen.

Sie können dies umgehen, indem Sie zwei Indizes erstellen:

und verwenden Sie diese Abfrage, um nicht abgeschlossene Arbeit zu finden:

Dies wird sich wie ein partitionierter Index verhalten: Die kompletten Werke werden vom ersten Index indiziert; die unvollständigen werden von der Sekunde indiziert.

Wenn Sie nicht nach vollständigen oder unvollständigen Arbeiten suchen müssen, können Sie immer die entsprechenden Indizes loswerden.

An alle, die sagten, dass die Kolumne Platzverschwendung ist:

Double Duty ist keine gute Sache in einer Datenbank. Ihr primäres Ziel sollte Klarheit sein. Viele Systeme, Werkzeuge, Leute benutzen Ihre Daten. Wenn Sie Werte verschleiern, indem Sie Bedeutungen innerhalb anderer Spalten verbergen, BEGEGEN Sie, dass ein anderes System oder ein anderer Benutzer es falsch interpretiert.

Und jeder, der denkt, dass es Platz spart, ist völlig falsch.

Sie benötigen zwei Indizes für diese Datumsspalte ... einer wird wie OMG vorgeschlagen, funktionsbasiert sein. Es sieht so aus:

NVL (Date_finished, TO_DATE ('01-JAN-9999 '))

Um unfertige Jobs zu finden, müssen Sie sicherstellen, dass Sie die where-Klausel richtig schreiben

Es wird so aussehen:

WO NVL (Date_finished, TO_DATE ('01-JAN-9999 ')) = TO_DATE ('01-JAN-9999')

Ja. Das ist so klar. Es ist vollkommen besser als

WO IS_Unfinished = 'JA'

Der Grund, warum Sie einen zweiten Index für dieselbe Spalte haben möchten, ist JEDE ANDERE Abfrage an diesem Datum ... Sie wollen diesen Index nicht verwenden, um Jobs nach Datum zu finden.

Sehen wir uns an, was Sie mit dem Vorschlag von OMG erreicht haben.

Sie haben mehr Platz verbraucht, Sie haben die Bedeutung der Daten verschleiert, Sie haben Fehler wahrscheinlicher gemacht ... GEWINNER!

Irgendwann scheint es, dass Programmierer immer noch in den 70ern leben, wenn ein MB Festplattenplatz eine Anzahlung für ein Haus war.

Sie können dabei platzsparend sein, ohne auf viel Klarheit zu verzichten. Machen Sie das Is_unfinished entweder Y oder NULL ... IF Sie werden nur diese Spalte verwenden, um nach 'zu tun' zu suchen. Dies wird diesen Index kompakt halten. Es wird nur so groß sein wie Reihen, die unvollendet sind (auf diese Weise nutzen Sie die nicht indexierten Nullen aus, anstatt von ihnen geschraubt zu werden). Sie haben ein wenig Platz in Ihrem Tisch, aber vor allem ist es weniger als das FBI. Sie benötigen 1 Byte für die Spalte, und Sie werden nur die unvollendeten Zeilen indexieren, so dass ein kleiner Bruchteil des Jobs und wahrscheinlich ziemlich konstant bleibt. Das FBI benötigt 7 Bytes für JEDE ZEILE, egal ob du sie finden willst oder nicht. Dieser Index wird mit der Größe der Tabelle Schritt halten, nicht nur mit der Größe der nicht abgeschlossenen Jobs.

Antworte auf den Kommentar von OMG

In seinem Kommentar gibt er / sie an, dass Sie, um unfertige Jobs zu finden, einfach

Aber in seiner Antwort sagt er

  

Sie können einen funktionsbasierten Index erstellen, um die NULL-Werte zu umgehen, die nicht indexiert werden

Wenn Sie dem Link folgen, auf den er hinweist, verwenden Sie NVL, um Nullwerte durch einen anderen willkürlichen Wert zu ersetzen. Ich bin mir nicht sicher, was es sonst noch zu erklären gibt.

In Bezug auf das Tabellen-Design finde ich es gut, dass Sie die is_finished -Spalte entfernt haben, da Sie gesagt haben, dass das nicht notwendig ist (es ist überflüssig). Es gibt keine Notwendigkeit, zusätzliche Daten zu speichern, wenn es nicht notwendig ist, es verschwendet nur Platz. In Bezug auf die Leistung sehe ich kein Problem für NULL-Werte. Sie sollten ignoriert werden.

Ich würde Nullen verwenden, da Indizes, wie bereits in anderen Antworten erwähnt, für alle Abfragen außer "WHERE date_finished IS NULL" funktionieren (daher hängt es davon ab, ob Sie diese Abfrage verwenden müssen). Ich würde definitiv keine Ausreißer wie das Jahr 9999 verwenden, wie es die Antwort nahelegt:

  

Sie könnten auch einen "Dummy" -Wert (z. B. den 31. Dezember 9999) als date_finished-Wert für nicht abgeschlossene Workflows

verwenden

Ausreißer wie Jahr 9999 beeinflussen die Leistung, weil (aus Ссылка ):

  

Die Selektivität eines Bereichs-Scans wird grundsätzlich vom CBO als die der Werte im interessierenden Bereich dividiert durch den vollständigen Bereich der möglichen Werte (IE . der maximale Wert minus der minimale Wert)

Wenn Sie einen Wert wie 9999 verwenden, wird der DB denken, dass der Wertebereich, der in dem Feld gespeichert wird, z. 2008-9999 statt der tatsächlichen 2008-2010; Daher scheint jede Bereichsabfrage (z. B. "zwischen 2008 und 2009") nur ein winziges Prozent der möglichen Werte abzudecken, im Gegensatz zu der Hälfte der Bandbreite. Es verwendet diese Statistik, um zu sagen, wenn die% der möglichen möglichen Werte hoch sind, werden wahrscheinlich viele Zeilen übereinstimmen, und dann ist ein vollständiger Tabellenscan schneller als ein Indexscan. Dies wird nicht korrekt durchgeführt, wenn Ausreißer in den Daten vorhanden sind.

gute Idee, die Spalte ableitbarer Wert zu entfernen, wie andere gesagt haben.

Ein weiterer Gedanke ist, dass Sie durch das Entfernen der Spalte paradoxe Bedingungen vermeiden, die Sie codieren müssen, etwa was passiert, wenn das is_finished = No und das finished_date = gestern ... etc.

Alternativ zu einem funktionsbasierten Index können Sie auch einen "Dummy" -Wert (z. B. 31. Dezember 9999 oder alternativ einen Tag vor dem frühesten erwarteten date_finished-Wert) als date_finished-Wert für nicht abgeschlossene Workflows verwenden.

EDIT: Alternativer Dummy-Datumswert, folgende Kommentare.

Ich bevorzuge die Ein-Säulen-Lösung.

In den Datenbanken, die ich am häufigsten verwende, sind jedoch NULL-Werte in den Indizes enthalten. Daher ist der übliche Fall, dass Sie nach offenen Workflows suchen, schnell, in Ihrem Fall jedoch langsamer. Da die Suche nach offenen Workflows wahrscheinlich eine der häufigsten Aufgaben ist, die Sie ausführen, benötigen Sie möglicherweise die redundante Spalte, um diese Suche zu unterstützen.

Testen Sie auf Leistung, um zu sehen, ob Sie die bessere Lösung leistungsmäßig verwenden können, und greifen Sie dann gegebenenfalls auf die weniger gute Lösung zurück.