Balancering van schrijven en lezen in de database

In de vorige artikel beschreef ik het concept en de implementatie van een database, gebaseerd op functies in plaats van tabellen en velden zoals in relationele databases. Er werden veel voorbeelden gegeven die de voordelen van deze aanpak ten opzichte van de klassieke benadering tonen. Veel mensen vonden deze voorbeelden niet overtuigend genoeg.

In dit artikel laat ik zien hoe zo'n concept het mogelijk maakt om snel en eenvoudig de balans tussen schrijven en lezen in de database te behouden zonder enige wijziging in de logica. Een soortgelijke functionaliteit is geprobeerd te implementeren in moderne commerciële DBMS (met name, Oracle en Microsoft SQL Server). Aan het einde van het artikel zal ik laten zien dat hun resultaten, om het voorzichtig te zeggen, niet zo goed zijn.

Omschrijving

Zoals eerder vermeld, zal ik beginnen met voorbeelden voor beter begrip. Stel dat we de logica moeten implementeren die een lijst van afdelingen met het aantal medewerkers daarin en hun totale salaris teruggeeft.

In een functionele database zou dit er als volgt uitzien:

CLASS Department 'Afdeling';
name 'Naam' = DATA STRING[100] (Afdeling);

CLASS Employee 'Medewerker';
department 'Afdeling' = DATA Department (Employee);
salary 'Salaris' = DATA NUMERIC[10,2] (Employee);

countEmployees 'Aantal medewerkers' (Department d) = 
    GROEP SOM 1 ALS afdeling(Werknemer e) = d;
salarySum 'Totaal salaris' (Department d) = 
    GROEP SOM salaris(werknemer e) ALS afdeling(e) = d;

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

De complexiteit van het uitvoeren van deze query in elk DBMS is equivalent aan O(aantal medewerkers), omdat voor deze berekening de hele tabel met medewerkers moet worden doorzocht en deze vervolgens op afdeling moet worden gegroepeerd. Er zal ook een kleine (laten we aannemen dat er veel meer medewerkers dan afdelingen zijn) toevoeging zijn, afhankelijk van het gekozen plan O(log aantal medewerkers) of O(aantal afdelingen) voor de groepering en dergelijke.

Het is duidelijk dat de overheadkosten voor uitvoering in verschillende DBMS verschillend kunnen zijn, maar de complexiteit zal op geen enkele manier veranderen.

In de voorgestelde implementatie zal de functionele DBMS één subquery genereren die de vereiste waarden per afdeling berekent en vervolgens een JOIN maken met de afdelingstabel om de naam te verkrijgen. Echter, voor elke functie is het mogelijk om een speciale marker MATERIALIZED op te geven bij de declaratie. Het systeem maakt automatisch het bijbehorende veld voor elke dergelijke functie. Wanneer de waarde van de functie verandert, verandert ook de waarde van het veld binnen dezelfde transactie. Bij het aanroepen van deze functie zal er een verwijzing zijn naar het vooraf berekende veld.

In het bijzonder, als je MATERIALIZED instelt voor functies countEmployees en salarySum, dan worden er twee velden aan de tabel met de afdelingen toegevoegd, waarin het aantal medewerkers en hun totale salaris wordt opgeslagen. Bij elke wijziging van medewerkers, hun salarissen of afdelingsverbanden, past het systeem automatisch de waarden van deze velden aan. De bovenstaande query zal dan direct naar deze velden verwijzen en zal uitgevoerd worden in O(aantal afdelingen).

Wat zijn de beperkingen? Slechts één: zo'n functie moet een eindig aantal invoerwaarden hebben waarvoor de waarde gedefinieerd is. Anders kan er geen tabel worden opgebouwd die al zijn waarden opslaat, omdat er niet zoiets als een tabel kan zijn met een oneindig aantal rijen.

Voorbeeld:

aantalMedewerkers 'Aantal medewerkers met salaris > N' (Afdeling d, NUMERIC[10,2] N) = 
    GROEP SOM salaris(Employee e) ALS afdeling(e) = d EN salaris(e) > N;


Deze functie is gedefinieerd voor een oneindig aantal waarden van het getal N (bijvoorbeeld, elke negatieve waarde is geldig). Daarom kan je er geen MATERIALIZED op zetten. Dit is een logisch, en geen technisch, beperking (dat wil zeggen, niet omdat we het niet konden implementeren). Voor de rest zijn er geen beperkingen. Groeperingen, sorteringen, AND en OR, PARTITION, recursies, en dergelijke kunnen worden gebruikt.

