Balansowanie zapisu i odczytu w bazie danych

W poprzednim artykuł Opisałem koncepcję i realizację bazy danych, opartą na funkcjach, a nie na tabelach i polach, jak w relacyjnych bazach danych. Zawierała wiele przykładów, które pokazują zalety takiego podejścia w porównaniu do klasycznego. Wiele osób uznało je za niewystarczająco przekonujące.

W tym artykule pokażę, w jaki sposób taka koncepcja pozwala szybko i łatwo zrównoważyć zapis i odczyt w bazie danych bez jakiejkolwiek zmiany logiki działania. Podobną funkcjonalność próbowano zrealizować w nowoczesnych komercyjnych systemach zarządzania bazą danych (szczególnie Oracle i Microsoft SQL Server). Na końcu artykułu pokażę, co im się udało, delikatnie mówiąc, nie najlepiej.

Opis

Jak wcześniej, dla lepszego zrozumienia rozpocznę opis na przykładach. Załóżmy, że musimy zrealizować logikę, która będzie zwracać listę działów z liczbą pracowników w nich i ich łączną pensją.

W funkcjonalnej bazie danych będzie to wyglądać następująco:

CLASS Department ‘Dział’;
name ‘Nazwa’ = DATA STRING[100] (Dział);

CLASS Employee ‘Pracownik’;
department ‘Dział’ = DATA Department (Employee);
salary ‘Pensja’ = DATA NUMERIC[10,2] (Employee);

countEmployees ‘Liczba pracowników’ (Department d) = 
    GROUP SUM 1 IF department(Employee e) = d;
salarySum ‘Łączna pensja’ (Department d) = 
    GROUP SUM salary(Employee e) IF department(e) = d;

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

Złożoność wykonania tego zapytania w dowolnej SGBD będzie równoważna O(liczba pracowników), ponieważ do tego obliczenia należy przeskanować całą tabelę pracowników, a następnie pogrupować ich według działu. Będzie również niewielki dodatek (zakładając, że pracowników jest znacznie więcej niż działów) w zależności od wybranego planu O(log liczba pracowników) lub O(liczba działów) na grupowanie i inne.

Jasne jest, że obciążenia związane z wykonaniem mogą się różnić w różnych SGBD, ale złożoność się nie zmieni.

W proponowanej implementacji funkcjonalna SGBD wygeneruje jedno zapytanie podrzędne, które obliczy potrzebne wartości według działu, a następnie połączy z tabelą działów, aby uzyskać nazwę. Jednak przy każdej funkcji podczas deklaracji istnieje możliwość ustawienia specjalnego wskaźnika MATERIALIZED. System automatycznie utworzy odpowiednie pole dla każdej takiej funkcji. Przy zmianie wartości funkcji wartość pola również zostanie zmieniona w tej samej transakcji. Gdy zostanie wywołana ta funkcja, odwołanie będzie dotyczyć już obliczonego pola.

W szczególności, jeśli ustawisz MATERIALIZED dla funkcji countEmployees i salarySum, to w tabeli z listą działów dodane zostaną dwa pola, w których będą przechowywane liczba pracowników i ich łączna pensja. Przy każdej zmianie pracowników, ich wynagrodzeń lub przynależności do działów system automatycznie zmieni wartości tych pól. Powyższe zapytanie będzie odwoływać się bezpośrednio do tych pól i zostanie wykonane za O(liczba działów).

Jakie są ograniczenia? Tylko jedno: taka funkcja musi mieć skończoną liczbę wartości wejściowych, dla których jej wartość jest określona. W przeciwnym razie niemożliwe będzie zbudowanie tabeli przechowującej wszystkie jej wartości, ponieważ nie może istnieć tabela z nieskończoną liczbą wierszy.

Przykład:

employeesCount 'Liczba pracowników z wynagrodzeniem > N' (Department d, NUMERIC[10,2] N) = 
    GROUP SUM salary(Employee e) IF department(e) = d AND salary(e) > N;


