I kølvandet på Highload++ Siberia 2019 - 8 opgaver om Oracle

Hej!

Den 24-25 juni fandt konferencen Highload++ Siberia 2019 sted i Novosibirsk. Vores team var også der. præsentationen «Oracle Container Databases (CDB/PDB) og deres praktiske anvendelse til softwareudvikling», vi vil dele den skriftlige version lidt senere. Det var fantastisk, tak. olegbunin for organiseringen, og til alle der kom.

I dette indlæg vil vi gerne dele de opgaver, som vi havde på vores stand, så du kan teste din viden om Oracle. Under indlægget - 8 opgaver, svarmuligheder og forklaring.

Hvad er den maksimale værdi for sekvensen, vi ser som resultat af kørsel af det følgende 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
• Ingen, der vil opstå en fejl.

SvarIfølge Oracle-dokumentationen (citeret fra 8.1.6):
Inden for en enkelt SQL-sætning vil Oracle kun øge sekvensen én gang pr. række. Hvis en sætning indeholder mere end én reference til NEXTVAL for en sekvens, øger Oracle sekvensen én gang og returnerer den samme værdi for alle forekomster af NEXTVAL. Hvis en sætning indeholder referencer til både CURRVAL og NEXTVAL, øger Oracle sekvensen og returnerer den samme værdi for både CURRVAL og NEXTVAL, uanset deres rækkefølge i sætningen.

Derfor vil den maksimale værdi svare til antallet af rækker, altså 5..

Hvor mange rækker vil der være i tabellen som resultat af kørsel af det følgende 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

SvarIfølge Oracle-dokumentationen (citeret fra 11.2):

Før enhver SQL-erklæring udføres, markerer Oracle en implicit gemmepunkt (ikke tilgængeligt for dig). Hvis erklæringen mislykkes, rulles den automatisk tilbage, og en gældende fejlkode returneres til SQLCODE i SQLCA. For eksempel, hvis en INSERT-erklæring forårsager en fejl ved at forsøge at indsætte en duplikeret værdi i et unikt indeks, rulles erklæringen tilbage.

Kaldet til den lagrede procedure fra klienten betragtes også og behandles som en single statement. Derfor afsluttes det første kald til proceduren tilfredsstillende og indsætter tre rækker; det andet kald til proceduren afsluttes med en fejl og ruller den fjerde række tilbage, som den nåede at indsætte; det tredje kald mislykkes med en fejl, og der er tre rækker i tabellen..

Hvor mange rækker vil der være i tabellen som resultat af kørsel af det følgende 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('Ups!');
end;
/

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

SvarIfølge Oracle-dokumentationen (citeret fra 11.2):

En checkbegrænsning giver dig mulighed for at angive en betingelse, som hver række i tabellen skal opfylde. For at opfylde begrænsningen skal hver række i tabellen gøre betingelsen enten SAND eller ukendt (på grund af en null). Når Oracle evaluerer en checkbegrænsningsbetingelse for en bestemt række, henviser eventuelle kolonnenavne i betingelsen til kolonneværdierne i den række.

Dermed vil null-værdien bestå kontrollen, og den anonyme blok vil blive udført med succes, indtil der prøves at indsætte værdien 3. Efter dette vil blokken for fejlhåndtering neutralisere undtagelsen, der ikke vil føre til noget rollback, og der vil forblive fire rækker i tabellen med værdierne 1, null, 2 og igen null.

Hvilke værdi-par vil optage samme plads i blokken?

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 og X
• B og Y
• C og K
• C og Z
• K og Z
• I og J
• J og X
• Alle de nævnte

SvarLad os se på uddrag fra dokumentationen (12.1.0.2) om lagring af forskellige datatyper i Oracle.

CHAR Datatype
CHAR-datatypen angiver en fast længde tegnstreng i database tegnsættet. Du specificerer database tegnsættet, når du opretter din database. Oracle sikrer, at alle værdier, der opbevares i en CHAR-kolonne, har den længde, som er angivet af størrelsen i det valgte længdesemantik. Hvis du indsætter en værdi, der er kortere end kolonnens længde, så vil Oracle tilføje mellemrum til værdien for at nå kolonnens længde.

VARCHAR2 Datatype
VARCHAR2-datatypen angiver en variabel længde tegnstreng i database tegnsættet. Du specificerer database tegnsættet, når du opretter din database. Oracle opbevarer en tegnværdi i en VARCHAR2-kolonne nøjagtigt som du specificerer den, uden nogen mellemrumspadning, forudsat at værdien ikke overstiger kolonnens længde.

