Ausgleich von Schreib- und Lesevorgängen in der Datenbank

Im vorigen Artikel Ich habe das Konzept und die Implementierung einer Datenbank beschrieben, die auf der Grundlage von Funktionen und nicht auf Tabellen und Feldern wie bei relationalen Datenbanken basiert. Es lieferte viele Beispiele, die die Vorteile dieses Ansatzes gegenüber dem klassischen zeigten. Viele fanden sie nicht überzeugend genug.

In diesem Artikel werde ich zeigen, wie Sie mit diesem Konzept schnell und bequem Schreib- und Lesevorgänge in der Datenbank ausgleichen können, ohne die Betriebslogik zu ändern. Es wurde versucht, ähnliche Funktionen in modernen kommerziellen DBMS (insbesondere Oracle und Microsoft SQL Server) zu implementieren. Am Ende des Artikels werde ich zeigen, dass das, was sie getan haben, gelinde gesagt, nicht sehr gut geklappt hat.

Beschreibung

Zum besseren Verständnis werde ich die Beschreibung wie zuvor mit Beispielen beginnen. Nehmen wir an, wir müssen eine Logik implementieren, die eine Liste der Abteilungen mit der Anzahl der dort beschäftigten Mitarbeiter und ihrem Gesamtgehalt zurückgibt.

In einer funktionsfähigen Datenbank würde es so aussehen:

CLASS Department ‘Отдел’;
name ‘Наименование’ = DATA STRING[100] (Department);

CLASS Employee ‘Сотрудник’;
department ‘Отдел’ = DATA Department (Employee);
salary ‘Зарплата’ =  DATA NUMERIC[10,2] (Employee);

countEmployees ‘Кол-во сотрудников’ (Department d) = 
    GROUP SUM 1 IF department(Employee e) = d;
salarySum ‘Суммарная зарплата’ (Department d) = 
    GROUP SUM salary(Employee e) IF department(e) = d;

SELECT name(Department d), countEmployees(d), salarySum(d);


Die Komplexität der Ausführung dieser Abfrage in einem beliebigen DBMS entspricht O(Anzahl der Mitarbeiter)denn für diese Berechnung muss die gesamte Tabelle der Mitarbeiter gescannt und anschließend nach Abteilungen gruppiert werden. Abhängig vom gewählten Plan wird es auch einen kleinen Zuschlag geben (wir gehen davon aus, dass es viel mehr Mitarbeiter als Abteilungen gibt). O(log Anzahl der Mitarbeiter) oder O(Anzahl der Abteilungen) zum Gruppieren usw.

Es ist klar, dass der Ausführungsaufwand in verschiedenen DBMS unterschiedlich sein kann, die Komplexität wird sich jedoch in keiner Weise ändern.

In der vorgeschlagenen Implementierung generiert das funktionale DBMS eine Unterabfrage, die die erforderlichen Werte für die Abteilung berechnet, und führt dann einen JOIN mit der Abteilungstabelle durch, um den Namen zu erhalten. Allerdings ist es für jede Funktion bei der Deklaration möglich, einen speziellen MATERIALIZED-Marker zu setzen. Für jede dieser Funktionen erstellt das System automatisch ein entsprechendes Feld. Wenn Sie den Wert einer Funktion ändern, ändert sich in derselben Transaktion auch der Wert des Feldes. Beim Zugriff auf diese Funktion wird auf das vorberechnete Feld zugegriffen.

Insbesondere, wenn Sie MATERIALIZED für Funktionen festlegen countMitarbeiter и GehaltSumme, dann werden der Tabelle mit der Abteilungsliste zwei Felder hinzugefügt, in denen die Anzahl der Mitarbeiter und deren Gesamtgehalt gespeichert werden. Bei jeder Änderung der Mitarbeiter, ihrer Gehälter oder Abteilungszugehörigkeiten ändert das System automatisch die Werte dieser Felder. Die obige Abfrage greift direkt auf diese Felder zu und wird in ausgeführt O(Anzahl der Abteilungen).

Welche Einschränkungen gibt es? Nur eines: Eine solche Funktion muss eine endliche Anzahl von Eingabewerten haben, für die ihr Wert definiert ist. Andernfalls ist es unmöglich, eine Tabelle zu erstellen, die alle ihre Werte speichert, da es keine Tabelle mit unendlich vielen Zeilen geben kann.