Bijvoorbeeld, in taak 2.2 van het vorige artikel kan je MATERIALIZED instellen op beide functies:

gekocht 'Gekocht' (Klant c, Product p, INTEGER y) = 
    GROEP SOM som(Detail d) ALS 
        klant(bestelling(d)) = c EN 
        product(d) = p EN 
        extractYear(date(order(d))) = y MATERIALIZED;
beoordeling 'Beoordeling' (Klant c, Product p, INTEGER y) = 
    PARTITION SUM 1 ORDER DESC bought(c, p, y), p BY c, y MATERIALIZED;
SELECT contactNaam(Klant c), naam(Product p) WAAR beoordeling(c, p, 1997) < 3;


Het systeem zal automatisch een tabel creëren met sleuteltypen Klant, Product en INTEGER, voegt er twee velden aan toe en zal de waarden in deze velden bijwerken bij elke wijziging. Bij verdere aanvragen naar deze functies zal er geen berekening plaatsvinden, maar zullen de waarden uit de overeenkomstige velden worden gelezen.

Met deze mechanismen kan men bijvoorbeeld zich ontdoen van recursies (CTE) in queries. Laten we specifiek groepen beschouwen die een boom vormen met een kind/ouder-relatie (elke groep heeft een verwijzing naar zijn ouder):

ouder = DATA Groep (Groep);


In de functionele databank kan de logica van recursies als volgt worden gedefinieerd:

niveau (Groep kind, Groep ouder) = RECURSION 1l ALS kind IS Groep EN ouder == kind
                                                             STAP 2l ALS ouder == ouder($ouder);
isOuder (Groep kind, Groep ouder) = WAAR ALS niveau(kind, ouder) GEMATERIALISEERD;


Aangezien voor de functie isParent MATERIALIZED is ingesteld, zal er een tabel worden aangemaakt met twee sleutels (groepen), waarin het veld isParent waar is alleen als de eerste sleutel een afstammeling van de tweede is. Het aantal records in deze tabel zal gelijk zijn aan het aantal groepen vermenigvuldigd met de gemiddelde diepte van de boom. Als het nodig is, bijvoorbeeld, om het aantal afstammelingen van een bepaalde groep te tellen, kan men deze functie aanroepen:

childrenCount (Groep g) = GROEP SOM 1 ALS isOuder(Groep kind, g);


Er zal geen CTE in de SQL-query zijn. In plaats daarvan zal er een eenvoudige GROUP BY zijn.

Met dit mechanisme kan ook eenvoudig de normalisatie van de database worden gemaakt indien nodig:

CLASS Order 'Bestelling';
datum 'Datum' = DATA DATUM (Bestelling);

CLASS OrderDetail 'Bestelregel';
order 'Bestelling' = DATA Order (OrderDetail);
date 'Datum' (OrderDetail d) = date(order(d)) MATERIALIZED INDEXED;

Bij het aanroepen van de functie date voor de bestelregel zal er een lezen uit de tabel met bestelregels plaatsvinden van het veld waarover een index bestaat. Wanneer de datum van de bestelling wordt gewijzigd, zal het systeem automatisch de denormaliserede datum in de regel opnieuw berekenen.

Voordelen

Waar is dit hele mechanisme voor nodig? In klassieke DBMS'en kunnen ontwikkelaars of DBA's zonder query's opnieuw te schrijven alleen de indexen wijzigen, statistieken bepalen en de queryplanner aanwijzingen geven over hoe deze uit te voeren (waarbij HINT's alleen in commerciële DBMS'en bestaan). Hoe goed ze ook hun best doen, ze zullen de eerste query in het artikel niet kunnen uitvoeren zonder (aantal afdelingen) zonder de query's te wijzigen en triggers toe te voegen. In het voorgestelde schema kan men zich in de ontwikkelingsfase geen zorgen maken over de gegevensopslagstructuur en over welke aggregaties te gebruiken. Dit kan allemaal op elk moment tijdens het gebruik worden gewijzigd.