NUMBER Datatype
NUMBER-datatypen opbevarer nul såvel som positive og negative faste tal med absolutte værdier fra 1.0 x 10-130 til men ikke inklusive 1.0 x 10126. Hvis du angiver et aritmetisk udtryk, hvis værdi har en absolut værdi større end eller lig med 1.0 x 10126, returnerer Oracle en fejl. Hver NUMBER værdi kræver fra 1 til 22 byte. Tag dette i betragtning, kan kolonnens størrelse i byte for et givet numerisk dataværdi NUMBER(p), hvor p er præcisionen af en given værdi, beregnes ved hjælp af følgende formel: ROUND((length(p)+s)/2))+1 hvor s er lig med nul, hvis tallet er positivt, og s er lig med 1, hvis tallet er negativt.

Desuden lad os tage et uddrag fra dokumentationen om opbevaring af null-værdier.

En null er fraværet af en værdi i en kolonne. Nuller indikerer manglende, ukendt eller ikke-anvendelige data. Nuller opbevares i databasen, hvis de falder mellem kolonner med dataværdier. I disse tilfælde kræver de 1 byte til at opbevare længden af kolonnen (nul). Efterfølgende nuller i en række kræver ingen opbevaring, fordi en ny rækkeheader signalerer, at de resterende kolonner i den forrige række er null. For eksempel, hvis de sidste tre kolonner i en tabel er null, så opbevares der ingen data for disse kolonner.

Baseret på disse data opbygger vi vores ræsonnering. Antager, at databasen bruger kodningen AL32UTF8. I denne kodning vil russiske bogstaver optage 2 byte.

1) A og X, værdien i feltet a ‘Y’ optager 1 byte, værdien i feltet x ‘D’ – 2 byte
2) B og Y, ‘Vasya’ i b vil blive udvidet med mellemrum op til 10 tegn og vil tage 14 byte, ‘Vasya’ i d – vil tage 8 byte.
3) C og K. Begge felter har værdien NULL, efter dem er der meningsfulde felter, derfor tager de hver 1 byte.
4) C og Z. Begge felter har værdien NULL, men felt Z – er det sidste i tabellen, derfor optager det ikke plads (0 byte). Felt C tager 1 byte.
5) K og Z. Ligner den foregående situation. Værdien i felt K tager 1 byte, i Z – 0.
6) I og J. Ifølge dokumentationen vil begge værdier tage 2 byte. Længden beregnes efter formlen fra dokumentationen: round((1 + 0) / 2) + 1 = 1 + 1 = 2.
7) J og X. Værdien i felt J vil tage 2 byte, værdien i felt X vil tage 2 byte.

I alt, de korrekte muligheder: C og K, I og J, J og X.

Hvad vil clustering factor omtrent være for indekset T_I?

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

• Omkring dages
• Omkring hundrede
• Omkring tusinde
• Omkring titusinde

SvarIfølge dokumentationen fra Oracle (citeret fra 12.1):

For et B-træ indeks måler indeks clustering factor den fysiske gruppering af rækker i forhold til en indeksværdi.

Indeks clustering factor hjælper optimeringsprogrammet med at beslutte, om et indeks scan eller fuld tabel scan er mere effektivt for visse forespørgsler. En lav clustering factor indikerer et effektivt indeks scan.

En clustering factor, der ligger tæt på antallet af blokke i en tabel, indikerer, at rækkerne er fysisk ordnet i tabelblokke efter indeksnøglen. Hvis databasen udfører en fuld tabel scan, vil databasen typisk hente rækkerne, som de er gemt på disk, sorteret efter indeksnøglen. En clustering factor, der ligger tæt på antallet af rækker, indikerer, at rækkerne er spredt tilfældigt over databasedelene i forhold til indeksnøglen. Hvis databasen udfører en fuld tabel scan, vil databasen ikke hente rækker i nogen sorteret rækkefølge efter denne indeksnøgle.

I dette tilfælde er dataene perfekt sorteret, derfor vil clustering factor være lig med eller tæt på antallet af optagede blokke i tabellen. For en standard blokstørrelse på 8 kilobyte kan man forvente, at omkring tusinde smalle number værdier passer i én blok, derfor vil antallet af blokke og som følge deraf clustering factor være omkring dages.

Ved hvilke værdier N vil følgende script blive udført med succes i en almindelig database med standardindstillinger?

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