Beispiel:

employeesCount ‘Количество сотрудников с зарплатой > N’ (Department d, NUMERIC[10,2] N) = 
    GROUP SUM salary(Employee e) IF department(e) = d AND salary(e) > N;


Diese Funktion ist für unendlich viele Werte von N definiert (zum Beispiel ist jeder negative Wert geeignet). Daher können Sie darauf kein MATERIALIZED setzen. Das ist also eine logische und keine technische Einschränkung (das heißt nicht, weil wir sie nicht umsetzen konnten). Ansonsten gibt es keine Einschränkungen. Sie können Gruppierungen, Sortierungen, UND und ODER, PARTITION, Rekursion usw. verwenden.

In Problem 2.2 des vorherigen Artikels können Sie beispielsweise MATERIALIZED auf beide Funktionen anwenden:

bought 'Купил' (Customer c, Product p, INTEGER y) = 
    GROUP SUM sum(Detail d) IF 
        customer(order(d)) = c AND 
        product(d) = p AND 
        extractYear(date(order(d))) = y MATERIALIZED;
rating 'Рейтинг' (Customer c, Product p, INTEGER y) = 
    PARTITION SUM 1 ORDER DESC bought(c, p, y), p BY c, y MATERIALIZED;
SELECT contactName(Customer c), name(Product p) WHERE rating(c, p, 1997) < 3;


Das System selbst erstellt eine Tabelle mit Typschlüsseln Kundenfälle, Produkt и GANZE ZAHL, fügt zwei Felder hinzu und aktualisiert die darin enthaltenen Feldwerte bei allen Änderungen. Bei weiteren Aufrufen dieser Funktionen werden diese nicht berechnet, sondern die Werte aus den entsprechenden Feldern ausgelesen.

Mit diesem Mechanismus können Sie beispielsweise Rekursionen (CTE) in Abfragen beseitigen. Betrachten Sie insbesondere Gruppen, die mithilfe der untergeordneten/übergeordneten Beziehung einen Baum bilden (jede Gruppe hat einen Link zu ihrem übergeordneten Element):

parent = DATA Group (Group);


In einer funktionalen Datenbank kann die Rekursionslogik wie folgt angegeben werden:

level (Group child, Group parent) = RECURSION 1l IF child IS Group AND parent == child
                                                             STEP 2l IF parent == parent($parent);
isParent (Group child, Group parent) = TRUE IF level(child, parent) MATERIALIZED;


Da für die Funktion istParent mit MATERIALIZED markiert ist, wird dafür eine Tabelle mit zwei Schlüsseln (Gruppen) erstellt, in der das Feld enthalten ist istParent ist nur dann wahr, wenn der erste Schlüssel ein Kind des zweiten ist. Die Anzahl der Einträge in dieser Tabelle entspricht der Anzahl der Gruppen multipliziert mit der durchschnittlichen Tiefe des Baums. Wenn Sie beispielsweise die Anzahl der Nachkommen einer bestimmten Gruppe zählen müssen, können Sie diese Funktion verwenden:

childrenCount (Group g) = GROUP SUM 1 IF isParent(Group child, g);


Die SQL-Abfrage enthält keinen CTE. Stattdessen wird es ein einfaches GROUP BY geben.

Mit diesem Mechanismus können Sie die Datenbank bei Bedarf auch einfach denormalisieren:

CLASS Order 'Заказ';
date 'Дата' = DATA DATE (Order);

CLASS OrderDetail 'Строка заказа';
order 'Заказ' = DATA Order (OrderDetail);
date 'Дата' (OrderDetail d) = date(order(d)) MATERIALIZED INDEXED;


Beim Aufruf einer Funktion Datum Für die Bestellzeile wird das Feld, für das ein Index vorhanden ist, aus der Tabelle mit Bestellzeilen gelesen. Wenn sich das Bestelldatum ändert, berechnet das System automatisch das denormalisierte Datum in der Zeile neu.

Vorteile