In de praktijk ziet dit er als volgt uit. Sommige mensen ontwikkelen de logica rechtstreeks op basis van de gestelde taak. Ze begrijpen niets van algoritmen en hun complexiteit, geen uitvoeringsplannen, geen soorten join's, en geen enkele andere technische component. Deze mensen zijn eerder businessanalisten dan ontwikkelaars. Vervolgens gaat alles naar tests of productie. Logboekregistratie voor langdurige verzoeken wordt ingeschakeld. Wanneer een langdurig verzoek wordt ontdekt, wordt er al door andere mensen (meer technisch onderlegd, in wezen DBA's) besloten om MATERIALIZED in te schakelen op een bepaalde tussenfunctie. Dit vertraagt de invoer een beetje (omdat een extra veld in de transactie moet worden bijgewerkt). Echter, dit versnelt niet alleen dit verzoek, maar ook alle andere die deze functie gebruiken. De beslissing over welke functie precies te materialiseren wordt relatief eenvoudig genomen. Twee belangrijke parameters: het aantal mogelijke invoerwaarden (precies zoveel records zullen in de bijbehorende tabel staan), en hoe vaak het in andere functies wordt gebruikt.

Er zijn verschillende systemen die vergelijkbaar zijn met OpenMusic. Wellicht is het meest bekende commerciële instrument

In moderne commerciële databasesystemen zijn er vergelijkbare mechanismen: MATERIALIZED VIEW met FAST REFRESH (Oracle) en INDEXED VIEW (Microsoft SQL Server). In PostgreSQL kan een MATERIALIZED VIEW niet binnen een transactie worden bijgewerkt, maar alleen op aanvraag (en met zeer strenge beperkingen), dus dat nemen we niet in overweging. Maar ze hebben enkele problemen, wat hun gebruik aanzienlijk beperkt.

Ten eerste kan je materialisatie alleen inschakelen als je al een gewone VIEW hebt aangemaakt. Anders moet je de andere query's herschrijven om naar de nieuw aangemaakte weergave te verwijzen, om deze materialisatie te gebruiken. Of je laat alles zoals het is, maar dat is minimaal ondoeltreffend als er bepaalde vooraf berekende gegevens zijn, terwijl veel query's die niet altijd gebruiken, maar opnieuw berekenen.

Ten tweede zijn er een enorme hoeveelheid beperkingen:

Oracle

5.3.8.4 Algemene beperkingen op Fast Refresh

De definitieve query van de materialized view is als volgt beperkt:

• De materialized view mag geen verwijzingen bevatten naar niet-herhalende expressies zoals SYSDATE en ROWNUM.
• De materialized view mag geen verwijzingen bevatten naar RAW or LONG RAW gegevens typen.
• Het mag geen SELECT lijstsubquery bevatten.
• Het mag geen analytische functies bevatten (bijvoorbeeld, RANK) in de SELECT clausule.
• Het mag geen tabel verwijzen waarop een XMLIndex index is gedefinieerd.
• Het mag geen MODEL clausule.
• Het mag geen HAVING clausule met een subquery.
• Het mag geen geneste query's bevatten die hebben ANY, ALL, of NOT EXISTS.
• Het mag geen [START WITH …] CONNECT BY clausule.
• Het mag geen meerdere detailtabellen op verschillende locaties bevatten.
• AAN COMMIT Materialized views mogen geen externe detailtabellen hebben.
• Geneste materialized views moeten een join of aggregaat hebben.
• Materialized join views en materialized aggregate views met een GROUP BY clausule mogen niet selecteren uit een index-georganiseerde tabel.

5.3.8.5 Beperkingen op Fast Refresh op Materialized Views met Alleen Joins

Definitiequery's voor materialized views met alleen joins en geen aggregaten hebben de volgende beperkingen op fast refresh:

• Alle beperkingen van «Algemene beperkingen op Fast Refresh«.
• Ze mogen geen GROUP BY clausules of aggregaten hebben.
• Rowids van alle tabellen in de FROM lijst moeten voorkomen in de SELECT lijst van de query.
• Materialized view-logs moeten bestaan met rowids voor alle basis tabellen in de FROM lijst van de query.
• Je kunt geen snel vernieuwbare materialized view maken van meerdere tabellen met eenvoudige joins die een objecttype kolom bevatten in de SELECT verklaring.

Daarnaast zal de vernieuwingsmethode die je kiest niet optimaal efficiënt zijn als:

• De definitieve query een outer join gebruikt die zich gedraagt als een inner join. Als de definitieve query zo'n join bevat, overweeg dan de definitieve query te herschrijven om een inner join te bevatten.
• De SELECT de lijst van de materialized view kolommen bevat expressies op kolommen van meerdere tabellen.