Ta funkcja jest określona dla nieskończonej liczby wartości liczby N (na przykład każda wartość ujemna jest odpowiednia). Dlatego nie można jej oznaczyć jako MATERIALIZED. W ten sposób jest to logiczne, a nie techniczne ograniczenie (to znaczy, że nie dlatego, że nie mogliśmy tego zaimplementować). Poza tym — żadnych ograniczeń. Można stosować grupowania, sortowania, AND i OR, PARTITION, rekurencje itp.

Na przykład w zadaniu 2.2 z poprzedniego artykułu można ustawić MATERIALIZED dla obu funkcji:

kupiony 'Kupiony' (Klient c, Produkt p, LICZBA y) = 
    SUMA GRUPY sum(Szczegół d) IF 
        klient(zamówienie(d)) = c I 
        produkt(d) = p I 
        wydobądźRok(data(zamówienie(d))) = y MATERIALIZOWANE;
ocena 'Ocena' (Klient c, Produkt p, LICZBA y) = 
    PARTYCJA SUMA 1 ZAMÓW DESC kupiony(c, p, y), p GROUP BY c, y MATERIALIZOWANE;
WYBIERZ contactName(Klient c), name(Produkt p) GDZIE ocena(c, p, 1997) < 3;


System automatycznie utworzy jedną tabelę z kluczami typów Customer, Produkt i INTEGER, doda do niej dwa pola i będzie aktualizować w nich wartości pola przy wszelkich zmianach. Przy dalszych odwołaniach do tych funkcji nie będą one obliczane, a odczytywane będą wartości z odpowiednich pól.

Za pomocą tego mechanizmu można na przykład zrezygnować z rekurencji (CTE) w zapytaniach. W szczególności rozważmy grupy, które tworzą drzewo za pomocą relacji child/parent (każda grupa ma odwołanie do swojego rodzica):

rodzic = DANE Grupa (Grupa);


W funkcjonalnej bazie danych logikę rekurencji można określić w następujący sposób:

poziom (dziecko grupy, rodzic grupy) = REKURSYJNE 1l JEŻELI dziecko JEST Grupą I rodzic == dziecko
                                                             KROK 2l JEŻELI rodzic == rodzic($parent);
isParent (dziecko grupy, rodzic grupy) = PRAWDA JEŻELI poziom(dziecko, rodzic) ZMATERIALOWANE;


Ponieważ dla funkcji isParent ustawiono MATERIALIZED, to pod nią zostanie utworzona tabela z dwoma kluczami (grupami), w której pole isParent będzie prawdziwe tylko wtedy, gdy pierwszy klucz jest potomkiem drugiego. Liczba rekordów w tej tabeli będzie równa liczbie grup pomnożonej przez średnią głębokość drzewa. Jeśli trzeba, na przykład, policzyć liczbę potomków określonej grupy, można użyć tej funkcji:

liczbaDzieci (Grupa g) = SUMA GRUPY 1 JEŻELI isParent(Grupa dziecko, g);


Nie będzie żadnego CTE w zapytaniu SQL. Zamiast tego będzie proste GROUP BY.

Za pomocą tego mechanizmu można także łatwo przeprowadzać denormalizację bazy danych w razie potrzeby:

CLASS Order 'Zamówienie';
date 'Data' = DATA DATE (Zamówienie);

CLASS OrderDetail 'Pozycja zamówienia';
order 'Zamówienie' = DATA Order (OrderDetail);
date 'Data' (OrderDetail d) = date(order(d)) MATERIALIZED INDEXED;

Przy wywołaniu funkcji date dla pozycji zamówienia będzie odbywać się odczyt z tabeli z pozycjami zamówień pola, dla którego istnieje indeks. Przy zmianie daty zamówienia system automatycznie przeliczy denormalizowaną datę w pozycji.

Zalety

Po co potrzebny jest cały ten mechanizm? W klasycznych DBMS, bez przepisywania zapytań, programista lub DBA mogą jedynie zmieniać indeksy, określać statystyki i podpowiadać plannerowi zapytań, jak je wykonać (przy czym HINT-y są tylko w komercyjnych DBMS). Jak bardzo by się nie starali, nie będą w stanie wykonać pierwszego zapytania w artykule za O (liczba działów) bez modyfikacji zapytań i dopisywania triggerów. W zaproponowanej schemacie, na etapie rozwoju można nie myśleć o strukturze przechowywania danych i o tym, jakie agregacje użyć. To wszystko można swobodnie zmieniać w trakcie eksploatacji.

