Naar aanleiding van Highload++ Siberië 2019 - 8 taken over Oracle

Hallo!

Op 24-25 juni vond in Novosibirsk de conferentie Highload++ Siberia 2019 plaats. Onze mensen waren ook aanwezig. presentatie «Container databases van Oracle (CDB/PDB) en hun praktische toepassing voor softwareontwikkeling», we zullen de tekstversie iets later publiceren. Het was geweldig, bedankt. olegbunin voor de organisatie, en ook voor iedereen die gekomen is.

In deze post willen we graag de opdrachten delen die we bij onze stand hadden, zodat je je kennis van Oracle kunt testen. Onder de omslag — 8 opdrachten, antwoordmogelijkheden en uitleg.

Wat is de maximale waarde van de sequence die we zullen zien na het uitvoeren van het volgende script?

create sequence s start with 1;

select s.currval, s.nextval, s.currval, s.nextval, s.currval
from dual
connect by level <= 5;

• 1
• 5
• 10
• 25
• Geen enkele, er zal een foutmelding zijn.

AntwoordVolgens de documentatie van Oracle (geciteerd uit 8.1.6):
Binnen een enkele SQL-instructie verhoogt Oracle de sequence slechts één keer per rij. Als een instructie meer dan één verwijzing naar NEXTVAL voor een sequence bevat, verhoogt Oracle de sequence één keer en retourneert dezelfde waarde voor alle vermeldingen van NEXTVAL. Als een instructie verwijzingen naar zowel CURRVAL als NEXTVAL bevat, verhoogt Oracle de sequence en retourneert dezelfde waarde voor zowel CURRVAL als NEXTVAL, ongeacht hun volgorde binnen de instructie.

Dus, de maximale waarde zal overeenkomen met het aantal rijen, dat wil zeggen 5..

Hoeveel rijen zullen er in de tabel zijn na het uitvoeren van het volgende script?

create table t(i integer check (i < 5));

create procedure p(p_from integer, p_to integer) as
begin
    for i in p_from .. p_to loop
        insert into t values (i);
    end loop;
end;
/

exec p(1, 3);
exec p(4, 6);
exec p(7, 9);

• 0
• 3
• 4
• 5
• 6
• 9

AntwoordVolgens de documentatie van Oracle (geciteerd uit 11.2):

Voordat een SQL-instructie wordt uitgevoerd, markeert Oracle een impliciete savepoint (niet beschikbaar voor jou). Als de instructie mislukt, wordt deze automatisch teruggedraaid en retourneert Oracle de toepasselijke foutcode aan SQLCODE in de SQLCA. Bijvoorbeeld, als een INSERT-instructie een fout veroorzaakt door een duplicaatwaarde in een unieke index in te voegen, wordt de instructie teruggedraaid.

Een aanroep van een opgeslagen procedure vanaf de client wordt ook behandeld als een enkele instructie. Dus de eerste aanroep van de opgeslagen procedure eindigt succesvol en voegt drie records in; de tweede aanroep van de opgeslagen procedure eindigt met een fout en draait het vierde record terug dat erin was toegevoegd; de derde aanroep eindigt met een fout, en er blijven drie records in de tabel over..

Hoeveel rijen zullen er in de tabel zijn na het uitvoeren van het volgende script?

create table t(i integer, constraint i_ch check (i < 3));

begin
    insert into t values (1);
    insert into t values (null);
    insert into t values (2);
    insert into t values (null);
    insert into t values (3);
    insert into t values (null);
    insert into t values (4);
    insert into t values (null);
    insert into t values (5);
exception
    when others then
        dbms_output.put_line('Oops!');
end;
/

• 1
• 2
• 3
• 4
• 5
• 6
• 7

AntwoordVolgens de documentatie van Oracle (geciteerd uit 11.2):

Een checkbeperking laat je een voorwaarde specificeren die elke rij in de tabel moet voldoen. Om aan de beperking te voldoen, moet elke rij in de tabel de voorwaarde ofwel WAAR of onbekend (door een null) maken. Wanneer Oracle een checkbeperkingsvoorwaarde voor een specifieke rij evalueert, verwijzen kolomnamen in de voorwaarde naar de kolomwaarden in die rij.