SvarIfølge Oracle-dokumentationen (citeret fra 11.2):

Logiske databasegrænser

Emne
Type af grænse
Grænseværdi

Indekser
Total størrelse af indext kolonne
75% af databaseblokstørrelsen minus lidt overhead

Således må den samlede størrelse af de indekserede kolonner ikke overstige 6 KB. Det næste afhænger af den valgte databasekodning. For AL32UTF8-kodningen kan et tegn maksimalt tage 4 byte, så i 6 kilobyte kan der i værste fald være omkring 1500 tegn. Derfor vil Oracle forbyde oprettelse af indeks ved N = 400 (når nøglens længde i værste tilfælde vil være 1600 tegn * 4 byte + længden af rowid), mens ved N = 200 (eller mindre) vil oprettelsen af indeks fungere uden problemer.

INSERT-operatoren med hintet APPEND er beregnet til at indlæse data i direktivmode. Hvad vil der ske, hvis den anvendes på en tabel med en trigger?

• Data vil blive indlæst i direktivmode, og triggeren vil fungere som den skal.
• Data vil blive indlæst i direktivmode, men triggeren vil ikke blive udført.
• Data vil blive indlæst i konventionel mode, og triggeren vil fungere som den skal.
• Data vil blive indlæst i konventionel mode, men triggeren vil ikke blive udført.
• Data vil ikke blive indlæst, der vil opstå en fejl.

SvarI princippet er dette spørgsmål mere logisk. For at finde det korrekte svar vil jeg foreslå følgende ræsonneringsmodel:

1. Indsættelse i direktivmode udføres ved direkte oprettelse af datablokke, uden om SQL-motoren, hvilket sikrer høj hastighed. Derfor er det svært at sikre, at triggeren bliver udført, hvis det overhovedet er muligt, og det giver ikke mening, da det stadig vil bremse indsættelsen betydeligt.
2. Manglende udførelse af triggeren vil medføre, at tilstanden af databasen samlet set (andre tabeller) vil afhænge af, hvilken mode der blev brugt til indsættelse af disse data. Dette vil åbenlyst ødelægge dataintegriteten og kan ikke anvendes som en løsning i produktion.
3. Manglende evne til at udføre den anmodede operation betragtes generelt som en fejl. Men her skal man huske på, at APPEND er et hint, og den generelle logik for hints er, at de tages i betragtning, hvis det er muligt; hvis ikke, udføres operationen uden at tage hintet i betragtning.

Således er det forventede svar - data vil blive indlæst i normal (SQL) mode, og triggeren vil fungere.

Ifølge Oracle-dokumentationen (citeret fra 8.04):

Overtrædelser af restriktionerne vil få erklæringen til at blive udført synkront, ved at bruge den konventionelle indsætningssti, uden advarsler eller fejlmeddelelser. En undtagelse er restriktionen om erklæringer, der får adgang til den samme tabel mere end én gang i en transaktion, hvilket kan forårsage fejlmeddelelser.
For example, if there are triggers or referential integrity on the table, the APPEND hint will be ignored when attempting to use direct-load INSERT (serial or parallel), as well as the PARALLEL hint or clause, if present.

What will happen when executing the following 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);

• Successful execution
• Failure due to a syntax error
• Error related to the invalidity of the autonomous transaction
• Error related to exceeding the maximum call nesting
• Error related to foreign key violation
• Error related to locks

SvarThe table and trigger are created correctly and this operation should not lead to problems. Autonomous transactions in triggers are also allowed, otherwise logging, for example, would be impossible.

After inserting the first row, the successful trigger firing would lead to the insertion of the second row, which would again trigger the insertion of a third row, and so on until the statement fails due to exceeding the maximum nesting depth. However, there is another subtle point. At the moment the trigger is executed for the first inserted record, the commit has not yet been performed. Therefore, the trigger running in an autonomous transaction attempts to insert a row into the table that references a not yet committed record via a foreign key. This results in a wait (the autonomous transaction waits for the main to commit to determine if data can be inserted) and at the same time the main transaction waits for the autonomous to commit in order to continue its work after the trigger. A deadlock occurs and as a result, the autonomous transaction is terminated due to locks.

Kun registrerede brugere kan deltage i undersøgelsen. Log ind, please.

Was it difficult?

• Like two fingers, I solved everything correctly right away.

• Not really, I made a couple of mistakes.

• I solved half correctly.

• I guessed the answer twice!

• I will write in the comments

14 users voted. 10 users abstained.

Kilde: habr.com