W praktyce wygląda to tak: niektórzy ludzie opracowują logikę bezpośrednio na podstawie postawionego zadania. Nie mają pojęcia o algorytmach i ich złożoności, planach wykonania, typach JOINów ani o żadnym innym aspekcie technicznym. Ci ludzie są bardziej analitykami biznesowymi niż programistami. Następnie wszystko to trafia do testowania lub na produkcję. Włącza się logowanie długich zapytań. Kiedy wykrywane jest długie zapytanie, decyzję o włączeniu MATERIALIZED na pewnej funkcji pośredniej podejmują już inni ludzie (bardziej techniczni — w zasadzie DBA). W ten sposób nieco spowalnia się zapis (ponieważ wymaga to aktualizacji dodatkowego pola w transakcji). Jednak znacznie przyspiesza to nie tylko to zapytanie, ale i wszystkie inne, które korzystają z tej funkcji. Przy tym podjęcie decyzji, którą funkcję zmaterializować, jest relatywnie nieskomplikowane. Dwa podstawowe parametry: liczba możliwych wartości wejściowych (dokładnie tyle rekordów będzie w odpowiedniej tabeli) oraz to, jak często jest ona używana w innych funkcjach.

Odpowiedniki

W nowoczesnych komercyjnych systemach baz danych istnieją podobne mechanizmy: MATERIALIZED VIEW z FAST REFRESH (Oracle) oraz INDEXED VIEW (Microsoft SQL Server). W PostgreSQL MATERIALIZED VIEW nie potrafi aktualizować się w transakcji, a tylko na żądanie (i to z bardzo surowymi ograniczeniami), więc tego nie bierzemy pod uwagę. Mają jednak kilka problemów, co znacznie ogranicza ich użycie.

Po pierwsze, materializację można włączyć tylko, jeśli już stworzono zwykły VIEW. W przeciwnym razie trzeba będzie przepisować inne zapytania do nowo stworzonego widoku, aby wykorzystać tę materializację. Można również zostawić wszystko jak jest, ale będzie to co najmniej nieefektywne, jeśli są już określone dane wstępnie wyliczone, lecz wiele zapytań nie zawsze je wykorzystuje, a oblicza je na nowo.

Po drugie, mają one ogromną liczbę ograniczeń:

Oracle

5.3.8.4 Ogólne ograniczenia dotyczące szybkiej aktualizacji

Zapytanie definiujące materializowany widok jest ograniczone w następujący sposób:

• Materializowany widok nie może zawierać odniesień do wyrażeń nierepetujących, takich jak SYSDATE i ROWNUM.
• Materializowany widok nie może zawierać odniesień do RAW lub LONG RAW typów danych.
• Nie może zawierać zapytania podrzędnego z listą. SELECT Nie może zawierać funkcji analitycznych (na przykład,
• RANK ) w klauzuli. SELECT Nie może odnosić się do tabeli, na której zdefiniowany jest
• XMLIndex indeks. MODEL
• Nie może zawierać zapytania podrzędnego z listą. klauzula HAVING z zapytaniem podrzędnym. Nie może odnosić się do tabeli, na której zdefiniowany jest
• Nie może zawierać zapytania podrzędnego z listą. Nie może zawierać zagnieżdżonych zapytań, które mają ANY
• ALL , lub, [START WITH …] CONNECT BY, or NOT EXISTS.
• Nie może zawierać zapytania podrzędnego z listą. [START WITH …] CONNECT BY Nie może odnosić się do tabeli, na której zdefiniowany jest
• Nie może zawierać wielu tabel szczegółowych w różnych lokalizacjach.
• WŁĄCZONE COMMIT Widoki zmaterializowane nie mogą mieć zdalnych tabel szczegółowych.
• Zagnieżdżone widoki zmaterializowane muszą mieć połączenie lub agregację.
• Widoki zmaterializowane połączeń i widoki zmaterializowane agregacji z GRUPA ZGODNIE Z klauzulą nie mogą wybierać z tabeli zorganizowanej według indeksu.