5.3.8.6 Beperkingen op Fast Refresh op Materialized Views met Aggregaten

Definitiequery's voor materialized views met aggregaten of joins hebben de volgende beperkingen op fast refresh:

• Alle beperkingen van «Algemene beperkingen op Fast Refresh«.

Fast refresh wordt ondersteund voor zowel AAN COMMIT en AAN DEMAND materialized views, echter gelden de volgende beperkingen:

• Alle tabellen in de materialized view moeten materialized view-logs hebben, en de materialized view-logs moeten:
  • Alle kolommen bevatten vanuit de tabel die in de materialized view wordt genoemd.
  • Specificeer met ROWID en INCLUSIEF NIEUWE WAARDEN.
  • Specificeer de SEQUENCE clausule als de tabel wordt verwacht een mix van inserts/direct-loads, deletes en updates te bevatten.

• Alleen SUM, COUNT, AVG, STDDEV, VARIANCE, MIN en MAX worden ondersteund voor fast refresh.
• COUNT(*) moet worden gespecificeerd.
• Aggregatiefuncties mogen alleen het buitenste deel van de expressie zijn. Dat wil zeggen, aggregaten zoals AVG(AVG(x)) or AVG(x)+ AVG(x) zijn niet toegestaan.
• Voor elke aggregatie zoals AVG(expr), moet de bijbehorende COUNT(expr) aanwezig zijn. Oracle raad aan dat SUM(expr) wordt opgegeven.
• Als VARIANCE(expr) or STDDEV(expr) wordt opgegeven, COUNT(expr) en SUM(expr) moet worden opgegeven. Oracle raad aan dat SUM(expr *expr) wordt opgegeven.
• De SELECT kolom in de definierende query kan geen complexe expressie met kolommen uit meerdere basis tabellen zijn. Een mogelijke oplossing hiervoor is om een geneste gematerialiseerde weergave te gebruiken.
• De SELECT lijst moet alle GROUP BY kolommen bevatten.
• De gematerialiseerde weergave is niet gebaseerd op een of meer externe tabellen.
• Als je een CHAR gegevenstype in de filterkolommen van een gematerialiseerde weergave-log gebruikt, moeten de tekenreeksen van de hoofdsites en de gematerialiseerde weergave hetzelfde zijn.
• Als de gematerialiseerde weergave een van de volgende heeft, wordt snelle verversing alleen ondersteund bij conventionele DML-inserties en directe laadoperaties.
  • Gematerialiseerde weergaven met MIN or MAX aggregaten
  • Gematerialiseerde weergaven die hebben SUM(expr) maar geen COUNT(expr)
  • Gematerialiseerde weergaven zonder COUNT(*)

  Zo'n gematerialiseerde weergave wordt een insert-only gematerialiseerde weergave genoemd.

• Een gematerialiseerde weergave met MAX or MIN is snel verversbaar na verwijderingen of gemengde DML-instructies als deze geen WAAR clausule.
  De max/min snelle verversing na verwijderingen of gemengde DML heeft niet hetzelfde gedrag als het insert-only geval. Het verwijdert en herberekent de max/min waarden voor de aangetaste groepen. Je moet je bewust zijn van de prestatietimpact.
• Gematerialiseerde weergaven met benoemde weergaven of subquery's in de FROM clausule kunnen snel worden ververst, mits de weergaven volledig kunnen worden samengevoegd. Voor informatie over welke weergaven kunnen samenvoegen, zie Oracle Database SQL Taalreferentie.
• Als er geen externe joins zijn, kun je willekeurige selecties en joins in de WAAR clausule.
• Gematerialiseerde aggregaatweergaven met externe joins zijn snel verversbaar na conventionele DML en directe laadoperaties, mits alleen de externe tabel is gewijzigd. Ook moeten unieke beperkingen bestaan op de join-kolommen van de interne join-tabel. Als er externe joins zijn, moeten alle joins verbonden zijn door ENs en moeten de gelijkheids (=) operator gebruiken.
• Voor gematerialiseerde weergaven met CUBE, ROLLUP, groeperingssets of concatenatie ervan, zijn de volgende beperkingen van toepassing:
  • De SELECT lijst moet een groeperingsonderscheider bevatten die een GROUPING_ID functie op alle GROUP BY expressies kan zijn of GROUPING functies een voor elke GROUP BY expressie. Bijvoorbeeld, als de GROUP BY clausule van de gematerialiseerde weergave «GROUP BY CUBE(a, b)«, dan moet de SELECT lijst ofwel «GROUPING_ID(a, b)» of «GROUPING(a) EN GROUPING(b)» bevatten om de gematerialiseerde weergave snel verversbaar te maken.
  • GROUP BY mag niet resulteren in enige dubbele groeperingen. Bijvoorbeeld, «GROUP BY a, ROLLUP(a, b)» is niet snel verversbaar omdat het resulteert in dubbele groeperingen «(a), (a, b), EN (a)«.