Wozu dient dieser ganze Mechanismus? In klassischen DBMS kann ein Entwickler oder DBA ohne Umschreiben von Abfragen lediglich Indizes ändern, Statistiken ermitteln und dem Abfrageplaner mitteilen, wie diese ausgeführt werden sollen (und HINTs sind nur in kommerziellen DBMS verfügbar). Egal wie sehr sie es versuchen, sie werden die erste Abfrage im Artikel nicht abschließen können O (Anzahl der Abteilungen) ohne Abfragen zu ändern oder Trigger hinzuzufügen. Bei dem vorgeschlagenen Schema müssen Sie sich in der Entwicklungsphase keine Gedanken über die Datenspeicherstruktur und die zu verwendenden Aggregationen machen. All dies kann einfach im laufenden Betrieb direkt im Betrieb geändert werden.

In der Praxis sieht es so aus. Manche Menschen entwickeln Logik direkt basierend auf der jeweiligen Aufgabe. Sie verstehen weder Algorithmen und deren Komplexität, noch Ausführungspläne, Join-Typen oder andere technische Komponenten. Diese Leute sind eher Geschäftsanalysten als Entwickler. Das alles fließt dann in den Test bzw. in den Betrieb. Ermöglicht die Protokollierung lang laufender Abfragen. Wenn eine lange Abfrage erkannt wird, entscheiden sich andere Personen (eher technischer Natur – im Wesentlichen DBA) dafür, MATERIALIZED für eine Zwischenfunktion zu aktivieren. Dadurch wird die Aufzeichnung etwas verlangsamt (da ein zusätzliches Feld in der Transaktion aktualisiert werden muss). Allerdings wird nicht nur diese Abfrage deutlich beschleunigt, sondern auch alle anderen, die diese Funktion nutzen. Gleichzeitig ist die Entscheidung, welche Funktion realisiert werden soll, relativ einfach. Zwei Hauptparameter: die Anzahl der möglichen Eingabewerte (so viele Datensätze befinden sich in der entsprechenden Tabelle) und wie oft sie in anderen Funktionen verwendet werden.

Analoga

Moderne kommerzielle DBMS verfügen über ähnliche Mechanismen: MATERIALIZED VIEW mit FAST REFRESH (Oracle) und INDEXED VIEW (Microsoft SQL Server). In PostgreSQL kann MATERIALIZED VIEW nicht in einer Transaktion aktualisiert werden, sondern nur auf Anfrage (und sogar mit sehr strengen Einschränkungen), daher berücksichtigen wir es nicht. Sie weisen jedoch mehrere Probleme auf, die ihren Einsatz erheblich einschränken.

Erstens können Sie die Materialisierung nur aktivieren, wenn Sie bereits eine reguläre VIEW erstellt haben. Andernfalls müssen Sie die verbleibenden Anforderungen für den Zugriff auf die neu erstellte Ansicht neu schreiben, um diese Materialisierung zu verwenden. Oder alles so lassen, wie es ist, aber es wird zumindest wirkungslos sein, wenn bestimmte bereits vorberechnete Daten vorhanden sind, viele Abfragen diese aber nicht immer verwenden, sondern neu berechnen.

Zweitens gibt es eine Vielzahl von Einschränkungen:

Oracle

5.3.8.4 Allgemeine Einschränkungen bei der Schnellaktualisierung

Die definierende Abfrage der materialisierten Ansicht ist wie folgt eingeschränkt:

• Die materialisierte Ansicht darf keine Verweise auf sich nicht wiederholende Ausdrücke wie enthalten SYSDATE und ROWNUM.
• Die materialisierte Ansicht darf keine Verweise auf enthalten RAW or LONG RAW Datentypen.
• Es darf kein enthalten SELECT Unterabfrage auflisten.
• Es darf keine analytischen Funktionen enthalten (z. B. RANK) In der SELECT Klausel.
• Es kann nicht auf eine Tabelle verwiesen werden, auf der ein XMLIndex Index definiert ist.
• Es darf kein enthalten MODEL Klausel.
• Es darf kein enthalten HAVING Klausel mit einer Unterabfrage.
• Es darf keine verschachtelten Abfragen enthalten ANY, ALL, oder NOT EXISTS.
• Es darf kein enthalten [START WITH …] CONNECT BY Klausel.
• Es darf nicht mehrere Detailtabellen an verschiedenen Standorten enthalten.
• ON COMMIT Materialisierte Ansichten können keine Remote-Detailtabellen haben.
• Verschachtelte materialisierte Ansichten müssen über einen Join oder ein Aggregat verfügen.
• Materialisierte Join-Ansichten und materialisierte Aggregatansichten mit a GROUP BY Die Klausel kann nicht aus einer indexorganisierten Tabelle auswählen.