5.3.8.5 Ograniczenia dotyczące szybkiego odświeżania widoków zmaterializowanych tylko z połączeniami

Definiowanie zapytań dla widoków zmaterializowanych tylko z połączeniami i bez agregatów ma następujące ograniczenia dotyczące szybkiego odświeżania:

• Wszystkie ograniczenia z „Ogólne ograniczenia dotyczące szybkiego odświeżania«.
• Nie mogą mieć GRUPA ZGODNIE Z klauzul ani agregatów.
• Rowid wszystkich tabel w Z FROM liście musi pojawić się w SELECT liście zapytania.
• Logi widoków zmaterializowanych muszą istnieć z rowid dla wszystkich tabel bazowych w Z FROM liście zapytania.
• Nie możesz utworzyć widoku zmaterializowanego o szybkim odświeżaniu z wielu tabel z prostymi połączeniami, które zawierają kolumnę typu obiekt w SELECT oświadczeniu.

Ponadto wybrana metoda odświeżania nie będzie optymalnie wydajna, jeśli:

• Definiujące zapytanie używa zewnętrznego połączenia, które zachowuje się jak wewnętrzne połączenie. Jeśli definujące zapytanie zawiera takie połączenie, rozważ przepisanie definującego zapytania, aby zawierało wewnętrzne połączenie.
• Lista SELECT widoku zmaterializowanego zawiera wyrażenia opierające się na kolumnach z wielu tabel.

5.3.8.6 Ograniczenia dotyczące szybkiego odświeżania widoków zmaterializowanych z agregatami

Definiujące zapytania dla widoków zmaterializowanych z agregatami lub połączeniami mają następujące ograniczenia dotyczące szybkiego odświeżania:

• Wszystkie ograniczenia z „Ogólne ograniczenia dotyczące szybkiego odświeżania«.

Szybkie odświeżanie jest wspierane dla obu WŁĄCZONE COMMIT i WŁĄCZONE POTRZEBY widoków zmaterializowanych, jednak stosują się następujące ograniczenia:

• Wszystkie tabele w widoku zmaterializowanym muszą mieć logi widoków zmaterializowanych, a logi widoków zmaterializowanych muszą:
  • Zawierać wszystkie kolumny z tabeli odniesionej w widoku zmaterializowanym.
  • Określ z ROWID i WŁĄCZAJĄC NOWE WARTOŚCI.
  • Określ klauzulę SEKWENCJA jeśli tabela ma mieć mieszankę wstawek/ładowania bezpośredniego, usunięć i aktualizacji.

• Tylko SUMA, LICZBA, ŚREDNIA, STDDEV, WARIANCJA, MIN i MAX są wspierane dla szybkiego odświeżania.
• LICZBA(*) musi być określona.
• Funkcje agregujące muszą występować tylko jako najbardziej zewnętrzna część wyrażenia. To znaczy, agregaty takie jak ŚREDNIA(ŚREDNIA(x)) lub ŚREDNIA(x)+ ŚREDNIA(x) są niedozwolone.
• Dla każdej agregacji takiej jak ŚREDNIA(expr), odpowiadająca LICZBA(expr) musi być obecna. Oracle zaleca, aby SUMA(expr) była określona.
• Jeśli WARIANCJA(expr) lub STDDEV(expr) jest określona, LICZBA(expr) i SUMA(expr) musi być określona. Oracle zaleca, aby SUMA(expr * expr) była określona.
• Lista SELECT kolumna w definującym zapytaniu nie może być złożonym wyrażeniem z kolumn z wielu tabel bazowych. Możliwym obejściem tego jest użycie zagnieżdżonego widoku zmaterializowanego.
• Lista SELECT lista musi zawierać wszystkie GRUPA ZGODNIE Z kolumny.
• Widok zmaterializowany nie jest oparty na jednej lub więcej zdalnych tabelach.
• Jeśli używasz typu danych CHAR w kolumnach filtra logu widoków zmaterializowanych, zestawy znaków witryny źródłowej i widoku zmaterializowanego muszą być takie same.
• Jeśli widok zmaterializowany ma jeden z następujących, szybkie odświeżanie jest wspierane tylko podczas konwencjonalnych wstawek DML i ładunków bezpośrednich.
  • Widoki zmaterializowane z MIN lub MAX agregatami
  • Widoki zmaterializowane, które mają SUMA(expr) ale nie LICZBA(expr)
  • Widoki zmaterializowane bez LICZBA(*)

  Taki widok zmaterializowany nazywa się widokiem zmaterializowanym tylko do wstawek.