5.3.8.7 Beperkingen op Snelle Verversing op Gematerialiseerde Weergaven met UNION ALL

Gematerialiseerde weergaven met de UNION ALL setoperator ondersteunen de REFRESH FAST optie als aan de volgende voorwaarden is voldaan:

• De definierende query moet de UNION ALL operator op het hoogste niveau hebben.

  De UNION ALL operator kan niet in een subquery worden ingebed, met één uitzondering: De UNION ALL kan zich in een subquery in de FROM clausule bevinden, mits de definierende query van de vorm is SELECT * FROM (weergave of subquery met UNION ALL) zoals in het volgende voorbeeld:

  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;
  

  Let op dat de weergave view_with_unionall voldoet aan de vereisten voor snelle verversing.

• Elk queryblok in de UNION ALL query moet voldoen aan de vereisten van een snel verversbare gematerialiseerde weergave met aggregaten of een snel verversbare gematerialiseerde weergave met joins.

  De juiste gematerialiseerde weergave-logboeken moeten op de tabellen worden gemaakt zoals vereist voor het overeenkomstige type van een snel verversbare gematerialiseerde weergave.
  Houd er rekening mee dat de Oracle Database ook de speciale situatie van een enkele tabel gematerialiseerde weergave met alleen joins toelaat, op voorwaarde dat de ROWID kolom is opgenomen in de SELECT lijst en in de log voor gematerialiseerde weergave. Dit wordt getoond in de definitieve query van de weergave view_with_unionall.

• De SELECT de lijst van elke query moet een UNION ALL marker bevatten, en de UNION ALL kolom moet een unieke constante numerieke of stringwaarde hebben in elke UNION ALL tak. Bovendien moet de marker kolom in dezelfde ordinale positie verschijnen in de SELECT lijst van elke queryblok. Zie «UNION ALL Marker en Query Rewrite» voor meer informatie over UNION ALL markeringen.
• Sommige functies zoals outer joins, alleen-insert aggregaat gematerialiseerde weergave queries en externe tabellen worden niet ondersteund voor gematerialiseerde weergaven met UNION ALL. Houd er echter rekening mee dat gematerialiseerde weergaven die in replicatie worden gebruikt, die geen joins of aggregaten bevatten, snel kunnen worden vernieuwd wanneer UNION ALL of externe tabellen worden gebruikt.
• De initialisatieparameter voor compatibiliteit moet zijn ingesteld op 9.2.0 of hoger om een snel vernieuwbare gematerialiseerde weergave met UNION ALL.

Ik wil de Oracle-fans niet beledigen, maar op basis van hun lijst met beperkingen lijkt het alsof dit mechanisme niet in het algemeen geval is geschreven, gebruikmakend van een of andere model, maar duizenden Indiërs, waar iedereen zijn eigen tak kreeg en ieder van hen deed wat hij kon. Het gebruik van dit mechanisme voor echte logica is als lopen op een mijnenveld. Op elk moment kan je een mijn krijgen, door op een van de niet-ideale beperkingen te stappen. Hoe dit werkt, is ook een apart vraag, maar dat valt buiten het bereik van dit artikel.

Microsoft SQL Server

Aanvullende Vereisten

Naast de SET-opties en vereisten voor deterministische functies, moeten de volgende vereisten worden voldaan:

• De gebruiker die CREATE INDEX uitvoert, moet de eigenaar van de weergave zijn.
• Tijdens het maken van de index moet de IGNORE_DUP_KEY optie op UIT (de standaardinstelling) worden ingesteld.
• Tabellen moeten worden aangeduid met tweedelige namen, schema.tablename in de definitie van de weergave.
• Door de gebruiker gedefinieerde functies die in de weergave worden aangeduid, moeten zijn gemaakt met de WITH SCHEMABINDING optie.
• Alle door de gebruiker gedefinieerde functies die in de weergave worden aangeduid, moeten zijn aangeduid met tweedelige namen, <schema>.<functie>.
• De gegevens toegangseigenschap van een door de gebruiker gedefinieerde functie moet zijn NO SQL, en de externe toegangseigenschap moet zijn NEE.
• Functies van de Common Language Runtime (CLR) kunnen voorkomen in de selectlijst van de weergave, maar kunnen geen deel uitmaken van de definitie van de geclusterde indexsleutel. CLR-functies kunnen niet voorkomen in de WHERE-clausule van de weergave of de ON-clausule van een JOIN-operatie in de weergave.
• CLR-functies en methoden van CLR door de gebruiker gedefinieerde types die in de definitie van de weergave worden gebruikt, moeten de eigenschappen hebben zoals weergegeven in de volgende tabel.

  Eigenschap
  Note

  DETERMINISTISCH = WAAR
  Moet expliciet worden gedeclareerd als een attribuut van de Microsoft .NET Framework-methode.

  PRECISE = WAAR
  Moet expliciet worden gedeclareerd als een attribuut van de .NET Framework-methode.

  GEGEVEN TOEGANG = NO SQL
  Bepaald door het instellen van het DataAccess-attribuut op DataAccessKind.None en het SystemDataAccess-attribuut op SystemDataAccessKind.None.

  EXTERNE TOEGANG = NEE
  Deze eigenschap is standaard op NEE voor CLR-routines.

• De weergave moet worden gemaakt met de WITH SCHEMABINDING optie.
• De weergave mag alleen basis tabellen refereren die zich in dezelfde database als de weergave bevinden. De weergave mag geen andere weergaven refereren.
• De SELECT-instructie in de definitie van de weergave mag niet de volgende Transact-SQL-elementen bevatten:

  COUNT
  ROWSET-functies (OPENDATASOURCE, OPENQUERY, OPENROWSET, EN OPENXML)
  Buitenlandse joins (LINKS, RECHTS, of VOLLEDIG)

  Afgeleide tabel (gedefinieerd door een SELECT in de clausule) FROM Zelf-joins
  Kolommen specificeren door gebruik te maken van
  SELECT * SELECT <table_name>.* or STDEV

  UNIEK
STDEVP, VAR, VARP, Gemeenschappelijke tabel expressie (CTE), of AVG
  ntext

  float1, text, XML, afbeelding, XML, of filestream kolommen
  Subquery
  OVER clausule, die rangschik- of aggregatiefuncties voor vensters omvat

  Volledige tekstpredicaten (BEVATTEN, VRIJETEKST)
  SUM functie die een nullable expressie verwijst
  ORDER BY

  CLR gebruikergeedefinieerde aggregatiefunctie
  TOP
CUBE, ROLLUP, of GROEPENDE SETS operatoren

  MIN, MAX
UNION, UITZONDEREN, of INTERSECT operatoren
  TABELMONSTER

  Tabelvariabelen
  BUITEN APPLY or CROSS APPLY
PIVOT, UNPIVOT

  Sparce kolomsets
  Inline (TVF) of multi-statement tabelwaarde functies (MSTVF)
  OFFSET

  CHECKSUM_AGG

  1 De geïndexeerde weergave kan bevatten float kolommen; echter, dergelijke kolommen kunnen niet in de sleutel van de geclusterde index worden opgenomen.

• Als Toegang tot de recursieve 'tabel' kan zich niet in een genestelde subquery bevinden. is aanwezig, de WEERGAVE-definitie moet bevatten COUNT_BIG(*) en mag niet bevatten HAVING. Deze Toegang tot de recursieve 'tabel' kan zich niet in een genestelde subquery bevinden. beperkingen zijn alleen van toepassing op de definitie van de geïndexeerde weergave. Een vraag kan een geïndexeerde weergave gebruiken in zijn uitvoeringsplan, zelfs als deze niet aan deze Toegang tot de recursieve 'tabel' kan zich niet in een genestelde subquery bevinden. beperkingen voldoet.
• Als de definitie van de weergave een Toegang tot de recursieve 'tabel' kan zich niet in een genestelde subquery bevinden. clausule bevat, kan de sleutel van de unieke geclusterde index alleen de kolommen refereren die in de Toegang tot de recursieve 'tabel' kan zich niet in een genestelde subquery bevinden. clausule.