5.3.8.5 Einschränkungen bei der schnellen Aktualisierung von materialisierten Ansichten nur mit Verknüpfungen

Beim Definieren von Abfragen für materialisierte Ansichten nur mit Joins und ohne Aggregate gelten die folgenden Einschränkungen für die schnelle Aktualisierung:

• Alle Einschränkungen ab «Allgemeine Einschränkungen für die schnelle Aktualisierung«.
• Sie können es nicht haben GROUP BY Klauseln oder Aggregate.
• Rowids aller Tabellen in der FROM Liste muss in der erscheinen SELECT Liste der Abfrage.
• Materialisierte Ansichtsprotokolle müssen mit Zeilen-IDs für alle Basistabellen in vorhanden sein FROM Liste der Abfrage.
• Sie können keine schnell aktualisierbare materialisierte Ansicht aus mehreren Tabellen mit einfachen Verknüpfungen erstellen, die eine Objekttypspalte enthalten SELECT Aussage.

Außerdem ist die von Ihnen gewählte Aktualisierungsmethode nicht optimal effizient, wenn:

• Die definierende Abfrage verwendet einen äußeren Join, der sich wie ein innerer Join verhält. Wenn die definierende Abfrage einen solchen Join enthält, sollten Sie erwägen, die definierende Abfrage so umzuschreiben, dass sie einen inneren Join enthält.
• Das SELECT Die Liste der materialisierten Ansicht enthält Ausdrücke für Spalten aus mehreren Tabellen.

5.3.8.6 Einschränkungen bei der schnellen Aktualisierung materialisierter Ansichten mit Aggregaten

Beim Definieren von Abfragen für materialisierte Ansichten mit Aggregaten oder Joins gelten die folgenden Einschränkungen für die schnelle Aktualisierung:

• Alle Einschränkungen ab «Allgemeine Einschränkungen für die schnelle Aktualisierung«.

Für beide wird die schnelle Aktualisierung unterstützt ON COMMIT und ON DEMAND materialisierte Ansichten, es gelten jedoch die folgenden Einschränkungen:

• Alle Tabellen in der materialisierten Ansicht müssen über materialisierte Ansichtsprotokolle verfügen, und die materialisierten Ansichtsprotokolle müssen:
  • Enthält alle Spalten der Tabelle, auf die in der materialisierten Ansicht verwiesen wird.
  • Geben Sie mit an ROWID und INCLUDING NEW VALUES.
  • Präzisiere das SEQUENCE -Klausel, wenn erwartet wird, dass die Tabelle eine Mischung aus Einfügungen/direkten Ladevorgängen, Löschvorgängen und Aktualisierungen enthält.

• Nur SUM, COUNT, AVG, STDDEV, VARIANCE, MIN und MAX werden für eine schnelle Aktualisierung unterstützt.
• COUNT(*) muss angegeben werden.
• Aggregatfunktionen dürfen nur als äußerster Teil des Ausdrucks vorkommen. Das heißt, Aggregate wie AVG(AVG(x)) or AVG(x)+ AVG(x) sind nicht erlaubt.
• Für jedes Aggregat wie z AVG(expr), die entsprechende COUNT(expr) muss anwesend sein. Oracle empfiehlt dies SUM(expr) angegeben werden.
• If VARIANCE(expr) or STDDEV(expr) angegeben, COUNT(expr) und SUM(expr) muss angegeben werden. Oracle empfiehlt dies SUM(expr *expr) angegeben werden.
• Das SELECT Die Spalte in der definierenden Abfrage darf kein komplexer Ausdruck mit Spalten aus mehreren Basistabellen sein. Eine mögliche Problemumgehung hierfür ist die Verwendung einer verschachtelten materialisierten Ansicht.
• Das SELECT Die Liste muss alle enthalten GROUP BY Säulen.
• Die materialisierte Ansicht basiert nicht auf einer oder mehreren Remote-Tabellen.
• Wenn Sie ein CHAR Für den Datentyp in den Filterspalten eines Materialized View-Protokolls müssen die Zeichensätze der Master-Site und der Materialized View identisch sein.
• Wenn die materialisierte Ansicht eine der folgenden Eigenschaften aufweist, wird die schnelle Aktualisierung nur bei herkömmlichen DML-Einfügungen und direkten Ladevorgängen unterstützt.
  • Materialisierte Ansichten mit MIN or MAX Aggregate
  • Materialisierte Ansichten, die haben SUM(expr) aber nein COUNT(expr)
  • Materialisierte Ansichten ohne COUNT(*)

  Eine solche materialisierte Ansicht wird als materialisierte Ansicht nur zum Einfügen bezeichnet.