Daarom zal de null-waarde de controle doorstaan, en het anonieme blok zal succesvol worden uitgevoerd tot het moment dat geprobeerd wordt de waarde 3 in te voegen. Daarna zal het foutafhandelingsblok de uitzondering opvangen, wordt er geen rollback uitgevoerd, en zullen er vier rijen in de tabel blijven. met de waarden 1, null, 2 en opnieuw null.

Welke paren waarden zullen dezelfde hoeveelheid ruimte in het blok innemen?

create table t (
    a char(1 char),
    b char(10 char),
    c char(100 char),
    i number(4),
    j number(14),
    k number(24),
    x varchar2(1 char),
    y varchar2(10 char),
    z varchar2(100 char));
 
insert into t (a, b, i, j, x, y)
    values ('Y', 'Vasya', 10, 10, 'D', 'Vasya');

• A en X
• B en Y
• C en K
• C en Z
• K en Z
• I en J
• J en X
• Alle bovenstaande

AntwoordLaten we enkele uittreksels uit de documentatie (12.1.0.2) over het opslaan van verschillende datatypes in Oracle weergeven.

CHAR Gegevenstype
Het CHAR gegevenstype specificeert een karakterreeks met vaste lengte in de databasetekenset. Je specificeert de databasetekenset wanneer je je database maakt. Oracle zorgt ervoor dat alle waarden die in een CHAR-kolom zijn opgeslagen, de lengte hebben die is opgegeven door de grootte in de geselecteerde lengte-semantiek. Als je een waarde invoegt die korter is dan de kolomlengte, voegt Oracle zwarte spaties toe aan de waarde tot de kolomlengte.

VARCHAR2 Gegevenstype
Het VARCHAR2 gegevenstype specificeert een karakterreeks met variabele lengte in de databasetekenset. Je specificeert de databasetekenset wanneer je je database maakt. Oracle slaat een tekenwaarde in een VARCHAR2-kolom precies op zoals je die opgeeft, zonder extra spaties, op voorwaarde dat de waarde de kolomlengte niet overschrijdt.

NUMBER Gegevenstype
Het NUMBER gegevenstype slaat nul op, evenals positieve en negatieve vaste getallen met absolute waarden van 1.0 x 10-130 tot maar niet inclusief 1.0 x 10126. Als je een wiskundige uitdrukking opgeeft waarvan de waarde een absolute waarde heeft die groter is dan of gelijk is aan 1.0 x 10126, dan geeft Oracle een foutmelding. Elke NUMBER-waarde vereist tussen 1 en 22 bytes. Houd hier rekening mee: de kolomgrootte in bytes voor een bepaalde numerieke datavalue NUMBER(p), waarbij p de precisie van een gegeven waarde is, kan worden berekend met de volgende formule: ROUND((length(p)+s)/2))+1 waarbij s gelijk is aan nul als het getal positief is, en s gelijk is aan 1 als het getal negatief is.

Bovendien, laten we een uittreksel uit de documentatie over het opslaan van Null-waarden bekijken.

Een null is de afwezigheid van een waarde in een kolom. Nulls geven ontbrekende, onbekende of niet-toepasbare gegevens aan. Nulls worden in de database opgeslagen als ze vallen tussen kolommen met gegevenswaarden. In deze gevallen is 1 byte nodig om de lengte van de kolom op te slaan (nul). Achterlopende nulls in een rij vereisen geen opslag omdat een nieuwe rijheader aangeeft dat de resterende kolommen in de vorige rij null zijn. Bijvoorbeeld, als de laatste drie kolommen van een tabel null zijn, worden er geen gegevens opgeslagen voor deze kolommen.

Op basis van deze gegevens bouwen we redeneringen op. We veronderstellen dat de DB gebruikmaakt van de codering AL32UTF8. In deze codering nemen Russische letters 2 bytes in beslag.

1) A en X, de waarde van de kolom a 'Y' neemt 1 byte in beslag, de waarde van de kolom x 'D' – 2 bytes.
2) B en Y, ‘Vasya’ in b zal worden aangevuld met spaties tot 10 tekens en zal 14 bytes innemen, ‘Vasya’ in d – zal 8 bytes innemen.
3) C en K. Beide velden hebben de waarde NULL, er zijn significante velden na, dus nemen ze elk 1 byte in.
4) C en Z. Beide velden hebben de waarde NULL, maar veld Z is het laatste in de tabel, dus neemt geen ruimte in (0 bytes). Veld C neemt 1 byte in.
5) K en Z. Vergelijkbaar met de vorige geval. De waarde in veld K neemt 1 byte in, in Z – 0.
6) I en J. Volgens de documentatie zullen beide waarden elk 2 bytes innemen. De lengte wordt berekend volgens de formule uit de documentatie: round((1 + 0)/2) + 1 = 1 + 1 = 2.
7) J en X. De waarde in veld J neemt 2 bytes in, de waarde in veld X neemt 2 bytes in.