Hier wordt duidelijk dat Indiërs niet zijn aangetrokken, omdat ze besloten om het schema te volgen ‘we doen het een beetje, maar goed’. Dat wil zeggen, ze hebben meer mijnen op het veld, maar hun ligging is transparanter. Het meest teleurstellende is deze beperking:

De weergave mag alleen basis tabellen refereren die zich in dezelfde database als de weergave bevinden. De weergave mag geen andere weergaven refereren.

In onze terminologie betekent dit dat de functie niet kan verwijzen naar een andere gematerialiseerde functie. Dit snijdt de hele ideologie in de kiem.
Ook deze beperking (en verderop in de tekst) vermindert de gebruiksmogelijkheden aanzienlijk:

De SELECT-instructie in de definitie van de weergave mag niet de volgende Transact-SQL-elementen bevatten:

COUNT
ROWSET-functies (OPENDATASOURCE, OPENQUERY, OPENROWSET, EN OPENXML)
Buitenlandse joins (LINKS, RECHTS, of VOLLEDIG)

Afgeleide tabel (gedefinieerd door een SELECT in de clausule) FROM Zelf-joins
Kolommen specificeren door gebruik te maken van
SELECT * SELECT <table_name>.* or STDEV

UNIEK
STDEVP, VAR, VARP, Gemeenschappelijke tabel expressie (CTE), of AVG
ntext

float1, text, XML, afbeelding, XML, of filestream kolommen
Subquery
OVER clausule, die rangschik- of aggregatiefuncties voor vensters omvat

Volledige tekstpredicaten (BEVATTEN, VRIJETEKST)
SUM functie die een nullable expressie verwijst
ORDER BY

CLR gebruikergeedefinieerde aggregatiefunctie
TOP
CUBE, ROLLUP, of GROEPENDE SETS operatoren

MIN, MAX
UNION, UITZONDEREN, of INTERSECT operatoren
TABELMONSTER

Tabelvariabelen
BUITEN APPLY or CROSS APPLY
PIVOT, UNPIVOT

Sparce kolomsets
Inline (TVF) of multi-statement tabelwaarde functies (MSTVF)
OFFSET

CHECKSUM_AGG

Buitenste JOINs, UNIE, ORDER BY en anderen zijn verboden. Misschien was het eenvoudiger geweest om aan te geven wat er gebruikt kan worden dan wat er niet kan worden gebruikt. De lijst zou waarschijnlijk veel korter zijn geweest.

Samengevat: een enorme set beperkingen in elke (merk op, commerciële) DBMS vs geen (behalve één logische, en geen technische) in de LGPL-technologie. Het moet echter worden opgemerkt dat het implementeren van dit mechanisme in relationele logica iets complexer is dan in de beschreven functionele.

Implementatie

Hoe werkt dit? PostgreSQL wordt gebruikt als de ‘virtuele machine’. Binnenin bevindt zich een complex algoritme dat zich bezighoudt met het opbouwen van queries. Hier source code. En het is niet zomaar een grote set heuristieken met een hoop if-statements. Dus als je een paar maanden hebt om te leren, kun je proberen de architectuur te begrijpen.

Werkt dit effectief? Vrij effectief. Helaas is het moeilijk om dit te bewijzen. Ik kan alleen zeggen dat als je duizenden verzoeken bekijkt die in grote applicaties voorkomen, ze gemiddeld efficiënter zijn dan bij een goede ontwikkelaar. Een uitstekende SQL-programmeur kan elke query efficiënter schrijven, maar bij duizend verzoeken heeft hij gewoon geen motivatie of tijd om dit te doen. Het enige dat ik nu als bewijs voor de effectiviteit kan aanvoeren, is dat er verschillende projecten zijn die op dit databasesysteem zijn gebouwd. ERP-systemen, waarin duizenden verschillende MATERIALIZED functies zijn, met duizenden gebruikers en terabytes aan databases met honderden miljoenen records, die draaien op een gewone tweekern-server. Voor wie dat wil, kan de effectiviteit controleren of ontkrachten door het platform en PostgreSQL, deze SQL-querylogging in te schakelen en te proberen de logica en gegevens daar te wijzigen.

In de volgende artikelen zal ik ook vertellen hoe je beperkingen op functies kunt toepassen, werken met sessies van wijzigingen, en nog veel meer.

Bron: habr.com