• Eine materialisierte Ansicht mit MAX or MIN ist nach Lösch- oder gemischten DML-Anweisungen schnell aktualisierbar, wenn keine vorhanden ist WHERE Klausel.
  Die maximale/minimale schnelle Aktualisierung nach dem Löschen oder gemischten DML weist nicht das gleiche Verhalten auf wie der Nur-Einfüge-Fall. Es löscht die Max/Min-Werte für die betroffenen Gruppen und berechnet sie neu. Sie müssen sich der Auswirkungen auf die Leistung bewusst sein.
• Materialisierte Ansichten mit benannten Ansichten oder Unterabfragen im FROM Die Klausel kann schnell aktualisiert werden, sofern die Ansichten vollständig zusammengeführt werden können. Informationen dazu, welche Ansichten zusammengeführt werden, finden Sie unter Oracle-Datenbank-SQL-Sprachreferenz.
• Wenn keine äußeren Verknüpfungen vorhanden sind, können Sie beliebige Auswahlen und Verknüpfungen vornehmen WHERE Klausel.
• Materialisierte Aggregatansichten mit Außenverknüpfungen können nach herkömmlichen DML- und direkten Ladevorgängen schnell aktualisiert werden, sofern nur die äußere Tabelle geändert wurde. Außerdem müssen eindeutige Einschränkungen für die Join-Spalten der inneren Join-Tabelle bestehen. Wenn äußere Verknüpfungen vorhanden sind, müssen alle Verknüpfungen durch verbunden sein ANDs und muss die Gleichheit verwenden (=) Operator.
• Für materialisierte Ansichten mit CUBE, ROLLUP, Gruppierungssätze oder deren Verkettung gelten die folgenden Einschränkungen:
  • Das SELECT Die Liste sollte ein Gruppierungsunterscheidungsmerkmal enthalten, das entweder ein sein kann GROUPING_ID Funktion auf alle GROUP BY Ausdrücke bzw GROUPING Funktionen jeweils eine GROUP BY Ausdruck. Wenn zum Beispiel die GROUP BY Die Klausel der materialisierten Sicht lautet „GROUP BY CUBE(a, b)", dann ist die SELECT Die Liste sollte entweder „GROUPING_ID(a, b)" oder "GROUPING(a) AND GROUPING(b)» Damit die materialisierte Ansicht schnell aktualisiert werden kann.
  • GROUP BY sollte nicht zu doppelten Gruppierungen führen. Zum Beispiel, "GROUP BY a, ROLLUP(a, b)„ist nicht schnell aktualisierbar, da es zu doppelten Gruppierungen führt“(a), (a, b), AND (a)«.

5.3.8.7 Einschränkungen bei der schnellen Aktualisierung materialisierter Ansichten mit UNION ALL

Materialisierte Ansichten mit dem UNION ALL Set-Operator unterstützt die REFRESH FAST Option, wenn die folgenden Bedingungen erfüllt sind:

• Die definierende Abfrage muss Folgendes haben: UNION ALL Betreiber auf oberster Ebene.

  Das UNION ALL Der Operator kann nicht in eine Unterabfrage eingebettet werden, mit einer Ausnahme: Der UNION ALL kann in einer Unterabfrage in der sein FROM -Klausel, sofern die definierende Abfrage die Form hat SELECT * FROM (Ansicht oder Unterabfrage mit UNION ALL) wie im folgenden Beispiel:

  CREATE VIEW view_with_unionall AS (SELECT c.rowid crid, c.cust_id, 2 umarker FROM customer c WHERE c.cust_last_name = 'Smith' UNION ALL SELECT c.rowid crid, c.cust_id, 3 umarker FROM customer c WHERE c.cust_last_name = 'Jones'); CREATE MATERIALIZED VIEW unionall_inside_view_mv REFRESH FAST ON Demand AS SELECT * FROM view_with_unionall;
  

  Beachten Sie, dass die Ansicht view_with_unionall erfüllt die Anforderungen an eine schnelle Aktualisierung.