• Widok zmaterializowany z MAX lub MIN jest szybki do odświeżenia po usunięciu lub mieszanych operacjach DML, jeśli nie ma WHERE Nie może odnosić się do tabeli, na której zdefiniowany jest
  Maksymalne/minimalne szybkie odświeżanie po usunięciu lub mieszanych DML nie ma takiego samego zachowania jak w przypadku wstawki tylko. Usuwa i oblicza ponownie maksymalne/minimalne wartości dla dotkniętych grup. Musisz być świadomy jego wpływu na wydajność.
• Widoki zmaterializowane z nazwanymi widokami lub podzapytaniami w Z FROM klauzuli mogą być szybko odświeżane, pod warunkiem że widoki mogą być całkowicie scalone. Aby uzyskać informacje, które widoki będą scalone, zobacz Oracle Database SQL Language Reference.
• Jeśli nie ma zewnętrznych połączeń, możesz mieć dowolne wybory i połączenia w WHERE Nie może odnosić się do tabeli, na której zdefiniowany jest
• Zmaterializowane widoki agregowane z zewnętrznymi połączeniami mogą być szybko odświeżane po konwencjonalnych operacjach DML i bezpośrednich załadunkach, pod warunkiem, że tylko zewnętrzna tabela została zmodyfikowana. Ponadto, muszą istnieć unikalne ograniczenia na kolumnach połączenia wewnętrznej tabeli. Jeśli są zewnętrzne połączenia, wszystkie połączenia muszą być połączone przez ANDi muszą używać równości (=) operatora.
• Dla materializowanych widoków z CUBE, ROLLUP, grupujących zestawów lub ich konkatenacji, obowiązują następujące ograniczenia:
  • Lista SELECT lista powinna zawierać identyfikator grupujący, który może być funkcją GROUPING_ID na wszystkich GRUPA ZGODNIE Z wyrażeniach lub funkcjach GROUPING, jedna dla każdego wyrażenia. Na przykład, jeśli klauzula widoku materializowanego to „ GRUPA ZGODNIE Z CUBE(a, b)“ to lista powinna zawierać albo „ GRUPA ZGODNIE Z GROUPING_ID(a, b)“ lub „GRUPA ZGODNIE Z GROUPING(a)GROUPING(b)“, aby widok materializowany mógł być szybko odświeżany. SELECT nie powinien skutkować żadnymi duplikatami grup. Na przykład, „GROUP BY a, ROLLUP(a, b)“ nie jest szybko odświeżalne, ponieważ powoduje duplikaty grup „(a), (a, b), I (a)5.3.8.7 Ograniczenia dotyczące szybkiego odświeżania w widokach materializowanych z UNION ALL AND Materializowane widoki z operatorem zestawu obsługują opcjęREFRESH
  • GRUPA ZGODNIE Z FASTjeśli spełnione są następujące warunki:Zapytanie definiujące musi mieć operatorna najwyższym poziomie.«.

operator nie może być osadzony w podzapytaniu, z jednym wyjątkiem: Operator

może znajdować się w podzapytaniu w klauzuli UNION [START WITH …] CONNECT BY pod warunkiem, że zapytanie definiujące ma postać SELECT * FROM (widok lub podzapytanie z ) jak w poniższym przykładzie:

• CREATE VIEW view_with_unionall AS (SELECT c.rowid crid, c.cust_id, 2 umarker FROM customers c WHERE c.cust_last_name = 'Smith' UNION ALL SELECT c.rowid crid, c.cust_id, 3 umarker FROM customers c WHERE c.cust_last_name = 'Jones');CREATE MATERIALIZED VIEW unionall_inside_view_mv REFRESH FAST ON DEMAND AS SELECT * FROM view_with_unionall; UNION [START WITH …] CONNECT BY Zauważ, że widok

  Lista UNION [START WITH …] CONNECT BY view_with_unionall UNION [START WITH …] CONNECT BY spełnia wymagania szybkiego odświeżania. Z FROM Każdy blok zapytania w zapytaniu musi spełniać wymagania widoku materializowanego, który można szybko odświeżyć z agregatami lub widoku materializowanego, który można szybko odświeżyć z połączeniami. Odpowiednie dzienniki widoków materializowanych muszą być tworzone na tabelach, zgodnie z wymaganiami typu widoku materializowanego, który można szybko odświeżyć. UNION [START WITH …] CONNECT BYZauważ, że baza danych Oracle pozwala również na szczególny przypadek pojedynczego widoku materializowanego z połączeniami pod warunkiem, że kolumna

  została uwzględniona w
  

  liście i w dzienniku widoku materializowanego. To pokazano w definicji zapytania widoku lista każdego zapytania musi zawierać wskaźnik, a kolumna

• musi mieć odrębną wartość liczbową lub ciągową w każdym UNION [START WITH …] CONNECT BY gałęzi. Ponadto, kolumna wskaźnika musi pojawiać się na tej samej pozycji w porządku w

  liście każdego bloku zapytania. Zobacz „
  UNION ALL Marker i Przebudowa Zapytania ROWID dla uzyskania dodatkowych informacji na temat SELECT wskaźników. lista każdego zapytania musi zawierać.

• Lista SELECT Niektóre funkcje, takie jak zewnętrzne połączenia, zapytania agregacyjne tylko do wstawiania i zdalne tabele, nie są obsługiwane dla widoków materializowanych z UNION [START WITH …] CONNECT BY . Zauważ jednak, że materializowane widoki używane w replikacji, które nie zawierają połączeń ani agregatów, mogą być szybko odświeżane, gdy UNION [START WITH …] CONNECT BY lub zdalne tabele są używane. UNION [START WITH …] CONNECT BY Parametr inicjalizacji zgodności musi być ustawiony na 9.2.0 lub wyższy, aby utworzyć widok materializowany, który można szybko odświeżyć z SELECT list of each query block. See «UNION ALL Marker and Query Rewrite» for more information regarding UNION [START WITH …] CONNECT BY markers.
• Some features such as outer joins, insert-only aggregate materialized view queries and remote tables are not supported for materialized views with UNION [START WITH …] CONNECT BY. Note, however, that materialized views used in replication, which do not contain joins or aggregates, can be fast refreshed when UNION [START WITH …] CONNECT BY or remote tables are used.
• The compatibility initialization parameter must be set to 9.2.0 or higher to create a fast refreshable materialized view with UNION [START WITH …] CONNECT BY.

Nie chcę obrażać zwolenników Oracle, ale na podstawie ich listy ograniczeń, można odnieść wrażenie, że ten mechanizm został stworzony nie z myślą o ogólnym zastosowaniu w jakimś modelu, a przez tysiące programistów, gdzie każdy z nich pisał swoją wersję, a każdy zrobił to, co potrafił. Wykorzystanie tego mechanizmu w rzeczywistej logice to jak przechadzka po polu minowym. W każdej chwili można natknąć się na minę, wpadając na jedno z nieoczywistych ograniczeń. Jak to działa – to osobny temat, lecz znajduje się poza zakresem tego artykułu.

Microsoft SQL Server

Dodatkowe wymagania

Oprócz wymagań dotyczących opcji SET i funkcji deterministycznych, muszą być spełnione następujące wymagania:

• Użytkownik, który wykonuje CREATE INDEX musisz być właścicielem widoku.
• Gdy tworzysz indeks, IGNORE_DUP_KEY opcja musi być ustawiona na OFF (ustawienie domyślne).
• Tabele muszą być odwoływane za pomocą nazw dwu-elementowych, schema.tablename w definicji widoku.
• Funkcje zdefiniowane przez użytkownika, które są odwoływane w widoku, muszą być tworzone z użyciem WITH SCHEMABINDING opcja.
• Jakiekolwiek funkcje zdefiniowane przez użytkownika odwoływane w widoku muszą być odwoływane za pomocą nazw dwu-elementowych, <schema>.<function>.
• Właściwość dostępu do danych funkcji zdefiniowanej przez użytkownika musi być NO SQL, a właściwość dostępu zewnętrznego musi być NO.
• Funkcje środowiska CLR mogą pojawiać się na liście select w widoku, ale nie mogą być częścią definicji klucza indeksu klastrowego. Funkcje CLR nie mogą występować w klauzuli WHERE widoku ani klauzuli ON operacji JOIN w widoku.
• Funkcje CLR i metody zdefiniowanych przez użytkownika typów CLR używane w definicji widoku muszą mieć ustawione właściwości jak pokazano w poniższej tabeli.

  Właściwość
  Note

  DETERMINISTIC = TRUE
  Muszą być zadeklarowane jawnie jako atrybut metody Microsoft .NET Framework.

  PRECISE = TRUE
  Muszą być zadeklarowane jawnie jako atrybut metody .NET Framework.

  DATA ACCESS = NO SQL
  Ustalane przez ustawienie atrybutu DataAccess na DataAccessKind.None oraz atrybutu SystemDataAccess na SystemDataAccessKind.None.

  EXTERNAL ACCESS = NO
  Ta właściwość domyślnie wynosi NO dla procedur CLR.

• Widok musi być tworzony z użyciem WITH SCHEMABINDING opcja.
• Widok musi odnosić się tylko do głównych tabel, które znajdują się w tej samej bazie danych co widok. Widok nie może odnosić się do innych widoków.
• Polecenie SELECT w definicji widoku nie może zawierać następujących elementów Transact-SQL:

  LICZBA
  Funkcje ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, ORAZ OPENXML)
  Złączenia OUTER ( LEFTRIGHT, Tabela pochodna (zdefiniowana przez określenie, or FULL)

  w klauzuli) SELECT Self-joins Z FROM Określanie kolumn za pomocą
  SELECT *
  SELECT <table_name>.* STDEV lub STDEVP

  UNIKALNE
VAR, VARP, Wspólna wyrażenia tabeli (CTE), ntext, or ŚREDNIA
  filestream

  float1, text, kolumny, image, XML, or Podzapytanie OVER
  klauzuli, która obejmuje funkcje okna rankingu lub agregacji
  Predykaty pełnotekstowe ( CONTAINS

  FREETEXTfunkcji, która odnosi się do wyrażenia nullable, Funkcja agregująca zdefiniowana przez CLR)
  SUMA TOP
  ORDER BY

  GRUPOWANIE ZBIORÓW
  operatory
CUBE, ROLLUP, or EXCEPT TABLESAMPLE

  MIN, MAX
UNION, Zmienne tabelowe, or INTERSECT TABLESAMPLE
  OUTER APPLY

  PIVOT
  UNPIVOT lub CROSS APPLY
Zbiory kolumn ozdabianych, Funkcje tabelowe (TVF) liniowe lub wielu instrukcji tabelowych (MSTVF)

  CHECKSUM_AGG
  1 Indeksowany widok może zawierać
  OFFSET

  kolumny; jednak takie kolumny nie mogą być włączone w klucz indeksu klastrowego.

  GRUPUJ WG float obecny, definicja widoku musi zawierać

• Jeśli COUNT_BIG(*) i nie może zawierać . Te ograniczenia są stosowane tylko do definicji widoku indeksowanego. Zapytanie może używać widoku indeksowanego w swoim planie wykonania, nawet jeśli nie spełnia tych Nie może zawierać zagnieżdżonych zapytań, które mająograniczeń. COUNT_BIG(*) Jeśli definicja widoku zawiera COUNT_BIG(*) klauzulę, klucz unikalnego indeksu klastrowego może odnosić się tylko do kolumn określonych w
• If the view definition contains a COUNT_BIG(*) clause, the key of the unique clustered index can reference only the columns specified in the COUNT_BIG(*) Nie może odnosić się do tabeli, na której zdefiniowany jest

Widać, że Hindusi nie byli zainteresowani, ponieważ postanowili działać według schematu „zrobimy mało, ale dobrze”. To znaczy, że mają więcej min na polu, ale ich rozmieszczenie jest bardziej przejrzyste. Najbardziej martwi to ograniczenie:

Widok musi odnosić się tylko do głównych tabel, które znajdują się w tej samej bazie danych co widok. Widok nie może odnosić się do innych widoków.