Samenvattend, de juiste combinaties zijn: C en K, I en J, J en X.

Wat zal ongeveer de clustering factor van de index T_I zijn?

create table t (i integer);
 
insert into t select rownum from dual connect by level <= 10000;
 
create index t_i on t(i);

• Enkele tientallen
• Enkele honderden
• Enkele duizenden
• Enkele tienduizenden

AntwoordVolgens de Oracle-documentatie (geciteerd uit 12.1):

Voor een B-tree index meet de index clustering factor de fysieke groepering van rijen in relatie tot een indexwaarde.

De index clustering factor helpt de optimizer te beslissen of een indexscan of een volledige tabelscan efficiënter is voor bepaalde queries. Een lage clustering factor geeft aan dat een indexscan efficiënt is.

Een clustering factor die dicht bij het aantal blokken in een tabel ligt, geeft aan dat de rijen fysiek zijn geordend in de tabelblokken volgens de index sleutel. Als de database een volledige tabelscan uitvoert, dan haalt de database de rijen op zoals ze op schijf zijn opgeslagen, gesorteerd op de index sleutel. Een clustering factor die dicht bij het aantal rijen ligt, geeft aan dat de rijen willekeurig verdeeld zijn over de databaseblokken in relatie tot de index sleutel. Als de database een volledige tabelscan uitvoert, zal de database rijen niet in een gesorteerde volgorde ophalen volgens deze index sleutel.

In dit geval zijn de gegevens perfect gesorteerd, dus zal de clustering factor gelijk zijn aan of dicht bij het aantal bezette blokken in de tabel. Voor een standaard blokgrootte van 8 kilobyte kan worden verwacht dat er ongeveer duizend smalle numerieke waarden per blok passen, dus het aantal blokken, en als gevolg daarvan de clustering factor zal enkele tientallen zijn..

Bij welke waarden van N zal het volgende script succesvol worden uitgevoerd in een normale database met standaardinstellingen?

create table t (
    a varchar2(N char),
    b varchar2(N char),
    c varchar2(N char),
    d varchar2(N char));
 
create index t_i on t (a, b, c, d);

• 100
• 200
• 400
• 800
• 1600
• 3200
• 6400

AntwoordVolgens de documentatie van Oracle (geciteerd uit 11.2):

Logische Database Limieten

Item
Type Limiet
Limiet Waarde

Indexen
Totale grootte van geïndexeerde kolom
75% van de databaseblokgrootte minus wat overhead

Het totale formaat van de geïndexeerde kolommen mag niet meer dan 6 KB bedragen. Wat verder gebeurt, hangt af van de gekozen datacodering. Voor de codering AL32UTF8 kan één teken maximaal 4 bytes innemen, waardoor in het slechtste geval ongeveer 1500 tekens in 6 kilobyte passen. Om deze reden zal Oracle het creëren van een index verbieden bij N = 400 (wanneer de sleutel in het slechtste geval 1600 tekens * 4 bytes + de lengte van rowid zal zijn), terwijl bij N = 200 (en minder) het creëren van een index probleemloos zal verlopen.

De INSERT-opdracht met de hint APPEND is bedoeld voor het laden van gegevens in de directe modus. Wat gebeurt er als deze wordt toegepast op een tabel met een trigger?

• Gegevens worden in directe modus geladen, de trigger zal worden geactiveerd zoals het hoort.
• Gegevens worden in directe modus geladen, maar de trigger zal niet worden uitgevoerd.
• Gegevens worden in de conventionele modus geladen, de trigger zal worden geactiveerd zoals het hoort.
• Gegevens worden in de conventionele modus geladen, maar de trigger zal niet worden uitgevoerd.
• Gegevens worden niet geladen, er zal een fout worden vastgelegd.

AntwoordIn principe is dit meer een vraag van logica. Voor het vinden van het juiste antwoord zou ik het volgende denkmodel voorstellen:

1. Inserting in directe modus gebeurt door het direct genereren van een gegevensblok, om de SQL-engine heen, wat hoge snelheid biedt. Het is daardoor zeer moeilijk, indien überhaupt mogelijk, om de trigger uit te voeren, en het heeft geen zin omdat het de insert alsnog aanzienlijk zou vertragen.
2. Het niet uitvoeren van de trigger zou ertoe leiden dat, bij gelijke gegevens in de tabel, de staat van de database als geheel (andere tabellen) afhankelijk zou zijn van de specifieke modus waarin deze gegevens zijn ingevoegd. Dit zou duidelijk de dataconsistentie aantasten en kan niet worden toegepast als oplossing in productie.
3. De onmogelijkheid om de gevraagde operatie uit te voeren, wordt in het algemeen opgevat als een fout. Maar hier moet men zich herinneren dat APPEND een hint is, en de algemene logica van hints is dat ze worden in overweging genomen als dat mogelijk is; als dit niet het geval is, wordt de operator uitgevoerd zonder rekening te houden met de hint.

Dus, het verwachte antwoord is: gegevens worden geladen in de normale (SQL) modus, de trigger zal worden geactiveerd.

Volgens de documentatie van Oracle (citaat uit 8.04):

Overtredingen van de beperkingen zullen ervoor zorgen dat de verklaring sequentieel wordt uitgevoerd, met gebruik van het conventionele invoerpad, zonder waarschuwingen of foutmeldingen. Een uitzondering is de beperking op verklaringen die dezelfde tabel meer dan eens binnen een transactie benaderen, wat foutmeldingen kan veroorzaken.
Bijvoorbeeld, als er triggers of referentiële integriteit op de tabel aanwezig zijn, dan zal de APPEND-hint worden genegeerd wanneer je probeert een directe INSERT (serieel of parallel) te gebruiken, net als de PARALLEL-hint of clausule, indien aanwezig.

Wat gebeurt er bij het uitvoeren van het volgende script?

create table t(i integer not null primary key, j integer references t);
 
create trigger t_a_i after insert on t for each row
declare
    pragma autonomous_transaction;
begin
    insert into t values (:new.i + 1, :new.i);
    commit;
end;
/
 
insert into t values (1, null);

• Succesvolle uitvoering
• Fout door een syntaxisfout
• Fout gerelateerd aan de ongeldigheid van de autonome transactie
• Fout gerelateerd aan het overschrijden van de maximale diepte van aanroepen
• Fout gerelateerd aan de schending van de buitenlandse sleutel
• Fout gerelateerd aan vergrendelingen

AntwoordDe tabel en trigger worden volledig correct aangemaakt en deze operatie zou geen problemen moeten veroorzaken. Autonome transacties in de trigger zijn ook toegestaan, anders zou het bijvoorbeeld onmogelijk zijn om te loggen.

Na de invoer van de eerste rij zou de succesvolle activering van de trigger leiden tot de invoer van de tweede rij, waardoor de trigger opnieuw zou afgaan, de derde rij zou invoegen en ga zo maar door totdat de instructie zou falen vanwege het overschrijden van de maximale diepte van aanroepen. Er is echter nog een subtiele kwestie. Op het moment dat de trigger voor de eerste ingevoerde record wordt uitgevoerd, is de commit nog niet uitgevoerd. Daarom probeert de trigger, die in een autonome transactie werkt, een rij in de tabel in te voegen die een verwijzing heeft naar een nog niet gecommitteerde record via de buitenlandse sleutel. Dit leidt tot een afwachting (de autonome transactie wacht op de commit van de hoofdtransactie om te begrijpen of de gegevens kunnen worden ingevoegd) en tegelijkertijd wacht de hoofdtransactie op de commit van de autonome transactie om de werkzaamheden na de trigger voort te zetten. Er ontstaat een deadlock en als gevolg daarvan wordt de autonome transactie beëindigd vanwege vergrendelingsproblemen..

Alleen geregistreerde gebruikers kunnen deelnemen aan de enquête. Log in, alstublieft.

Was het moeilijk?

• Als twee vingers, heb alles meteen goed opgelost.

• Niet echt, ik vergiste me in een paar vragen.

• Ik had de helft goed opgelost.

• Ik heb twee keer het juiste antwoord geraden!

• Ik zal in de reacties schrijven

14 gebruikers stemden, 10 gebruikers onthielden zich.

Bron: habr.com