• Jeder Abfrageblock in der UNION ALL Die Abfrage muss die Anforderungen einer schnell aktualisierbaren materialisierten Ansicht mit Aggregaten oder einer schnell aktualisierbaren materialisierten Ansicht mit Verknüpfungen erfüllen.

  Die entsprechenden Materialized View-Protokolle müssen für die Tabellen erstellt werden, je nach Bedarf für den entsprechenden Typ der schnell aktualisierbaren Materialized View.
  Beachten Sie, dass die Oracle-Datenbank auch den Sonderfall einer materialisierten Einzeltabellenansicht mit Verknüpfungen nur dann zulässt, wenn dies der Fall ist ROWID Die Spalte wurde in die aufgenommen SELECT Liste und im Materialized View-Protokoll. Dies wird in der definierenden Abfrage der Ansicht angezeigt view_with_unionall.

• Das SELECT Die Liste jeder Abfrage muss Folgendes enthalten: UNION ALL Markierung und die UNION ALL Jede Spalte muss einen eindeutigen konstanten numerischen oder Zeichenfolgenwert haben UNION ALL Zweig. Darüber hinaus muss die Markierungsspalte in der gleichen Ordinalposition erscheinen SELECT Liste jedes Abfrageblocks. Sehen "UNION ALL Marker und Abfrage-Rewrite» für weitere Informationen bzgl UNION ALL Marker.
• Einige Funktionen wie Outer-Joins, aggregierte Abfragen materialisierter Ansichten mit ausschließlicher Einfügung und Remotetabellen werden für materialisierte Ansichten mit nicht unterstützt UNION ALL. Beachten Sie jedoch, dass bei der Replikation verwendete materialisierte Ansichten, die keine Joins oder Aggregate enthalten, bei Bedarf schnell aktualisiert werden können UNION ALL oder Remote-Tabellen verwendet werden.
• Der Kompatibilitätsinitialisierungsparameter muss auf 9.2.0 oder höher eingestellt sein, um eine schnell aktualisierbare materialisierte Ansicht mit zu erstellen UNION ALL.

Ich möchte Oracle-Fans nicht beleidigen, aber gemessen an ihrer Liste der Einschränkungen scheint es, dass dieser Mechanismus nicht allgemein nach einem Modell geschrieben wurde, sondern von Tausenden von Indern, bei denen jedem die Möglichkeit gegeben wurde Schreiben Sie ihren eigenen Zweig, und jeder von ihnen tat, was er konnte. und tat es. Die Verwendung dieses Mechanismus für echte Logik ist wie ein Spaziergang durch ein Minenfeld. Sie können jederzeit eine Mine erhalten, indem Sie eine der nicht offensichtlichen Einschränkungen treffen. Wie es funktioniert, ist ebenfalls eine separate Frage, die jedoch den Rahmen dieses Artikels sprengen würde.

Microsoft SQL Server

Zusätzliche Anforderungen

Zusätzlich zu den SET-Optionen und deterministischen Funktionsanforderungen müssen die folgenden Anforderungen erfüllt sein:

• Der Benutzer, der ausführt CREATE INDEX muss der Eigentümer der Ansicht sein.
• Wenn Sie den Index erstellen, wird der IGNORE_DUP_KEY Die Option muss auf AUS (Standardeinstellung) eingestellt sein.
• Tabellen müssen durch zweiteilige Namen referenziert werden. Schema.Tabellenname in der Ansichtsdefinition.
• Benutzerdefinierte Funktionen, auf die in der Ansicht verwiesen wird, müssen mithilfe von erstellt werden WITH SCHEMABINDING .
• Alle benutzerdefinierten Funktionen, auf die in der Ansicht verwiesen wird, müssen durch zweiteilige Namen referenziert werden. ..
• Die Datenzugriffseigenschaft einer benutzerdefinierten Funktion muss sein NO SQL, und die externe Zugriffseigenschaft muss sein NO.
• CLR-Funktionen (Common Language Runtime) können in der Auswahlliste der Ansicht angezeigt werden, dürfen jedoch nicht Teil der Definition des Clustered-Index-Schlüssels sein. CLR-Funktionen dürfen nicht in der WHERE-Klausel der Ansicht oder der ON-Klausel einer JOIN-Operation in der Ansicht erscheinen.
• CLR-Funktionen und -Methoden benutzerdefinierter CLR-Typen, die in der Ansichtsdefinition verwendet werden, müssen über die in der folgenden Tabelle gezeigten Eigenschaften verfügen.

  Immobilien
  Note

  DETERMINISTISCH = WAHR
  Muss explizit als Attribut der Microsoft .NET Framework-Methode deklariert werden.

  PRÄZISE = WAHR
  Muss explizit als Attribut der .NET Framework-Methode deklariert werden.

  DATENZUGRIFF = KEIN SQL
  Wird bestimmt, indem das DataAccess-Attribut auf DataAccessKind.None und das SystemDataAccess-Attribut auf SystemDataAccessKind.None gesetzt wird.

  EXTERNER ZUGRIFF = NEIN
  Diese Eigenschaft ist für CLR-Routinen standardmäßig auf NO gesetzt.

• Die Ansicht muss mithilfe von erstellt werden WITH SCHEMABINDING .
• Die Ansicht darf nur auf Basistabellen verweisen, die sich in derselben Datenbank wie die Ansicht befinden. Die Ansicht kann nicht auf andere Ansichten verweisen.
• Die SELECT-Anweisung in der Ansichtsdefinition darf die folgenden Transact-SQL-Elemente nicht enthalten:

  COUNT
  ROWSET-Funktionen (OPENDATASOURCE, OPENQUERY, OPENROWSET, UND OPENXML)
  OUTER schließt sich an (LEFT, RIGHT, oder FULL)

  Abgeleitete Tabelle (definiert durch Angabe von a SELECT Aussage in der FROM Klausel)
  Selbstverknüpfungen
  Angeben von Spalten mithilfe von SELECT * or SELECT <table_name>.*

  DISTINCT
STDEV, STDEVP, VAR, VARP, oder AVG
  Gemeinsamer Tabellenausdruck (CTE)

  schweben1, Text, ntext, Image, XML, oder Datenfluss Spalten
  Subquery
  OVER -Klausel, die Ranking- oder Aggregatfensterfunktionen enthält

  Volltextprädikate (CONTAINS, FREETEXT)
  SUM Funktion, die auf einen nullbaren Ausdruck verweist
  ORDER BY

  Benutzerdefinierte CLR-Aggregatfunktion
  TOP
CUBE, ROLLUP, oder GROUPING SETS Betreiber

  MIN, MAX
UNION, EXCEPT, oder INTERSECT Betreiber
  TABLESAMPLE

  Tabellenvariablen
  OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

  Sparse-Spaltensätze
  Inline- (TVF) oder Tabellenwertfunktionen mit mehreren Anweisungen (MSTVF)
  OFFSET

  CHECKSUM_AGG

  1 Die indizierte Ansicht kann enthalten schweben Säulen; Allerdings können solche Spalten nicht in den Clustered-Index-Schlüssel aufgenommen werden.

• If GROUP BY vorhanden ist, muss die VIEW-Definition enthalten COUNT_BIG(*) und darf nicht enthalten HAVING. Diese GROUP BY Einschränkungen gelten nur für die indizierte Ansichtsdefinition. Eine Abfrage kann in ihrem Ausführungsplan eine indizierte Ansicht verwenden, auch wenn sie diese nicht erfüllt GROUP BY Beschränkungen.
• Wenn die Ansichtsdefinition a enthält GROUP BY -Klausel kann der Schlüssel des eindeutigen Clustered-Index nur auf die in der angegebenen Spalten verweisen GROUP BY Klausel.

Hier ist klar, dass die Inder nicht beteiligt waren, da sie beschlossen hatten, es nach dem Schema „Wir werden wenig, aber gut tun“ zu tun. Das heißt, sie haben mehr Minen auf dem Feld, aber ihre Lage ist transparenter. Am enttäuschendsten ist diese Einschränkung:

Die Ansicht darf nur auf Basistabellen verweisen, die sich in derselben Datenbank wie die Ansicht befinden. Die Ansicht kann nicht auf andere Ansichten verweisen.