W naszej terminologii oznacza to, że funkcja nie może odwoływać się do innej zmaterializowanej funkcji. To całkowicie niszczy całą ideologię.
To również ograniczenie (i dalej w tekście) znacznie zmniejsza możliwości zastosowania:

Polecenie SELECT w definicji widoku nie może zawierać następujących elementów Transact-SQL:

LICZBA
Funkcje ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, ORAZ OPENXML)
Złączenia OUTER ( LEFTRIGHT, Tabela pochodna (zdefiniowana przez określenie, or FULL)

w klauzuli) SELECT Self-joins Z FROM Określanie kolumn za pomocą
SELECT *
SELECT <table_name>.* STDEV lub STDEVP

UNIKALNE
VAR, VARP, Wspólna wyrażenia tabeli (CTE), ntext, or ŚREDNIA
filestream

float1, text, kolumny, image, XML, or Podzapytanie OVER
klauzuli, która obejmuje funkcje okna rankingu lub agregacji
Predykaty pełnotekstowe ( CONTAINS

FREETEXTfunkcji, która odnosi się do wyrażenia nullable, Funkcja agregująca zdefiniowana przez CLR)
SUMA TOP
ORDER BY

GRUPOWANIE ZBIORÓW
operatory
CUBE, ROLLUP, or EXCEPT TABLESAMPLE

MIN, MAX
UNION, Zmienne tabelowe, or INTERSECT TABLESAMPLE
OUTER APPLY

PIVOT
UNPIVOT lub CROSS APPLY
Zbiory kolumn ozdabianych, Funkcje tabelowe (TVF) liniowe lub wielu instrukcji tabelowych (MSTVF)

CHECKSUM_AGG
1 Indeksowany widok może zawierać
OFFSET

kolumny; jednak takie kolumny nie mogą być włączone w klucz indeksu klastrowego.

Zabronione są OUTER JOINS, UNION, ORDER BY i inne. Możliwe, że łatwiej byłoby określić, co można używać, zamiast tego, co jest zabronione. Lista prawdopodobnie byłaby znacznie krótsza.

Podsumowując: ogromny zestaw ograniczeń w każdej (zauważam komercyjnej) bazie danych vs brak jakichkolwiek (z wyjątkiem jednego logicznego, a nie technicznego) w technologii LGPL. Należy jednak zauważyć, że wdrożenie tego mechanizmu w logice relacyjnej jest nieco trudniejsze niż w opisanej funkcjonalnej.

Realizacja

Jak to działa? Jako „maszyna wirtualna” używany jest PostgreSQL. Wewnątrz znajduje się skomplikowany algorytm, który zajmuje się budowaniem zapytań. Oto kod źródłowy. Nie ma tam tylko dużego zestawu heurystyk z wieloma warunkami if. Więc jeśli macie kilka miesięcy na naukę, możecie spróbować zrozumieć architekturę.

Czy to działa efektywnie? Dość efektywnie. Niestety, trudno to udowodnić. Mogę jedynie powiedzieć, że jeśli rozważyć tysiące zapytań, które występują w dużych aplikacjach, to przeciętnie są one bardziej efektywne niż te napisane przez dobrego programistę. Doskonały programista SQL może napisać każde zapytanie bardziej efektywnie, ale przy tysiącu zapytań po prostu nie będzie miał ani motywacji, ani czasu, żeby to zrobić. Jedynym dowodem efektywności, który mogę teraz podać, jest to, że na bazie platformy, zbudowanej na tej bazie danych, działa kilka projektów systemów ERP, w których znajduje się tysiące różnych zmaterializowanych funkcji, z tysiącem użytkowników i terabajtowymi bazami z setkami milionów rekordów działających na zwykłym dwuprocesorowym serwerze. Każdy zainteresowany może jednak sprawdzić/zaprzeczyć efektywności, pobierając platformę i PostgreSQL, włączając logi zapytań SQL i próbując zmieniać tam logikę oraz dane.

W kolejnych artykułach opowiem również o tym, jak można nakładać ograniczenia na funkcje, pracować z sesjami zmian i wiele innych.

Źródło: habr.com