In unserer Terminologie bedeutet dies, dass eine Funktion nicht auf eine andere materialisierte Funktion zugreifen kann. Dadurch wird jede Ideologie im Keim erstickt.
Außerdem reduziert diese Einschränkung (und weiter im Text) die Anwendungsfälle erheblich:

Die SELECT-Anweisung in der Ansichtsdefinition darf die folgenden Transact-SQL-Elemente nicht enthalten:

COUNT
ROWSET-Funktionen (OPENDATASOURCE, OPENQUERY, OPENROWSET, UND OPENXML)
OUTER schließt sich an (LEFT, RIGHT, oder FULL)

Abgeleitete Tabelle (definiert durch Angabe von a SELECT Aussage in der FROM Klausel)
Selbstverknüpfungen
Angeben von Spalten mithilfe von SELECT * or SELECT <table_name>.*

DISTINCT
STDEV, STDEVP, VAR, VARP, oder AVG
Gemeinsamer Tabellenausdruck (CTE)

schweben1, Text, ntext, Image, XML, oder Datenfluss Spalten
Subquery
OVER -Klausel, die Ranking- oder Aggregatfensterfunktionen enthält

Volltextprädikate (CONTAINS, FREETEXT)
SUM Funktion, die auf einen nullbaren Ausdruck verweist
ORDER BY

Benutzerdefinierte CLR-Aggregatfunktion
TOP
CUBE, ROLLUP, oder GROUPING SETS Betreiber

MIN, MAX
UNION, EXCEPT, oder INTERSECT Betreiber
TABLESAMPLE

Tabellenvariablen
OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

Sparse-Spaltensätze
Inline- (TVF) oder Tabellenwertfunktionen mit mehreren Anweisungen (MSTVF)
OFFSET

CHECKSUM_AGG

OUTER JOINS, UNION, ORDER BY und andere sind verboten. Es wäre möglicherweise einfacher gewesen, anzugeben, was verwendet werden könnte, als was nicht. Die Liste wäre wahrscheinlich viel kürzer.

Zusammenfassend lässt sich sagen: eine große Reihe von Einschränkungen in jedem (beachten wir kommerzielle) DBMS im Vergleich zu keiner (mit Ausnahme eines logischen, nicht technischen) in der LGPL-Technologie. Es ist jedoch zu beachten, dass die Implementierung dieses Mechanismus in der relationalen Logik etwas schwieriger ist als in der beschriebenen funktionalen Logik.

Implementierung

Wie es funktioniert? PostgreSQL wird als „virtuelle Maschine“ verwendet. Im Inneren befindet sich ein komplexer Algorithmus, der Abfragen erstellt. Hier Quellcode. Und es gibt nicht nur eine große Menge an Heuristiken mit einer Menge Wenns. Wenn Sie also ein paar Monate Zeit zum Lernen haben, können Sie versuchen, Architektur zu verstehen.

Funktioniert es effektiv? Ziemlich effektiv. Leider ist dies schwer zu beweisen. Ich kann nur sagen: Wenn man die Tausenden von Abfragen berücksichtigt, die in großen Anwendungen vorhanden sind, dann sind diese im Durchschnitt effizienter als die eines guten Entwicklers. Ein ausgezeichneter SQL-Programmierer kann jede Abfrage effizienter schreiben, aber bei tausend Abfragen fehlt ihm einfach die Motivation oder die Zeit, dies zu tun. Das einzige, was ich jetzt als Wirksamkeitsbeweis anführen kann, ist, dass mehrere Projekte auf der auf diesem DBMS basierenden Plattform arbeiten ERP-Systeme, die über Tausende verschiedener MATERIALIZED-Funktionen verfügen, mit Tausenden von Benutzern und Terabyte-Datenbanken mit Hunderten Millionen Datensätzen, die auf einem regulären Zwei-Prozessor-Server laufen. Allerdings kann jeder die Wirksamkeit per Download überprüfen/widerlegen eine Plattform und PostgreSQL, eingeschaltet SQL-Abfragen protokollieren und versuchen, dort die Logik und Daten zu ändern.

In den folgenden Artikeln werde ich auch darüber sprechen, wie Sie Einschränkungen für Funktionen festlegen, mit Änderungssitzungen arbeiten und vieles mehr.

Source: habr.com